博客

SQLServer数据库按年度归档

  • Post author:
  • Post category:其他


/*

维护计划每年2月1日执行

备份年度=当年年度-2

*/

declare @termYear varchar(4);

declare @bakSql varchar(1000);

declare @fromBase varchar(20);

declare @toBase varchar(20);

set @termYear = CONVERT(NUMERIC(4),(CONVERT(varchar(4), GETDATE(), 120))) – 2;

–SELECT @termYear;

set @fromBase = ‘dbname’;

set @toBase = @fromBase + @termYear;

set @bakSql = ‘create database ‘ + @toBase + ‘;

Backup Database ‘ + @fromBase + ‘ To DISK = ”D:\dbbak\’ + @toBase + ‘.bak”

RESTORE DATABASE ‘ + @toBase + ‘

FROM DISK = ”D:\dbbak\’ + @toBase + ‘.bak”

WITH REPLACE,

MOVE ”’ + @fromBase + ”’ TO ”D:\dbbak\’ + @toBase + ‘.mdf”,

MOVE ”’ + @fromBase + ‘_log” TO ”D:\dbbak\’ + @toBase + ‘.ldf”;’

exec (@bakSql);