copy database wizard

[ykfc]

I am new to SQL server 2000.

After installing SQL 2000 (eval copy), my console tree shows only one SQL server under the SQL Server Group.

I build some database using the Enterprise manager. Now I want to create a copy of the database I created before proceeding.

I read an article here (an answer to a post) describing how to make a copy of a database. I started using the copy database wizard.

But the system complains the source and destination server cannot be the same. Well, I did enter the same server name for source and destination. But what else can I do. I do not know how to create two server names within the same machine.

Thanks

 

[jgoodman00]

If you need to have two working copies with different names:
-Detach the DB.
-Make a copy of the data & log files.
-Attach the original DB.
-Attach the copy DB specifying a different name.

If you just want to keep a copy:
-Backup the DB. You can restore at any stage if required.

James Goodman MCSE, MCDBA

http://www.j.goodman.dsl.pipex.com

 

[ykfc]

Good explanation for the beginners. Thanks James.

Here is my experience to share for beginners:

1) "Copy database Wizard": I will not use it until I need copying my database to another server.

2) How do I backup database
Just use the "backup database" function.
I created a backup device and gave it a name. This is the name I need to specify when doing the backup. Later I found the name becomes Windows filename in the hard drive. It has no file extension.
Then I played around my database, messed up something. Later I selected the "restore database" function, specifying the backup name I made up. The system restores my database and removes the mess.

3) How do I create a duplicate of a database TEST?
step a.
It is good to note down the file location of TEST by right-click on the database and select properties. Suppose the files were testData.mdf (I might have created log files for this database with extension .ldf)

step b.
Do a "backup database" so that I have the latest database status of TEST. Note down the name of the backup I specify, say it is bakupToday. This is only one file, as mentioned above.

step c.
Detach database TEST

step d.
Use "restore database" function but specify a new database name, say TESTCOPY. Chose my backup in step b. I can verify it from the parameters window. Under "Restore From", it should show "bakupToday"

step e.
now you have a new database called TESTCOPY

step f.
Proceed to re-instate TEST.
Select "Attach database". The system asks me to locate either the .mdf or .ldf file for TEST. Because I noted it down in step a, it is easy. I have my TEST back.