Configure MSSQL database backups
Last updated on: 2020-08-19
Authored by: Karoline Mills
This article describes how to perform SQL Server® database backups by using SQL Server Management Studio (SSMS) and PowerShell®.
Backup limitations
Backups have the following limitations:
- You cannot restore backups that were created with a newer version of SQL Server to older versions of SQL Server.
- You must take a full database backup before you can perform differential and transaction log backups.
- You need Backup database or Backup log permissions to perform backup operations. By default, the system grants these permissions to the sysadmin fixed server role and the db_owner and db_backupoperator fixed database roles.
Use SQL Server Management Studio to create a database backup
- Connect to your database instance and expand the databases section on the left-hand side.
- Right-click on the database you want to back up and select Back Up….
- Select the desired backup type (full, differential, or log) from the drop-down list.
- Choose the desired backup destination and select OK to start the backup process.
Alternatively, you can use the following steps to initiate a backup operation:
-
When logged into the correct database instance, select New Query.
-
Use the following query when backing up to a disk. Replace the location and database name with the respective names on your server:
USE SQLTestDatabase; GO BACKUP DATABASE SQLTestDatabase TO DISK = 'd:\backups\SQLTestDatabase.bak' WITH FORMAT, MEDIANAME = 'SQLServerBackups', NAME = 'Full Backup of SQLTestDatabase'; GO
Use PowerShell to create a database backup
You can also create database backups by using PowerShell.
First, open PowerShell with administrator permissions and type Install-Module -Name SqlServer
. This commmand installs the
SQL Server module, which you need to perform backup operations in PowerShell.
Next, use the following example to perform a full database backup to the default backup location, replacing the location and database name with your server’s respective names:
$credential = Get-Credential
Backup-SqlDatabase -ServerInstance Computer[\Instance] -Database <SQLTestDatabase> -BackupAction Database -Credential $credential
Finally, to learn more about syntax and examples for different backup operations, review the official Microsoft documentation.