Sql-server – Help in Automating Database Restoration in SQL Server

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.