Automated DB Restore for SQL Server 2008 R2
We have a production system on Server1 running a SQL Server 2008 R2 database. Currently we have a maintenance plan to do full backups of selected databases nightly. We have a test server, Server2, which is also running SQL Server 2008 R2. We'd like some sort of script or way to copy the fresh backup from Server1 to Server2 then do a full overwrite of the database on Server2. This way each day Server2 has a restoration of Server1.
Any changes to Server2 DB would be overwritten that night with the new Server1 database.
I'm having challenges in being able to figure out how to pick the correct backup file name, copy it to Server2 and then restore it. I can do all of this manually, however that's a hassle.
Anyone have a script or a method to assist this? Would be greatly appreciated as I've beat my head against a wall for the past two days trying to figure it out without much results.
Thanks!
Any changes to Server2 DB would be overwritten that night with the new Server1 database.
I'm having challenges in being able to figure out how to pick the correct backup file name, copy it to Server2 and then restore it. I can do all of this manually, however that's a hassle.
Anyone have a script or a method to assist this? Would be greatly appreciated as I've beat my head against a wall for the past two days trying to figure it out without much results.
Thanks!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
That script is based on what MS does when you tell it to Verify the backup you just ran. It grabs the latest one to know which file to run the "RESTORE VERIFYONLY..." on. Everytime you run a full backup (unless you tell it to do a Copy Only Backup) it adds an entry in that table with the next Identity value. Is it possible the backups since last week are either Transaction Log, incremental or Copy Only backups?
ASKER
CGLuttrell
Got all of this working correctly. I must have been running the script initially on the wrong server or something. It's working so far.
Thanks for all of the help and quick responses!
Got all of this working correctly. I must have been running the script initially on the wrong server or something. It's working so far.
Thanks for all of the help and quick responses!
Your welcome, glad to have helped.
ASKER
Thanks for the response. Your first script to select the backup job name is returning results (well a single result), however the filename is from a week ago. I double checked there are backups for the past 3 days for sure that have been written by a maintenance plan. SO I'm not sure why it's returning results for a week ago rather than last nights backup.
As for the restore script, By manually copying over the backup file and changing the name manually within the script, it appears to working correctly. Thank you!