{"id":170,"date":"2011-09-02T16:16:42","date_gmt":"2011-09-02T21:16:42","guid":{"rendered":"\/\/www.beauchamp.me\/techno\/?p=170"},"modified":"2012-03-22T09:23:05","modified_gmt":"2012-03-22T14:23:05","slug":"duplicate-a-database-with-sql-server-express-2008","status":"publish","type":"post","link":"https:\/\/www.beauchamp.me\/techno\/blog\/2011\/09\/02\/duplicate-a-database-with-sql-server-express-2008\/","title":{"rendered":"Duplicate a database with SQL Server Express 2008"},"content":{"rendered":"<p>Following my <a title=\"Duplicating a database in SQL Server Express\" href=\"http:\/\/www.beauchamp.me\/techno\/sql-server-2005\/duplicating-a-database-in-sql-server-express\/\">previous post<\/a>, here is the same script for SQL Server Express 2008:<\/p>\n<pre class=\"brush:sql\">USE master\r\nGO\r\n\r\n-- the original database (use 'SET @DB = NULL' to disable backup)\r\nDECLARE @DB varchar(200)\r\nSET @DB = 'MyDB'\r\n\r\n-- the backup filename\r\nDECLARE @BackupFile varchar(2000)\r\nSET @BackupFile = 'c:\\temp\\mydb.dat'\r\n\r\n-- the new database name\r\nDECLARE @TestDB varchar(200)\r\nSET @TestDB = 'MyDbCopy'\r\n\r\n-- the new database files without .mdf\/.ldf\r\nDECLARE @RestoreFile varchar(2000)\r\nSET @RestoreFile = 'c:\\temp\\MyDbCopy'\r\n\r\n-- ****************************************************************\r\n--                    no change below this line\r\n-- ****************************************************************\r\n\r\nDECLARE @query varchar(2000)\r\n\r\nDECLARE @DataFile varchar(2000)\r\nSET @DataFile = @RestoreFile + '.mdf'\r\n\r\nDECLARE @LogFile varchar(2000)\r\nSET @LogFile = @RestoreFile + '.ldf'\r\n\r\nIF @DB IS NOT NULL\r\nBEGIN\r\n    SET @query = 'BACKUP DATABASE ' + @DB + ' TO DISK = ' + QUOTENAME(@BackupFile, '''')\r\n    EXEC (@query)\r\nEND\r\n\r\n-- RESTORE FILELISTONLY FROM DISK = 'C:\\temp\\backup.dat'\r\n-- RESTORE HEADERONLY FROM DISK = 'C:\\temp\\backup.dat'\r\n-- RESTORE LABELONLY FROM DISK = 'C:\\temp\\backup.dat'\r\n-- RESTORE VERIFYONLY FROM DISK = 'C:\\temp\\backup.dat'\r\n\r\nIF EXISTS(SELECT * FROM sysdatabases WHERE name = @TestDB)\r\nBEGIN\r\n    SET @query = 'DROP DATABASE ' + @TestDB\r\n    EXEC (@query)\r\nEND\r\n\r\nSET @query = 'RESTORE HEADERONLY FROM DISK = ' + QUOTENAME(@BackupFile , '''')\r\n\r\nRESTORE HEADERONLY FROM DISK = 'c:\\temp\\mydb.dat'\r\nCREATE TABLE #restoreheader\r\n(\r\n    BackupName  nvarchar(128)\r\n,   BackupDescription   nvarchar(255)\r\n,   BackupType  smallint\r\n,   ExpirationDate  datetime\r\n,   Compressed  tinyint\r\n,   Position    smallint\r\n,   DeviceType  tinyint\r\n,   UserName    nvarchar(128)\r\n,   ServerName  nvarchar(128)\r\n,   DatabaseName    nvarchar(128)\r\n,   DatabaseVersion int\r\n,   DatabaseCreationDate    datetime\r\n,   BackupSize  numeric(20,0)\r\n,   FirstLSN    numeric(25,0)\r\n,   LastLSN numeric(25,0)\r\n,   CheckpointLSN   numeric(25,0)\r\n,   DatabaseBackupLSN   numeric(25,0)\r\n,   BackupStartDate datetime\r\n,   BackupFinishDate    datetime\r\n,   SortOrder   smallint\r\n,   CodePage    smallint\r\n,   UnicodeLocaleId int\r\n,   UnicodeComparisonStyle  int\r\n,   CompatibilityLevel  tinyint\r\n,   SoftwareVendorId    int\r\n,   SoftwareVersionMajor    int\r\n,   SoftwareVersionMinor    int\r\n,   SoftwareVersionBuild    int\r\n,   MachineName nvarchar(128)\r\n,   Flags   int\r\n,   BindingID   uniqueidentifier\r\n,   RecoveryForkID  uniqueidentifier\r\n,   Collation   nvarchar(128)\r\n,   FamilyGUID  uniqueidentifier\r\n,   HasBulkLoggedData   bit\r\n,   IsSnapshot  bit\r\n,   IsReadOnly  bit\r\n,   IsSingleUser    bit\r\n,   HasBackupChecksums  bit\r\n,   IsDamaged   bit\r\n,   BeginsLogChain  bit\r\n,   HasIncompleteMetaData   bit\r\n,   IsForceOffline  bit\r\n,   IsCopyOnly  bit\r\n,   FirstRecoveryForkID uniqueidentifier\r\n,   ForkPointLSN    numeric(25,0) NULL\r\n,   RecoveryModel   nvarchar(60)\r\n,   DifferentialBaseLSN numeric(25,0) NULL\r\n,   DifferentialBaseGUID    uniqueidentifier\r\n,   BackupTypeDescription   nvarchar(60)\r\n,   BackupSetGUID   uniqueidentifier NULL\r\n,\tCompressedBackupSize int\r\n)\r\nINSERT #restoreheader EXEC (@query)\r\n\r\nDECLARE @File int\r\nSELECT @File=COUNT(*) FROM #restoreheader\r\nPRINT '@File: ' + CAST(@File AS VARCHAR(2))\r\n\r\nTRUNCATE TABLE #restoreheader\r\nDROP TABLE #restoreheader\r\n\r\nDECLARE @Data varchar(500)\r\nDECLARE @Log varchar(500)\r\n\r\nSET @query = 'RESTORE FILELISTONLY FROM DISK = ' + QUOTENAME(@BackupFile , '''')\r\n\r\nCREATE TABLE #restoretemp\r\n(\r\n    LogicalName  nvarchar(128)\r\n,   PhysicalName     nvarchar(260)\r\n,   Type     char(1)\r\n,   FileGroupName    nvarchar(128)\r\n,   Size     numeric(20,0)\r\n,   MaxSize  numeric(20,0)\r\n,   FileID   bigint\r\n,   CreateLSN    numeric(25,0)\r\n,   DropLSN  numeric(25,0) NULL\r\n,   UniqueID     uniqueidentifier\r\n,   ReadOnlyLSN  numeric(25,0) NULL\r\n,   ReadWriteLSN     numeric(25,0) NULL\r\n,   BackupSizeInBytes    bigint\r\n,   SourceBlockSize  int\r\n,   FileGroupID  int\r\n,   LogGroupGUID     uniqueidentifier NULL\r\n,   DifferentialBaseLSN  numeric(25,0) NULL\r\n,   DifferentialBaseGUID     uniqueidentifier\r\n,   IsReadOnly   bit\r\n,   IsPresent    bit\r\n,\tTDEThumbprint\tvarbinary(32)\r\n)\r\nINSERT #restoretemp EXEC (@query)\r\n\r\nSELECT @Data = LogicalName FROM #restoretemp WHERE type = 'D'\r\nSELECT @Log = LogicalName FROM #restoretemp WHERE type = 'L'\r\n\r\nPRINT '@Data: ' + @Data\r\nPRINT '@Log: ' + @Log\r\n\r\nTRUNCATE TABLE #restoretemp\r\nDROP TABLE #restoretemp\r\n\r\nIF @File &gt; 0\r\nBEGIN\r\n    SET @query = 'RESTORE DATABASE ' + @TestDB + ' FROM DISK = ' + QUOTENAME(@BackupFile, '''') +\r\n        ' WITH MOVE ' + QUOTENAME(@Data, '''') + ' TO ' + QUOTENAME(@DataFile, '''') + ', MOVE ' +\r\n        QUOTENAME(@Log, '''') + ' TO ' + QUOTENAME(@LogFile, '''') + ', FILE = ' + CONVERT(varchar, @File)\r\n    EXEC (@query)\r\nEND\r\nGO<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Following my previous post, here is the same script for SQL Server Express 2008: USE master GO &#8212; the original database (use &#8216;SET @DB = NULL&#8217; to disable backup) DECLARE @DB varchar(200) SET @DB = &#8216;MyDB&#8217; &#8212; the backup filename DECLARE @BackupFile varchar(2000) SET @BackupFile = &#8216;c:\\temp\\mydb.dat&#8217; &#8212; the new database name DECLARE @TestDB varchar(200) [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[12],"tags":[],"class_list":["post-170","post","type-post","status-publish","format-standard","hentry","category-sql-server-2008"],"_links":{"self":[{"href":"https:\/\/www.beauchamp.me\/techno\/wp-json\/wp\/v2\/posts\/170","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.beauchamp.me\/techno\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.beauchamp.me\/techno\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.beauchamp.me\/techno\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.beauchamp.me\/techno\/wp-json\/wp\/v2\/comments?post=170"}],"version-history":[{"count":4,"href":"https:\/\/www.beauchamp.me\/techno\/wp-json\/wp\/v2\/posts\/170\/revisions"}],"predecessor-version":[{"id":185,"href":"https:\/\/www.beauchamp.me\/techno\/wp-json\/wp\/v2\/posts\/170\/revisions\/185"}],"wp:attachment":[{"href":"https:\/\/www.beauchamp.me\/techno\/wp-json\/wp\/v2\/media?parent=170"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.beauchamp.me\/techno\/wp-json\/wp\/v2\/categories?post=170"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.beauchamp.me\/techno\/wp-json\/wp\/v2\/tags?post=170"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}