0

This question is a continuation of my last question How to use variables with RESTORE FILELISTONLY.

Every two months, I have to clone about 50 databases from production to a development environment. Each database has one database file and one log file stored on 8 different disk drives D to K.

In production the path is {Drive Letter:\DATA} for databases and {Drive Letter:\LOG} for transaction logs.

On this development server, the paths are {Drive Letter:\MSSQL10_50.INSTANCE_NAME\MSSQL\DATA} for both database and transaction log files. The paths can not be changed in production or development to match one another.

Example:

D:\DATA needs to be D:\MSSQL10.MYINSTANCE\MSSQL\DATA

H:\LOG needs to be H:\MSSQL10.MYINSTANCE\MSSQL\DATA

The root directory of each instance is C:\Program Files\Microsoft SQL Server\MSSQL10_50.INSTANCE_NAME

The goal of this process is to update a column called [FilGroups_Move_To_FullQuery] in a table called db_restconfig with the entire MOVE portion of the RESTORE DATABASE statement. This column is later used to automate restoring databases from production.

/* 1. stored procedure to fill msdb..restorelist_files */

USE [msdb]

IF object_id('ps_restore_filelist') IS NOT NULL
    drop procedure ps_restore_filelist
    go

CREATE PROCEDURE dbo.ps_restore_filelist
  @backup_path NVARCHAR(MAX)
AS
BEGIN
    SET NOCOUNT ON;
    RESTORE FILELISTONLY FROM DISK = @backup_path;
END
GO

/* 2. create table to store filelistpaths */
    IF OBJECT_ID('msdb..restorelist_files') IS NOT NULL DROP TABLE msdb..restorelist_files
    create table msdb..restorelist_files(
    LogicalName varchar(255),
    PhysicalName varchar(255),
    Type varchar(255),
    FileGroupName varchar(255),
    Size varchar(255),
    MaxSize varchar(255),
    FileId varchar(255),
    CreateLSN varchar(255),
    DropLSN varchar(255),
    UniqueId varchar(255),
    ReadOnlyLSN varchar(255),
    ReadWriteLSN varchar(255),
    BackupSizeInBytes varchar(255),
    SourceBlockSize varchar(255),
    FileGroupId varchar(255),
    LogGroupGUID varchar(255),
    DifferentialBaseLSN varchar(255),
    DifferentialBaseGUID varchar(255),
    IsReadOnly varchar(255),
    IsPresent varchar(255),
    TDEThumbprint varchar(255))

/* 3. fill table with files and their paths */
TRUNCATE TABLE msdb..restorelist_files
GO

DECLARE @BACKUP_PATH as nvarchar(max) = N'Z:\BACKUPS\MY_DATABASE_BACKUP.bak'

/* execute stored procedure that will recover list of files for this backup */
insert into msdb..restorelist_files 
exec ps_restore_filelist @BACKUP_PATH

/* create the MOVE TO portion of the restore database statement using the new path name based on the service/instance name */
DECLARE @instance_name as varchar(255) = @@SERVICENAME
DECLARE @path_name as varchar(255) = 'MSSQL10_50.' + @instance_name + '\DATA\'
DECLARE @DB as varchar(50) = 'MY_DATABASE'
DECLARE @SQL_RESTORE as nvarchar(max) 

