Cleveland DBA

SQL Server administration in Cleveland, OH

SQL Server A to Z – Backups

One of the most important, if not the most important tasks of the DBA is backing up your database.  The database backup is the most basic disaster recovery plan you can have in place.  It’s certainly the easiest to implement.  Today I’m going to go over the basics of backups, including the different types of backups and how they work.

Full Backup

A full backup of a database includes all data pages in the database and just enough of the transaction log to be able to recover up to the end time of the backup.  Curious as to all the internal steps involved in a full backup?  Turn on trace flags 3004 and 3605 using DBCC TRACEON (3004, 3605, -1) and perform a backup a backup.  Then take a look at your errorlog.  Just don’t forget to turn off the trace flags when you’re done, since they can cause some performance degradation in your backups.

Differential Backup

A differential backup is an incremental backup of all changed data since the last full backup.  The last full backup.  Not the last differential backup.  This is an important distinction to keep in mind because a lot of new DBAs (especially former Oracle DBAs) think that, in the event of a restore, they need to restore the full backup and then all of the differential backups taken after.  And that’s simply not the case.  You only need to restore the latest differential.

So how do differentials work?  SQL Server uses something called a Differential Change Map (DCM) page to track changes to extents.  The 6th page in every data file is a DCM page, and it tracks about 4GB worth of data.  So, for every 4GB of data in your database, there’s a corresponding DCM keeping watch.  Let’s take a quick look.

-- Start with a full backup
BACKUP DATABASE [AdventureWorks] TO  DISK = N'D:\SQL2008\Backup\AdventureWorks.bak' WITH NOFORMAT, INIT,  NAME = N'AdventureWorks-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

-- Look at the DCM
DBCC PAGE('adventureworks',1,6,3)

Scroll down through the results until you get to the DIFF MAP section. Your results should look something like this:

DIFF_MAP: Extent Alloc Status @0x000000000F51A0C2

(1:0) – (1:24) = CHANGED
(1:32) – (1:21752) = NOT CHANGED

Now let’s update some data and take another look.

-- update some data
update Production.TransactionHistory
set Quantity = 100

-- check out the differential map now
DBCC PAGE('adventureworks',1,6,3)

Again, scroll through the results to the DIFF MAP section. Now it probably looks more like this:

DIFF_MAP: Extent Alloc Status @0x000000000F51A0C2

(1:0) – (1:24) = CHANGED
(1:32) – (1:152) = NOT CHANGED
(1:160) – = CHANGED
(1:168) – (1:224) = NOT CHANGED
(1:232) – = CHANGED
(1:240) – (1:20096) = NOT CHANGED
(1:20104) – (1:20160) = CHANGED
(1:20168) – (1:20224) = NOT CHANGED
(1:20232) – (1:20288) = CHANGED

So by reading 1 page for every 4GB of data, SQL Server knows exactly what extents have been changed and therefore knows what extents need to be backed up. And that’s what makes differential backups so fast. If we were to perform a differential backup now and check the DCM page, we’d see no difference. However, if we performed a full backup, the DCM page would revert back to its original state.

Note that you can take a special type of full backup, called a Copy-Only backup that will not reset the DCM pages.  Use this if you need to take a one-off backup, say to refresh a Dev environment, without interrupting your normal backup plan.

File Backup

Individual database files can be backed up and restored using full or differential file backups.  This is helpful if only one data file is corrupted or lost and needs to be restored.  File backups can also make backing up very large databases more manageable.  But you really need to think through your backup/restore strategy if you’re using file backups.  Miss one file and you could render your database unrecoverable.  In my humble opinion, they’re generally not worth the administrative nightmare.

Partial backup

SQL Server 2005 introduced partial backups.  Like file backups, partial backups are helpful in very large databases, however they don’t entail quite so much administration.  A full or differential partial backup will back up all read/write filegroups in the database and, optionally, any read-only filegroups you specify.

Transaction Log Backup

