By: Tim Smith | Updated: 2014-12-18 | Comments | Related: > Backup
Problem
We've looked at our current schedule for DBA tasks, such as checking database integrity, re-indexing, and backing up databases, and found that it fails to match when the users access it. We'd like to create a schedule structure with conservative time estimates and begin with backing up, as some of our other processes occur before backing up databases. Is there a way to automate timing and tracking processes, such as backing up a database to adjust a schedule if necessary later, when the database grows or when access to it begins to change?
Solution
When we inherit a new environment, I suggest timing the maintenance tasks, as it helps with the schedule and allows us to change our environment in a way that complements its use. Imagine the analogy of an athlete eating a structured breakfast prior to stretching and working out, and needing to complete the entire morning routine in a three hour window before classes begin. Without measurements in place, the athlete may exceed the three hours, and face a conflict by missing the classes for the day. Since the athlete's routines may change, knowing the time-cushion (if there's any) also offers the potential to allow growth of any of the pre-class activities, if the needs for breakfast, stretching, or working out change. Likewise, if an environment experiences down time (and this can change), I prefer to place my maintenance within this window if it doesn't relate to maintenance requiring heavy use (such as monitoring).
Let's use the example of tracking time while backing up databases. When interviewing DBAs, a great question to get a feel with how they view maintenance architecture is to ask, "What's your back up schedule?" and from there ask what made them choose that schedule and how the growth of the database (if applicable) altered that schedule related to other maintenance. DBAs know too well that obtaining an average (or median) will offer more accurate information because we seldom see consistent figures; instead, they generally fall within a range. Like an athlete timing a morning routine, we want to track a measurement before beginning and track a measurement when the process ends; this conservative estimate will be our maximum figure for that specific measurement.
Note: you can back up a database while its in use, as it does not cause blocking. This does not mean that backing up is a light operation or that you should backup a database while it's receiving heavy traffic. That being written, some environments experience no down time.
For initial tracking, we will perform a full database backup, using the option CHECKSUM, saving the start time as a timestamp before running the backup operation and a timestamp after the backup has finished. We will also capture the difference in seconds between the start and stop time because if we wanted to capture the results in minutes, we'd only need to divide by 60, or in hours, we'd divide by 3600. In the table below, I also add columns for the database name and ID. Depending on preference, since we have the option of using DB_ID() or DB_NAME() provided that the other is referenced, we could store one and not the other.
---- T-SQL Structure for table, tb_BackupLogging, in Logging database CREATE TABLE tb_BackupLogging( DatabaseID SMALLINT, DatabaseName VARCHAR(150), StartTime DATETIME, StopTime DATETIME, TimeDiff BIGINT ) ---- Example of backing up a database and logging the times (this is generated by PowerShell in the below script) BACKUP DATABASE AdventureWorks TO DISK = 'D:\20141028\AdventureWorks.BAK' WITH CHECKSUM INSERT INTO tb_BackupLogging VALUES (8,'AdventureWorks','10/28/2014 10:28:45',GETDATE(),DATEDIFF(SS,'10/28/2014 10:28:45',GETDATE()))
With the PowerShell function, we pass in the server ($server) that we want to backup the databases, the database for logging the start and stop information ($database), the SMO library location ($smolibrary), and the drive (and location) where we want to save the full backups to ($dev). When we execute the function, it will create a folder with today's date and place the backups in that folder on the specified location. For instance, if we wanted our backups to go to E:\Backups\, for the $dev parameter, we'd pass in "E:\Backups\" and a new folder will be created with today's date and all .BAK files will be placed into it.
Function Backup-DBs ($server, $database, $smolibrary, $dev)
{
Add-Type -Path $smolibrary
$nl = [Environment]::NewLine
$d = Get-Date -uFormat "%Y%m%d"
$fd = $dev + $d + "\"
New-Item $fd -ItemType Directory
$srv = New-Object Microsoft.SqlServer.Management.SMO.Server($server)
$scon = New-Object System.Data.SqlClient.SqlConnection
$scon.ConnectionString = "SERVER=" + $server + ";DATABASE=" + $database + ";Integrated Security=true"
$bklog = New-Object System.Data.SqlClient.SqlCommand
$bklog.Connection = $scon
foreach ($db in $srv.Databases | Where-Object {$_.Name -notlike "tempdb"})
{
$dn = $db.Name
$di = $db.ID
$now = Get-Date
$backup = "BACKUP DATABASE $dn TO DISK = '$fd" + "$dn.BAK' WITH CHECKSUM"
$backup += $nl + "INSERT INTO tb_BackupLogging VALUES ($di,'$dn','$now',GETDATE(),DATEDIFF(SS,'$now',GETDATE()))"
$bklog.CommandText = $backup
$bklog.CommandTimeout = 0
$scon.Open()
$bklog.ExecuteNonQuery() | Out-Null
$scon.Close()
}
$scon.Dispose()
$bklog.Dispose()
}
Backup-DBs -server "OURSERVER\OURINSTANCE" -database "Logging" -smolibrary "C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.Smo.dll" -dev "D:\"
If we keep a record of active processes, we can also measure how backup time increases or decreases, which also allows for us to change schedules, when necessary. The below example query measures how the TimeDiff changes from most recent backup, and when we look at our databases and active processes, we may decide that the schedule for some (or all) should change.
;WITH BAKDiff AS( SELECT DatabaseName , DENSE_RANK() OVER (ORDER BY CAST(StopTime AS DATE) DESC) AS DateID , CAST(StopTime AS DATE) BAKDate , TimeDiff FROM tb_BackupLogging ) SELECT t.DatabaseName , (t.TimeDiff - tt.TimeDiff) RecentChange_Seconds FROM BAKDiff t INNER JOIN BAKDiff tt ON t.DateID = (tt.DateID - 1) AND t.DatabaseName = tt.DatabaseName WHERE t.DateID = 1
While the above script backups all the databases on a server, we may determine, based on our findings, that a few of our databases should be backed up at different times than the others. The below derivative script of the above script, will only backup the database that we pass in with the name ($name) parameter, with the other parameters being the same as used in the above script:
Function Backup-DBs ($server, $database, $smolibrary, $dev, $name)
{
Add-Type -Path $smolibrary
$nl = [Environment]::NewLine
$d = Get-Date -uFormat "%Y%m%d"
$fd = $dev + $d + "\"
if ((Test-Path -Path $fd) -eq $false)
{
New-Item $fd -ItemType Directory
}
$srv = New-Object Microsoft.SqlServer.Management.SMO.Server($server)
$scon = New-Object System.Data.SqlClient.SqlConnection
$scon.ConnectionString = "SERVER=" + $server + ";DATABASE=" + $database + ";Integrated Security=true"
$bklog = New-Object System.Data.SqlClient.SqlCommand
$bklog.Connection = $scon
foreach ($db in $srv.Databases | Where-Object {$_.Name -eq "$name"})
{
$dn = $db.Name
$di = $db.ID
$now = Get-Date
$backup = "BACKUP DATABASE $dn TO DISK = '$fd" + "$dn.BAK' WITH CHECKSUM"
$backup += $nl + "INSERT INTO tb_BackupLogging VALUES ($di,'$dn','$now',GETDATE(),DATEDIFF(SS,'$now',GETDATE()))"
$bklog.CommandText = $backup
$bklog.CommandTimeout = 0
$scon.Open()
$bklog.ExecuteNonQuery() | Out-Null
$scon.Close()
}
$scon.Dispose()
$bklog.Dispose()
}
Backup-DBs -server "TIMOTHY\SQLEXPRESS" -database "Logging" -smolibrary "C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.Smo.dll" -dev "D:\" -name "durkah"Next Steps
- Depending on your environment, how much time do you have daily/weekly/monthly for maintenance tasks?
- How do you track this length of time and whether it grows?
- Using derivatives of the above tools, what other maintenance tasks would be useful to track?
- Check out these related tips
About the author
Tim Smith works as a DBA and developer and also teaches Automating ETL on Udemy.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2014-12-18
