Backup and Restore SQL Server database (.bak) using C# .NET

Backup

To back up a SQL Server database, we need to know the name of the database and the path where we want to save it locally.

Consider the following method and appsettings.json

There are a few things going on here. This method takes two parameters: databaseName, the name of the database to back up on the server, and localDatabasePath, which is optional and by default null. If localDatabasePath is left null, the basePath for SQL Server specified in the appsettings.json file will be used (SqlServerBasePath). Note this is the base directory of MSSQL. localDatabasePath will end up looking like the following (where ‘Database’ is the databaseName specified):

C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\Database.bak

localDatabasePath must end with .bak. If you specify localDatabasePath, make sure that the NT Service\MSSQLSERVER account has permissions on the folder to be able to save the backup.

The T-SQL that gets executed

BACKUP DATABASE @databaseName
TO DISK = @localDatabasePath
WITH FORMAT,
MEDIANAME = @formatMediaName,
NAME = @formatName

Accepts four parameters: databaseName, the name of the database; localDatabasePath, the location where the backup will be saved including name; formatMediaName and formatName, which are auto-generated from the databaseName. We’re using WITH FORMAT here so that it will overwrite all data. Be cautious when doing this. See the following from the Microsoft docs:

“Use extreme caution when you are using the FORMAT clause of the BACKUP statement because this destroys any backups that were previously stored on the backup media.”

Restore

To restore a SQL Server database, we need the database name and path to the database .bak file we want to restore.

Consider the following method (Using the same appsettings.json from above)

When restoring a database we’re going to specify the path to the .mdf and .ldf files in addition to the .bak location. Inside of appsettings.json there is a SQLServerBasePath that points to the local installation of SQL Server.

C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL

From here we can build the paths /DATA/.mdf|.ldf using the SQLServerBasePath. Using the localDatabasePath and the following query we can ask SQL Server for the file list from the .bak file. This gives us the names we need when building the paths to the .mdf and .ldf files.

RESTORE FILELISTONLY FROM DISK = @localDatabasePath

When reading the result from the query, in order to get the right values you have to check the “Type”.

var type = reader["Type"].ToString();

If the Type is “D” then the LogicalName is for the .mdf, if it’s Type “L” then it’s for .ldf, as specified here.

Now we can restore the database. To do this seamlessly first we need to set the database to SINGLE_USER and kill any active connections that might cause the restore to fail. Passing in the databaseName parameter, we must use dynamic SQL to run the statement since SQL Server doesn’t allow variable names when specifying a database name in ALTER DATABASE.

declare @database varchar(max) = quotename(@databaseName)
EXEC('ALTER DATABASE ' + @database + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE')

Before we run the restore command, we need some variables.

// start with the base path and add DATA
var dataPath = Path.Combine(options.Value.SqlServerBasePath, "DATA");
// Use the dataName for the mdf
var fileListDataPath = Path.Combine(dataPath, $"{fileListDataName}.mdf");
// Use the logName for the ldf
var fileListLogPath = Path.Combine(dataPath, $"{fileListLogName}.ldf");

Next we need to run the actual restore command.

RESTORE DATABASE @databaseName
FROM DISK = @localDatabasePath
WITH REPLACE,
MOVE @fileListDataName to @fileListDataPath,
MOVE @fileListLogName to @fileListLogPath

Given the databaseName, localDatabasePath, fileListDataName, fileListLogName, and their paths, restore the database {databaseName} using the path {localDatabasePath} with replacing (WITH REPLACE means it will overwrite the existing database, be careful when using this as stated here as it will override checks in place to prevent data loss), specifying where to move the .mdf and .ldf files.

After doing this, we need to set the database back to MULTI_USER, so let’s run the following command

declare @database varchar(max) = quotename(@databaseName)
EXEC('ALTER DATABASE ' + @database + ' SET MULTI_USER')

GitHub Repository

To see the entire project, check out the GitHub repository.

https://github.com/joemoceri/database-toolkit