{"id":167,"date":"2011-08-12T22:07:54","date_gmt":"2011-08-13T03:07:54","guid":{"rendered":"\/\/www.beauchamp.me\/techno\/?p=167"},"modified":"2011-09-02T16:23:19","modified_gmt":"2011-09-02T21:23:19","slug":"duplicating-a-database-in-sql-server-express","status":"publish","type":"post","link":"https:\/\/www.beauchamp.me\/techno\/blog\/2011\/08\/12\/duplicating-a-database-in-sql-server-express\/","title":{"rendered":"Duplicating a database in SQL Server Express"},"content":{"rendered":"

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<\/del> Here is the script for SQL Server Express 2008<\/a>).<\/p>\n

Here you will find the original script:<\/p>\n

http:\/\/weblogs.asp.net\/mschwarz\/archive\/2004\/08\/26\/220735.aspx<\/a><\/p>\n

And here is the modified script that will run on SQL Server 2005<\/p>\n

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\nCREATE TABLE #restoreheader\r\n(\r\n\tBackupName\tnvarchar(128)\r\n,\tBackupDescription\tnvarchar(255)\r\n,\tBackupType\tsmallint\r\n,\tExpirationDate\tdatetime\r\n,\tCompressed\ttinyint\r\n,\tPosition\tsmallint\r\n,\tDeviceType\ttinyint\r\n,\tUserName\tnvarchar(128)\r\n,\tServerName\tnvarchar(128)\r\n,\tDatabaseName\tnvarchar(128)\r\n,\tDatabaseVersion\tint\r\n,\tDatabaseCreationDate\tdatetime\r\n,\tBackupSize\tnumeric(20,0)\r\n,\tFirstLSN\tnumeric(25,0)\r\n,\tLastLSN\tnumeric(25,0)\r\n,\tCheckpointLSN\tnumeric(25,0)\r\n,\tDatabaseBackupLSN\tnumeric(25,0)\r\n,\tBackupStartDate\tdatetime\r\n,\tBackupFinishDate\tdatetime\r\n,\tSortOrder\tsmallint\r\n,\tCodePage\tsmallint\r\n,\tUnicodeLocaleId\tint\r\n,\tUnicodeComparisonStyle\tint\r\n,\tCompatibilityLevel\ttinyint\r\n,\tSoftwareVendorId\tint\r\n,\tSoftwareVersionMajor\tint\r\n,\tSoftwareVersionMinor\tint\r\n,\tSoftwareVersionBuild\tint\r\n,\tMachineName\tnvarchar(128)\r\n,\tFlags\tint\r\n,\tBindingID\tuniqueidentifier\r\n,\tRecoveryForkID\tuniqueidentifier\r\n,\tCollation\tnvarchar(128)\r\n,\tFamilyGUID\tuniqueidentifier\r\n,\tHasBulkLoggedData\tbit\r\n,\tIsSnapshot\tbit\r\n,\tIsReadOnly\tbit\r\n,\tIsSingleUser\tbit\r\n,\tHasBackupChecksums\tbit\r\n,\tIsDamaged\tbit\r\n,\tBeginsLogChain\tbit\r\n,\tHasIncompleteMetaData\tbit\r\n,\tIsForceOffline\tbit\r\n,\tIsCopyOnly\tbit\r\n,\tFirstRecoveryForkID\tuniqueidentifier\r\n,\tForkPointLSN\tnumeric(25,0) NULL\r\n,\tRecoveryModel\tnvarchar(60)\r\n,\tDifferentialBaseLSN\tnumeric(25,0) NULL\r\n,\tDifferentialBaseGUID\tuniqueidentifier\r\n,\tBackupTypeDescription\tnvarchar(60)\r\n,\tBackupSetGUID\tuniqueidentifier NULL\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\tLogicalName\t nvarchar(128)\r\n,\tPhysicalName\t nvarchar(260)\r\n,\tType\t char(1)\r\n,\tFileGroupName\t nvarchar(128)\r\n,\tSize\t numeric(20,0)\r\n,\tMaxSize\t numeric(20,0)\r\n,\tFileID\t bigint\r\n,\tCreateLSN\t numeric(25,0)\r\n,\tDropLSN\t numeric(25,0) NULL\r\n,\tUniqueID\t uniqueidentifier\r\n,\tReadOnlyLSN\t numeric(25,0) NULL\r\n,\tReadWriteLSN\t numeric(25,0) NULL\r\n,\tBackupSizeInBytes\t bigint\r\n,\tSourceBlockSize\t int\r\n,\tFileGroupID\t int\r\n,\tLogGroupGUID\t uniqueidentifier NULL\r\n,\tDifferentialBaseLSN\t numeric(25,0) NULL\r\n,\tDifferentialBaseGUID\t uniqueidentifier\r\n,\tIsReadOnly\t bit\r\n,\tIsPresent\t bit\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 > 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":"
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 […]<\/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":[3],"tags":[],"class_list":["post-167","post","type-post","status-publish","format-standard","hentry","category-sql-server-2005"],"_links":{"self":[{"href":"https:\/\/www.beauchamp.me\/techno\/wp-json\/wp\/v2\/posts\/167","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=167"}],"version-history":[{"count":4,"href":"https:\/\/www.beauchamp.me\/techno\/wp-json\/wp\/v2\/posts\/167\/revisions"}],"predecessor-version":[{"id":171,"href":"https:\/\/www.beauchamp.me\/techno\/wp-json\/wp\/v2\/posts\/167\/revisions\/171"}],"wp:attachment":[{"href":"https:\/\/www.beauchamp.me\/techno\/wp-json\/wp\/v2\/media?parent=167"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.beauchamp.me\/techno\/wp-json\/wp\/v2\/categories?post=167"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.beauchamp.me\/techno\/wp-json\/wp\/v2\/tags?post=167"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}