I want to transfer all of my data from my current sql server to a new one. I was wondering what the best way was to accomplish this task. I want to keep my primary keys, constraints, objects, etc. in tact. I have setup dts packages in the past to copy the production server to a test server. However, the packages have a tendency to fail on some of the dbs because of foreign keys. Does anyone have any suggestions on the best method to transfer all of my data from my current sql server to a new one? Are DTS packages the best solution? Thanks for any help you can provide.

Why not move the entire db then? Here are some ways that will be much
easier than using DTS.


http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scri...p?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server

http://www.support.microsoft.com/?id=274463 Copy DB
Wizard issues

--
Andrew J. Kelly SQL MVP


"mike" <anonymous (AT) discussions (DOT) microsoft.com> wrote

keep my primary keys, constraints, objects, etc. in tact. I have setup dts
packages in the past to copy the production server to a test server.
However, the packages have a tendency to fail on some of the dbs because of
foreign keys. Does anyone have any suggestions on the best method to
transfer all of my data from my current sql server to a new one? Are DTS
packages the best solution? Thanks for any help you can provide.

You might look into the DTS copy database wizard if you do not want to use
T-SQL jobs.

--
Ray Higdon MCSE, MCDBA, CCNA
---
"mike" <anonymous (AT) discussions (DOT) microsoft.com> wrote

keep my primary keys, constraints, objects, etc. in tact. I have setup dts
packages in the past to copy the production server to a test server.
However, the packages have a tendency to fail on some of the dbs because of
foreign keys. Does anyone have any suggestions on the best method to
transfer all of my data from my current sql server to a new one? Are DTS
packages the best solution? Thanks for any help you can provide.