Solved

copy a database

Posted on 2011-03-25
7
224 Views
Last Modified: 2012-05-11
How do I copy a SQL server database?  This is SQL server 2000.
0
Comment
Question by:HLRosenberger
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

734 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