sqlserver2012数据库备份与还原

一、备份

 

(1)完整备份

--1:完整备份
declare @dbname varchar(100)
declare @sql nvarchar(max)
set @dbname = 'DataBaseName'
set @sql = '
--'+@dbname+'_full 
BACKUP DATABASE ['+@dbname+'] 
TO  DISK = ''D:\DBBackup\'+@dbname+'_full.bak''
WITH NOFORMAT, NOINIT,  NAME = '''+@dbname+'-完整数据库备份'', 
SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO'
print @sql

修改DataBaseName为你自己的数据库名称,运行之后会生成一个sql脚本,运行这个脚本即可。

(2)完整备份还原

--2:完整备份还原
declare @dbname varchar(100)
declare @sql nvarchar(max)
set @dbname = 'DataBaseName'
set @sql = '
--RESTORE '+@dbname+'_full
RESTORE DATABASE ['+@dbname+'] 
FROM  DISK = ''D:\DBBackup\'+@dbname+'_full.bak''  WITH  FILE = 1,  
MOVE N''DataBase_Name'' TO N''D:\DataBase\'+@dbname+'.mdf'',  
MOVE N''DataBase_Name_log'' TO N''D:\DataBase\'+@dbname+'_log.ldf'',  
NORECOVERY,  NOUNLOAD,  REPLACE,  STATS = 10
GO'
print @sql

与备份时一样,DataBaseName(数据库名称)要对应一致,同样也是会生成一个脚本,直接运行这个脚本就可以

 

(3)差异备份

--3:差异备份
declare @dbname varchar(100)
declare @sql nvarchar(max)
set @dbname = 'DataBaseName'
set @sql = '
--'+@dbname+'_diff
BACKUP DATABASE ['+@dbname+'] 
TO  DISK = N''D:\DBBackup\'+@dbname+'_diff.bak''
WITH  DIFFERENTIAL , NOFORMAT, NOINIT,  NAME = N'''+@dbname+'-差异数据库备份'', 
SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
'
print @sql

(4)差异备份还原

--4:差异备份还原
declare @dbname varchar(100)
declare @sql nvarchar(max)
set @dbname = 'DataBaseName'
set @sql = '
--RESTORE '+@dbname+'_full
RESTORE DATABASE ['+@dbname+'] 
FROM  DISK = ''D:\DBBackup\'+@dbname+'_diff.bak''  WITH  FILE = 1,  
NOUNLOAD,  STATS = 10
GO'
print @sql

操作方式与完整备份一样。

 

二、可能遇到的错误

(1)

问题:逻辑文件名称不对应

解决方法:查找该数据的逻辑文件名,通过下面的语句即可查出,再修改还原语句中的名称即可。

restore filelistonly from disk='F:\sqlserver\shlpgcmCM_full.bak'

参考链接:https://www.cnblogs.com/gaizai/archive/2011/03/30/2000175.html

https://www.cnblogs.com/ggll611928/p/6377545.html

已标记关键词 清除标记
相关推荐
©️2020 CSDN 皮肤主题: 数字50 设计师:CSDN官方博客 返回首页