sql server 数据库模型备份恢复总结备份脚本

2021-11-15 09:20:03

事务日志是可以基于时间点恢复的，必须在full或bulk_logged模式下

Alter database [DBName] set recover bulk_logged

, then the following operation will not be logged:
*SELECT INTO
*BULK COPY and Bulk Copy Program (BCP)
*CREATE INDEX
*特定文字操作

差异备份的数据文件不和数据备份的文件用一个文件，尽管可以
每一种备份模式下，备份的同时要备份master和msdb数据库
数据备份和清空日志没有关系，但清空日志要发生在事务日志备份之后，在这个之间

模式设置：

alter database CACDB_S1000 set recovery bulk_logged

数据备份：

backup database CACDB_S1000 to disk='E:\backup\data\CACDB_S1000_200801031245.data'

差异备份：

backup database CACDB_S1000 to disk=' E:\backup\diff\CACDB_S1000_200801031245.diff' with DIFFERENTIAL

清空日志：

DUMP TRANSACTION CACDB_S1000 WITH NO_LOG
BACKUP LOG CACDB_S1000 WITH NO_LOG
DBCC SHRINKDATABASE (CACDB_S1000)

事务日志备份：

BACKUP LOG CACDB_S1000 to disk = ' E:\backup\log\CACDB_S1000_200801031245.log'

还原：

RESTORE DATABASE CACDB_S1000 FROM DISK = 'E:\backup\data\CACDB_S1000_200801031245.data' with NORECOVERY
RESTORE LOG CACDB_S1000 from disk = ' E:\backup\log\CACDB_S1000_200801031250.log'

备份脚本：

declare @sql varchar(8000), @name varchar(255), @type varchar(255), @sqlT nvarchar(4000)

declare x cursor for select name from master.dbo.sysdatabases
open x

fetch next from x into @name
while(@@fetch_status = 0)
begin
        if(@name <> 'tempdb')
        begin
                print @name
                set @sql = 'backup database '+@name+' to disk=''D:\backup_20080421\'
                set @sql = @sql + @name + '_20080421117.data'''
                exec(@sql)
                set @sqlT = 'SELECT DATABASEPROPERTYEX('''+@name+''',''Recovery'')'
                exec sp_executesql @sqlT,N'@type varchar(255) out',@type out
                if(@type <> 'SIMPLE')
                begin
                        set @sql = 'backup log '+@name+' to disk=''D:\backup_20080421\'
                        set @sql = @sql + @name + '_20080421117.log'''
                        exec(@sql)
                end
        end
        fetch next from x into @name
end

close x
deallocate x

备份再还原时遇到设备激活错误之类的问题，解决方案：
(原因，同名数据库的文件逻辑名不一样)
sql server手动创建的数据库，例dbtest，会带上_Data，结果文件逻辑名为dbtest_Data
程序自动创建的，如果照sql server手册写，文件逻辑名为dbtest_dat
手动创建时，没有指定参数，文件逻辑名为dbtest

这样备份后再还原时，就会出错，因为'被还原的库的文件逻辑名'与'备份文件中的文件逻辑名'不对应

用

RESTORE FILELISTONLY

来显示文件逻辑名和物理文件名的对应关系

用

alter database 数据库名
modify file (
name=逻辑名
,newname=新逻辑名
)

来改变文件逻辑名

如果遇到文件路径的问题，可以restore database 的时候，带上with move/,move参数

码农公寓

相关文章