Introduction:
The SQL Server backup provides an important solution for protecting critical data that is stored in SQL databases. And in order to minimize the risk of data loss, you need to make sure that you back up your databases regularly taking into consideration the changes applied to your data. It is a best practice to test your backups by restoring random backup files to a test environment and check that the backup files are not corrupted.
In addition to the normal disaster of data loss, the DBA can benefits from the backups if there is a media failure in one of the disks or any hardware damage, an accidental drop or delete applied by one of the users or usually copy the data from one server to another one for purposes such as setting up mirroring site or Always On Availability Groups.
Before scheduling the backup job, you need to have an estimation of how much disk space will be used by the database full backup. Also, you need to have an initial estimation of the database size increment, because when the database size increases, the full database backups will require more storage space.
It is better first to estimate how much disk space requires for your database full database backup. The backup operation copies the data in the database to the backup file, this contains only the used data space only in your database and not any unused one. Which is usually smaller than the database size. In order to estimate the size of the database full backup you can use the sp_spaceused system stored procedure, that will display the number of rows, disk space reserved, and disk space used by a table, indexed view, or displays the disk space reserved and used by the database depending on the parameters.
SQL Server offers many backup types, which depends on the recovery model of the database – that controls how the transaction log is managed in your database: Full backups, Differential backups, File backups, Filegroup backups and Transaction log backups.
As a database administrator, you should make sure that each database is backed up successfully and on time. SQL Server backup is simple to be performed via SQL Server Management Studio, using T-SQL BACKUP DATABASE command or PowerShell command Backup-SqlDatabase cmdlet. But in the case of large databases, it takes you long time to complete the backup process, you don’t have enough space to complete the backup to a particular drive or it is difficult to copy this large backup file via the network or to the backup media.
What should be done to make sure that the database is backed up completely on time in such cases?
A possible idea to overcome the large database backup space and time issues is to divide the database backup to multiple files and reducing the time necessary to perform the database backups and to use the available space on multiple drives. What make this process faster is that you have the ability to write to multiple files at the same time and therefore split up the workload using multiple threads, as well as having smaller files that can be moved across the network or copied to the backup media. Another advantage of splitting the backup to multiple files is getting better I/O throughput.
In order to perform the backup process to multiple files from the SQL Server Management Studio, right-click your database, choose backup from the tasks list as below:
In the Backup Databases dialog box below, specify where to keep the database backup files by clicking on the Add button, under Destination. What differs here is that we need to specify more than one file to back up the SQLShackDemo database to. In our example, the first backup file resides on the D drive, and the second backup file resides on the C drive, where each backup file will be of identical size.
If you are using SQL Server 2008 Enterprise Edition or SQL Server 2008 R2 or SQL Server 2012 Standard and Enterprise Editions, you can even take benefits of the Database Backup Compression feature to reduce the database backup size, from Options tap as follows:
In the Set backup compression option, you can choose Use the default server setting which will take the setting already set in the backup compression default server-configuration option. Or choose Compress backup that will compress the backup, regardless of the server-level default, or finally choose Do not compress backup which will create an uncompressed backup, regardless of the server-level default.
From the above Backup dialog screen, click OK button to start the multiple files backup for the database. A dialog box will appear once the backup is finished successfully.
You can perform the same backup process to multiple files using the below T-SQL script:
|
1 2 3 4 5 6 7 8 |
BACKUP DATABASE [SQLShackDemo] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\SQLShackDemo.bak', DISK = N'D:\ Backup\SQLShackDemo2.bak' WITH NOFORMAT, NOINIT, NAME = N'SQLShackDemo-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10 GO |
Now, let’s try to restore our database from the multiple backup files we took previously. From the SQL Server Management Studio; right-click Databases node and select the Restore Database option. In the Restore Database dialog box below, Specify the Source where the backup files located and select the checkbox under Restore plan. Here you need to specify both backup files to restore the database successfully.
If you don’t specify all the backup files which the full backup process generated, you would end up with the error message displayed below:
Finally, click OK in the Restore Database dialog box to restore the database from the specified backup files. A dialog box will appear once the restore is completed successfully.
Using the T-SQL script below, you can perform the database restore process from multiple files applied previously:
|
1 2 3 4 5 6 7 |
USE [master] RESTORE DATABASE [SQLShackDemo] FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\SQLShackDemo.bak', DISK = N'D:\Backup\SQLShackDemo2.bak' WITH FILE = 1, NOUNLOAD, STATS = 5 GO |
Test the backup to multiple files to check how much faster you can get your backups to run and that it is easier to copy it to a network path or tape drive.
You can have test backup and restore scenario on a production database by using the copy-only backup option. A copy-only backup is a SQL Server backup option that is independent of the database backup sequence, which you can use to take a database backup without affecting the overall backup operation for your database.
Useful links:
He is a Microsoft Certified Solution Expert in Data Management and Analytics, Microsoft Certified Solution Associate in SQL Database Administration and Development, Azure Developer Associate and Microsoft Certified Trainer.
Also, he is contributing with his SQL tips in many blogs.
View all posts by Ahmad Yaseen
- Azure Data Factory Interview Questions and Answers - February 11, 2021
- How to monitor Azure Data Factory - January 15, 2021
- Using Source Control in Azure Data Factory - January 12, 2021