MSSQL Server 备份数据库脚本

备份数据库脚本 , 实现如下脚本

1. 定时备份

2.  平日以N开头, 周一以W开头, 每月1号以M开头, 每年1月1日以Y开头,  (保留最近7天, 4周, 12月和10年的备份)

3. 每周一 还原备份到 test库, 并将密码改为123

declare @bakfile varchar(100) , @db varchar(100), @pre varchar(10) ,@today datetime
set @db = '数据库名称'
set @bakfile = convert(varchar(20), getdate(),120)
set @bakfile = replace(replace(replace(@bakfile,'-',''),' ',''),':','')

set @today =  GETDATE()

set datefirst 1
set @pre = case 
    when  day(@today)=1 and MONTH(@today)=1 then 'Y'
    when  day(@today)=1  then 'M'
    when  datepart(weekday,@today) =1 then 'W'
    else 'N' end 

set  @bakfile = @pre + '_' + @db + '_' +@bakfile + '.bak'


exec(' BACKUP DATABASE 正式库 TO  DISK = N''D:\自动备份\'+@bakfile+''' WITH NOFORMAT, INIT,   COMPRESSION,  STATS = 10')

if datepart(weekday,@today) = 1
begin
    -- 还原数据库到 Test
    ALTER DATABASE [test] SET SINGLE_USER WITH
    ROLLBACK IMMEDIATE
    declare @file nvarchar(500)
    set @file = N'D:\自动备份\'+@bakfile

    RESTORE DATABASE [test] FROM  DISK = @file WITH  FILE = 1,  
    MOVE N'ESM2003_Data' TO N'D:\sqlData\test.mdf',  
    MOVE N'ESM2003_Log' TO N'D:\sqlData\test_log.LDF',  
    NOUNLOAD,  REPLACE,  STATS = 5

    ALTER DATABASE [test] SET MULTI_USER

    update [test].dbo.bas_user set use_pws = '123'
end