By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,263 Members | 1,321 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,263 IT Pros & Developers. It's quick & easy.

Need a script to backup db on one server and restore that db to another server

P: 8
I need to what is the best way and how to create a script or procedure where I can create a backup of a database on one server(production) and restore that database onto another server(test). The database currently exists on both servers but I want to keep the data on test up to date with production server. Does anyone know the best way to do this and possibly how to do this.

Thanks
Oct 1 '09 #1
Share this Question
Share on Google+
4 Replies


ck9663
Expert 2.5K+
P: 2,878
One word: REPLICATION

Good luck!!!

--- CK
Oct 1 '09 #2

P: 8
@ck9663
I am probably gonna need a little more assistance then this. What do I do in Replication? Does replication build a sql backup script for the database on a prod server that I can restore into a database on a test server
Oct 1 '09 #3

ck9663
Expert 2.5K+
P: 2,878
It replicates your database into multiple subscribers. Depending on the setting, you may sync the subscribing database every x frequency.

If the DB is small, you can just backup your production DB, and restore it as a testDB.

--- CK
Oct 1 '09 #4

codegecko
Expert 100+
P: 533
In the old days, this kind of job would be a multi-step DTS (Data Transformation Services) transaction, where you would run a SQL script that backed up the database, then ran a shell command to FTP the backed-up database to the new server. On the receiving server, you'd have another DTS job to check for a new file received via FTP in a certain folder - once the file exists, you'd then run a SQL statement to restore it.

This functionality is now handled by SSIS (SQL Server Integration Services) from SQL Server 05 onwards and requires Workgroup Edition or above (won't work on Express). I'm not too familiar with it, but MSDN's comprehensive online reference guide should be a good place to start. Failing that, looking at replication (which you can find details on via the above link) would be the other option. Bear in mind your test server has to be Internet-facing for any such solution to not require manual intervention at some stage in the transfer process.

Hope this helps.

codegecko
Oct 1 '09 #5

Post your reply

Sign in to post your reply or Sign up for a free account.