Wednesday, March 23, 2011

SQL 2005 - quick way to quickly duplicate a database + data

I have my development database, I want to spin off a duplicate for testing. I want to do this often.

What's the fastest and easiest way to do this often? I can get the job done by generating scripts w/ data included but I feel like there must be a better way. Should I back up database 1 then screw around with it so I can restore it as a different name?

From stackoverflow
  • There's a command line tool (DOS) for creating and restoring databases.

    Also you can perform this as T-SQL script.

  • Detach the database, copy the .MDF file (and .ldf file) and you can attach both. Or only one, to keep one as a copy for a fast copy later.

    The "Copy database" command from Mcirosoft SQL Server Management Studio can also work like this.

    You can automate/script this easily, using PowerShell or just .Cmd files with calls to osql.

  • And you can also use the SQL Management Studio UI to create a file backup, and then restore it into a new database on the same or any other server.

    Redbeard 0x0A : Just keep in mind to watch where the restore operation is trying to put the mdf and ldf files (watch for it trying to overwrite files you don't want it to overwrite)...
    Kip : when i try restoring to a different database, i get an error: "The backup set holds a backup of a database other than the existing 'dbname' database"
    Charles Bretana : There's a checkbox somewhere there that allows you to restore from a backup taken from another server.. I don't have access to the screens right now, and it's in a different place depending on which version you have... but look for that checkbox option
  • In SQL 2005, I would say the fastest way is to use Copy Database, under Database --> Tasks

    The source and destination can be the same, and it allows you to rename the database if desired.

  • I would use scripts, as this aids transparency of what you're doing.
    (Especially if you want to do it often, as changes are each one will have slightly different data.)

    However if you don't want to go to the hassle of creating the scripts, the easiest way is to backup and then restore to a different name.

  • If you want to script it you can use the following. This is also best if you can't take the db offline:

    USE master
    -- the original database (use 'SET @DB = NULL' to disable backup)
    DECLARE @DB varchar(200)
    SET @DB = 'PcTopp'
        -- the backup filename
    DECLARE @BackupFile varchar(2000)
    SET @BackupFile = 'c:\pctopp\sqlserver\backup.dat'
    -- the new database name
    DECLARE @TestDB varchar(200)
    SET @TestDB = 'TestDB'
    -- the new database files without .mdf/.ldf
    DECLARE @RestoreFile varchar(2000)
    SET @RestoreFile = 'c:\pctopp\sqlserver\backup'
    -- ****************************************************************
    --                    no change below this line
    -- ****************************************************************
    DECLARE @query varchar(2000)
    DECLARE @DataFile varchar(2000)
    SET @DataFile = @RestoreFile + '.mdf'
    DECLARE @LogFile varchar(2000)
    SET @LogFile = @RestoreFile + '.ldf'
        SET @query = 'BACKUP DATABASE ' + @DB + ' TO DISK = ' + QUOTENAME(@BackupFile, '''')
        EXEC (@query)
    -- RESTORE FILELISTONLY FROM DISK = 'C:\temp\backup.dat'
    -- RESTORE HEADERONLY FROM DISK = 'C:\temp\backup.dat'
    -- RESTORE LABELONLY FROM DISK = 'C:\temp\backup.dat'
    -- RESTORE VERIFYONLY FROM DISK = 'C:\temp\backup.dat'
    IF EXISTS(SELECT * FROM sysdatabases WHERE name = @TestDB)
        SET @query = 'DROP DATABASE ' + @TestDB
        EXEC (@query)
    DECLARE @File int
    SET @File = @@ROWCOUNT
    DECLARE @Data varchar(500)
    DECLARE @Log varchar(500)
    SET @query = 'RESTORE FILELISTONLY FROM DISK = ' + QUOTENAME(@BackupFile , '''')
    CREATE TABLE #restoretemp
     LogicalName varchar(500),
     PhysicalName varchar(500),
     type varchar(10),
     FilegroupName varchar(200),
     size int,
     maxsize bigint
    INSERT #restoretemp EXEC (@query)
    SELECT @Data = LogicalName FROM #restoretemp WHERE type = 'D'
    SELECT @Log = LogicalName FROM #restoretemp WHERE type = 'L'
    PRINT @Data
    PRINT @Log
    TRUNCATE TABLE #restoretemp
    DROP TABLE #restoretemp
    IF @File > 0
        SET @query = 'RESTORE DATABASE ' + @TestDB + ' FROM DISK = ' + QUOTENAME(@BackupFile, '''') + 
            ' WITH MOVE ' + QUOTENAME(@Data, '''') + ' TO ' + QUOTENAME(@DataFile, '''') + ', MOVE ' +
            QUOTENAME(@Log, '''') + ' TO ' + QUOTENAME(@LogFile, '''') + ', FILE = ' + CONVERT(varchar, @File)
        EXEC (@query)

    Copied from:

  • One more option to throw in,

    Here's a blog entry that talks about using the Database Publishing Wizard and how it's used to export all elements of a database, including data, to an SQL file. Might be helpful and seems pretty straightforward.


Post a Comment