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@]

ITPub博客

定时做数据库恢复测试sqlserver

最新文章