How can I clone an SQL Server database on the same server in SQL Server 2008 Express?

Question

Hi,

I have an MS SQL Server 2008 Express system which contains a database that I would like to 'copy and rename' (for testing purposes) but I am unaware of a simple way to achieve this.

I notice that in the R2 version of SQL Server there is a copy database wizard, but sadly I can't upgrade.

Thanks in advance

UPDATE The database in question is around a gig. I attempted to restore a backup of the database I want to copy into a new database, but with no luck.

Answer 1

If the database is not very large, you might look at the 'Script Database' commands in SQL Server Management Studio Express, which are in a context menu off the database item itself in the explorer.

You can choose what all to script; you want the objects and the data, of course. You will then save the entire script to a single file. Then you can use that file to re-create the database; just make sure the USE command at the top is set to the proper database.

Answer 2

You could try to detach the database, copy the files to new names at a command prompt, then attach both DBs.

In SQL:

   user master;
   go 
   EXEC sp_detach_db
          @dbname = N'OriginalDB';
   go

At Command prompt (I've simplified the file paths for the sake of this example):

copy c:\OriginalDB.mdf c:\NewDB.mdf
copy c:\OriginalDB.ldf c:\NewDB.ldf

In SQL again:

USE master;
GO
CREATE DATABASE OriginalDB
    ON (FILENAME = 'C:\OriginalDB.mdf'),
       (FILENAME = 'C:\OriginalDB.ldf')
    FOR ATTACH;
GO
CREATE DATABASE NewDB
    ON (FILENAME = 'C:\NewDB.mdf'),
       (FILENAME = 'C:\NewDB.ldf')
    FOR ATTACH;
GO

Answer 3

right click databases- tasks- copy database. follow the wizard and you're done.

Answer 4

It turns out that I had attempted to restore from a backup incorrectly.

Initially I created a new database and then attempted to restore the backup here. What I should have done, and what worked in the end, was to bring up the restore dialog and type the name of the new database in the destination field.

So, in short, restoring from a backup did the trick.

Thanks for all the feedback and suggestions guys