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.