Non-Standard Backups: 5 Ways to Make an SQL Server Backup

BACKUP DATABASE is the main but not only way to make an SQL SERVER backup.

I work for a company that makes an SQL instance backup utility. Over the years, we have accumulated a wealth of knowledge about how to backup Microsoft SQL Server databases in different ways. You might wonder why it is necessary to have several different methods instead of just always using the best one. The answer is that the “best” backup is not always appropriate for every job, so you will need to know the various alternatives.

The word “backup” is sometimes narrowly defined as a specific kind of file. However, in this article, I will use the term more broadly to mean any file which you can use to restore data in the event of a failure.

T-SQL: BACKUP DATABASE

If you google “how to backup SQL Server,” you will find the T-SQL Command BACKUP DATABASE.

This command is the main backup tool, and it is really good. You just need to run the following SQL query:

BACKUP DATABASE [AdventureWorks] TO DISK = 'D:\AdventureWorks.bak'

And now you have a transactionally consistent physical backup as a .bak file. You did not have to turn off the database and creating a backup should not have a noticeable effect on performance.

The resulting file is a page-by-page copy of the database. Such a file contains not only data pages but also index pages. Thus, the size of the backup will be significant, but on the other hand, when restoring it you will not have to recalculate the indices.

Restoring a database is as easy as creating it:

RESTORE DATABASE [AdventureWorks] FROM DISK = 'D:\AdventureWorks.bak' WITH REPLACE

Also, physical backups can be differential and incremental, containing only the data that has been changed. The size of such backups is usually very small, which allows people to make them often. For example, they can be created every five minutes. By configuring the creation of backups in this way, the maximum data loss interval for you will be only five minutes.

What’s wrong with such a backup?

Usually, there is nothing wrong with making such a backup. But to make a physical backup, two conditions must be met.

1. You need access to the database.
2. You need access to the file system of the server where the database is located.

In practice, there may be jobs when there is access to the database, but there is no access to the server or vice versa. Then you will have to attempt an alternative method of creating a backup.

Remote Backup

If you are not the owner of the database, but you can connect to SQL Server using SSMS, then you have two mechanisms to create a copy of all data on a remote SQL Server:

1. Using an Export/Import Data-Tier Application. This is an SQL Server mechanism for transferring data between different SQL Server engines. For example, to export data from the SQL Server and import it into Azure SQL.
2. Generate an SQL script that will contain instructions for rebuilding the database.

Both methods have critical drawbacks, however. Export and script creation are not transactionally consistent operations. A situation may arise when a foreign key’s value will refer to a record that is not included in the exported file. You will be able to detect broken links only when you try to restore the database. In theory, you can fix this manually, but in practice, it can be very time-consuming.

The second drawback is that these operations take a long time and overburden the server. Therefore, they cannot be used for large databases.

Snapshots

To ensure consistent import, you need to ensure that the data does not change during the import. The easiest way is to put the database in a read-only state, but it is much better to take a database snapshot.

A snapshot is a static read-only view of a database. The snapshot is transactionally consistent and fully equivalent to the original database at the time of creation.

To create a snapshot, you need to run the following T-SQL statement:

CREATE DATABASE AdventureWorks_snapshot ON
( NAME = AdventureWorks2017, FILENAME =
'W:\temp\AdventureWorks2017.ss' ) AS SNAPSHOT OF AdventureWorks;

In addition to the name of the snapshot to be created (AdventureWorks_snapshot) and the name of the source database (AdventureWorks), you will need to specify a sparse file (AdventureWorks2017.ss) for each data file of the source database (AdventureWorks2017).

The names of the data files for AdventureWorks can be obtained by running the following query:

USE [AdventureWorks];
SELECT [name], [physical_name]
FROM [sys].[database_files]
WHERE [type] = 0

Sparse files store the difference between the original database and the snapshot. Initially, such files will be empty, but as the database grows, their size will increase.

Export Data

Now that we have a snapshot of the database, it’s time to make a copy of the data. Although you can generate a script and run Export Data-Tier Application with SSMS, doing this manually each time is tedious. Instead, we will use software tools to export data.

You can download the sqlpackage.exe utility here. It is a simple command prompt utility for exporting and importing data. To export data, just run the following command:

