Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Sqlbuddy
Starting Member
27 Posts |
 Posted - 2009-05-12 : 16:27:36
|
| Hi, I have around 150 databases on a SQL Server 2000 instance and I want to migrate all of them to the new SQL Server 2005 instance (CONSOLIDATION)? Can we use Copy database wizard method for this migration? If so which one is better? Detach and Attach method or the SMO method?I think it would be a tedious task to backup and restore all 150 databases?Could anyone help me with this? Thank you,YoursSQLBuddy. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-05-12 : 16:32:02
|
| You could use the copy database wizard, but it's not the best option for a migration. The best options are detach/attach or BACKUP/RESTORE. For many databases, you an easily script out your commands. Here's a simple example:SELECT 'BACKUP DATABASE ' + name + ' TO DISK = ''E:\Backup\' + name + '.bak'' WITH INIT'FROM sysdatabasesWHERE ...Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
 |
|
|
Sqlbuddy
Starting Member
27 Posts |
Posted - 2009-05-12 : 17:27:13
|
| Hi Tara, Thank you very much for your reply. But the problem is that I am not very good in scripting? Could you please tell me whether there will be any problems with the copy database wizard (Detach/Attach) method? Bcs I have to use this migration on the production servers. Thank you,YoursSQLBuddy |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Sqlbuddy
Starting Member
27 Posts |
Posted - 2009-05-12 : 18:14:39
|
| Hi Tara, Thank you very much for the reply.Yours,SQLBuddy |
|
|
|
|
|
|
Can we use Copy Database wizard for migration