Solved

copy a database

Posted on 2011-03-25
7
218 Views
Last Modified: 2012-05-11
How do I copy a SQL server database?  This is SQL server 2000.
0
Comment
Question by:HLRosenberger
7 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35215165
You can either:
1. Backup and restore or
2. Detach, copy data files and attach.
0
 
LVL 7

Expert Comment

by:lundnak
ID: 35215185
You want to copy the database from SQL 2000 to SQL 2008?

There are several methods.
Detach the database
Copy the database files to the SQL 2008 server.
Re-Attach the database.
Setup the logins on the SQL 2008 Server.
   http://support.microsoft.com/kb/246133
Change Compatibility mode to SQL 2008 for the database and Page Verify option to "CheckSum"


Another method is to backup the SQL 2000 database.
Restore the backup file to the SQL 2008 server
Complete the last three steps described above.
0
 
LVL 7

Expert Comment

by:lundnak
ID: 35215194
Here is a MS article describing the attach/detach method.
http://support.microsoft.com/kb/224071
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 1

Author Comment

by:HLRosenberger
ID: 35215313
No I want to make a copy of the 2000 database within the same server, with a different name of course.
0
 
LVL 5

Accepted Solution

by:
bitref earned 250 total points
ID: 35215387
The best way is to Backup the database at source then restore at distination.
The backup restore is the best because:
It saves storage space
Can be performed while database is online and running
0
 
LVL 7

Assisted Solution

by:lundnak
lundnak earned 250 total points
ID: 35215490
Either method will work.

If you use Attach/Detach then you will need to detach the source database..
Copy the files to new file names.
Reattach the source database
Attach the copied files with a new database name.

If you use Backup/Restore, you will restore the database with a new name.  SSMS restore wizard will walk you through it.  Select the backup file and then change database name.
0
 
LVL 1

Author Closing Comment

by:HLRosenberger
ID: 35216266
Thanks
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now