{"id":4152,"date":"2013-06-09T08:00:35","date_gmt":"2013-06-09T15:00:35","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/paul\/?p=4152"},"modified":"2017-04-13T11:40:38","modified_gmt":"2017-04-13T18:40:38","slug":"the-accidental-dba-day-9-of-30-backups-essential-backup-options","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/the-accidental-dba-day-9-of-30-backups-essential-backup-options\/","title":{"rendered":"The Accidental DBA (Day 9 of 30): Backups: Essential BACKUP Options"},"content":{"rendered":"

This month the SQLskills team is presenting a series of blog posts aimed at helping Accidental\/Junior DBAs ‘keep the SQL Server lights on’. It’s a little taster to let you know what we cover in our\u00a0Immersion Event for The Accidental\/Junior DBA<\/a>, which we present\u00a0several times each year<\/a>. You can find all the other posts in this series at\u00a0http:\/\/www.SQLskills.com\/help\/AccidentalDBA<\/a>. Enjoy!<\/em><\/p>\n

The BACKUP<\/em> command has a lot of different options so in today’s post I want to introduce a few of the most useful options to you.<\/p>\n

COMPRESSION<\/strong><\/p>\n

Backup compression was added in SQL Server 2008 in Enterprise Edition, and then in Standard Edition as well from SQL Server 2008 R2 onward. Not only can backup compression make your backups smaller, it can also speed up your backup operations AND speed up your restore operations. In that way, backup compression can be thought of as an availability feature, as anything that reduces downtime makes the data more available. The trade-off is that compressing and decompressing backups uses more CPU (you don’t get anything for free!) but most SQL Server systems these days are I\/O-bound rather than CPU-bound so this is usually not a problem.<\/p>\n

You can read more about my thoughts on backup compression in this post<\/a>, including whether to enable it by default for the entire SQL Server instance. If you’re going to use it, make sure that the backups you’re using it for actually get a decent compression ratio (e.g. 20% or above) otherwise the extra CPU being used isn’t worth it.<\/p>\n

COPY_ONLY<\/strong><\/p>\n

Once you’ve figured out what backups you need to satisfy your downtime and data-loss requirements (see the\u00a0post<\/a>\u00a0from day 8), you’ll either have manually created SQL Agent jobs to perform regular backups, used one of the free database maintenance solutions available online (like this one<\/a>, which I like to recommend), or created a database maintenance plan using SQL Server Management Studio.<\/p>\n

If at any time you want to take a one-off, out-of-band backup, you need to be very careful how you do it.\u00a0If \u00a0you perform a full backup, that full backup becomes the basis for subsequent differential backups. If you perform a log backup, that becomes part of the log backup chain. In both cases, these backups may be required during a disaster recovery operation.<\/p>\n

To avoid having one-off backups affecting your differential backups or your log backup chain, you can use the COPY_ONLY<\/em> option. For a full backup, this option makes the backup NOT become the basis for subsequent differential backups, and for a log backup, this option make the backup NOT become part of the log backup chain.<\/p>\n

On any database where automated, regular backups are being performed, always use the COPY_ONLY<\/em> option unless you have a very good reason not to. Better to be safe than sorry.<\/p>\n

DESCRIPTION and File Names<\/strong><\/p>\n

It’s a really good practice to ensure your backup files a descriptively named – e.g. 20130609_1330_SalesDB_Log.bak<\/em> – and that you set the DESCRIPTION<\/em> option appropriately. Using the DESCRIPTION<\/em> field is especially important if you perform a manual backup, so you have some idea of why the backup was taken and what it includes.<\/p>\n

Note that my example file name ended with .bak<\/em>. You can call your files anything you want, but .bak<\/em> or .bck<\/em> are commonly for data backups, and .trn<\/em> is commonly used for log backups.<\/p>\n

CHECKSUM<\/strong><\/p>\n

This option does two things:<\/p>\n

    \n
  1. As the data file pages are being read from the data files, their page checksums are validated. If an invalid checksum is found, by default the backup will fail and report a message indicating which page is corrupt.<\/span><\/li>\n
  2. Calculate a checksum over the entire backup and store it in the backup header.<\/li>\n<\/ol>\n

    As an aside, page checksums are a checksum written to each data file page just before it is written to disk and checked when a page is read from disk. It’s a way for SQL Server to determine that the I\/O subsystem has corrupted a page.<\/p>\n

    It’s extremely important that your backups are valid and do not contain corruption, so this option should definitely be used on all backups you perform, manual or automated. By ensuring that the backup contains valid pages when the backup is performed, the backup can be verified later with more extensive checking.\u00a0In tomorrow’s post I’ll discuss backup validation in more depth.<\/p>\n

    STATS<\/strong><\/p>\n

    This option makes the BACKUP<\/em> command print progress information as it executes. The default is to print every 10% through the operation and you can change that by using STATS = X<\/em>, where X is the percentage interval between progress prints. This is especially useful when you’re performing manual backups of large databases and you want some indication that progress is being made. Incidentally, this option also applies to the RESTORE<\/em> command too.<\/p>\n

    Summary<\/strong><\/p>\n

    This post has just scratched the surface – you can get more information on all the BACKUP<\/em> options from SQL Server Books Online here<\/a>. Make sure that you know what your backup commands are doing, and especially make sure that each backup is going to a new file, which is named descriptively, and that existing files are not being overwritten using the INIT<\/em> option.<\/p>\n

    Additionally, and we’re not covering it in this series but we do cover it in the Immersion Event, you should investigate the RESTORE<\/em> command (see Books Online here<\/a>) \u00a0and make sure you’ve tried basic restore operations – that is the reason you’re taking backups after all!<\/p>\n","protected":false},"excerpt":{"rendered":"

    This month the SQLskills team is presenting a series of blog posts aimed at helping Accidental\/Junior DBAs ‘keep the SQL Server lights on’. It’s a little taster to let you know what we cover in our\u00a0Immersion Event for The Accidental\/Junior DBA, which we present\u00a0several times each year. You can find all the other posts in […]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[13,24,31,35,52],"tags":[],"class_list":["post-4152","post","type-post","status-publish","format-standard","hentry","category-backuprestore","category-classes","category-database-maintenance","category-disaster-recovery","category-involuntary-dba"],"yoast_head":"\nThe Accidental DBA (Day 9 of 30): Backups: Essential BACKUP Options - Paul S. Randal<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/the-accidental-dba-day-9-of-30-backups-essential-backup-options\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"The Accidental DBA (Day 9 of 30): Backups: Essential BACKUP Options - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"This month the SQLskills team is presenting a series of blog posts aimed at helping Accidental\/Junior DBAs ‘keep the SQL Server lights on’. It’s a little taster to let you know what we cover in our\u00a0Immersion Event for The Accidental\/Junior DBA, which we present\u00a0several times each year. You can find all the other posts in […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/the-accidental-dba-day-9-of-30-backups-essential-backup-options\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2013-06-09T15:00:35+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-04-13T18:40:38+00:00\" \/>\n<meta name=\"author\" content=\"Paul Randal\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Paul Randal\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"5 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/the-accidental-dba-day-9-of-30-backups-essential-backup-options\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/the-accidental-dba-day-9-of-30-backups-essential-backup-options\/\",\"name\":\"The Accidental DBA (Day 9 of 30): Backups: Essential BACKUP Options - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2013-06-09T15:00:35+00:00\",\"dateModified\":\"2017-04-13T18:40:38+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/the-accidental-dba-day-9-of-30-backups-essential-backup-options\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/the-accidental-dba-day-9-of-30-backups-essential-backup-options\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/the-accidental-dba-day-9-of-30-backups-essential-backup-options\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"The Accidental DBA (Day 9 of 30): Backups: Essential BACKUP Options\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\",\"name\":\"Paul S. Randal\",\"description\":\"In Recovery...\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\",\"name\":\"Paul Randal\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"caption\":\"Paul Randal\"},\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/paul\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"The Accidental DBA (Day 9 of 30): Backups: Essential BACKUP Options - Paul S. Randal","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/paul\/the-accidental-dba-day-9-of-30-backups-essential-backup-options\/","og_locale":"en_US","og_type":"article","og_title":"The Accidental DBA (Day 9 of 30): Backups: Essential BACKUP Options - Paul S. Randal","og_description":"This month the SQLskills team is presenting a series of blog posts aimed at helping Accidental\/Junior DBAs ‘keep the SQL Server lights on’. It’s a little taster to let you know what we cover in our\u00a0Immersion Event for The Accidental\/Junior DBA, which we present\u00a0several times each year. You can find all the other posts in […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/the-accidental-dba-day-9-of-30-backups-essential-backup-options\/","og_site_name":"Paul S. Randal","article_published_time":"2013-06-09T15:00:35+00:00","article_modified_time":"2017-04-13T18:40:38+00:00","author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/the-accidental-dba-day-9-of-30-backups-essential-backup-options\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/the-accidental-dba-day-9-of-30-backups-essential-backup-options\/","name":"The Accidental DBA (Day 9 of 30): Backups: Essential BACKUP Options - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2013-06-09T15:00:35+00:00","dateModified":"2017-04-13T18:40:38+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/the-accidental-dba-day-9-of-30-backups-essential-backup-options\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/the-accidental-dba-day-9-of-30-backups-essential-backup-options\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/the-accidental-dba-day-9-of-30-backups-essential-backup-options\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"The Accidental DBA (Day 9 of 30): Backups: Essential BACKUP Options"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/","name":"Paul S. Randal","description":"In Recovery...","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce","name":"Paul Randal","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","caption":"Paul Randal"},"sameAs":["http:\/\/3.209.169.194\/blogs\/paul"],"url":"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/4152","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/comments?post=4152"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/4152\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=4152"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=4152"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=4152"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}