February 8, 2007 at 7:16 pm
Is there a way to restore a SQL Server backup each night? I need to reset a database back to its original state every night.
Thanks
February 8, 2007 at 7:31 pm
Yes, you can just setup a SQL Server Agent job to run the restore each night.
February 12, 2007 at 6:07 am
just to add to Michael Valentine Jones ' reply :
don't forget to throw everyone out of the database befor you try to restore it or you'll get an error "db in use".
e.g.
ALTER DATABASE yourdb Set RESTRICTED_USER , READ_ONLY WITH ROLLBACK IMMEDIATE
Restore database yourdb from yourBackupDevice
with MOVE N'data' TO N'E:\MSSQL\Data\yourdb.mdf'
, MOVE N'data_2' TO N'E:\MSSQL\Data\yourdb_2.ndf'
, MOVE N'log' TO N'D:\MSSQL\Log\yourdb_log.ldf'
, replace
, recovery
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 12, 2007 at 8:08 am
This is an even more effective way to get everyone out of the database and prevent them from reconnecting.
alter database MyDatabase set offline with rollback immediate Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply