Basics – Recovering Microsoft SQL Server Databases as Flat Files

You don’t always want to be able to recover a database backup as a live database – 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.

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.

In the above screen capture, the options I had to select to progress down the path of recovering flat files were:

• The NetWorker server
• The SQL Server host (the machine I was running the recovery from)
• The SQL Server instance
• The database

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.

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

The next step was to run the recovery, which gave me the following output:

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:

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:

And there you have it – recovering a Microsoft SQL Server database to a flat file, rather than immediately back into a database.