create PROCEDURE SQLBackup AS
--建立映射 exec master..xp_cmdshell 'net use z: \\ip或者计算机名\d$\路径 登录密码 /user:ip或者计算机名\登录账号' --1、计算机名称cnshUser102，这里用ip也可以。用计算机名的好处是ip改了之后对这个不影响 --2、需要映射的路径d$\路径，这里是把该路径映射到本地的z盘
--定义变量 declare @bkfile nvarchar(1000) declare @i int declare @name nvarchar(1000) declare @cpfile nvarchar(1000)
--取当天的星期，星期天的值为1 set @i=datepart(weekday,getdate())
/*系统数据库每天备份*/
--master
set @bkfile=N'D:\SqlServerBackup\master\master_'+convert(nchar(8),getdate(),112)+'.BAK' backup database master to disk=@bkfile WITH INIT,name='master_full' set @bkfile=N'del D:\SqlServerBackup\master\master_'+convert(nchar(8),getdate()-7,112)+'.BAK' exec master.dbo.xp_cmdshell @bkfile,no_output set @cpfile=N'copy /Y D:\SqlServerBackup\master\master_'+convert(nchar(8),getdate(),112)+'.BAK z:\master' exec master.dbo.xp_cmdshell @cpfile,no_output set @cpfile=N'del z:\master\master_'+convert(nchar(8),getdate()-7,112)+'.BAK' exec master.dbo.xp_cmdshell @cpfile,no_output
--model
set @bkfile=N'D:\SqlServerBackup\model\model_'+convert(nchar(8),getdate(),112)+'.BAK' backup database model to disk=@bkfile WITH INIT,name='model_full' set @bkfile=N'del D:\SqlServerBackup\model\model_'+convert(nchar(8),getdate()-7,112)+'.BAK' exec master.dbo.xp_cmdshell @bkfile,no_output set @cpfile=N'copy /Y D:\SqlServerBackup\model\model_'+convert(nchar(8),getdate(),112)+'.BAK z:\model' exec master.dbo.xp_cmdshell @cpfile,no_output set @cpfile=N'del z:\model\model_'+convert(nchar(8),getdate()-7,112)+'.BAK' exec master.dbo.xp_cmdshell @cpfile,no_output
--msdb
set @bkfile=N'D:\SqlServerBackup\msdb\msdb_'+convert(nchar(8),getdate(),112)+'.BAK' backup database msdb to disk=@bkfile WITH INIT,name='msdb_rfull' set @bkfile=N'del D:\SqlServerBackup\msdb\msdb_'+convert(nchar(8),getdate()-7,112)+'.BAK' exec master.dbo.xp_cmdshell @bkfile,no_output set @cpfile=N'copy /Y D:\SqlServerBackup\msdb\msdb_'+convert(nchar(8),getdate(),112)+'.BAK z:\msdb' exec master.dbo.xp_cmdshell @cpfile,no_output set @cpfile=N'del z:\msdb\msdb_'+convert(nchar(8),getdate()-7,112)+'.BAK' exec master.dbo.xp_cmdshell @cpfile,no_output
/*应用数据库每周六完全备份，其余差异备份*/
--User
--完全备份
if @i=7 begin set @bkfile=N'D:\SqlServerBackup\User\User_'+convert(nchar(8),getdate(),112)+'.BAK' backup database User to disk=@bkfile WITH INIT,name='User_full' set @bkfile=N'del D:\SqlServerBackup\User\User_'+convert(nchar(8),getdate()-14,112)+'.BAK' exec master.dbo.xp_cmdshell @bkfile,no_output set @cpfile=N'copy /Y D:\SqlServerBackup\User\User_'+convert(nchar(8),getdate(),112)+'.BAK z:\User' exec master.dbo.xp_cmdshell @cpfile,no_output set @cpfile=N'del z:\User\User_'+convert(nchar(8),getdate()-7,112)+'.BAK' exec master.dbo.xp_cmdshell @cpfile,no_output end
--差异备份
else begin set @bkfile=N'D:\SqlServerBackup\User\User_'+convert(nchar(8),getdate(),112)+'.DIFF' set @name=N'User_diff_'+convert(nchar(1),@i) backup database User to disk=@bkfile WITH DIFFERENTIAL,INIT,name=@name set @bkfile=N'del D:\SqlServerBackup\User\User_'+convert(nchar(8),getdate()-14,112)+'.DIFF' exec master.dbo.xp_cmdshell @bkfile,no_output set @cpfile=N'copy /Y D:\SqlServerBackup\User\User_'+convert(nchar(8),getdate(),112)+'.DIFF z:\User' exec master.dbo.xp_cmdshell @cpfile,no_output set @cpfile=N'del z:\User\User_'+convert(nchar(8),getdate()-7,112)+'.DIFF' exec master.dbo.xp_cmdshell @cpfile,no_output end
--User_MGT
--完全备份
if @i=7 begin set @bkfile=N'D:\SqlServerBackup\User_MGT\User_MGT_'+convert(nchar(8),getdate(),112)+'.BAK' backup database User_MGT to disk=@bkfile WITH INIT,name='User_MGT_full' set @bkfile=N'del D:\SqlServerBackup\User_MGT\User_MGT_'+convert(nchar(8),getdate()-14,112)+'.BAK' exec master.dbo.xp_cmdshell @bkfile,no_output set @cpfile=N'copy /Y D:\SqlServerBackup\User_MGT\User_MGT_'+convert(nchar(8),getdate(),112)+'.BAK z:\User_MGT' exec master.dbo.xp_cmdshell @cpfile,no_output set @cpfile=N'del z:\User_MGT\User_MGT_'+convert(nchar(8),getdate()-7,112)+'.BAK' exec master.dbo.xp_cmdshell @cpfile,no_output end
--差异备份
else begin set @bkfile=N'D:\SqlServerBackup\User_MGT\User_MGT_'+convert(nchar(8),getdate(),112)+'.DIFF' set @name=N'User_MGT_diff_'+convert(nchar(1),@i) backup database User_MGT to disk=@bkfile WITH DIFFERENTIAL,INIT,name=@name set @bkfile=N'del D:\SqlServerBackup\User_MGT\User_MGT_'+convert(nchar(8),getdate()-14,112)+'.DIFF' exec master.dbo.xp_cmdshell @bkfile,no_output set @cpfile=N'copy /Y D:\SqlServerBackup\User_MGT\User_MGT_'+convert(nchar(8),getdate(),112)+'.DIFF z:\User_MGT' exec master.dbo.xp_cmdshell @cpfile,no_output set @cpfile=N'del z:\User_MGT\User_MGT_'+convert(nchar(8),getdate()-7,112)+'.DIFF' exec master.dbo.xp_cmdshell @cpfile,no_output end
--删除映射
exec master..xp_cmdshell 'net use z: /delete'
