Automated Restore

Hi,

I have 2 databases, one called "ManagementDB" and the other called "ManagementDBYesterday".

Basically I get asked very frequently to look at yesterdays data for various reasons and I thought it'd be a good idea to always have yesterdays data to hand instead of restoring all the time.

I have tried DTS but this takes too long, I've also thought of replication however the database is modified too frequently for this to be easliy implemented.

Restoring from a backup is quite quick so I thought if I could somehow automate this, I'd have a readily available copy of yesterdays data to hand.

I have a mon-sun daily backup so I need to write some SQL script to restore from the correct backup to the "yesterday" database.

I've only used restore via the enterprise manager and I was wondering if anyone here could help.

kind regards,

Mike,You need to create a scheduled job that will execute something like this:

restore database ManagementDBYesterday
from disk = 'backup_location\ManagementDB.bak' with recovery,
move 'ManagementDB_Data' to 'data_location\ManagementDBYesterday_Data.ndf',
move 'ManagementDB_Primary' to 'data_location\ManagementDBYesterday_Primary.mdf',
move 'ManagementDB_Log' to 'log_location\ManagementDBYesterday_Log.ldf'
If you have different logical file names for each database, you may also want to do this for clarity:

alter database ManagementDBYesterday
modify file (name=ManagementDB_Primary, newname=ManagementDBYesterday_Primary)

alter database ManagementDBYesterday
modify file (name=ManagementDB_Data, newname=ManagementDBYesterday_Data)

alter database ManagementDBYesterday
modify file (name=ManagementDB_Log, newname=ManagementDBYesterday_Log)