Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
RyanSmith
Starting Member
8 Posts |
 Posted - 2008-01-16 : 13:41:51
|
| I have a database (actually several of them) on a production server. I want to be able to create an automatic process so every evening the production database is copied to a different server that runs staging sites.I know that this is possible through database mirroring, but I don't want the database to be constantly mirrored. I would prefer to have a script that ran every night that just make an exact copy over to the staging server.Is there any way to automate this, possibly through the copy database wizard? Does anyone have any good suggestions as to how I can make this work? If not, does anyone know of a good straight forward article on database mirroring that will get me started quick?Thanks.http://www.dynamicajax.com |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-01-16 : 13:54:37
|
| The simplest way to do it is to use a SQL Agent job to restore it onto the other server from your database backup.CODO ERGO SUM |
 |
|
|
RyanSmith
Starting Member
8 Posts |
Posted - 2008-01-16 : 14:07:16
|
| Thanks for the reply Michael. Do you have any links that explain how to setup this process?http://www.dynamicajax.com |
|
|
|
sqlsquirrel
Starting Member
21 Posts |
Posted - 2008-01-16 : 17:01:31
|
| Michael is correct... Have 2 steps in your SQL Agent backup job. The first step is backup the database. The second step does an RPC call to a restore job on the staging server. You will first need to setup the staging server as a linked server on the production server inorder to do the RPC call. Your RPC call will look like this: exec <server name>.msdb.dbo.sp_start_job 'name of restore job here'I am using this method for full, differential and log restores.Good Luck!Brett DavisSenior SQL Server DBAFor more helpful tips checkout my blog at: http://www.lockergnome.com/sqlsquirrel/ |
|
|
|
hey001us
Posting Yak Master
185 Posts |
|
|
montu
Yak Posting Veteran
60 Posts |
Posted - 2008-01-16 : 18:17:42
|
| or what i did is back up the database then schedule windows task to copy the backup file across the other server then restore database through SQL scheduled task. only thing u need to consider is time between copy and restore task. |
|
|
|
|
|
|
Automated Database Copy