Create a full database backup

Applies to: SQL Server

This article describes how to create a full database backup in SQL Server by using SQL Server Management Studio, Transact-SQL, or PowerShell.

For more information, see SQL Server backup and restore with Azure Blob Storage and SQL Server backup to URL for Azure Blob Storage.

Limitations

• The BACKUP statement isn't allowed in an explicit or implicit transaction.
• Backups created by more recent versions of SQL Server can't be restored in earlier versions of SQL Server.

For an overview of, and deeper dive into, backup concepts and tasks, see Backup overview (SQL Server) before proceeding.

Recommendations

• As a database increases in size, full database backups take more time to complete and require more storage space. For large databases, consider supplementing full database backups with a series of differential database backups.
• Estimate the size of a full database backup by using the sp_spaceused system stored procedure.
• By default, every successful backup operation adds an entry in the SQL Server error log and in the system event log. If you back up frequently, success messages accumulate quickly, resulting in huge error logs, which makes finding other messages difficult. In such cases, you can suppress these backup log entries by using trace flag 3226 if none of your scripts depend on those entries. For more information, see Trace Flags (Transact-SQL).

Security

TRUSTWORTHY is set to OFF on a database backup. For information about how to set TRUSTWORTHY to ON, see ALTER DATABASE SET options.

Beginning with SQL Server 2012 (11.x), the PASSWORD and MEDIAPASSWORD options aren't available for creating backups. You can still restore backups created with passwords.

Permissions

BACKUP DATABASE and BACKUP LOG permissions default to members of the sysadmin fixed server role and the db_owner and db_backupoperator fixed database roles.

Ownership and permission problems on the backup device's physical file can interfere with a backup operation. The SQL Server service should read from and write to the device. The account under which the SQL Server service runs must have write permissions to the backup device. However, sp_addumpdevice, which adds an entry for a backup device in the system tables, doesn't check file access permissions. Problems in the backup device's physical file might not appear until the backup is used or a restore attempted.

Use SQL Server Management Studio

1. After connecting to the appropriate instance of the SQL Server Database Engine, in Object Explorer, expand the server tree.

2. Expand Databases, and either select a user database or expand System Databases and select a system database.

3. Right-click the database that you want to back up, point to Tasks, and then select Back Up....

4. In the Back Up Database dialog, the database that you selected appears in the dropdown list. (You can change the database to any other database on the server.)

5. In the Backup type list, select a backup type. The default is Full.

   Important

   You must perform at least one full database backup before you can perform a differential or transaction log backup.

6. Under Backup component, select Database.

7. In the Destination section, review the default location for the backup file (in the ../mssql/data folder).

   You can use the Back up to list to select a different device. Select Add to add backup objects and/or destinations. You can stripe the backup set across multiple files for increased backup speed.

   To remove a backup destination, select it and then select Remove. To view the contents of an existing backup destination, select it and then select Contents.

8. (Optional) Review the other available settings on the Media Options and Backup Options pages.

   For more information about the various backup options, see Back Up Database (General Page), Back Up Database (Media Options page), and Back Up Database (Backup Options Page).

9. Select OK to start the backup.

10. When the backup completes successfully, select OK to close the SQL Server Management Studio dialog.

Additional information

• After creating a full database backup, you can create a differential database backup or a transaction log backup.

• (Optional) You can select the Copy-only backup checkbox to create a copy-only backup. A copy-only backup is a SQL Server backup that's independent of the sequence of conventional SQL Server backups. For more information, see Copy-only backups. A copy-only backup isn't available for the Differential backup type.

• The Overwrite media option is disabled on the Media Options page if you're backing up to a URL.

Examples

For the following examples, create a test database with the following Transact-SQL code:

USE [master]
GO

CREATE DATABASE [SQLTestDB]
GO

USE [SQLTestDB]
GO
CREATE TABLE SQLTest
   (
      ID INT NOT NULL PRIMARY KEY,
      c1 VARCHAR(100) NOT NULL,
      dt1 DATETIME NOT NULL DEFAULT getdate()
   );
GO

USE [SQLTestDB]
GO

INSERT INTO SQLTest (ID, c1) VALUES (1, 'test1')
INSERT INTO SQLTest (ID, c1) VALUES (2, 'test2')
INSERT INTO SQLTest (ID, c1) VALUES (3, 'test3')
INSERT INTO SQLTest (ID, c1) VALUES (4, 'test4')
INSERT INTO SQLTest (ID, c1) VALUES (5, 'test5')
GO

SELECT * FROM SQLTest
GO

A. Full backup to disk to the default location

In this example, the SQLTestDB database is backed up to disk at the default backup location.

1. After connecting to the appropriate instance of the SQL Server Database Engine, in Object Explorer, expand the server tree.

2. Expand Databases, right-click SQLTestDB, point to Tasks, and then select Back Up....

3. Select OK.

