copy database to same server for test environment

[SidCharming]

What is being done wrong by using the wizard to copy one database within MS SQL server (live environment) to another database (different name) on the same server? It copies nearly everything fine minus the default value for bit fields, description naming and primay keys.

My reason for doing this is to test a new front end with dummy real data.

One of our consultants said to backup and restore, that is how he gets around this ordeal... but the twist to that statment is some name changing has to happen if going on the same MS SQL server.

Why would the wizard go so far to help you copy a database and then stop at the 99% finished where the last 1% of stuff causes grief when troubleshooting.



Sid from Minnesota

 

[ecobb]

There's no trick to restoring a backup. Whenever you go through the restore wizard in Enterprise Manager, it gives the "Restore Database As:" option on the first screen and you can name it whatever you want to. I restore copies of databases on the same server all of the time, just name it something different.



Hope This Helps!

Ecobb

"My work is a game, a very serious game." - M.C. Escher

 

[SidCharming]

So is copying a database on a same server done not with the copy wizard, but with backup and restore as different name?


Sid from Minnesota

 

[ecobb]

That's generally how I do it, but when I need a copy of the database I want all of the data in it as well. (for testing, bug fixes, program additions, etc...) If you want the data in the database, then I would definately restore as a different name. If you don't want the data, you may want to look into scripting the database and recreating a new one from that script. I really can't offer any comparison against the copy wizard because I've never used it.



Hope This Helps!

Ecobb

"My work is a game, a very serious game." - M.C. Escher

 

[SidCharming]

Okay, I can accept that.


Sid from Minnesota