SQLServer数据库按年度归档

/*
 维护计划每年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);