Restore Database backup consisting of multiple files via script

Question

I frequently need to restore database from backup files on my SQL server and I have a configurable script that takes care of that. Basically the script is using RESTORE command and has variables all over to replace database name, bak file folder, etc. However, one issue I face is restoring a database which is split into multiple files, at times the count goes up till 100.

So my question is how do I make the script dynamic so that I can just specify a file count and the script will iterate through all files and restore it. From what I understand the RESTORE command does not allow this flexibility.

One option that I see right now is to dynamically generate the entire RESTORE command and then execute it, but I want to use it only as a last option.

Are there any other options?

Thanks,

Answer 1

I think what you are looking for is a solution I came up with to restore uncompressed LiteSpeed files. Since the amount of files differs based on how many threads litespeed had available at the time of the backup, we never know how many files we will need to include to restore to our reporting environment. I used Vladimir's use of the stuff function and another script to do the following. Replace FILELOCATION with your folder and update your database, data and log file names.

Declare @BackupFolder nvarchar(100) = N'FILELOCATION'
DECLARE @BackupDirectory SYSNAME = @BackupFolder

IF OBJECT_ID('tempdb..#DirTree') IS NOT NULL
DROP TABLE #DirTree

CREATE TABLE #DirTree (
Id int identity(1,1),
SubDirectory nvarchar(255),
Depth smallint,
FileFlag bit,
ParentDirectoryID int
)

INSERT INTO #DirTree (SubDirectory, Depth, FileFlag)
EXEC master..xp_dirtree @BackupDirectory, 10, 1

UPDATE #DirTree
SET ParentDirectoryID = (
SELECT MAX(Id) FROM #DirTree d2
WHERE Depth = d.Depth - 1 AND d2.Id < d.Id
)
FROM #DirTree d

DECLARE 
@ID INT,
@BackupFile VARCHAR(MAX),
@Depth TINYINT,
@FileFlag BIT,
@ParentDirectoryID INT,
@wkSubParentDirectoryID INT,
@wkSubDirectory VARCHAR(MAX)

DECLARE @BackupFiles TABLE
(
FileNamePath VARCHAR(MAX),
TransLogFlag BIT,
BackupFile VARCHAR(MAX),    
DatabaseName VARCHAR(MAX)
)

DECLARE FileCursor CURSOR LOCAL FORWARD_ONLY FOR
SELECT * FROM #DirTree WHERE FileFlag = 1

OPEN FileCursor
FETCH NEXT FROM FileCursor INTO 
@ID,
@BackupFile,
@Depth,
@FileFlag,
@ParentDirectoryID  

SET @wkSubParentDirectoryID = @ParentDirectoryID

WHILE @@FETCH_STATUS = 0
BEGIN
--loop to generate path in reverse, starting with backup file then prefixing subfolders in a loop
WHILE @wkSubParentDirectoryID IS NOT NULL
BEGIN
SELECT @wkSubDirectory = SubDirectory, @wkSubParentDirectoryID = ParentDirectoryID 
FROM #DirTree 
WHERE ID = @wkSubParentDirectoryID

SELECT @BackupFile = @wkSubDirectory + '\' + @BackupFile
END

SELECT @BackupFile = @BackupDirectory + @BackupFile

INSERT INTO @BackupFiles (FileNamePath) VALUES(@BackupFile)

FETCH NEXT FROM FileCursor INTO 
  @ID,
  @BackupFile,
  @Depth,
  @FileFlag,
  @ParentDirectoryID 

SET @wkSubParentDirectoryID = @ParentDirectoryID      
END

CLOSE FileCursor
DEALLOCATE FileCursor

SELECT @BackupFolder + '\' + SubDirectory from #DirTree
Declare @filecount int = (select count(*) from  #DirTree)


declare @sctript nvarchar(max)
select 
@sctript = 'RESTORE DATABASE rrrealty FROM ' +
STUFF( (SELECT +',' + results.MoveTo
                         from 
    (
    select 'DISK = N''' + 'FILELOCATION'  + SubDirectory + '''' as     MoveTo
    FROM #DirTree -- hear I mode files to another folder
    ) as results
                         FOR XML PATH('')),
                        1, 1, '') + ' WITH FILE = 1
  MOVE N''file'' TO N''file.mdf'', 
MOVE N''file_Log'' TO N''L:\filelog.ldf'', 
NOUNLOAD,  REPLACE,  STATS = 5'



Exec (@sctript)

Answer 2

I am not sure that I understand you about "specify a file count", but I use some kind of script for implement testing environment for myself. I hope it helps you. Feel free to ask me

declare @fileListTable table
(
    LogicalName          nvarchar(128),
    PhysicalName         nvarchar(260),
    [Type]               char(1),
    FileGroupName        nvarchar(128),
    Size                 numeric(20,0),
    MaxSize              numeric(20,0),
    FileID               bigint,
    CreateLSN            numeric(25,0),
    DropLSN              numeric(25,0),
    UniqueID             uniqueidentifier,
    ReadOnlyLSN          numeric(25,0),
    ReadWriteLSN         numeric(25,0),
    BackupSizeInBytes    bigint,
    SourceBlockSize      int,
    FileGroupID          int,
    LogGroupGUID         uniqueidentifier,
    DifferentialBaseLSN  numeric(25,0),
    DifferentialBaseGUID uniqueidentifier,
    IsReadOnl            bit,
    IsPresent            bit,
    TDEThumbprint        varbinary(32)
)
insert into @fileListTable exec('restore filelistonly from disk = ''C:\Share\BackUp\Reporting\Prod23.bak''') -- hear I get all files from backup
select * from @fileListTable

declare @sctript nvarchar(max)
select 
@sctript = 'restore database Prod from disk = ''C:\Share\BackUp\Reporting\Prod23.bak'' WITH FILE = 1,' + 
STUFF( (SELECT results.MoveTo + ' , '
                             from 
        (
        select
        'MOVE ''' + LogicalName+ ''' TO ''E:\MobiledbnkDB_Report\' + LogicalName +
        case [Type]
         when 'D' then '.mdf'
         when 'L' then '.ldf'
        end  + ''' ' as MoveTo
        FROM @fileListTable -- hear I mode files to another folder
        ) as results
                             FOR XML PATH('')),
                            1, 0, '')
+ ' NOUNLOAD,  STATS = 5'



use master

if exists(SELECT *  FROM sys.databases d WITH(NOLOCK) where d.name = 'Prod')
begin 
 alter database Prod set restricted_user with rollback immediate
 drop database Prod
end

print @sctript
exec (@sctript)