Split backup method for SQL Server data backup file _mssql

When the database is fully backed up, we may sometimes experience an extreme situation, such as three c,d,e on a server with only 5G of space left

But what if it takes 12G of space to fully back up the business library?

Use filegroup backup? But the database does not make table partitions, there are no multiple filegroups, there is only one primary filegroup AH

This time we can use backup file segmentation

I use my own machine to demonstrate that I have a TEMP2 database on my machine with a database size of 1GB

Backup

We do a full backup of the TEMP2 database

Copy Code code as follows:

DECLARE @CurrentTime VARCHAR (m), @FileName VARCHAR () SET @CurrentTime = replace (replace (CONVERT) (VARCHAR, GETDATE (), (), '-', ' _ '), ', ' _ '), ': ', '--(TEMP2 Database full backup) SET @FileName = ' C:\Temp2_FullBackup_ ' + @CurrentTime + ' . bak ' BACKUP DATABASE [temp2]to disk= @FileName with FORMAT

You can see the 31MB size required

So how do I split backup files? The method is simple.

Just to Backup to C disk, now we back up to C and D disk

Copy Code code as follows:

DECLARE @CurrentTime VARCHAR (m), @FileName VARCHAR, @FileName2 VARCHAR SET @CurrentTime = replace (replace ( REPLACE (CONVERT (VARCHAR, GETDATE (), (), '-', ' _ '), ', ' _ '), ': ', '--(TEMP2 Database full backup) SET @FileName = ' C:\Temp2_Full Backup_partial1_ ' + @CurrentTime + '. Bak ' SET @FileName2 = ' D:\Temp2_FullBackup_Partial2_ ' + @CurrentTime + '. Bak ' BACKUP DATABASE [Temp2]to disk= @FileName, disk= @FileName2WITH FORMAT

C Disk

D Plate

You can see that each backup file has an average size of 16MB, and if it's divided into 3 backup files, divide by 3, which is the size of each backup file.

Of course, if you want to query the backup file information, regardless of the query which backup file can be queried

Copy Code code as follows:

Restore filelistonly from disk= ' C:\Temp2_FullBackup_Partial1_2014_12_19_150533.bak ' restore filelistonly from disk= ' D : \temp2_fullbackup_partial2_2014_12_19_150533.bak ' RESTORE headeronly from disk= ' C:\Temp2_FullBackup_Partial1_2014 _12_19_150533.bak ' RESTORE headeronly from disk= ' D:\Temp2_FullBackup_Partial2_2014_12_19_150533.bak '

Restores

Copy Code code as follows:

Use [Master]restore DATABASE [TEMP2] FROM disk = N ' D:\Temp2_FullBackup_Partial1_2014_12_19_150533.bak ', disk = N ' d:\ Temp2_fullbackup_partial2_2014_12_19_150533.bak ' with FILE = 1, move n ' Temp ' to N ' E:\DataBase\Temp2.mdf ', move n ' temp_l og ' to N ' E:\DataBase\Temp2_log.ldf ', Nounload, REPLACE, STATS = 5
Go

Restore time only need to specify all the backup split file path, of course, we generally in the server relocation will put these backup files together to the new server under the same letter, easy to restore

Instead of a C set, one put D plate, one put e disk

OK, we'll check the data.

Restored database no problem, you can call it a wrap.

Summarize

There are times when the server does not have enough space to put down a full backup file, but there is an urgent need for a full backup, so this can be done

Of course, you can also insert a mobile hard drive, the database backup to a mobile hard drive, but when you do cluster relocation, only remotely to the server to do backup, this approach is more useful