Copy database from a live server to a test server 1

[hmartineau]

We are setting up a test environment to replicate one of our client's environment. They have a SQL 2000 database (standard edition SP4) running on a windows 2003 server (std edition).

How can i copy the live database to a test server with minimal interruptions to the live environment (ideally no interruptions at all). I am looking for an exact duplicate (same users, same security...etc)

thanks

 

[DrSql]

1.) Migrate users to the new sever. (There are several methods for this and its available in Google)
2.) Set database Recovery mode to FULL
3.) Backup database on old server
4.) Restore database on to new server
5.) Stop all traffic
6.) Backup the Tlog
7.) Restore the Tlog
Step 6 and 7 depends on the size trasnaction log, if its big then I would suggest backup/restore Tlog at a shorter window.

This is the best soultion available in 2000, if you goto 2005 or 2008 you can use mirroring and failover with limited outage.

Good Luck




Dr.Sql
Good Luck.

 

[SQLBill]

If you are going to do DrSQLs suggestion, you must use WITH NO RECOVERY when you do the Backup restore. Once you recover the database you will no longer be able to restore log backups.

If you need it to remain current, look into replication. Make the client database the publisher and your database the subscriber.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875