I needed to make a stored procedure that could make a copy of a database on a SQL 2005 server. I wanted to do it entirely through T-SQL.

I found this excellent guide at: http://weblogs.asp.net/mschwarz/archive/2004/08/26/220735.aspx

So, thank you Michael’s blog for the start, but the problem was it did not seem to work on SQL2005. So I had to make a few changes:

So starting with Michael’s original code I made the following modifications:

1.  Added INIT to the backup database. This tells the sql server not to append to the backup file but to overwrite it. I was going to run this fairly often so I did not need a ever growing backup file.

2. I got rid of the drop. Instead the procedure only runs if the database does not already exist. I did not feel comfortable with this.

3. RESTORE FILELISTONLY FROM DISK appears to return a different result set than it did in SQL2000, so I had to update the temptable.

4. @@ROWCOUNT and RESTORE HEADERONLY FROM DISK were not playing nicely together in 2005. It always returned 0 records. Instead of fight with it, I did away with the file argument in the BACKUP DATABASE. So this does concern me that if the backup fails it will attempt to restore it. But it’s a risk I’m willing to live with at this time.

So here’s my changes to Michael’s code. All credit to him for sharing the original method:

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, ””) + ‘ WITH INIT’
EXEC (@query)
END

IF NOT EXISTS(SELECT * FROM sysdatabases WHERE name = @TestDB)
BEGIN
DECLARE @File int
RESTORE HEADERONLY FROM DISK = @BackupFile
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(25,0),
DropLSN numeric(25,0),
UniqueID uniqueidentifier,
ReadOnlyLSN numeric(25,0),
ReadWriteLSN numeric(25,0),
BackupSizeInBytes bigint,
SourceBlockSize int,
FileGroupID int,
LogGroupGUID uniqueidentifier NULL,
DifferentialBaseLSN numeric(25,0),
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
PRINT @Log
TRUNCATE TABLE #restoretemp
DROP TABLE #restoretemp

SET @query = ‘RESTORE DATABASE ‘ + @TestDB + ‘ FROM DISK = ‘ + QUOTENAME(@BackupFile, ””) +
‘ WITH MOVE ‘ + QUOTENAME(@Data, ””) + ‘ TO ‘ + QUOTENAME(@DataFile, ””) + ‘, MOVE ‘ +
QUOTENAME(@Log, ””) + ‘ TO ‘ + QUOTENAME(@LogFile, ””)
EXEC (@query)

END