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

Question

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.

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

1. Install Microsoft SQL Management Studio, which you can download for free from Microsoft's website:

   Version 2008

   Microsoft SQL Management Studio 2008 is part of SQL Server 2008 Express with Advanced Services

   Version 2012

   Click download button and check ENU\x64\SQLManagementStudio_x64_ENU.exe

   Version 2014

   Click download button and check MgmtStudio 64BIT\SQLManagementStudio_x64_ENU.exe

2. Open Microsoft SQL Management Studio.

3. Backup original database to .BAK file (db -> Task -> Backup).
4. Create empty database with new name (clone). Note comments below as this is optional.
5. Click to clone database and open restore dialog (see image)
6. Select Device and add the backup file from step 3.
7. Change destination to test database
8. Change location of database files, it must be different from the original. You can type directly into text box, just add postfix. (NOTE: Order is important. Select checkbox, then change the filenames.)
9. Check WITH REPLACE and WITH KEEP_REPLICATION