4. When the backup completes successfully, select OK to close the SQL Server Management Studio dialog.

B. Full backup to disk to a nondefault location

In this example, the SQLTestDB database is backed up to disk at a location that you choose.

1. After connecting to the appropriate instance of the SQL Server Database Engine, in Object Explorer, expand the server tree.

2. Expand Databases, right-click SQLTestDB, point to Tasks, and then select Back Up....

3. On the General page in the Destination section, select Disk in the Back up to list.

4. Select Remove until all existing backup files are removed.

5. Select Add. The Select Backup Destination dialog opens.

6. Enter a valid path and file name in the File name box. Use .bak as the extension to simplify the classification of the file.

7. Select OK and then select OK again to start the backup.

8. When the backup completes successfully, select OK to close the SQL Server Management Studio dialog.

C. Create an encrypted backup

In this example, the SQLTestDB database is backed up with encryption to the default backup location.

1. After connecting to the appropriate instance of the SQL Server Database Engine, in Object Explorer, expand the server tree.

2. Expand Databases, expand System Databases, right-click master, and then select New Query to open a query window with a connection to your SQLTestDB database.

3. Run the following commands to create a database master key and a certificate within the master database.

   -- Create the master key.
   CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';
   
   -- If the master key already exists, open it in the same session that you create the certificate. (See next step.)
   OPEN MASTER KEY DECRYPTION BY PASSWORD = '<password>'
   
   -- Create the certificate encrypted by the master key.
   CREATE CERTIFICATE MyCertificate
   WITH SUBJECT = 'Backup Cert', EXPIRY_DATE = '20201031';
   

4. In Object Explorer, in the Databases node, right-click SQLTestDB, point to Tasks, and then select Back Up....

5. On the Media Options page, in the Overwrite media section, select Back up to a new media set, and erase all existing backup sets.

6. On the Backup Options page, in the Encryption section, select Encrypt backup.

7. In the Algorithm list, select AES 256.

8. In the Certificate or Asymmetric key list, select MyCertificate.

9. Select OK.

D. Back up to Azure Blob Storage

This example creates a full database backup of SQLTestDB to Azure Blob Storage. The example is written with the assumption that you already have a storage account with a blob container. The example creates a shared access signature. The example fails if the container has an existing shared access signature.

If you don't have a Blob Storage container in a storage account, create one before continuing. See Create a general purpose storage account and Create a container.

1. After connecting to the appropriate instance of the SQL Server Database Engine, in Object Explorer, expand the server tree.

2. Expand Databases, right-click SQLTestDB, point to Tasks, and then select Back Up....

3. On the General page, in the Destination section, select URL in the Back up to list.

4. Select Add. The Select Backup Destination dialog opens.

5. If you've previously registered the Azure storage container that you want to use with SQL Server Management Studio, select it. Otherwise, select New container to register a new container.

6. In the Connect to a Microsoft Subscription dialog, sign in to your account.

7. In the Select Storage Account box, select your storage account.

8. In the Select Blob Container box, select your blob container.

9. In the Shared Access Policy Expiration calendar box, select an expiration date for the shared access policy that you create in this example.

10. Select Create Credential to generate a shared access signature and credential in SQL Server Management Studio.

11. Select OK close the Connect to a Microsoft Subscription dialog.

12. In the Backup File box, change the name of the backup file if you want to.

13. Select OK to close the Select a backup destination dialog.

14. Select OK to start the backup.

15. When the backup completes successfully, select OK to close the SQL Server Management Studio dialog.

Use Transact-SQL

Create a full database backup by running the BACKUP DATABASE statement, specifying:

• The name of the database to back up.
• The backup device where the full database backup is written.

The basic Transact-SQL syntax for a full database backup is:

BACKUP DATABASE <database>
TO <backup_device> [ , ...n ]
[ WITH <with_options> [ , ...o ] ];

Optionally, specify one or more WITH options. A few basic WITH options are described here. For information about all the WITH options, see BACKUP.

Basic backup set WITH options:

• { COMPRESSION | NO_COMPRESSION }. In SQL Server 2008 (10.0.x) Enterprise and later only, specifies whether backup compression is performed on the backup, overriding the server-level default.
• ENCRYPTION (ALGORITHM, SERVER CERTIFICATE | ASYMMETRIC KEY). In SQL Server 2014 or later only, specifies the encryption algorithm to use, and the certificate or asymmetric key to use to secure the encryption.
• DESCRIPTION = { 'text' | @text_variable }. Specifies the free-form text that describes the backup set. The string can have a maximum of 255 characters.
• NAME = { backup_set_name | @backup_set_name_var }. Specifies the name of the backup set. Names can have a maximum of 128 characters. If NAME isn't specified, it's blank.

By default, BACKUP appends the backup to an existing media set, preserving existing backup sets. To explicitly specify this configuration, use the NOINIT option. For information about appending to existing backup sets, see Media sets, media families, and backup sets (SQL Server).

