question

UnishSQL avatar image
UnishSQL asked

How to schedule restore of an database from production server to test server

I am searching for the information to schedule restore operation from one server to another server.I am not getting the relevant info about can any one help in solving it. **I want job like it will take full backup of a database and same database should be restored in another server** is it possible? can schedule this type of job.
sql-server-2008sql-server-2008-r2restorebackup-restoreschedule
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

WilliamD avatar image
WilliamD answered
I have done this in the past, so it is possible to do, you just have to create the steps manually. The steps I have done in the past are: 1. Make a job on the source server to backup the database to a fileshare/location available to both machines 2. Make a job to restore the backup to the target machine 3. Schedule the jobs so that they both run in the right order, with enough time to allow each job to finish Things to consider when doing this (in no particular order): 1. Do you have the right permissions on both machines? 2. Can you ensure that nobody is accessing the database on the target machine? This can block a restore without prior preparation? 3. Do you need informing about the steps finishing? 4. What will you need to do if a restore fails? 5. Is it time critical to do the restores? (Do other processes rely on this restore? How long does a restore take?) That should give you a decent start.
4 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Using SQL Agent
2 Likes 2 ·
they are no process rely on the that particular database.but how can i schedule a job to restore backup @WilliamD
0 Likes 0 ·
i know by using sql agent u can schedule backup but how can i schedule restore @SQLShark
0 Likes 0 ·
Not sure if it applied to you, but one these we have to do when restoring data is to anonymise it. This is for data protections of our clients.
0 Likes 0 ·
VishalhSingh avatar image
VishalhSingh answered
Well automatically scheduling will not be a good idea (guess so someone had something created or if test is also being used for some level of dev etc) however you are sure you want to do it the below should help you: 1) SSIS copy database task 2) TSQL for copying backup file from production and restoring to test (challenging as requires to execute xp_cmdshell unless you write a powershell script to copy it over the network. Also, killing connected user to bring database in exclusive mode for restoration on test server would be tricky. 3) Backup and restore job with parted to restore only to test (tricky) some advance and tricky and only if you want to go: Snapshot replication (don't consider it what it meant for but only to replicate the database. though configure carefully else will add load on production. Log Shipping: use for a specific time frame and change the database status afterwards. you need to do it each time though. I will add if I have missed out anything here.
8 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I have to differ with you. I think it's a great idea. There are a number of very valid reasons why you would want to automate restores of a database to a different server. First, and most important, it's a great way to validate that your backup is actually working. Another reason is to use the backup to do all the logical DBCC checks, reducing the load on your production machine. Maybe you want to give access to production data to some people without having them run queries on production. Maybe it's the first part in building a test database for further development and QA. This is not just a good idea, it's very much a standard tool in many DBAs toolbox.
5 Likes 5 ·
@WilliamD already has. You use SQL Agent. You schedule a RESTORE DATABASE command to restore the database. You can run it from the Agent on the server you want to restore to, or from another server that has appropriate permissions, but your answer is right there with @williamd. If you need to understand the RESTORE command, it's there in the books online: http://msdn.microsoft.com/en-us/library/ms186858.aspx
3 Likes 3 ·
I was trying this what have u said.but problem here is powershell scripting is nightmare for me @VishalhSingh
0 Likes 0 ·
can u please explain the way which can schedule restore of a database @Grant FRitchey<><>
0 Likes 0 ·
can u tell me how to schedule restore of a database @grant
0 Likes 0 ·
Show more comments
Håkan Winther avatar image
Håkan Winther answered
Red-Gate backup is the easiest solution for this. You configure the schedule of the backup, file copy and database restore inside the Backup GUI.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.