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

Sergio picture Sergio · Sep 30, 2010 · Viewed 355.9k times · Source

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

Tomas Kubes picture Tomas Kubes · Oct 8, 2014
  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) restore dialog
  6. Select Device and add the backup file from step 3. add backup file
  7. Change destination to test database change destination
  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.) change location
  9. Check WITH REPLACE and WITH KEEP_REPLICATION with replace