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

User · Answer

Using MS SQL Server 2012  you need to perform 3 basic steps    First  generate  sql file containing only the structure of the source DB   right click on the source DB and then Tasks then Generate Scripts follow the wizard and save the  sql file locally  Second  replace the source DB with the destination one in the  sql file   Right click on the destination file  select New Query and Ctrl-H or  Edit - Find and replace - Quick replace   Finally  populate with data   Right click on the destination DB  then select Tasks and Import Data Data source drop down set to   net framework data provider for SQL server    set the connection string text field under DATA ex  Data Source Mehdi SQLEXPRESS Initial Catalog db test User ID sa Password sqlrpwrd15 do the same with the destination check the table you want to transfer or check box besides  source       to check all of them    You are done

User · Answer

This is the script I use  A bit tricky but it works  Tested on SQL Server 2012   DECLARE  backupPath nvarchar 400   DECLARE  sourceDb nvarchar 50   DECLARE  sourceDb log nvarchar 50   DECLARE  destDb nvarchar 50   DECLARE  destMdf nvarchar 100   DECLARE  destLdf nvarchar 100   DECLARE  sqlServerDbFolder nvarchar 100    SET  sourceDb    db1  SET  sourceDb log    sourceDb     log  SET  backupPath    E  tmp     sourceDb     bak  --ATTENTION  file must already exist and SQL Server must have access to it SET  sqlServerDbFolder    E  DB SQL MSSQL11 MSSQLSERVER MSSQL DATA   SET  destDb    db2  SET  destMdf    sqlServerDbFolder    destDb     mdf  SET  destLdf    sqlServerDbFolder    destDb     log      ldf   BACKUP DATABASE  sourceDb TO DISK    backupPath  RESTORE DATABASE  destDb FROM DISK    backupPath WITH REPLACE     MOVE  sourceDb     TO  destMdf     MOVE  sourceDb log TO  destLdf

User · Answer

You could just create a new database and then go to tasks  import data  and import all the data from the database you want to duplicate to the database you just created

User · Answer

Right-click the database to clone  click Tasks  click Copy Database     Follow the wizard and you re done

User · Answer

In SQL Server 2008 R2  back-up the database as a file into a folder      Then chose the restore option that appears in the  Database  folder      In the wizard enter the new name that you want in the target database      And choose restore frrom file and use the file you just created      I jsut did it and it was very fast  my DB was small  but still  Pablo

User · Answer

None of the solutions mentioned here worked for me - I am using SQL Server Management Studio 2014   Instead I had to uncheck the  Take tail-log backup before restore  checkbox in the  Options  screen  in my version it is checked by default and prevents the Restore operation to be completed  After unchecking it  the Restore operation proceeded without issues

User · Answer

Another way that does the trick by using import export wizard  first create an empty database  then choose the source which is your server with the source database  and then in the destination choose the same server with the destination database  using the empty database you created at first   then hit finish  It will create all tables and transfer all the data into the new database

User · Answer

You could try to detach the database  copy the files to new names at a command prompt  then attach both DBs   In SQL   USE 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

User · Answer

The solution  based on this comment  https   stackoverflow com a 22409447 2399045   Just set settings  DB name  temp folder  db files folder  And after run you will have the copy of DB with Name in  sourceDBName yyyy-mm-dd  format   -- Settings -- -- New DB name will have name   sourceDB yyyy-mm-dd declare  sourceDbName nvarchar 50     MyDbName   declare  tmpFolder nvarchar 50     C  Temp   declare  sqlServerDbFolder nvarchar 100     C  Databases    --  Execution -- declare  sourceDbFile nvarchar 50   declare  sourceDbFileLog nvarchar 50   declare  destinationDbName nvarchar 50     sourceDbName          select convert varchar 10  getdate    121   declare  backupPath nvarchar 400     tmpFolder    destinationDbName     bak  declare  destMdf nvarchar 100     sqlServerDbFolder    destinationDbName     mdf  declare  destLdf nvarchar 100     sqlServerDbFolder    destinationDbName     log      ldf   SET  sourceDbFile    SELECT top 1 files name                      FROM sys databases dbs                      INNER JOIN sys master files files                          ON dbs database id   files database id                      WHERE dbs name    sourceDbName                         AND files  type    0   SET  sourceDbFileLog    SELECT top 1 files name                      FROM sys databases dbs                      INNER JOIN sys master files files                          ON dbs database id   files database id                      WHERE dbs name    sourceDbName                         AND files  type    1   BACKUP DATABASE  sourceDbName TO DISK    backupPath  RESTORE DATABASE  destinationDbName FROM DISK    backupPath WITH REPLACE     MOVE  sourceDbFile     TO  destMdf     MOVE  sourceDbFileLog  TO  destLdf

