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

[mldardy]

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

[ck9663]

One word: REPLICATION

Good luck!!!

--- CK

[mldardy]

@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

[ck9663]

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

[JamieHowarth0]

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