Restore database script to another database (name), LogicalName lookup included.<\/p>\n
\r\n-- (1) Our configuration\r\nDECLARE @BackupFile nvarchar(max), @RestoreDB nvarchar(max);\r\nSET @BackupFile = N'C:\\manual_backups\\MyDatabase1.bak';\r\nSET @RestoreDB = N'MyDatabase2';\r\n\r\n-- (2) Get the backup content LogicalNames\r\nDECLARE @FileList TABLE (\r\n\tLogicalName nvarchar(128) NOT NULL,\r\n\tPhysicalName nvarchar(260) NOT NULL,\r\n\tType char(1) NOT NULL,\r\n\tFileGroupName nvarchar(120) NULL,\r\n\tSize numeric(20, 0) NOT NULL,\r\n\tMaxSize numeric(20, 0) NOT NULL,\r\n\tFileID bigint NULL,\r\n\tCreateLSN numeric(25,0) NULL,\r\n\tDropLSN numeric(25,0) NULL,\r\n\tUniqueID uniqueidentifier NULL,\r\n\tReadOnlyLSN numeric(25,0) NULL ,\r\n\tReadWriteLSN numeric(25,0) NULL,\r\n\tBackupSizeInBytes bigint NULL,\r\n\tSourceBlockSize int NULL,\r\n\tFileGroupID int NULL,\r\n\tLogGroupGUID uniqueidentifier NULL,\r\n\tDifferentialBaseLSN numeric(25,0)NULL,\r\n\tDifferentialBaseGUID uniqueidentifier NULL,\r\n\tIsReadOnly bit NULL,\r\n\tIsPresent bit NULL,\r\n\tTDEThumbprint varbinary(32) NULL\r\n);\r\n\r\nDECLARE @RestoreStatement nvarchar(max);\r\nSET @RestoreStatement = N'RESTORE FILELISTONLY FROM DISK=N''' + @BackupFile + ''''\r\nINSERT INTO @FileList EXEC(@RestoreStatement);\r\n\r\nDECLARE @logical_data nvarchar(max), @logical_log nvarchar(max);\r\nSET @logical_data = (SELECT LogicalName FROM @FileList WHERE Type = 'D' and FileID = 1)\r\nSET @logical_log = (SELECT LogicalName FROM @FileList WHERE Type = 'L' and FileID = 2)\r\n\r\n-- (3) Get the data directory of master.mdf (usually is the default data dir)\r\nDECLARE @DataDir nvarchar(max), @NewDBFile nvarchar(max), @NewDBLogFile nvarchar(max);\r\nSELECT @DataDir = SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)\r\n FROM master.sys.master_files\r\n WHERE database_id = 1 AND file_id = 1\r\n\r\n-- Otherwise just specify the directory, with: SET @DataDir = 'C:\\MsSqlData\\';\r\n\t\t\t\t \r\n-- (4) Construct the new database filenames\r\nSET @NewDBFile = @DataDir + '' + @RestoreDB + '.mdf'; \r\nSET @NewDBLogFile = @DataDir + '' + @RestoreDB + '_log.ldf';\r\n\r\n-- Optional: Print the values for the restore command (comment the restore command below to preview the values)\r\nPRINT @logical_data\r\nPRINT @logical_log\r\nPRINT @NewDBFile\r\nPRINT @NewDBLogFile\r\n\r\n-- (4) Restore the database to the new filename\r\nRESTORE DATABASE @RestoreDB FROM DISK = @BackupFile WITH \r\n\tMOVE @logical_data TO @NewDBFile,\r\n\tMOVE @logical_log TO @NewDBLogFile;\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"Restore database script to another database (name), LogicalName lookup included. — (1) Our configuration DECLARE @BackupFile nvarchar(max), @RestoreDB nvarchar(max); SET @BackupFile = N’C:\\manual_backups\\MyDatabase1.bak’; SET @RestoreDB = N’MyDatabase2′; — (2) Get the backup content LogicalNames DECLARE @FileList TABLE ( LogicalName nvarchar(128) NOT NULL, PhysicalName nvarchar(260) NOT NULL, Type char(1) NOT NULL, FileGroupName nvarchar(120) NULL, Size numeric(20, […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"inline_featured_image":false,"footnotes":""},"categories":[8],"tags":[],"class_list":["post-102","post","type-post","status-publish","format-standard","hentry","category-other-scripts"],"_links":{"self":[{"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/posts\/102","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/comments?post=102"}],"version-history":[{"count":1,"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/posts\/102\/revisions"}],"predecessor-version":[{"id":103,"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/posts\/102\/revisions\/103"}],"wp:attachment":[{"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/media?parent=102"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/categories?post=102"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/tags?post=102"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}