Partitioning and backup of SQL Server data backup files, SQL Server Data Backup

Partitioning and backup of SQL Server data backup files, SQL Server Data Backup

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.

Copy codeThe Code is as follows:
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.

Copy codeThe Code is as follows:
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

Copy codeThe Code is as follows:
RESTORE FileListOnly From Disk = 'C: \ Temp2_FullBackup_Partial1_2014_12_19_150533.bak 'RESTORE FileListOnly From Disk = 'd: \ comment '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

Copy codeThe Code is as follows:
USE [master] restore database [Temp2] from disk = n' D: \ Temp2_FullBackup_Partial1_2014_12_19_150533.bak ', DISK = n' D: \ comment' 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 = 5
GO

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.