User · Answer

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

User · Answer

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 Open Microsoft SQL Management Studio   Backup original database to  BAK file  db -  Task -  Backup   Create empty database with new name  clone   Note comments below as this is optional   Click to clone database and open restore dialog  see image   Select Device and add the backup file from step 3   Change destination to test database  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    Check WITH REPLACE and WITH KEEP REPLICATION

User · Answer

Script based on Joe answer  detach  copy files  attach both     Run Managment Studio as Administrator account    It s not necessary  but maybe access denied error on executing    Configure sql server for execute xp cmdshel   EXEC sp configure  show advanced options   1 GO RECONFIGURE GO EXEC sp configure  xp cmdshell   1 GO RECONFIGURE GO    Run script  but type your db names in  dbName and  copyDBName variables before    USE master  GO   DECLARE  dbName NVARCHAR 255     Products  DECLARE  copyDBName NVARCHAR 255     Products branch   -- get DB files CREATE TABLE   DBFileNames  FileName  NVARCHAR 255   EXEC       INSERT INTO   DBFileNames  FileName       SELECT  filename  FROM      dbName     sys sysfiles    -- drop connections EXEC  ALTER DATABASE      dbName     SET OFFLINE WITH ROLLBACK IMMEDIATE    EXEC  ALTER DATABASE      dbName     SET SINGLE USER    -- detach EXEC  EXEC sp detach db  dbname          dbName          -- copy files DECLARE  filename NVARCHAR 255    path NVARCHAR 255    ext NVARCHAR 255    copyFileName NVARCHAR 255    command NVARCHAR MAX       DECLARE       oldAttachCommand NVARCHAR MAX              CREATE DATABASE      dbName     ON          newAttachCommand NVARCHAR MAX              CREATE DATABASE      copyDBName     ON    DECLARE curs CURSOR FOR  SELECT  filename  FROM   DBFileNames OPEN curs   FETCH NEXT FROM curs INTO  filename WHILE   FETCH STATUS   0   BEGIN     SET  path   REVERSE RIGHT REVERSE  filename   LEN  filename -CHARINDEX      REVERSE  filename  1   1       SET  ext   RIGHT  filename 4      SET  copyFileName    path    copyDBName    ext      SET  command    EXEC master  xp cmdshell   COPY       filename            copyFileName             PRINT  command     EXEC  command        SET  oldAttachCommand    oldAttachCommand     FILENAME         filename             SET  newAttachCommand    newAttachCommand     FILENAME         copyFileName              FETCH NEXT FROM curs INTO  filename END CLOSE curs  DEALLOCATE curs  -- attach SET  oldAttachCommand   LEFT  oldAttachCommand  LEN  oldAttachCommand  - 1      FOR ATTACH  SET  newAttachCommand   LEFT  newAttachCommand  LEN  newAttachCommand  - 1      FOR ATTACH   -- attach old db PRINT  oldAttachCommand EXEC  oldAttachCommand   -- attach copy db PRINT  newAttachCommand EXEC  newAttachCommand   DROP TABLE   DBFileNames

User · Answer

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

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

Examples related to sql-server

Examples related to sql-server-2008-express