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

sql-server 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

Related questions

Limitations of SQL Server Express

My hosting provider (Rackspace) is offering a fully managed dedicated server with SQL Server Web version () installed. My company handles web development, and has about 20+ clients using ASP.Net + SQL Server 2005. I am thinking of cutting down costs by installing …

Read More
How to create jobs in SQL Server Express edition

Could anyone please explain to me how to create jobs in SQL Server Express edition?

Read More
c# 2008 SQL Server Express Connection String

I have a 2008 SQL Server Express installed on one of my machines and I'm attempting to establish a remote connection... when I use the MS SQL Server Management Studio I can log into the database without any problems at all (…

Read More