Creation and storage of database backups in MS SQL. Practical tips

I am a little database administrator by occupation. One way or another, I have to maintain several dozen databases.
Having stumbled upon an article recently published, I considered it necessary to supplement it with some practical recommendations. What was touched upon in the last article was omitted in this one.

1. Backup size

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

For example, here’s a query like this:

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

It is performed much faster than backup, and allows you to more or less accurately assess 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. The following code will enable compression by default:

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 with compression enabled is exactly 2 times faster. And the size of the final file comes out 2 times larger than the size of the 7z archive (maximum compression) of the backup without initial compression. Oh, that magic number 2 . Given that the archiving process took 10 hours, the benefits are huge.

2. Delete old backups

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

EXEC master..xp_cmdshell 'path for bat script

But 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 operator’s email, do this:

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 must configure the Database Mail component. The easiest way to do this is through Management Studio and the Database Mail component of the same name



. Everything is quite simple there. Next, in the maintenance plan, add the appropriate component.

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

With large insert operations in the database, it makes sense to switch the recovery mode to simple, and after inserting the data again to full.
At me, sometimes, such feints are manifested by the fact that the transaction log ceases to be truncated. In most cases, it is enough to backup the transaction log 2 times. But in order not to guess on the coffee grounds, I recommend that you complete this request:

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

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

5. What can be done at the same time?

If the database is large, it’s hard to schedule it so that neighboring operations do not overlap. And if they suddenly intersect, what is the danger?

Here's a cheat sheet to help you. A thick dot indicates operations that cannot be performed at the same time.



And do not forget to backup system databases. In particular, master and msdb.

Literature

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

In the comments, I suggest sharing his practical work.

^{PS I'm new, if I didn’t post to the wrong hub - correct it.}