Restore SQL database from multi-part backup

originally posted 2015-01-27

1	originally posted 2015-01-27

The database backup is named like “Something_[1-x].bak”, where x is the number of backup file parts.

$(DB) is the database name
$(CHUNKS) is the number of backup file parts
$(PATH) is the path of the first backup file (assumes all of the backup files are in the same directory)

:setvar DB "gazelle_demo"
:setvar CHUNKS "1"
:setvar PATH "H:\DatabaseBackups\Nightly\SQLA\Nightly\gazelle_demo_NPT_[1-1].bak"

USE [master];
declare @error nvarchar(100);
set @error = null;
    
if exists(select * from sys.databases where name = '$(DB)')
begin
    --if target database exists, disconnect users
    begin try
        ALTER DATABASE [$(DB)] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    end try
    begin catch
        declare @dummy bit;
    end catch
end

begin try
  declare @chunks int;
  set @chunks = $(CHUNKS);
      
  declare @diskChunkSql nvarchar(max);
  set @diskChunkSql = '';
      
  declare @count int;
  set @count = 1;
  while(@count&amp;lt;=@chunks)
  begin	
    set @diskChunkSql = @diskChunkSql + 'DISK=''' + replace('$(PATH)','_[1','_[' + convert(nvarchar(10),@count))  + '''';
    if(@count&amp;lt;@chunks and @chunks &amp;gt; 1) set @diskChunkSql = @diskChunkSql + ',';
    set @count = @count + 1;
  end

  --get default dir for data files
  declare @defaultDataDir nvarchar(512)
  select @defaultDataDir = convert(nvarchar(512),SERVERPROPERTY('instancedefaultdatapath'))

  --get default dir for log files
  declare @defaultLogDir nvarchar(512)
  select @defaultLogDir = convert(nvarchar(512),SERVERPROPERTY('instancedefaultlogpath'))

  --table of files contained by the backup set
  declare @dbFilesTab 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
  ,[IsReadOnly] bit
  ,[IsPresent] bit
  ,[TDEThumbprint] varbinary(32)
  )

  --table of new file named derived from the backup set
  declare @dbNewFilesTab table
  (
  [LogicalName] nvarchar(128)
  ,[Ext] nvarchar(128)
  ,[Type] char(1)
  ,[Row] int
  ,[NewPhysicalName] nvarchar(260)
  )

  --get files in backup set
  insert into @dbFilesTab exec('restore filelistonly from disk = ''$(PATH)''')

  --determine the distinct list of file extensions in the backup set
  declare @extensionsTab table ([Ext] nvarchar(3));
  insert @extensionsTab select distinct right([PhysicalName],3) from @dbFilesTab

  --derive new names for each physical file in the backup set for the new location
  while(1=1)
  begin
    declare @ext nvarchar(3);
    set @ext = null;
    select top 1 @ext = [Ext] from @extensionsTab
    if(@ext is not null)
    begin
      insert @dbNewFilesTab ([LogicalName],[Ext],[Type],[Row],[NewPhysicalName])
      select 
      [LogicalName]
      ,[Ext]
      ,[Type]
      ,[Row]
      ,[NewPhysicalName] =
        case when [Type] = 'L' then @defaultLogDir + '$(DB)' + convert(nvarchar(2),[Row]) + '.' + [Ext]
        else @defaultDataDir + '$(DB)' + convert(nvarchar(2),[Row]) + '.' + [Ext]
        end
      from
      (
        select [LogicalName], [Ext] = right([PhysicalName],3), [Type], [Row] = row_number() over(order by[LogicalName])
        from @dbFilesTab 
        where right([PhysicalName],3) = @ext
      ) as q1

      delete @extensionsTab where [Ext] = @ext
    end
    else break;
  end

  --create a concatentated string of SQL that specifies the new names for each file
  declare @moveSql nvarchar(max)
  select @moveSql = stuff(
  (
    select ',move N''' + [LogicalName] + ''' TO N''' + [NewPhysicalName] + ''''
    from @dbNewFilesTab
    order by [Ext],[Row]
    for xml path('')
  ),1,1,'')
      
    declare @sql nvarchar(max);
    set @sql = 'use [master]; restore database [$(DB)] from ' +  @diskChunkSql + ' WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 5, ' + @moveSql;
    
  exec(@sql)
end try
begin catch
    set  @error = ERROR_MESSAGE();
end catch

--if target database exists, re-enable connections
begin try
    ALTER DATABASE [$(DB)] SET MULTI_USER;
end try
begin catch
    declare @dummy2 bit;
end catch
            
if(@error is not null) RAISERROR(@error,11,100)

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

:setvar DB "gazelle_demo"

:setvar CHUNKS "1"

:setvar PATH "H:\DatabaseBackups\Nightly\SQLA\Nightly\gazelle_demo_NPT_[1-1].bak"

USE [master];

declare @error nvarchar(100);

set @error = null;

if exists(select * from sys.databases where name = '$(DB)')

begin

--if target database exists, disconnect users

begin try

ALTER DATABASE [$(DB)] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

end try

begin catch

declare @dummy bit;

end catch

end

begin try

declare @chunks int;

set @chunks = $(CHUNKS);

declare @diskChunkSql nvarchar(max);

set @diskChunkSql = '';

declare @count int;

set @count = 1;

while(@count&lt;=@chunks)

begin

set @diskChunkSql = @diskChunkSql + 'DISK=''' + replace('$(PATH)','_[1','_[' + convert(nvarchar(10),@count)) + '''';

if(@count&lt;@chunks and @chunks &gt; 1) set @diskChunkSql = @diskChunkSql + ',';

set @count = @count + 1;

end

--get default dir for data files

declare @defaultDataDir nvarchar(512)

select @defaultDataDir = convert(nvarchar(512),SERVERPROPERTY('instancedefaultdatapath'))

--get default dir for log files

declare @defaultLogDir nvarchar(512)

select @defaultLogDir = convert(nvarchar(512),SERVERPROPERTY('instancedefaultlogpath'))

--table of files contained by the backup set

declare @dbFilesTab 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

,[IsReadOnly] bit

,[IsPresent] bit

,[TDEThumbprint] varbinary(32)

)

--table of new file named derived from the backup set

declare @dbNewFilesTab table

(

[LogicalName] nvarchar(128)

,[Ext] nvarchar(128)

,[Type] char(1)

,[Row] int

,[NewPhysicalName] nvarchar(260)

)

--get files in backup set

insert into @dbFilesTab exec('restore filelistonly from disk = ''$(PATH)''')

--determine the distinct list of file extensions in the backup set

declare @extensionsTab table ([Ext] nvarchar(3));

insert @extensionsTab select distinct right([PhysicalName],3) from @dbFilesTab

--derive new names for each physical file in the backup set for the new location

while(1=1)

begin

declare @ext nvarchar(3);

set @ext = null;

select top 1 @ext = [Ext] from @extensionsTab

if(@ext is not null)

begin

insert @dbNewFilesTab ([LogicalName],[Ext],[Type],[Row],[NewPhysicalName])

select

[LogicalName]

,[Ext]

,[Type]

,[Row]

,[NewPhysicalName] =

case when [Type] = 'L' then @defaultLogDir + '$(DB)' + convert(nvarchar(2),[Row]) + '.' + [Ext]

else @defaultDataDir + '$(DB)' + convert(nvarchar(2),[Row]) + '.' + [Ext]

end

from

(

select [LogicalName], [Ext] = right([PhysicalName],3), [Type], [Row] = row_number() over(order by[LogicalName])

from @dbFilesTab

where right([PhysicalName],3) = @ext

) as q1

delete @extensionsTab where [Ext] = @ext

end

else break;

end

--create a concatentated string of SQL that specifies the new names for each file

declare @moveSql nvarchar(max)

select @moveSql = stuff(

(

select ',move N''' + [LogicalName] + ''' TO N''' + [NewPhysicalName] + ''''

from @dbNewFilesTab

order by [Ext],[Row]

for xml path('')

),1,1,'')

declare @sql nvarchar(max);

set @sql = 'use [master]; restore database [$(DB)] from ' + @diskChunkSql + ' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 5, ' + @moveSql;

exec(@sql)

end try

begin catch

set @error = ERROR_MESSAGE();

end catch

--if target database exists, re-enable connections

begin try

ALTER DATABASE [$(DB)] SET MULTI_USER;

end try

begin catch

declare @dummy2 bit;

end catch

if(@error is not null) RAISERROR(@error,11,100)