September 3, 2008 at 6:14 am
In my SQL Server 2005, I have a database named DB1 populated with data. In the same server, I would like to create a new database called DB2 which is a duplicate of DB1 (with the same tables and data). How can I do this in SQL Server Management Studio. I tried Copy Database, Restore etc., but none of these works.
Appreciate any suggestions and thanks in advance.
sg2000
September 3, 2008 at 6:33 am
What errors/problems did you have?
The following will do what you want:
Back up the database and restore it with the new name
Detach the database, copy and rename the files and then attach them with the new name
Use the copy database wizard.
September 3, 2008 at 6:40 am
Copy db or a Restore should work. The restore would have to include renaming the physical files. It's easier to use TSQL, but if you want to use the GUI, click restore on the Databases folder. Type in a new name. Assuming a backup file, click on the From Device radio button and locate the backup file from the old database. Make sure the check box is selected next to the file once it's added to the Devices list. That should do it.
Of course, that assumes you backed up the other database.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
September 3, 2008 at 7:29 am
Thank you very much DNA and Grant Fritchey for the quick responses! Yes, I was able to use Copy Database to duplicate my DB. I found out that on the previous attempts, the errors were caused by the fact that the SQL Server Agent was not started. After starting this service manually, the Copy Database service completed without problems.
Thanks again!
sg2000
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply