SQL Server aggregates historical backup information to compare database growth, SQL Server

SQL Server aggregates historical backup information to compare database growth, SQL Server

Most of the time, when we plan the space for the SQL Server database or ask for storage space, we need to estimate the size of the database space to be applied, the simplest way to estimate the future is to look at past trends, which is usually the most reasonable method.

Generally, a well-maintained database requires regular baseline to know what is normal with the baseline. For example, if the blood pressure of some people is usually low, the pressure of 80 is abnormal. But the reality is that most systems do not have the habit of collecting baselines. Therefore, when planning space to see historical growth, there will be no accurate data in the past.

One solution is to view the historical backup size to view the past data growth trend. During database backup, the entire file is not backed up, but the allocated page is only backed up on the FPS page, the database may be 20 GB, but the backup is only 3 GB. Therefore, the database backup can be used as the basis for viewing data growth.

During each backup, the database records backup information in the msdb. dbo. backupset table. Therefore, you can view the following information to aggregate historical backup information and compare the growth of the database:

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

Code List 1. View historical data growth on a monthly basis

Result 1 is displayed.

Figure 1. Growth of historical data

In code list 1, replace database_name with the name of the database you want to view.

Note: Policy-based management is introduced after SQL Server 2008. A job is created by default in this policy, as shown in Figure 2. The job runs at every day and runs according to the policy (record retention days, 3) Clear expired history records.

Figure 2. view policy management

Figure 3. Retention days of history records. The default value is 0, which never clears

This policy creates a job (4 ).

Figure 4. History cleanup job

If the job or a manually created job clears the data in the msdb. dbo. backupset table, the results in code listing 1 may be affected.

Which of the following methods can be used for automatic database backup in SQL Server? Detailed description

/******************** Complete backup job ***************** **/-- full backup, USE MasterGOdeclare every week @ str varchar (100) set @ str = 'd: \ DBtext \ jgj \ DBABak \ FullBak '+ replace (convert (varchar, getdate (), 20), '-', ''),'', ''), ':','') + '. bak 'backup DATABASE [demo] to disk = @ strWITH RETAINDAYS = 15, NOFORMAT, NOINIT, NAME = n' Demo full BACKUP ', SKIP, NOREWIND, NOUNLOAD, STATS = 10GO

/******************* Differential backup job ***************** **/-- truncation log use MasterGOBACKUP log Demo WITH NO_LOGGO -- shrink LOG File USE DemoGODBCC SHRINKFILE (n'demo _ log ', 0, TRUNCATEONLY) GO -- Differential backup, once a day USE MasterGOdeclare @ str varchar (100) set @ str = 'd: \ DBtext \ jgj \ DBABak \ DiffBak '+ replace (convert (varchar, getdate (), 20 ),'-',''),'', ''), ':','') + '. diff 'backup DATABASE [Demo] to disk = @ strWITH DIFFERENTIAL, RETAINDAYS = 8, NOFORMAT, NOINIT, NAME = n' Demo differential backup ', SKIP, NOREWIND, NOUNLOAD, STATS = 10GO

/******************* Log backup job ****************** */-- log backup, USE DemoGOdeclare @ str varchar (100) set @ str = 'd: \ DBtext \ jgj \ DBABak \ logbak '+ replace (convert (varchar, getdate (), 20), '-', ''),'', ''), ':','') + '. trn 'backup LOG [Demo] to disk = @ strWITH RETAINDAYS = 3, NOFORMAT, NOINIT, NAME = n' Demo log backup ', SKIP, NOREWIND, NOUNLOAD, STATS = 10GO

-- Delete the expired backup file. declare @ str varchar (100), @ dir varchar (100), @ file ...... the remaining full text>

SQL query problems, Aggregate functions; SQL Server database

Statement.
Select. userID, B. userName, B. tel, SUM (. num) as Sum from A, B where. userID = B. userID group by. userID, B. userName, B. tel;