To format the backup media, use the FORMAT option:

FORMAT [ , MEDIANAME = { media_name | @media_name_variable } ] [ , MEDIADESCRIPTION = { text | @text_variable } ]

Use the FORMAT clause when you're using media for the first time or when you want to overwrite all existing data. Optionally, assign the new media a media name and description.

Examples

For the following examples, create a test database with the following Transact-SQL code:

USE [master]
GO

CREATE DATABASE [SQLTestDB]
GO

USE [SQLTestDB]
GO
CREATE TABLE SQLTest (
   ID INT NOT NULL PRIMARY KEY,
   c1 VARCHAR(100) NOT NULL,
   dt1 DATETIME NOT NULL DEFAULT GETDATE()
)
GO

USE [SQLTestDB]
GO

INSERT INTO SQLTest (ID, c1) VALUES (1, 'test1')
INSERT INTO SQLTest (ID, c1) VALUES (2, 'test2')
INSERT INTO SQLTest (ID, c1) VALUES (3, 'test3')
INSERT INTO SQLTest (ID, c1) VALUES (4, 'test4')
INSERT INTO SQLTest (ID, c1) VALUES (5, 'test5')
GO

SELECT * FROM SQLTest
GO

A. Back up to a disk device

The following example backs up the complete SQLTestDB database to disk. It uses FORMAT to create a new media set.

USE SQLTestDB;
GO
BACKUP DATABASE SQLTestDB
TO DISK = 'c:\tmp\SQLTestDB.bak'
   WITH FORMAT,
      MEDIANAME = 'SQLServerBackups',
      NAME = 'Full Backup of SQLTestDB';
GO

B. Back up to a tape device

The following example backs up the complete SQLTestDB database to tape. It appends the backup to the previous backups.

USE SQLTestDB;
GO
BACKUP DATABASE SQLTestDB
   TO TAPE = '\\.\Tape0'
   WITH NOINIT,
      NAME = 'Full Backup of SQLTestDB';
GO

C. Back up to a logical tape device

The following example creates a logical backup device for a tape drive. The example then backs up the complete SQLTestDB database to that device.

-- Create a logical backup device,
-- SQLTestDB_Bak_Tape, for tape device \\.\tape0.
USE master;
GO
EXEC sp_addumpdevice 'tape', 'SQLTestDB_Bak_Tape', '\\.\tape0'; USE SQLTestDB;
GO
BACKUP DATABASE SQLTestDB
   TO SQLTestDB_Bak_Tape
   WITH FORMAT,
      MEDIANAME = 'SQLTestDB_Bak_Tape',
      MEDIADESCRIPTION = '\\.\tape0',
      NAME = 'Full Backup of SQLTestDB';
GO

Use PowerShell

Use the Backup-SqlDatabase cmdlet. To explicitly indicate a full database backup, specify the -BackupAction parameter with its default value, Database. This parameter is optional for full database backups.

Examples

A. Full backup (local)

The following example creates a full database backup of the <myDatabase> database to the default backup location of the server instance Computer\Instance. Optionally, this example specifies -BackupAction Database.

For full syntax examples, see Backup-SqlDatabase.

$credential = Get-Credential

Backup-SqlDatabase -ServerInstance Computer[\Instance] -Database <myDatabase> -BackupAction Database -Credential $credential

B. Full backup to Azure

The following example creates a full backup of the database <myDatabase> on the <myServer> instance to Blob Storage. A stored access policy has been created with read, write, and list rights. The SQL Server credential, https://<myStorageAccount>.blob.core.windows.net/<myContainer>, was created by using a shared access signature that's associated with the stored access policy. The command uses the $backupFile parameter to specify the location (URL) and the backup file name.

$credential = Get-Credential
$container = 'https://<myStorageAccount>blob.core.windows.net/<myContainer>'
$fileName = '<myDatabase>.bak'
$server = '<myServer>'
$database = '<myDatabase>'
$backupFile = $container + '/' + $fileName

Backup-SqlDatabase -ServerInstance $server -Database $database -BackupFile $backupFile -Credential $credential

Related tasks

• Create a differential database backup (SQL Server)
• Restore a database backup using SSMS
• Restore a database backup under the simple recovery model (Transact-SQL)
• Restore database to point of failure - full recovery
• Restore a database to a new location (SQL Server)
• Use the Maintenance Plan Wizard

Related content

• Troubleshoot SQL Server backup and restore operations
• Backup overview (SQL Server)
• Transaction log backups (SQL Server)
• Media sets, media families, and backup sets (SQL Server)
• sp_addumpdevice (Transact-SQL)
• BACKUP (Transact-SQL)
• Back Up Database (General Page)
• Back Up Database (Backup Options Page)
• Differential backups (SQL Server)
• Full database backups (SQL Server)