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

Question

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?

Answer 1

the post above SQL 2005 - quick way to quickly duplicate a database + data works...

but if u r using sql server 2008 r2 for example, u may need a slight mod on the "create table #restoretemp" portion because the return from "restore filelistonly..." has changed since...

basically add this after "maxsize bigint"

 maxsize bigint,
 fileid bigint,
 createlsn numeric(26),
 droplsn numeric(26),
 uniqueid uniqueidentifier,
 readonlylsn numeric(26),
 readwritelsn numeric(26),
 BackupSizeInBytes bigint,
 SourceBlockSize int,
 FileGroupId int,
 LogGroupGUID uniqueidentifier,
 DifferentialBaseLSN numeric(26),
 DifferentialBaseGUID uniqueidentifier,
 IsReadOnly bit,
 IsPresent bit,
 TDEThumbprint varbinary(40)

and u will end up with the working script below...

USE master
GO

-- the original database (use 'SET @DB = NULL' to disable backup)
DECLARE @DB varchar(200)
SET @DB = 'source_db_name'

-- the backup filename
DECLARE @BackupFile varchar(2000)
SET @BackupFile = 'D:\somewhere\DB\backup.dat'

-- the new database name
DECLARE @TestDB varchar(200)
SET @TestDB = 'boon_db'

-- the new database files without .mdf/.ldf
DECLARE @RestoreFile varchar(2000)
SET @RestoreFile = 'D:\somewhere\DB'


-- ****************************************************************
--                    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'

IF @DB IS NOT NULL
BEGIN
    SET @query = 'BACKUP DATABASE ' + @DB + ' TO DISK = ' + QUOTENAME(@BackupFile, '''')
    EXEC (@query)
END

-- 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)
BEGIN
    SET @query = 'DROP DATABASE ' + @TestDB
    EXEC (@query)
END

RESTORE HEADERONLY FROM DISK = @BackupFile
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,
 fileid bigint,
 createlsn numeric(26),
 droplsn numeric(26),
 uniqueid uniqueidentifier,
 readonlylsn numeric(26),
 readwritelsn numeric(26),
 BackupSizeInBytes bigint,
 SourceBlockSize int,
 FileGroupId int,
 LogGroupGUID uniqueidentifier,
 DifferentialBaseLSN numeric(26),
 DifferentialBaseGUID uniqueidentifier,
 IsReadOnly bit,
 IsPresent bit,
 TDEThumbprint varbinary(40)
)
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
BEGIN
    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)
END
GO

Answer 2

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.

http://blog.sqlauthority.com/2007/11/16/sql-server-2005-generate-script-with-data-from-database-database-publishing-wizard/

Answer 3

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
GO

-- 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'

IF @DB IS NOT NULL
BEGIN
    SET @query = 'BACKUP DATABASE ' + @DB + ' TO DISK = ' + QUOTENAME(@BackupFile, '''')
    EXEC (@query)
END

-- 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)
BEGIN
    SET @query = 'DROP DATABASE ' + @TestDB
    EXEC (@query)
END

RESTORE HEADERONLY FROM DISK = @BackupFile
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
BEGIN
    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)
END
GO

Copied from: http://weblogs.asp.net/mschwarz/archive/2004/08/26/220735.aspx

Answer 4

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.

Answer 5

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.

Answer 6

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.

Answer 7

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.

Answer 8

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

Also you can perform this as T-SQL script. http://www.sqlmag.com/Article/ArticleID/46560/sql_server_46560.html