The method of aggregating historical backup information in SQL Server compared with database growth


Many times, when we plan the space of SQL Server database or ask for space from storage, we need to estimate the size of the required database space. The simplest way to estimate the future is to look at the past trend, which is usually the most reasonable way.

Generally speaking, a database with good operation and maintenance needs to do a regular baseline, with which we can know what is normal. A simple example, for example, some people’s blood pressure is usually low, then 80 low pressure is abnormal for him. But the reality is that most systems do not have the habit of collecting baselines, so when we need to plan space and want to see historical growth, there is no accurate data in the past.

One solution is to look at the data growth trend in the past by looking at the size of historical backup. When the database is backed up, the entire file will not be backed up, but only the assigned pages marked in the FPS page will be backed up. If the database is 20g, but the backup is only 3G, the database backup can be used as the basis for viewing the data growth.

Every time a database is backed up, it will record the backup related information in the msdb.dbo.backup table. Therefore, you can aggregate the historical backup information through the following views to compare the growth of the database:

[database_name] AS "Database",
DATEPART(month,[backup_start_date]) AS "Month",
AVG([backup_size]/1024/1024) AS "Backup Size MB"
FROM msdb.dbo.backupset
 [database_name] = N'Adventureworks2012'
AND [type] = 'D'
GROUP BY [database_name],DATEPART(mm,[backup_start_date]);

Code listing 1. View the growth of historical data by month

The results are shown in Figure 1.


Figure 1. Growth of historical data

The database in code listing 1_ Change the name to the name of the database you need to view.

Note: after SQL Server 2008, policy based management was introduced. The policy will create a job by default, as shown in Figure 2. The job will run at 2 o’clock every day, and the expired history will be cleaned up according to the policy (History retention days, as shown in Figure 3).


Figure 2. View policy management


Figure 3. History retention days, default to 0, never clean up

This strategy will create a job (as shown in Figure 4).


Figure 4. Cleaning up history jobs

If the data in the msdb.dbo.backup table is cleared by this job or a artificially created job, the result in code listing 1 may be affected to some extent.