Summary: Backing up the database in the SQL server is essential to protect data and maintain business continuity. This post shares various methods to automate SQL Server database backups. It also explains ways to create full as well as differential backups.
Database Administrator knows the importance of SQL database backup. However, creating Database backup manually is a tedious task as it requires to generate long scripts for every database on the SQL server. This post discusses methods to automate a full database back up in SQL Server by using SQL Server Management Studio, Transact-SQL and PowerShell cmdlets. In addition, it shares the methods to create a differential database backup with the help of SQL Server Management Studio or Transact-SQL, after the full database backup.
Consider the following points before starting the backup process:
- Backup is possible in case of explicit or implicit transactions
- You can’t restore the backup to earlier SQL database versions when it is created in latest or higher version
- With time, the database becomes humongous and requires more time and storage space. It’s better to supplement Full backup with a series of differential backups.
- Check that DBA has full permissions and is a member of the sysadmin fixed-server role as well as assigned with db_owner and db_backupoperator fixed database roles.
- SQL Server Service must have permission to read and write the device. It means that the SQL Server service account must have write permissions of the backup device.
Methods to Take Full Backup
Method 1: Full SQL Backup by using SQL Server Management Studio (SSMS)
When you specify a backup task by using SSMS, you can also generate a Script (Transact-SQL Backup Script) by clicking the Script button and specifying the destination for Script.
Steps to create Full backup by using SSMS:
- Connect to the appropriate instance of the Microsoft SQL Server Database Engine. Go to Object Explorer and expand the server tree.
- Click Databases and select a user database. Alternately, expand System Databases and select a system database.
- Right-click the database to backup, point to Tasks, and click Back Up.
- In the Back-Up Database dialog box, the selected database appears in drop-down list (which can be changed to another database on the server).
- In the Backup type drop-down list, select the backup type (default is Full). Under the Backup component, select Database.
- In the Destination section, review the default location for the backup file (in the ../mssql/data folder). Change the location, if required.
Click OK to initiate the backup.
- After successful completion, click OK to close the SSMS dialog box.
Method 2: SQL Backup by using Transact-SQL
You can also create a full backup of the SQL database with the help of T-SQL. Follow the given steps:
- Execute the BACKUP DATABASE statement to create the full database backup. Specify the following:
- Name of SQL database to back up
- Backup device where the full database backup is written
- Use the following Transact-SQL syntax for a full database backup:
BACKUP DATABASE database TO backup_device [ ,…n ] [ WITH with_options [ ,…o ] ] ;
Note: You can back up to a tape device, a logical tape device, and a disk device with the help of Transact-SQL. Syntax codes for all devices are different.
Method 3: SQL Backup by using PowerShell cmdlet
You can also use the Backup-SqlDatabase cmdlet for creating a Full backup of the SQL database. You just need to indicate that this is a full database backup by specifying the -BackupAction parameter with its default value, Database. However, this parameter is optional for full database backups.
Important: Your SSMS credentials for connecting to the installation of SQL Server are automatically used for establishing a connection between PowerShell cmdlet and SQL Server instance.
The following PowerShell cmdlet is used for full backup on the local system:
$credential = Get-Credential
Backup-SqlDatabase -ServerInstance Computer[\Instance] -Database <myDatabase> -BackupAction Database -Credential $credential
Methods to Take Differential Backup
Method 1: Differential backup by using SQL Server Management Studio
If you have a full backup available, you can create a differential backup or a Transaction Log backup.
Note: Backup statement doesn’t work in an implicit or explicit transaction.
Steps to create Differential SQL Backup by using SSMS
- Connect to the instance of Microsoft SQL Server Database Engine. Go to Object Explorer and click the server name to expand the server tree.
- Expand Databases, and select a user database. Alternatively, expand System Databases and select a system database.
- Right-click selected database, point to Tasks and click Back Up. The Backup Database dialog box appears. In the Database list box, verify the database name.
There is an option to perform a differential backup for any recovery model (full, bulk-logged, or simple).
- In the Backup type list box, select Differential.
Important: In a case of Differential, clear the checkbox for Copy Only Backup.
- For the Backup component, click Database.
- Define or enter a description of the backup set.
- Specify when the backup set will expire.
- Choose a backup destination – Disk or Tape. For advanced options, go to Select a page pane and click Options.
- Next, choose an Overwrite Media option, by clicking one of the following: Back up to the existing media set or Back up to a new media set, and erase all existing backup sets.
Backing up to a tape drive activates the option Unload the tape after backup. Click this option to activate Rewind the tape before the unloading option.
Method 2: Create Differential Backup by using Transact-SQL
Transact-SQL helps in creating a differential database backup by executing the following steps:
- Specify the following:
- The name of the database to back up.
- The backup device where full database backup is written.
- The DIFFERENTIAL clause, to specify that database changed after the last full database backup was created are backed up.
- Execute the following BACKUP DATABASE syntax to create a differential database backup. BACKUP DATABASE database_name TO <backup_device> WITH DIFFERENTIAL
This post outlines various methods to automate SQL Server database backup. The best way is to schedule a combination of full and differential backup.
As backups are an integral part of disaster recovery, DBAs must take measures to verify that SQL Server database backups are regularly updated. However, backups can sometimes become corrupt. In that case, the only option is to repair the corrupt SQL database. The easiest way to repair a corrupt database is by using SQL recovery software such as Stellar Repair for MS SQL. The software not only repairs the database but also ensures peace of mind for Database Administrators.