Creating and storing backup copies of databases in MS SQL. Practical advice

By occupation, I am a little database administrator. Anyway, I have to maintain a few dozen databases.
Stumbling upon an article published recently, considered it necessary to supplement it with some practical recommendations. What was touched upon in the last article has been omitted.

1. Backup size

You can estimate the size of the backup using the sp_spaceused stored procedure

For example, here’s the query:
')

 USE your_database; GO EXEC sp_spaceused @updateusage = N'TRUE'; GO 

runs much faster backup, and allows you to more or less accurately estimate the future size of the backup without compression.

If you use MS SQL 2008 or higher, I recommend that you use backup compression using SQL tools. The following code will enable default compression:

 USE master; GO EXEC sp_configure 'backup compression default', '1'; RECONFIGURE WITH OVERRIDE; 

This will reduce the time to create backups. For example, on one of our databases, about 290 GB in size, creating a full backup copy with compression enabled takes place exactly 2 times faster. And the size of the final file is 2 times larger than the size of the archive 7z (maximum compression) of the backup without initial compression. Oh, this magic number 2 . If we consider that the archiving process took 10 hours - the benefits are enormous.

2. Delete old backups

Another advantage of using SQL compression is that SQL remembers the backup history. Therefore, you can set the backup lifetime. You can delete old backups by writing a * .bat script and calling it directly in the service plan:

EXEC master..xp_cmdshell 'path for bat script

But in order for this thing to work, you need to execute the request once:

 -- To allow advanced options to be changed. EXEC sp_configure 'show advanced options', 1 GO -- To update the currently configured value for advanced options. RECONFIGURE GO -- To enable the feature. EXEC sp_configure 'xp_cmdshell', 1 GO -- To update the currently configured value for this feature. RECONFIGURE GO 

3. Notify administrator by mail

It is very convenient to receive an email after creating a backup. To add an e-mail operator, do the following:

 USE [msdb] GO EXEC msdb.dbo.sp_add_operator @name=N'Operator name', @enabled=1, @pager_days=0, @email_address=N'Operator e-mail', @pager_address=N'', @netsend_address=N'' GO 

In addition, you need to configure Database Mail. The easiest way to do this is through Management Studio and Database Mail of the same name.



Everything is simple enough. Further in the service plan we add the corresponding component.

4. If the transaction log is not truncated after the checkpoint

For large insert operations in the database, it makes sense to switch the recovery mode to simple, and after inserting the data back to full.
At me, sometimes, such feints are shown by the fact that the transaction log ceases to be truncated. In most cases, it is enough to make 2 times the transaction log backup. But in order not to read the tea leaves, I recommend to perform such a query:

 select log_reuse_wait_desc from sys.databases where name = 'your_database' 

In response, we get an answer about what kind of action SQL expects. If in response we get LOG_backup , then we make a backup of the log again.

5. What can you do at the same time?

If the database is large, it is difficult to set up a schedule so that neighboring operations would not overlap. And if they suddenly intersect, what is it fraught with?

Here is a crib to help you. The fat point indicates operations that cannot be performed simultaneously.



And do not forget to make backups of system databases. In particular, master and msdb.

Literature

• msdn.microsoft.com/en-ru/library/ms130214%28v=sql.105%29.aspx
• dba.stackexchange.com
• www.sql.ru

In the comments I propose to share their practical developments.

^{PS I'm new, if not in the hub post it - correct.}