Following my previous post, here is the same script for SQL Server Express 2008:

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

RESTORE HEADERONLY FROM DISK = 'c:\temp\mydb.dat'
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
,	CompressedBackupSize int
)
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
,	TDEThumbprint	varbinary(32)
)
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.