11-13-07, 21:22 #1Member
- Join Date
- Dec 2001
Unanswered: Transfer a database from non-clustering to clustering env.
We have 2 env. : Testing and Production, both are running Windows 2003 Enterprise Server with SQL Server 2005. The difference is Testing is NOT running Windows cluster but Production do so, what is the best way to transfer a database from testing to production?
We have another systems that both testing and production are running on NON-cluster and we use backup/restore to transfer the database, can it apply in this case.
And I found that there are a tools called DTC, which can transfer all DB objects from one DB to another, is it a best way to transfer between non-cluster and cluster env.?
11-13-07, 23:25 #2Resident Curmudgeon
Provided Answers: 54
- Join Date
- Feb 2004
- In front of the computer
There are many ways to move a database from one Microsoft SQL Server to another.
Microsoft's first suggestion for moving a database always seems to be using the BACKUP and RESTORE commands. This is both simple and straightforward, and it is probably the least complicated way to achieve your goal.
If speed is an issue, there is a faster way using sp_detach_db and sp_attach_db, but this can get complicated in a clustered environment.
MS-DTC is not a good choice for moving databases in most situations.
11-14-07, 17:54 #3Registered User
- Join Date
- Dec 2002
Another choice would be to script the database in the test environment and then run the script to create the empty schema in production. You may need to make allowances for environmental differences (storage and logins primarily). You will also need to have another script handy to load base data into the database (lookup tables and such).
This approach often results in the "cleanest" starting point for a new database. It should force a careful review of all data entries and prompt you to remove obvious test artifacts. It is best if you have a development environment and a dedicated test environment. You practice your production deployment steps when you deploy to the test environment and validate that everything meets expectations.
You may want to investigate database comparison tools such as SQL Compare from RedGate. Once databases get into production for a while, you will need an automated process to compare two instances and quickly hone in on the schema differences.
Red Gate also has SQL Data compare which allows you to focus on data differences between two instances.
As a side note, I don't rep for Red Gate, but I have used their products for the last 5 years and I'm a happy user.
hmscottHave you hugged your backup today?