August 13, 2009 at 1:43 am
Hi, I'm not a SQL Server dba by any stretch of the imagination. I know enough to use SQL Server to create databases, write stored procedures, create user functions etc - but that's about it.
How do you copy a complete database, data and everything, from one server to another? Not to create a backup - but to move the whole thing to a new development server.
Thanks for any help.
August 13, 2009 at 2:35 am
use the Copy Database Wizard in the enterprise manager
August 13, 2009 at 2:58 am
if you want to move the database completely (ie to remove it from server A and add it to server B) i personally would use the detatch/attach method.
August 13, 2009 at 3:12 am
Thanks for your replies.
I want to copy the database from Server A to Server B.
On Server A it is a Sql Server 2000 Database.
I manage it using SQL Server Enterprise Manager 2005 from my development machine.
On Server B it will become a Sql Server 2008 database.
Which machine should I be on to do the copying? Should I be on Server B and copy from Server A?
Or from A to B? Or can I copy from my development machine?
August 13, 2009 at 3:48 am
It doesnt matter what physical machine your on. connect to server A through EM/SSMS right click on the database, tasks -> detach.
move the mdf and ldf file(s) over from server A to server B just through windows explorer
Connect to server B using SSMS, right click databases -> Attach. Point it to your mdf file. Make sure you backup your database.
One note - if you want to put the database back on server A at some point make sure you keep a SQL 2000 backup file as you wont be able to use a SQL 2008 backup to restore.
August 13, 2009 at 6:52 am
Personally, being a paranoid type, I'd use backup & restore. Why not. It leaves the database in place on the first server in case something goes wrong while getting it onto the second server.
Otherwise, detach/attach is the way to go, just don't mess up copying the files around.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply