{"id":615,"date":"2014-02-11T16:22:47","date_gmt":"2014-02-11T16:22:47","guid":{"rendered":"http:\/\/elbsolutions.com\/projects\/?p=615"},"modified":"2022-02-03T11:25:03","modified_gmt":"2022-02-03T17:25:03","slug":"smo-sql-backup-sorts","status":"publish","type":"post","link":"https:\/\/elbsolutions.com\/projects\/smo-sql-backup-sorts\/","title":{"rendered":"SMO for SQL Backup &#8230; of sorts"},"content":{"rendered":"[Latest Edit Feb 14th] So, I go to edit a few Stored Procs on my MSSQL database and&#8230; errors &#8211; table missing &#8211; what? I seems over the weekend, all the databases were reset to some time in the past &#8211; this means 30 SP&#8217;s and 20 tables on our dev environment have &#8211; poof vanished &#8211; an honest mistake due to a communication misunderstanding. Not a big deal &#8211; everything is version controlled. <strong>It was the testing data that was lost<\/strong> and that people are semi-using at the moment that really gets my goat and makes me want to pull my hair out. I had a set of data that was perfect for making the next module.<\/p>\n<p>So I searched for backup routines and since I use c# and think the SMO addition is so cool, I found some more great links &#8211; and the code worked in &lt; 10 minutes each.<!--more--><\/p>\n<ul>\n<li><a href=\"http:\/\/technet.microsoft.com\/en-us\/library\/microsoft.sqlserver.management.smo.backup.sqlbackup.aspx\" target=\"_blank\" rel=\"noopener noreferrer\">true backup in VB<\/a> &#8211; in c# below (my hacked up code below #2 except)<\/li>\n<li><a href=\"http:\/\/stackoverflow.com\/a\/13236447\" target=\"_blank\" rel=\"noopener noreferrer\">scripting the data from the tables<\/a>\u00a0(this is so SUPER easy) (code copied below #1 excerpt [Feb14th modified to reduce out of mem error])\u00a0<strong>**preferred for my purposes**<\/strong><\/li>\n<\/ul>\n<p>Next is to put this into Scheduled Task (<a title=\"Windows Task Scheduler \u2013 a consistent way to execute exe files\" href=\"http:\/\/elbsolutions.com\/projects\/windows-task-scheduler-a-consistent-way-to-execute-exe-files\/\">you HAVE to read my blog to set this up quickly<\/a> &#8211; or you could spend hours getting to the conclusion by the school of hard knocks.)\u00a0I had to take a copy of the solution on my LOCAL drive- not a network copy <strong>or else the scheduled tasks does not work.<\/strong><\/p>\n<p><!--more-->I have to admit, the using the SMO tool in c# or .net is SOOOO easy to use. I will admit, that I use c# to whip up a script here and there and keep fiddling with it forever. I also always keep code on a network drive AND use a repository &#8211; love gitHub.<\/p>\n<h2>Scripting the tables<\/h2>\n<p>(<a href=\"http:\/\/stackoverflow.com\/a\/13236447\" target=\"_blank\" rel=\"noopener noreferrer\">link for full code<\/a>) &#8230; AND they included the USING parts of the code at the top. This is the important bit. How easy was that? Poof- done!<\/p>\n<pre class=\"brush: csharp; title: ; notranslate\" title=\"\">\r\n\/\/Using statements in the blog down below\r\n\/\/ Feb 14th - changed from a string builder in memory to a file writer as large tables produce an out of memory error (of course - hindsight 20\/20)&lt;\/pre&gt;\r\n\/\/var output = new StringBuilder(); \/\/1\r\n if ((machineName = myargs.Item(&quot;scripttables&quot;)) != null)\r\n {\r\n string outputFileName = @&quot;data\\bley.&quot; + db.Name + &quot;.&quot; + obj.Schema + &quot;.&quot; + obj.Name.Replace(&quot;tbl&quot;, &quot;data&quot;) + &quot;.sql&quot;;\/\/2\r\n System.IO.StreamWriter file = new System.IO.StreamWriter(outputFileName);\/\/2\r\n\r\nvar scripter = new Scripter(srv) { Options = { ScriptData = true } };\r\n var script = scripter.EnumScript(new SqlSmoObject&#x5B;] { obj });\r\n foreach (var line in script)\r\n file.WriteLine(line);\r\n \/\/output.AppendLine(line);\r\n\r\n\/\/File.WriteAllText(outputFileName, output.ToString()); \/\/1\r\n file.Close(); \/\/\r\n Console.WriteLine(@&quot;Data Scripted \/ Backed up Successfully to data directory for &quot; + obj.Name);\r\n\r\n&amp;nbsp;\r\n\r\n }\r\n&lt;pre&gt;\r\n<\/pre>\n<h2>Full Backup Script<\/h2>\n<p>(was translated to c# and made into a sub routine\/method &#8211; originally in <a href=\"http:\/\/technet.microsoft.com\/en-us\/library\/microsoft.sqlserver.management.smo.backup.sqlbackup.aspx\" target=\"_blank\" rel=\"noopener noreferrer\">VB at this link<\/a>)<\/p>\n<pre class=\"brush: csharp; title: ; notranslate\" title=\"\">\r\nusing System;\r\nusing System.Collections.Generic;\r\nusing System.Linq;\r\nusing System.Text;\r\nusing Microsoft.SqlServer.Management.Smo;\r\nusing Microsoft.SqlServer.Management.Smo.SqlEnum;\r\nusing System.Configuration;\r\nusing System.Collections.Specialized;\r\nusing System.Text.RegularExpressions;\r\nusing System.IO;\r\nusing System.IO.Compression;\r\nusing System.IO.Compression;\r\nusing GetArguments;\r\n\r\nstatic void BackupTables(Server srv, Database db, string dBName)\r\n {\r\n\r\n \/\/Connect to the local, default instance of SQL Server.\r\n\/\/\/ Server srv = new Server();\r\n \/\/Reference the AdventureWorks2012 database.\r\n\/\/ Database db = srv.Databases&#x5B;dBName];\r\n \/\/Store the current recovery model in a variable.\r\n \/\/ int recoverymod = db.DatabaseOptions.RecoveryModel;\r\n\r\n \/\/Define a Backup object variable.\r\n Backup bk = new Backup();\r\n \/\/Specify the type of backup, the description, the name, and the database to be backed up.\r\n bk.Action = BackupActionType.Database;\r\n bk.BackupSetDescription = &quot;Full backup of &quot; + dBName;\r\n bk.BackupSetName = dBName +&quot;_Backup&quot;;\r\n bk.Database = dBName;\r\n\r\n \/\/Declare a BackupDeviceItem by supplying the backup device file name in the constructor, and the type of device is a file.\r\n BackupDeviceItem bdi = new BackupDeviceItem(&quot;Bleys_Automated_AutoPLANTInteraction_Full_Backup&quot;, DeviceType.File);\r\n \/\/Add the device to the Backup object.\r\n bk.Devices.Add(bdi);\r\n \/\/Set the Incremental property to False to specify that this is a full database backup.\r\n bk.Incremental = false;\r\n \/\/Set the expiration date.\r\n DateTime backupdate = new DateTime(2006, 10, 5);\r\n bk.ExpirationDate = backupdate;\r\n \/\/Specify that the log must be truncated after the backup is complete.\r\n bk.LogTruncation = BackupTruncateLogType.Truncate;\r\n \/\/'Run SqlBackup to perform the full database backup on the instance of SQL Server.\r\n bk.SqlBackup(srv);\r\n }\r\n\r\n<\/pre>\n<p>Zip the files just created<\/p>\n<p>And my favourite part &#8211; zipping the files one they have been created<\/p>\n<pre class=\"brush: csharp; title: ; notranslate\" title=\"\">\r\n\r\nDateTime filenameTimeStamp = DateTime.Now;\r\n string fileTSstring = filenameTimeStamp.ToString(&quot;yyyyMMss_HHmmsstt&quot;);\r\n string startPath = Directory.GetCurrentDirectory()+@&quot;\\data&quot;;\/\/ @&quot;.&quot;;\r\n string zipPath = @&quot;tablesScripted_&quot; + db.Name + &quot;_&quot; + fileTSstring + &quot;.zip&quot;;\r\n\r\nConsole.WriteLine(&quot;Completed ONLY scripting the tables and making the archive called '&quot; + zipPath + &quot;'&quot;);\r\n System.IO.Compression.ZipFile.CreateFromDirectory(startPath, zipPath);\r\n\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>[Latest Edit Feb 14th] So, I go to edit a few Stored Procs on my MSSQL database and&#8230; errors &#8211; table missing &#8211; what? I seems over the weekend, all the databases were reset to some time in the past &#8211; this means 30 SP&#8217;s and 20 tables on our dev environment have &#8211; poof [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5,1],"tags":[],"class_list":["post-615","post","type-post","status-publish","format-standard","hentry","category-bentley-autoplant","category-general"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.7 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>SMO for SQL Backup ... of sorts - ELB Solutions.com Inc.<\/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:\/\/elbsolutions.com\/projects\/smo-sql-backup-sorts\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SMO for SQL Backup ... of sorts - ELB Solutions.com Inc.\" \/>\n<meta property=\"og:description\" content=\"[Latest Edit Feb 14th] So, I go to edit a few Stored Procs on my MSSQL database and&#8230; errors &#8211; table missing &#8211; what? I seems over the weekend, all the databases were reset to some time in the past &#8211; this means 30 SP&#8217;s and 20 tables on our dev environment have &#8211; poof [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/elbsolutions.com\/projects\/smo-sql-backup-sorts\/\" \/>\n<meta property=\"og:site_name\" content=\"ELB Solutions.com Inc.\" \/>\n<meta property=\"article:published_time\" content=\"2014-02-11T16:22:47+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2022-02-03T17:25:03+00:00\" \/>\n<meta name=\"author\" content=\"Etienne Bley\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Etienne Bley\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/elbsolutions.com\\\/projects\\\/smo-sql-backup-sorts\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/elbsolutions.com\\\/projects\\\/smo-sql-backup-sorts\\\/\"},\"author\":{\"name\":\"Etienne Bley\",\"@id\":\"https:\\\/\\\/elbsolutions.com\\\/projects\\\/#\\\/schema\\\/person\\\/51e717c68f4f5917c63baf88f0896c39\"},\"headline\":\"SMO for SQL Backup &#8230; of sorts\",\"datePublished\":\"2014-02-11T16:22:47+00:00\",\"dateModified\":\"2022-02-03T17:25:03+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/elbsolutions.com\\\/projects\\\/smo-sql-backup-sorts\\\/\"},\"wordCount\":828,\"articleSection\":[\"B-AutoPLANT\",\"General\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/elbsolutions.com\\\/projects\\\/smo-sql-backup-sorts\\\/\",\"url\":\"https:\\\/\\\/elbsolutions.com\\\/projects\\\/smo-sql-backup-sorts\\\/\",\"name\":\"SMO for SQL Backup ... of sorts - ELB Solutions.com Inc.\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/elbsolutions.com\\\/projects\\\/#website\"},\"datePublished\":\"2014-02-11T16:22:47+00:00\",\"dateModified\":\"2022-02-03T17:25:03+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/elbsolutions.com\\\/projects\\\/#\\\/schema\\\/person\\\/51e717c68f4f5917c63baf88f0896c39\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/elbsolutions.com\\\/projects\\\/smo-sql-backup-sorts\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/elbsolutions.com\\\/projects\\\/smo-sql-backup-sorts\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/elbsolutions.com\\\/projects\\\/smo-sql-backup-sorts\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/elbsolutions.com\\\/projects\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SMO for SQL Backup &#8230; of sorts\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/elbsolutions.com\\\/projects\\\/#website\",\"url\":\"https:\\\/\\\/elbsolutions.com\\\/projects\\\/\",\"name\":\"ELB Solutions.com Inc.\",\"description\":\"Bringing all your IT Pieces together\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/elbsolutions.com\\\/projects\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/elbsolutions.com\\\/projects\\\/#\\\/schema\\\/person\\\/51e717c68f4f5917c63baf88f0896c39\",\"name\":\"Etienne Bley\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/f8971dfb65b25b768415568f83247df4057f15d037137e386928a804e2c997b9?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/f8971dfb65b25b768415568f83247df4057f15d037137e386928a804e2c997b9?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/f8971dfb65b25b768415568f83247df4057f15d037137e386928a804e2c997b9?s=96&d=mm&r=g\",\"caption\":\"Etienne Bley\"},\"url\":\"https:\\\/\\\/elbsolutions.com\\\/projects\\\/author\\\/etienne-bley\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"SMO for SQL Backup ... of sorts - ELB Solutions.com Inc.","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:\/\/elbsolutions.com\/projects\/smo-sql-backup-sorts\/","og_locale":"en_US","og_type":"article","og_title":"SMO for SQL Backup ... of sorts - ELB Solutions.com Inc.","og_description":"[Latest Edit Feb 14th] So, I go to edit a few Stored Procs on my MSSQL database and&#8230; errors &#8211; table missing &#8211; what? I seems over the weekend, all the databases were reset to some time in the past &#8211; this means 30 SP&#8217;s and 20 tables on our dev environment have &#8211; poof [&hellip;]","og_url":"https:\/\/elbsolutions.com\/projects\/smo-sql-backup-sorts\/","og_site_name":"ELB Solutions.com Inc.","article_published_time":"2014-02-11T16:22:47+00:00","article_modified_time":"2022-02-03T17:25:03+00:00","author":"Etienne Bley","twitter_misc":{"Written by":"Etienne Bley","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/elbsolutions.com\/projects\/smo-sql-backup-sorts\/#article","isPartOf":{"@id":"https:\/\/elbsolutions.com\/projects\/smo-sql-backup-sorts\/"},"author":{"name":"Etienne Bley","@id":"https:\/\/elbsolutions.com\/projects\/#\/schema\/person\/51e717c68f4f5917c63baf88f0896c39"},"headline":"SMO for SQL Backup &#8230; of sorts","datePublished":"2014-02-11T16:22:47+00:00","dateModified":"2022-02-03T17:25:03+00:00","mainEntityOfPage":{"@id":"https:\/\/elbsolutions.com\/projects\/smo-sql-backup-sorts\/"},"wordCount":828,"articleSection":["B-AutoPLANT","General"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/elbsolutions.com\/projects\/smo-sql-backup-sorts\/","url":"https:\/\/elbsolutions.com\/projects\/smo-sql-backup-sorts\/","name":"SMO for SQL Backup ... of sorts - ELB Solutions.com Inc.","isPartOf":{"@id":"https:\/\/elbsolutions.com\/projects\/#website"},"datePublished":"2014-02-11T16:22:47+00:00","dateModified":"2022-02-03T17:25:03+00:00","author":{"@id":"https:\/\/elbsolutions.com\/projects\/#\/schema\/person\/51e717c68f4f5917c63baf88f0896c39"},"breadcrumb":{"@id":"https:\/\/elbsolutions.com\/projects\/smo-sql-backup-sorts\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/elbsolutions.com\/projects\/smo-sql-backup-sorts\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/elbsolutions.com\/projects\/smo-sql-backup-sorts\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/elbsolutions.com\/projects\/"},{"@type":"ListItem","position":2,"name":"SMO for SQL Backup &#8230; of sorts"}]},{"@type":"WebSite","@id":"https:\/\/elbsolutions.com\/projects\/#website","url":"https:\/\/elbsolutions.com\/projects\/","name":"ELB Solutions.com Inc.","description":"Bringing all your IT Pieces together","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/elbsolutions.com\/projects\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/elbsolutions.com\/projects\/#\/schema\/person\/51e717c68f4f5917c63baf88f0896c39","name":"Etienne Bley","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/f8971dfb65b25b768415568f83247df4057f15d037137e386928a804e2c997b9?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/f8971dfb65b25b768415568f83247df4057f15d037137e386928a804e2c997b9?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/f8971dfb65b25b768415568f83247df4057f15d037137e386928a804e2c997b9?s=96&d=mm&r=g","caption":"Etienne Bley"},"url":"https:\/\/elbsolutions.com\/projects\/author\/etienne-bley\/"}]}},"_links":{"self":[{"href":"https:\/\/elbsolutions.com\/projects\/wp-json\/wp\/v2\/posts\/615","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/elbsolutions.com\/projects\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/elbsolutions.com\/projects\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/elbsolutions.com\/projects\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/elbsolutions.com\/projects\/wp-json\/wp\/v2\/comments?post=615"}],"version-history":[{"count":9,"href":"https:\/\/elbsolutions.com\/projects\/wp-json\/wp\/v2\/posts\/615\/revisions"}],"predecessor-version":[{"id":2826,"href":"https:\/\/elbsolutions.com\/projects\/wp-json\/wp\/v2\/posts\/615\/revisions\/2826"}],"wp:attachment":[{"href":"https:\/\/elbsolutions.com\/projects\/wp-json\/wp\/v2\/media?parent=615"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/elbsolutions.com\/projects\/wp-json\/wp\/v2\/categories?post=615"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/elbsolutions.com\/projects\/wp-json\/wp\/v2\/tags?post=615"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}