sqlserver远程备份和还原

xp_cmdshell需要通过sqlserver2005-->配置工具-->sqlserver外围应用配置器-->功能的外围应用配置器(把xp_cmdshell勾打开)全备sql脚本:

DECLARE @strPath NVARCHAR(200)
set @strPath = replace(replace(replace(CONVERT(varchar, getdate(), 120 ),'-',''),' ',''),':','')
set @strPath = REPLACE(@strPath, ':' , '.')
exec master..xp_cmdshell 'net use  \\172.16.3.35\bak ```111q01 /user:domain\ll'
set @strPath = '\\172.16.3.35\bak\test_' + @strPath + '.bak'
backup database test  to disk=@strPath
--BACKUP DATABASE test TO DISK = @strPath WITH NOINIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT

 

 

差异备份sql脚本

DECLARE @strPath NVARCHAR(200)
set @strPath = replace(replace(replace(CONVERT(varchar, getdate(), 120 ),'-',''),' ',''),':','')
set @strPath = REPLACE(@strPath, ':' , '.')
exec master..xp_cmdshell 'net use  \\172.16.3.35\bak ```111q01 /user:domain\ll'
set @strPath = '\\172.16.3.35\bak\test_' + @strPath + '_incr.bak'
backup database test  to disk=@strPath WITH DIFFERENTIAL
--BACKUP DATABASE test TO DISK = @strPath WITH NOINIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT

还原:

restore database test from disk='\\172.16.3.35\bak\test_20140102154316.bak' with norecovery
restore database test from disk='\\172.16.3.35\bak\test_20140102154408_incr.bak'

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24237320/viewspace-1066522/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24237320/viewspace-1066522/