Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

copy a database

Posted on 2011-03-25
7
Medium Priority
?
229 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
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
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 1000 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 1000 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

824 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