{"id":8018,"date":"2019-04-16T17:09:50","date_gmt":"2019-04-16T07:09:50","guid":{"rendered":"https:\/\/nsrd.info\/blog\/?p=8018"},"modified":"2019-04-17T15:05:54","modified_gmt":"2019-04-17T05:05:54","slug":"basics-recovering-microsoft-sql-server-databases-as-flat-files","status":"publish","type":"post","link":"https:\/\/nsrd.info\/blog\/2019\/04\/16\/basics-recovering-microsoft-sql-server-databases-as-flat-files\/","title":{"rendered":"Basics \u2013\u00a0Recovering Microsoft SQL Server Databases as Flat Files"},"content":{"rendered":"\n

You don’t always want to be able to recover a database backup as a live database \u2013 either overwriting an existing database or going to another database. Sometimes, you might want to ship the recovered database somewhere else, and sometimes, you’ll find the database administrators wanting to do other work on the recovered database files before bringing them in as a database into a SQL server environment. To facilitate that, you can recover the database files as plain flat files, rather than recovering them into or over a database.<\/p>\n\n\n\n

To test this out, I setup a Windows 2016 server, and installed SQL Server 2016 as well. I then created a test database, installed the NetWorker client, and the NetWorker Module for Microsoft with SQL components. Then it was just a case of running the backup.<\/p>\n\n\n\n

With the backup done, I launched SQL Server Management Studio, and clicked NetWorker Backup<\/em> plugin within the GUI. When the plugin launch, I selected to do a database recovery:<\/div><\/p>\n\n\n\n
\"01
01 SSMS Select Database and Redirect to Flat File<\/figcaption><\/figure>\n\n\n\n

In the above screen capture, the options I had to select to progress down the path of recovering flat files were:<\/p>\n\n\n\n

  • The NetWorker server<\/li>
  • The SQL Server host (the machine I was running the recovery from)<\/li>
  • The SQL Server instance<\/li>
  • The database<\/li><\/ul>\n\n\n\n

    I also clicked the radio button for “Restore backups as files”, and clicked the “…” button so I could change the recovery path. From there, I was presented with a dialog where I could browse to a directory to choose as the recovery destination, or create a new one.<\/p>\n\n\n\n

    \"02
    02 SSMS Create new Directory for Recovery<\/figcaption><\/figure>\n\n\n\n

    There I created a C:\\Temp directory, as shown above, and clicked OK to be presented with an updated view:<\/p>\n\n\n\n

    \"\"
    03 SSMS Ready to Run Recovery<\/figcaption><\/figure>\n\n\n\n

    The next step was to run the recovery, which gave me the following output:<\/p>\n\n\n\n

    \"04
    04 SSMS Recovery Complete<\/figcaption><\/figure>\n\n\n\n

    With the recovery complete, it’s just a case of browsing to the directory and making sure the database had been recovered as flat file:<\/p>\n\n\n\n

    \"05
    05 SSMS Recovery in Explorer<\/figcaption><\/figure>\n\n\n\n

    You don’t have to use SSMS to perform a flat file recovery, though. Here’s an example of it being run from the command line on the SQL server, as well:<\/p>\n\n\n\n

    \"06
    06 Recovery from Command Line<\/figcaption><\/figure>\n\n\n\n

    And there you have it \u2013 recovering a Microsoft SQL Server database to a flat file, rather than immediately back into a database. <\/p>\n","protected":false},"excerpt":{"rendered":"

    You don’t always want to be able to recover a database backup as a live database \u2013 either overwriting an existing…<\/p>\n","protected":false},"author":1,"featured_media":8012,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[6,19],"tags":[1243,286,1249,1252],"class_list":["post-8018","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-basics","category-recovery","tag-basics","tag-database","tag-networker","tag-recovery"],"aioseo_notices":[],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"https:\/\/nsrd.info\/blog\/wp-content\/uploads\/2019\/04\/01-SSMS-Select-Database-and-Redirect-to-Flat-File.png","jetpack_shortlink":"https:\/\/wp.me\/pKpIN-25k","jetpack_sharing_enabled":true,"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/nsrd.info\/blog\/wp-json\/wp\/v2\/posts\/8018","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/nsrd.info\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/nsrd.info\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/nsrd.info\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/nsrd.info\/blog\/wp-json\/wp\/v2\/comments?post=8018"}],"version-history":[{"count":5,"href":"https:\/\/nsrd.info\/blog\/wp-json\/wp\/v2\/posts\/8018\/revisions"}],"predecessor-version":[{"id":8025,"href":"https:\/\/nsrd.info\/blog\/wp-json\/wp\/v2\/posts\/8018\/revisions\/8025"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/nsrd.info\/blog\/wp-json\/wp\/v2\/media\/8012"}],"wp:attachment":[{"href":"https:\/\/nsrd.info\/blog\/wp-json\/wp\/v2\/media?parent=8018"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/nsrd.info\/blog\/wp-json\/wp\/v2\/categories?post=8018"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/nsrd.info\/blog\/wp-json\/wp\/v2\/tags?post=8018"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}