If your database is in Full or Bulk-Logged recovery mode, you need to take transaction log backups.  A transaction log backup backs up the active portion of the transaction log, including all transactions since the last log backup.  If you’re using Bulk-Logged recovery, the log backup also includes the actual data pages for any minimally logged transactions.  Once a log backup is performed, that portion of the transaction log is freed up for reuse.  This prevents those pesky transaction log full (9002) errors.


July 11, 2011 Posted by | Features, General, Maintenance | , , , | Leave a comment

Scripting out a database

Switching gears a little bit, much of my attention this week has revolved around backups. We have several large databases that have gotten to the point where full backups every night have become unwieldy. So I’ve been redesigning the maintenance plans for them around a full backup once a week and differentials nightly. So far it’s working out very well, saving us a lot of time, disk, and tape. Woot!

While I was thinking about backups in general, I also started thinking about a question that’s come up before: how to script out all the database objects in an automated fashion. Whether it’s so you can create a second ’empty shell” copy of your database, or so you can restore a single stored procedure that you accidentally dropped, or just because it’s a development environment and you don’t care about the data so much as the object definitions. (Or because it’s just cool.)  Whatever the reason, I personally think this is something that could come in handy, and I hadn’t come across any way in SQL Server to automate the scripting a database.

So i did some searching and found a few third party tools (nah), some options that revolved around VB or C# or something (yeah, have I mentioned I’m not a programmer?). Then I came across a few partial solutions using powershell. Now, again me != programmer, but I can handle a little bit of powershell. They were pretty straightforward, but they weren’t quite… enough. So I took a little of each, threw in a few touches of my own, and voila, a solution that I’m pretty darn pleased with. Can it be improved upon? I fairly confident it can be. And probably will be. But in the meantime it gets the job done. And it can be scheduled either through a database job or via the Windows scheduler, which was the whole point.  And so, without further ado, I present… the script… (ooooooo!)

# SQLExportDBSchema.ps1
# Usage -
# From PS:  .\SQLExportDBSchema.ps1 "[output location]" "[Server]" "[Database]"
# From CMD: powershell -command "& 'D:\powershell\SQLExportDBSchema.ps1' "[output location]" "[Server]" "[Database]" "


[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

$filepath = $filepath+"\"

$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') $srv


$scrp = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($s)

$scrp.Options.AppendToFile = $False
$scrp.Options.ClusteredIndexes = $True
$scrp.Options.DriAll = $True
$scrp.Options.ScriptDrops = $False
$scrp.Options.IncludeHeaders = $True
$scrp.Options.IncludeIfNotExists = $True
$scrp.Options.ToFileOnly = $True
$scrp.Options.Indexes = $True
$scrp.Options.Triggers = $True
$scrp.Options.Permissions = $True

$outfile =$filepath+$database+"_create.sql"
$scrp.Options.FileName = $outfile

$outfile = $filepath+$database+"_security.sql"
$scrp.Options.FileName = $outfile
$scrp.Options.IncludeDatabaseRoleMemberships = $true
$scrp.Options.AppendToFile = $True

$scrp.Options.AppendToFile = $False
$scrp.Options.WithDependencies = $True

$outfile = $filepath+$database+"_partitioning.sql"
$scrp.Options.FileName = $outfile
$scrp.Options.AppendToFile = $True

$scrp.Options.AppendToFile = $False

$outfile = $filepath+$database+"_tables.sql"
$scrp.Options.FileName = $outfile

$outfile = $filepath+$database+"_triggers.sql"
$scrp.Options.FileName = $outfile

$outfile = $filepath+$database+"_views.sql"
$scrp.Options.FileName = $outfile
$views = $db.Views | where {$_.IsSystemObject -eq $false}

$outfile = $filepath+$database+"_functions.sql"
$scrp.Options.FileName = $outfile
$functions = $db.UserDefinedFunctions | where {$_.IsSystemObject -eq $false}

$outfile = $filepath+$database+"_storedProcs.sql"
$scrp.Options.FileName = $outfile
$storedprocs = $db.StoredProcedures | where {$_.IsSystemObject -eq $false}

$outfile = $filepath+$database+"_synonyms.sql"
$scrp.Options.FileName = $outfile

May 5, 2011 Posted by | Features, General, Maintenance | , , | 3 Comments