sqlpackage.exe /action:Export /SourceServerName:. /SourceDatabaseName:AdventureWorks_snapshot /SourceUser:sa /SourcePassword:my-secret-pw /TargetFile:w:\backup\AdventureWorks.bacpac

The resulting .bacpac file is already an archive, so there is no point in compressing it. In the future, you can restore data using the following command:

sqlpackage.exe /SourceFile:”w:\backup\AdventureWorks.bacpac” /Action:Import /TargetServerName:”.” /TargetDatabaseName:”AdventureWorks” /TargetUser:”sa” /TargetPassword:my-secret-pw

Or Generate a Script

Another way to create a complete copy of the data is to use mssql-scripter — an open-source project on Github whose main developer is Microsoft. You can read the installation instructions here.

The following command creates a schema and a data dump:

mssql-scripter -S localhost -d AdventureWorks_snapshot -U sa — schema-and-data > ./adventureworks.sql

It will take slightly longer to do this than manually exporting. It is recommended to compress the resulting .sql file. Use 7zip to do it for Windows.

7z.exe a adventureworks.7z adventureworks.sql

You can restore data using the sqlcmd utility:

sqlcmd -i adventureworks.sql

Generating a .sql file can sometimes be a more convenient solution than an export data-tier application because you can edit the file before restoring it.

However, specific T-SQL instructions are generated for a specific version of SQL Server when the script is generated. When restoring data, you will have to replace the snapshot name with the restored database’s name. Also, when restoring data to a server with a different version, errors may occur.

Anything else?

Delete the snapshot after you get the data file. You can do it with a simple T-SQL command.

DROP DATABASE [AdventureWorks_snapshot]

If you forget to do this, then over time, the sparse files with which the snapshot is associated will grow to large sizes.

Copying Database Files

There are situations when the administrator has access to the server, but access to the database is significantly restricted or completely closed. At the same time, SQL Server itself uses Transparent Data Encryption to protect data files from theft.

You might wonder if it is possible to back up the database if you cannot connect to it. The answer is yes, because ultimately the database is stored in physical files. If you copy these files, you can get a backup. However, you cannot just take and copy the active database file. And if you succeed, then most likely the database files will be damaged.

Cold Backup

The easiest way is to stop the database and then copy it. If SQL Server is used only on the internal network, most likely all employees are sleeping at night, and a temporary shutdown of the database for maintenance will not hurt anyone.

For a Windows server, it can be done the following way:

1. Stop SQL Server:

net stop MSSQLSERVER

2. Copy .mdf file of the database in question:

copy “C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\AdventureWorks.mdf” d:\Backups\AdventureWorks.mdf

3. Launch SQL Server:

net start MSSQLSERVER

This is a rough method, but fast and reliable.

Volume Shadow Copy

A Windows server has Volume Shadow Copy Service (VSS). This service allows performing a consistent backup of files while they are in use. For this trick to work, the backup utility and application that use the file must be able to integrate with VSS.

SQL Server can integrate with VSS, as do most modern volume backup tools. Below is a brief guide on how to make a backup with a “Windows server backup” feature.

You can run this utility from the server management console menu.

For this demo, I will select Backup Once, but as a rule, of course, a Backup Schedule is required.

If we want to backup only SQL Server data files, then select Custom

Specify the path to the directory where the .mdf files are located

And that is all. Then we need to select a separate drive or a network folder to store the backup. Then press the Backup button.

This method creates a snapshot of the database files in a consistent state without stopping the database.

It’s important to note that typically, volume snapshot tools create backups in their own format. To restore data from a snapshot, you will need to use the same tool that made it in the first place.

Bottom Line

By default, always back up your data using the BACKUP DATABASE command. It is good practice to use differential and transaction log backups in your backup strategy.

If you do not have access to the file system, you can create a snapshot and either generate a script or make an Export Data-Tier application. Generating a script may be preferable as you can easily view and even edit the data before restoring it. However, you should know where you will restore data at the time of creating a backup.

You might wonder what to do if you have access to the file system but cannot access SQL Server. You can either do a cold backup or use a file or volume backup utility that can integrate with the Volume Shadow Copy Service.

Have you tried our backup products?

We have two products:

SQLBackupAndFTP — desktop applications for backing up various SQL instances (Windows Only)

SqlBak — SaaS for administering backups across multiple servers via a browser (Windows, Linux, Docker, Raspberry).