: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<=@chunks)
begin
set @diskChunkSql = @diskChunkSql + 'DISK=''' + replace('$(PATH)','_[1','_[' + convert(nvarchar(10),@count)) + '''';
if(@count<@chunks and @chunks > 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)
