December 10, 2008 at 11:47 am
I have a SQL 2005 server that I want to make a copy of an existing database that resides on that server. I also want to restore that same database to the same SQL server as the origional but I want to name it something different. Can someone send me an example or a link of what I need to do to accomplish this?
December 10, 2008 at 12:16 pm
If you can have the original database offline for a period:
1) detach the original database (right click the database in the object explorer and select Tasks -> Detach)
2) copy the mdf and ldf files
3) attach the original database from the original files (right click "Databases" in object explorer and select "Attach")
4) attach the new database from the copy files, instead of hitting OK hit the script button and change the database name to whatever you want
If you need to keep the original database online then you need to do a full backup, and then restore to a new database (right click "Databases" and select "Restore database"). Choose the original database in "from database" and it should allow you to select the backup. Rename the files in the options page.
December 10, 2008 at 2:17 pm
Backup the database as normal.
Restore the database, changing the name, and using a WITH MOVE for the data and log files so that they are in another directory (or under a different name)
December 10, 2008 at 3:27 pm
Nicholas is probably the better approach to follow here and you can also use the GUI to do the work for you. It will do the move commands as well as change the file names (which will be important since you are restoring it to the same server and probably don't want to overwrite your previous database files).
December 10, 2008 at 4:21 pm
Follow what Nicholas recommended. There is no reason to incur downtime on the database when a backup/restore process will accomplish the same thing, in a similar amount of time.
Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]
December 11, 2008 at 7:35 am
Everyone,
Thanks for the info....
Happy Holidays
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply