Wednesday, July 16, 2008

Duplicating Databases in MS-SQL 2000 using the SQL Server Manager

When copying databases using MS-SQL Server 2000 Enterprise Manager, the safest option is to use the “Copy table(s) and view(s) from the source database”, but this abandons stored procedures, triggers and other more complex objects.

If you choose “Copy objects and data between SQL Server databases” using the defaults, you may run into fatal errors that cause the copy to fail. In true Microsoft fashion, the error messages are generally unhelpful. What I have gathered from Googling this problem, the user permissions often cause problems. Here is how to get around that problem:

Right click on the table in the SQL Server Manager tree and choose [Export]

cid:image001.png@01C8DC2F.AD2F9710

Choose the “Copy objects and data…” option

Click [Next]

cid:image002.png@01C8DC2E.43784160

Uncheck [Include all dependent objects]

Uncheck [Copy all objects]

Uncheck [Use default options]

cid:image003.png@01C8DC2E.43784160

Click [Select Objects]

Uncheck any objects you are sure that you didn’t use.

Click in the Objects: window and click [select all], then [check]

cid:image004.png@01C8DC2E.43784160

Click [ok]

Click [Options…]

Uncheck [Copy SQL Server logins…]

Uncheck [Copy object-level permissions]

cid:image005.png@01C8DC2E.43784160

Click [OK]

Click [Next]

cid:image006.png@01C8DC2E.43784160

Click [Next]

cid:image007.png@01C8DC2E.43784160

Click [Finish]

Autoincrement numbers are still lost, even with this method, so if you rely on that property, be prepared to do some leg work after the export is finished.

No comments:

Post a Comment