Inconspicuous feature backup file segmentation of SQLSERVER

An inconspicuous feature of SQLSERVER backup file segmentation when fully backing up the database, we may sometimes encounter an extreme situation, such as the C, D, E has only 5 GB space left for all three drive letters, but what should we do if we need 12 GB space for full backup of the Business Database? Is File Group Backup used? However, no table is created in the database.

An inconspicuous feature of SQLSERVER backup file segmentation when fully backing up the database, we may sometimes encounter an extreme situation, such as the C, D, E has only 5 GB space left for all three drive letters, but what should we do if we need 12 GB space for full backup of the Business Database? Is File Group Backup used? However, no table is created in the database.

Inconspicuous feature backup file segmentation of SQLSERVER

When backing up a complete database, we may sometimes encounter an extreme situation where only 5 GB of space is left for the three drive letters C, D, and E on the server.

But what if we need 12 GB of space to completely back up the business database?

Is File Group Backup used? However, the database does not partition tables and there are no multiple file groups. There is only one primary file group.

In this case, we can useBackup File Segmentation

I use my own machine to demonstrate that my machine has a Temp2 database with a database size of 1 GB.

Backup

We make a full backup of the Temp2 database.

DECLARE @ CurrentTime VARCHAR (50), @ FileName VARCHAR (200) SET @ CurrentTime = REPLACE (CONVERT (VARCHAR, GETDATE (), 120 ),'-', '_'), '', '_'), ':','') -- (full backup of Temp2 database) SET @ FileName = 'C: \ Temp2_FullBackup _ '+ @ CurrentTime + '. bak 'backup DATABASE [Temp2] to disk = @ FileName WITH FORMAT

We can see that the size of 31 MB is required.

So how to split the backup file? The method is simple.

Back up disk C. Now we back up disk C and disk D.

DECLARE @ CurrentTime VARCHAR (50), @ FileName VARCHAR (200), @ FileName2 VARCHAR (200) SET @ CurrentTime = REPLACE (CONVERT (VARCHAR, GETDATE (), 120), '-', '_'), ',' _ '),': ',') -- (full backup of Temp2 database) SET @ FileName = 'C: \ Temp2_FullBackup_Partial1 _ '+ @ CurrentTime + '. bak 'set @ FileName2 = 'd: \ temp2_fullbackup_partial _ '+ @ CurrentTime + '. bak 'backup DATABASE [Temp2] to disk = @ FileName, DISK = @ FileName2WITH FORMAT

Drive C

Disk D

We can see that the size of each backup file is average, which is 16 MB. If it is divided into three backup files, it is divided by 3, that is, the size of each backup file.

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

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'

Restore

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_log' TO N'E:\DataBase\Temp2_log.ldf',  NOUNLOAD,  REPLACE,  STATS = 5GO

During restoration, you only need to specify the path of all backup split files. Of course, when the server is relocated, we usually put these backup files together under the same drive letter of the new server, easy recovery

Instead of a C disk, a D disk, or an e disk.

Restored. Let's query the data.

There is no problem with the restored database. You can close the job.

Summary

Sometimes, when the space of any drive letter on the server is insufficient to put a complete backup file, but a complete backup is urgently needed, this method can be used.

Of course, you can also insert a mobile hard disk to back up the database to a mobile hard disk. However, when you migrate a cluster, you can only remotely back up the database to the server, this method is more useful.

If anything is wrong, you are welcome to make a brick o