定時做資料庫恢復測試sqlserver

CREATE DATABASE [test20090821]
ON PRIMARY
(NAME = N'test20090821_Data', FILENAME = N'f:sql_dbtest20090821_Data.MDF' , SIZE = 1, FILEGROWTH = 10%)
LOG ON (NAME = N'test20090821_Log', FILENAME = N'f:sql_logtest20090821_Log.LDF' , SIZE = 1, FILEGROWTH = 10%)

alter database test20090821 set OFFLINE

RESTORE DATABASE test20090821 FROM disk='F:SQL_DBVimicro_Ax30_SP4_FORMAL_02_2300'
WITH REPLACE, RECOVERY ,move 'Vimicro_Ax30_SP4_FORMAL_Data' to 'F:SQL_DBtest20090821_Data.MDF', move 'Vimicro_Ax30_SP4_FORMAL_Log'
to 'F:SQL_LOGtest20090821_Log.LDF'
go

exec sp_configure 'allow updates',1
reconfigure with override
go
update a set a.sid = b.sid
from "test20090821".dbo.sysusers a
join master.dbo.syslogins b on a.name = b.name
where (a.name = 'bmssa' or a.name = 'iss') and a.islogin = 1
go


update "test20090821".dbo.sysusers set password = 1 where name = 'bmssa'
go

exec sp_configure 'allow updates',0 reconfigure with override
go

上面的指令碼是自動恢復資料庫，如果把這個指令碼放入job作業中，既可以實現定時自動恢復。

（注：指令碼可以透過事件探察器跟蹤來生成）

[@more@]