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.
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.
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.
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.
@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
You go to SQL Agent and you create a job. In that job you add a step, a t-sql step. That step will have a script that calls the RESTORE DATABASE command. You'll have to write that script using the RESTORE command I linked above. You then add a schedule to that job. Here's the basics on how to create a job from the books online:
http://msdn.microsoft.com/en-us/library/ms190268.aspx. This is the books online reference for scheduling a job:
http://msdn.microsoft.com/en-us/library/ms191439.aspx I can't break it down further without doing the work for you, and that's not possible remotely like this. Plus, I would only do the work for you if I were getting paid. If some detail of everything you've been supplied isn't working, please post a new question and I, or someone, will help out with that detail.
@Grant Fritchey - I stated restoring on a test/dev machine..other and only for specific reasons. However, it always pays (for all the reasons you had stated above) to restore it somewhere where it won't be in conflict with other work.
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.
Connect to the appropriate instance of the SQL Server Database Engine, and then in Object Explorer, select the server name to expand the server tree. Right-click Databases, and then select Restore Database. The Restore Database dialog box opens. Select the database to restore from the drop-down list.
Regards,
Will
18 People are following this question.
