{"id":4789,"date":"2015-05-18T14:57:27","date_gmt":"2015-05-18T12:57:27","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/using-vss-snapshots-with-sql-server-part-i\/"},"modified":"2015-05-18T14:57:27","modified_gmt":"2015-05-18T12:57:27","slug":"using-vss-snapshots-with-sql-server-part-i","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/using-vss-snapshots-with-sql-server-part-i\/","title":{"rendered":"Using VSS snapshots with SQL Server – part I"},"content":{"rendered":"
\"\"<\/div>\n

 <\/p>\n

This is probably a series of blog posts about some thoughts concerning VSS snapshots with database servers. Let\u2019s begin with this first story:<\/p>\n

Some time ago, I implemented a backup strategy at one of my customers based on FULL \/ DIFF and log backups. No issues during a long time but one day a call of my customer who told me that since some days, the differential backup didn\u2019t work anymore with the following error message:<\/p>\n

 <\/p>\n

Msg 3035, Level 16, State 1, Line 1<\/em><\/span><\/div>\n
Cannot perform a differential backup for database “demo”, because a current database backup does not exist. Perform a full database backup by reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option.<\/em><\/span><\/div>\n
Msg 3013, Level 16, State 1, Line 1<\/em><\/span><\/div>\n
BACKUP DATABASE is terminating abnormally.<\/em><\/span><\/div>\n

 <\/p>\n

After looking at the SQL Server error log message I was able to find out some characteristic entries:<\/p>\n

I\/O is frozen on database demo. No user action is required. However, if I\/O is not resumed promptly, you could cancel the backup.<\/em><\/div>\n

…<\/p>\n

I\/O was resumed on database demo. No user action is required.<\/em><\/div>\n

Just in case, did you have implemented a snapshot of your database server? And effectively the problem came from the implementation of Veeam backup software for bare-metal recovery purpose. In fact after checking out the Veeam backup software user guide, I noticed that my customer forgot to switch the transaction log option value to the \u201cperform backup only\u201d with application-aware image processing method.<\/p>\n

This is a little detail that makes the difference here. Indeed, in this case, Veeam backup software relies on VSS framework and using process transaction log option doesn\u2019t preserve the chain of full\/differential backup files and transaction logs. For those who like internal stuff you can interact with the VSS writers by specifying some options during the initialization of the backup dialog. The requestor may configure VSS_BACKUP_TYPE option by using the IVssBackupComponents interface and SetBackupState method.<\/p>\n

In this case, configuring the \u201cperform backup only\u201d means that Veeam backup software will specify to the SQL writer to use the option VSS_BT_COPY rather than VSS_BT_FULL to preserve the log of the databases. There are probably other specific tools that will run on the same way, so you will have to check outeach related user guide.<\/p>\n

Let\u2019s demonstrate the kind of issue you mayface in this case.<\/p>\n

First let\u2019s perform a full database backup as follows:<\/p>\n

 <\/p>\n

BACKUP DATABASE demo TO DISK = 'C:\\Program Files\\Microsoft SQL Server\\MSSQL11.MSSQLSERVER\\MSSQL\\Backup\\demo.bak' WITH INIT, STATS = 10;<\/code><\/p>\n

 <\/p>\n

Next, let\u2019s take a snapshot. If you take a look at the SQL Server error log you will find the related entries that concern I\/O frozen an I\/O resume operations for your databases.<\/p>\n

Moreover, thereis another way to retrieve snapshot events. Let\u2019s have a look at the msdb.dbo.backupset table. You can identify a snapshot by referring to the is_snapshot column value
\nUSE msdb;
\nGO
\nSELECT
\nbackup_start_date,
\nbackup_finish_date,
\ndatabase_name,
\nbackup_set_id,
\ntype,
\ndatabase_backup_lsn,
\ndifferential_base_lsn,
\ncheckpoint_lsn,
\nfirst_lsn,
\nlast_lsn,
\nis_snapshot
\nFROM msdb.dbo.backupset
\nWHERE database_name = 'demo'
\norder by backup_start_date DESC;<\/code>
\n\"blog_43_-_1_-_backup_history\"<\/p>\n

\u2026 and this time the backup failed with the following error message:<\/p>\n

Msg 3035, Level 16, State 1, Line 1<\/em><\/span><\/div>\n
Cannot perform a differential backup for database “demo”, because a current database backup does not exist. Perform a full database backup by reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option.<\/em><\/span><\/div>\n
Msg 3013, Level 16, State 1, Line 1<\/em><\/span><\/div>\n
BACKUP DATABASE is terminating abnormally.<\/em><\/span><\/div>\n

 <\/p>\n

In fact, the differential database backup relies on the last full database backup (most recent database_backup_lsnvalue) which is a snapshot and a non-valid backup in this case.<\/p>\n

Probably the best advice I may provide here is to double check potential conflicts you may get from your existing backup processes and additional stuff like VSS snapshots. The good thing is that one of my other customersthat uses Veeam backup software was aware of this potential issue but we had to deal with other interesting issue. I will discuss about it to the next blog post dedicated to VSS snapshots.<\/p>\n

By David Barbarin<\/p>\n","protected":false},"excerpt":{"rendered":"

  This is probably a series of blog posts about some thoughts concerning VSS snapshots with database servers. Let\u2019s begin with this first story: Some time ago, I implemented a backup strategy at one of my customers based on FULL \/ DIFF and log backups. No issues during a long time but one day a […]<\/p>\n","protected":false},"author":26,"featured_media":3556,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[198],"tags":[237,51,577,578],"type_dbi":[],"class_list":["post-4789","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-database-management","tag-snapshot","tag-sql-server","tag-veeam","tag-vss"],"acf":[],"yoast_head":"\nUsing VSS snapshots with SQL Server - part I - dbi Blog<\/title>\n<meta name=\"description\" content=\"Using VSS snapshot with SQL Server - part I\" \/>\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.dbi-services.com\/blog\/using-vss-snapshots-with-sql-server-part-i\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Using VSS snapshots with SQL Server - part I\" \/>\n<meta property=\"og:description\" content=\"Using VSS snapshot with SQL Server - part I\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/using-vss-snapshots-with-sql-server-part-i\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2015-05-18T12:57:27+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/2e1ax_default_entry_SQLServer_20140326-103734_1.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"473\" \/>\n\t<meta property=\"og:image:height\" content=\"164\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"Microsoft Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Microsoft Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/using-vss-snapshots-with-sql-server-part-i\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/using-vss-snapshots-with-sql-server-part-i\/\"},\"author\":{\"name\":\"Microsoft Team\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/bfab48333280d616e1170e7369df90a4\"},\"headline\":\"Using VSS snapshots with SQL Server – part I\",\"datePublished\":\"2015-05-18T12:57:27+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/using-vss-snapshots-with-sql-server-part-i\/\"},\"wordCount\":641,\"commentCount\":0,\"image\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/using-vss-snapshots-with-sql-server-part-i\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/2e1ax_default_entry_SQLServer_20140326-103734_1.jpg\",\"keywords\":[\"Snapshot\",\"SQL Server\",\"Veeam\",\"VSS\"],\"articleSection\":[\"Database management\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/using-vss-snapshots-with-sql-server-part-i\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/using-vss-snapshots-with-sql-server-part-i\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/using-vss-snapshots-with-sql-server-part-i\/\",\"name\":\"Using VSS snapshots with SQL Server - part I - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/using-vss-snapshots-with-sql-server-part-i\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/using-vss-snapshots-with-sql-server-part-i\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/2e1ax_default_entry_SQLServer_20140326-103734_1.jpg\",\"datePublished\":\"2015-05-18T12:57:27+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/bfab48333280d616e1170e7369df90a4\"},\"description\":\"Using VSS snapshot with SQL Server - part I\",\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/using-vss-snapshots-with-sql-server-part-i\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/using-vss-snapshots-with-sql-server-part-i\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/using-vss-snapshots-with-sql-server-part-i\/#primaryimage\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/2e1ax_default_entry_SQLServer_20140326-103734_1.jpg\",\"contentUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/2e1ax_default_entry_SQLServer_20140326-103734_1.jpg\",\"width\":473,\"height\":164},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/using-vss-snapshots-with-sql-server-part-i\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Using VSS snapshots with SQL Server – part I\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/bfab48333280d616e1170e7369df90a4\",\"name\":\"Microsoft Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g\",\"caption\":\"Microsoft Team\"},\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/microsoft-team\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Using VSS snapshots with SQL Server - part I - dbi Blog","description":"Using VSS snapshot with SQL Server - part I","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.dbi-services.com\/blog\/using-vss-snapshots-with-sql-server-part-i\/","og_locale":"en_US","og_type":"article","og_title":"Using VSS snapshots with SQL Server - part I","og_description":"Using VSS snapshot with SQL Server - part I","og_url":"https:\/\/www.dbi-services.com\/blog\/using-vss-snapshots-with-sql-server-part-i\/","og_site_name":"dbi Blog","article_published_time":"2015-05-18T12:57:27+00:00","og_image":[{"width":473,"height":164,"url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/2e1ax_default_entry_SQLServer_20140326-103734_1.jpg","type":"image\/jpeg"}],"author":"Microsoft Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Microsoft Team","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/using-vss-snapshots-with-sql-server-part-i\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/using-vss-snapshots-with-sql-server-part-i\/"},"author":{"name":"Microsoft Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/bfab48333280d616e1170e7369df90a4"},"headline":"Using VSS snapshots with SQL Server – part I","datePublished":"2015-05-18T12:57:27+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/using-vss-snapshots-with-sql-server-part-i\/"},"wordCount":641,"commentCount":0,"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/using-vss-snapshots-with-sql-server-part-i\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/2e1ax_default_entry_SQLServer_20140326-103734_1.jpg","keywords":["Snapshot","SQL Server","Veeam","VSS"],"articleSection":["Database management"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/using-vss-snapshots-with-sql-server-part-i\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/using-vss-snapshots-with-sql-server-part-i\/","url":"https:\/\/www.dbi-services.com\/blog\/using-vss-snapshots-with-sql-server-part-i\/","name":"Using VSS snapshots with SQL Server - part I - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/using-vss-snapshots-with-sql-server-part-i\/#primaryimage"},"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/using-vss-snapshots-with-sql-server-part-i\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/2e1ax_default_entry_SQLServer_20140326-103734_1.jpg","datePublished":"2015-05-18T12:57:27+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/bfab48333280d616e1170e7369df90a4"},"description":"Using VSS snapshot with SQL Server - part I","breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/using-vss-snapshots-with-sql-server-part-i\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/using-vss-snapshots-with-sql-server-part-i\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/using-vss-snapshots-with-sql-server-part-i\/#primaryimage","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/2e1ax_default_entry_SQLServer_20140326-103734_1.jpg","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/2e1ax_default_entry_SQLServer_20140326-103734_1.jpg","width":473,"height":164},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/using-vss-snapshots-with-sql-server-part-i\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Using VSS snapshots with SQL Server – part I"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/bfab48333280d616e1170e7369df90a4","name":"Microsoft Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/c44a1a792c059f24055763aa77d80a244467f6eef724a8bd13db8d4a350b7a4c?s=96&d=mm&r=g","caption":"Microsoft Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/microsoft-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/4789","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/26"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=4789"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/4789\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media\/3556"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=4789"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=4789"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=4789"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=4789"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}