SELECT  'MOVE N''' + LogicalName +  '''TO N''' + LEFT(physicalname, CHARINDEX('.', REVERSE(physicalname)) - 1)  +  
             @path_name +
       REVERSE(LEFT(REVERSE(physicalname),CHARINDEX('\', REVERSE(physicalname), 1) - 1))  + ''','
from msdb..restorelist_files

The end result looks like this

MOVE N'MY_DATABASE'TO N'D:\MSSQL10_50.INSTANCE\DATA\MY_DATABASE.mdf',
MOVE N'MY_DATABASE_1'TO N'E:\MSSQL10_50.INSTANCE\DATA\MY_DATABASE_1.ndf',
MOVE N'MY_DATABASE_2'TO N'F:\MSSQL10_50.INSTANCE\DATA\MY_DATABASE_2.ndf',
MOVE N'MY_DATABASE_3'TO N'G:\MSSQL10_50.INSTANCE\DATA\MY_DATABASE_3.ndf',
MOVE N'MY_DATABASE_4'TO N'H:\MSSQL10_50.INSTANCE\DATA\MY_DATABASE_4.ndf',
MOVE N'MY_DATABASE_5'TO N'I:\MSSQL10_50.INSTANCE\DATA\MY_DATABASE_5.ndf',
MOVE N'MY_DATABASE_6'TO N'J:\MSSQL10_50.INSTANCE\DATA\MY_DATABASE_6.ndf',
MOVE N'MY_DATABASE_7'TO N'K:\MSSQL10_50.INSTANCE\DATA\MY_DATABASE_7.ndf',
MOVE N'MY_DATABASE_log'TO N'D:\MSSQL10_50.INSTANCE\DATA\MY_DATABASE_8.ldf',
MOVE N'MY_DATABASE_log_1'TO N'E:\MSSQL10_50.INSTANCE\DATA\MY_DATABASE_9.ldf',
MOVE N'MY_DATABASE_log_2'TO N'F:\MSSQL10_50.INSTANCE\DATA\MY_DATABASE_10.ldf',
MOVE N'MY_DATABASE_log_3'TO N'G:\MSSQL10_50.INSTANCE\DATA\MY_DATABASE_11.ldf',
MOVE N'MY_DATABASE_log_4'TO N'H:\MSSQL10_50.INSTANCE\DATA\MY_DATABASE_12.ldf',
MOVE N'MY_DATABASE_log_5'TO N'I:\MSSQL10_50.INSTANCE\DATA\MY_DATABASE_13.ldf',
MOVE N'MY_DATABASE_log_6'TO N'J:\MSSQL10_50.INSTANCE\DATA\MY_DATABASE_14.ldf',
MOVE N'MY_DATABASE_log_7'TO N'K:\MSSQL10_50.INSTANCE\DATA\MY_DATABASE_15.ldf',

Question:

How can I concatenate the output of the SELECT above into one result so that I can update the [FilGroups_Move_To_FullQuery] column ?

NOTE: Yes there are eight log files. As I am not involved with production, I am not the one who made the decision to use eight log files.

  • Whoa, wait a second - you have databases with EIGHT LOG FILES? Please read. – Aaron Bertrand Oct 31 '14 at 14:26
  • Hello @AaronBertrand Yes I know, this is how the production environment is! – Craig Efrein Oct 31 '14 at 14:29
  • Well, you should have whoever is responsible for production read that article and fix production. – Aaron Bertrand Oct 31 '14 at 14:30
  • Is there only one row in the table db_restlog? If not, how exactly is the row to update identified? And did you really mean FilGroups_... (missing e, and these aren't filegroups) instead of Files_...? – Aaron Bertrand Oct 31 '14 at 14:34
  • @AaronBertrand, there is one db_restconfig table per instance and one row per database. – Craig Efrein Oct 31 '14 at 14:36
1

Assuming the row in the config table is identified by WHERE DB = @DB:

UPDATE msdb.dbo.db_restconfig 
SET FilGroups_Move_To_FullQuery = STUFF((SELECT ',MOVE N''' + LogicalName 
   +  '''TO N''' + LEFT(physicalname, CHARINDEX('.', REVERSE(physicalname)) - 1)  
   + @path_name 
   + REVERSE(LEFT(REVERSE(physicalname),CHARINDEX('\', REVERSE(physicalname), 1) - 1))  
   + ''''
 FROM msdb.dbo.restorelist_files
 FOR XML PATH, TYPE).value(N'.[1]',N'nvarchar(max)'),1,1,'')
WHERE DB = @DB;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.