With SQL Server Standard Edition and above, it is easy to copy a database. You simply have to right-click on its name, select Tasks and select Copy Database… However that menu item is not there in SQL Server Express. Googling for a solution, I found a script that does just that, but it was written for SQL Server 2000. So I decided to fix it so that it works with SQK Server 2005 (I haven’t tested it with 2008 Here is the script for SQL Server Express 2008).

Here you will find the original script:

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

And here is the modified script that will run on SQL Server 2005

USE master
GO

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

-- the backup filename
DECLARE @BackupFile varchar(2000)
SET @BackupFile = 'c:\temp\mydb.dat'

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

-- the new database files without .mdf/.ldf
DECLARE @RestoreFile varchar(2000)
SET @RestoreFile = 'c:\temp\MyDbCopy'

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

SET @query = 'RESTORE HEADERONLY FROM DISK = ' + QUOTENAME(@BackupFile , '''')

CREATE TABLE #restoreheader
(
	BackupName	nvarchar(128)
,	BackupDescription	nvarchar(255)
,	BackupType	smallint
,	ExpirationDate	datetime
,	Compressed	tinyint
,	Position	smallint
,	DeviceType	tinyint
,	UserName	nvarchar(128)
,	ServerName	nvarchar(128)
,	DatabaseName	nvarchar(128)
,	DatabaseVersion	int
,	DatabaseCreationDate	datetime
,	BackupSize	numeric(20,0)
,	FirstLSN	numeric(25,0)
,	LastLSN	numeric(25,0)
,	CheckpointLSN	numeric(25,0)
,	DatabaseBackupLSN	numeric(25,0)
,	BackupStartDate	datetime
,	BackupFinishDate	datetime
,	SortOrder	smallint
,	CodePage	smallint
,	UnicodeLocaleId	int
,	UnicodeComparisonStyle	int
,	CompatibilityLevel	tinyint
,	SoftwareVendorId	int
,	SoftwareVersionMajor	int
,	SoftwareVersionMinor	int
,	SoftwareVersionBuild	int
,	MachineName	nvarchar(128)
,	Flags	int
,	BindingID	uniqueidentifier
,	RecoveryForkID	uniqueidentifier
,	Collation	nvarchar(128)
,	FamilyGUID	uniqueidentifier
,	HasBulkLoggedData	bit
,	IsSnapshot	bit
,	IsReadOnly	bit
,	IsSingleUser	bit
,	HasBackupChecksums	bit
,	IsDamaged	bit
,	BeginsLogChain	bit
,	HasIncompleteMetaData	bit
,	IsForceOffline	bit
,	IsCopyOnly	bit
,	FirstRecoveryForkID	uniqueidentifier
,	ForkPointLSN	numeric(25,0) NULL
,	RecoveryModel	nvarchar(60)
,	DifferentialBaseLSN	numeric(25,0) NULL
,	DifferentialBaseGUID	uniqueidentifier
,	BackupTypeDescription	nvarchar(60)
,	BackupSetGUID	uniqueidentifier NULL
)
INSERT #restoreheader EXEC (@query)

DECLARE @File int
SELECT @File=COUNT(*) FROM #restoreheader
PRINT '@File: ' + CAST(@File AS VARCHAR(2))

TRUNCATE TABLE #restoreheader
DROP TABLE #restoreheader

DECLARE @Data varchar(500)
DECLARE @Log varchar(500)

SET @query = 'RESTORE FILELISTONLY FROM DISK = ' + QUOTENAME(@BackupFile , '''')

CREATE TABLE #restoretemp
(
	LogicalName	 nvarchar(128)
,	PhysicalName	 nvarchar(260)
,	Type	 char(1)
,	FileGroupName	 nvarchar(128)
,	Size	 numeric(20,0)
,	MaxSize	 numeric(20,0)
,	FileID	 bigint
,	CreateLSN	 numeric(25,0)
,	DropLSN	 numeric(25,0) NULL
,	UniqueID	 uniqueidentifier
,	ReadOnlyLSN	 numeric(25,0) NULL
,	ReadWriteLSN	 numeric(25,0) NULL
,	BackupSizeInBytes	 bigint
,	SourceBlockSize	 int
,	FileGroupID	 int
,	LogGroupGUID	 uniqueidentifier NULL
,	DifferentialBaseLSN	 numeric(25,0) NULL
,	DifferentialBaseGUID	 uniqueidentifier
,	IsReadOnly	 bit
,	IsPresent	 bit
)
INSERT #restoretemp EXEC (@query)

SELECT @Data = LogicalName FROM #restoretemp WHERE type = 'D'
SELECT @Log = LogicalName FROM #restoretemp WHERE type = 'L'

PRINT '@Data: ' + @Data
PRINT '@Log: ' + @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

Leave a Reply

Your email address will not be published.