|
1 |
originally posted 2015-01-21 |
A typical SQL Server backup uses one “DISK =” statement in the backup SQL, which creates a single-part backup. It’s possible to split backups into multiple parts, ostensibly to spread the data over multiple disks or tapes, or to create multiple smaller files that may be more manageable than a single large one. This is done by using multiple device statements in the backup TSQL like, “… DISK = ‘c:\backups\foo1.bak, DISK = c:\backups\foo2.bak, …”.
This example creates an out-of-band, overwriting backup that is split into nine parts.
|
1 2 3 4 5 6 7 8 9 10 11 |
BACKUP DATABASE [NTO] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\NTO_1.bak' ,DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\NTO_2.bak' ,DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\NTO_3.bak' ,DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\NTO_4.bak' ,DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\NTO_5.bak' ,DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\NTO_6.bak' ,DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\NTO_7.bak' ,DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\NTO_8.bak' ,DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\NTO_9.bak' WITH NOFORMAT, NOINIT, NAME = N'NTO-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10 |
This next query gets the number of parts of a given SQL Server backup. If you wish to create a new backup that overwrites the existing one, the number of parts (devices) used to create the latest backup must be the same as the existing backup. This TSQL gets the backup’s label data, which contains the number of parts into which the backup was originally split in the [FamilyCount] field. This query would return 9 for the above backup. If a backup was created with the 64 devices (the maximum), then the [FamilyCount] field will contain 64.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
declare @tab table ( [MediaName] nvarchar(max) ,[MediaSetId] uniqueidentifier ,[FamilyCount] int ,[FamilySequenceNumber] int ,[MediaFamilyId] uniqueidentifier ,[MediaSequenceNumber] int ,[MediaLabelPresent] int ,[MediaDescription] nvarchar(max) ,[SoftwareName] nvarchar(max) ,[SoftwareVendorId] int ,[MediaDate] datetime ,[MirrorCount] int ,[IsCompressed] bit ) declare @path nvarchar(max); set @path = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\NTO_1.bak'; begin try insert @tab exec('RESTORE LABELONLY FROM DISK=''' + @path + '''') end try begin catch declare @dummy bit; end catch declare @mediaFamilyCount int select @mediaFamilyCount = [FamilyCount] from @tab select [MediaFamilyCount] = @mediaFamilyCount |