Solved

Automated DB Restore for SQL Server 2008 R2

Posted on 2013-01-22
5
312 Views
Last Modified: 2013-01-23
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!
0
Comment
Question by:dobbshelp
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 27

Accepted Solution

by:
Chris Luttrell earned 500 total points
ID: 38808210
To find the latest backup file name for your database try this:
SELECT msdb.dbo.backupset.name 
FROM msdb..backupset
WHERE msdb.dbo.backupset.backup_set_id = (select max(backup_set_id) from msdb..backupset where database_name=N'YourDatabaseName')

Open in new window


The restore scripts are pretty easy once you get the file to a known location on the destination server, but getting it copied over is tricky and will depend on what tools and permissions you have on each server and the network.  I always have to play the hand that is dealt when it comes to that.
The restore scripts look something like this:
ALTER DATABASE DatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

RESTORE DATABASE [DatabaseName] FROM  DISK = N'C:\Temp\DatabaseName_backup_2013_01_13_203002_3003367.bak' WITH  FILE = 1,  
MOVE N'DatabaseName' TO N'M:\Sql_Data\DatabaseName1.mdf',   -- If Needed
MOVE N'DatabaseName_log' TO N'L:\Sql_TLogs\DatabaseName1_1.ldf',    -- If Needed
NOUNLOAD,  REPLACE,  STATS = 10

ALTER DATABASE [DatabaseName] MODIFY FILE (NAME=N'DatabaseName', NEWNAME=N'DatabaseName')  -- If Needed

ALTER DATABASE [DatabaseName] MODIFY FILE (NAME=N'DatabaseName_log', NEWNAME=N'DatabaseName_log')  -- If Needed


ALTER DATABASE DatabaseName SET MULTI_USER;

Open in new window

0
 
LVL 3

Author Comment

by:dobbshelp
ID: 38811157
CGLuttrell

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!
0
 
LVL 27

Expert Comment

by:Chris Luttrell
ID: 38811418
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?
0
 
LVL 3

Author Comment

by:dobbshelp
ID: 38812054
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!
0
 
LVL 27

Expert Comment

by:Chris Luttrell
ID: 38812061
Your welcome, glad to have helped.
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question