Backing up all databases on an instance - SQL Backup

You may want to back up all the databases on an instance, or all but one or two. Both the backup wizards and the BACKUP command syntax include options to back up all databases on an instance and exclude particular databases.

Using the backup wizards

To take a single backup, open the Back Up wizard. To create a scheduled backup job, open the Schedule Backup Jobs wizard.

On step 1 of the wizard, select the SQL Server instance.

On step 2 of the wizard, select the type of backup (full, differential or transaction log), then select the databases you want to back up. To back up all databases, or all system or user databases, keep the default option of Include these and then select All, System or User as appropriate. The selected databases are marked with

If you select differential backups, only databases that have previously had a full backup taken are available:

If you select transaction log backups, only databases that are in full or bulk-logged recovery mode and have previously had a full backup taken are available:

To back up all databases on the instance except for specified databases, select Exclude these from the drop-down list. Then select the databases that you do not want to back up. The excluded databases are marked with All other databases that are online and operational when the job runs will be backed up.

Proceed through the rest of the wizard as normal. On the final step of the wizard you can view a summary of the options you have selected, including the databases you have included or excluded.

Note that if you have included all databases or have selected Exclude these, any databases which you subsequently add to the instance will also be included in the backup job the next time it runs. This is because the backup command generated by the wizard uses the wildcard character * to select all databases on the instance.

If you add a database to an instance and do not want to include it in the job, you will need to edit the job to exclude the database. From the Jobs tab, couble-click the job to open the Edit Backup Job wizard.

If you have created a differential or transaction log backup job and later add a database to the instance that you do not exclude from the job, you should take a full backup of that database. If a full backup does not exist, the differential or transaction log backup for that database may fail with SQL Backup VDI error 1010 and SQL error 3059 (differential backups) or 4214 (transaction log backups), depending on the version of SQL Server you are using. This is because a full backup is required to restore a differential or transaction log backup. Refer to your SQL Server documentation for more information.

Alternatively, you can add the SQL Backup keyword FULLIFREQUIRED to the command to ensure that a full backup of the database is taken before a differential or transaction log backup is attempted. You can add the keyword by editing the extended stored procedure or command line instruction (this option cannot be added from the backup wizards). This keyword is explained in more detail below and in The BACKUP command.

Using the BACKUP DATABASES command with the extended stored procedure or command line

In the following examples, the extended stored procedure version of the command is given first (beginning EXECUTE master..sqlbackupC) followed by the command line version (beginning SQLBackupC.exe). For more information, see Using the extended stored procedure and Using the command line.

To back up all databases on the instance you are connected to, use ALL or a single wildcard character (*). For example:

EXECUTE master..sqlbackup '-SQL "BACKUP ALL DATABASES TO DISK = ''C:\Backups\<AUTO>''"'

SQLBackupC.exe -SQL "BACKUP ALL DATABASES TO DISK = 'C:\Backups\<AUTO>'"

EXECUTE master..sqlbackup '-SQL "BACKUP DATABASES [*] TO DISK = ''C:\Backups\<AUTO>''"'

SQLBackupC.exe -SQL "BACKUP DATABASES [*] TO DISK = 'C:\Backups\<AUTO>'"

To back up all system databases (master, model and msdb) on the instance you are connected to, use SYSTEM. For example:

EXECUTE master..sqlbackup '-SQL "BACKUP SYSTEM DATABASES TO DISK = ''C:\Backups\<AUTO>''"'

SQLBackupC.exe -SQL "BACKUP SYSTEM DATABASES TO DISK = 'C:\Backups\<AUTO>'"

To back up all online and operational user databases on the instance you are connected to, use USER. For example:

EXECUTE master..sqlbackup '-SQL "BACKUP USER DATABASES TO DISK = ''C:\Backups\<AUTO>''"'

SQLBackupC.exe -SQL "BACKUP USER DATABASES TO DISK = 'C:\Backups\<AUTO>'"

To back up all online and operational databases (both system and user) on the instance you are connected to, apart from certain specified databases, use EXCLUDE. For example:

EXECUTE master..sqlbackup '-SQL "BACKUP DATABASES EXCLUDE [master, model, Testing] TO DISK = ''C:\Backups\<AUTO>''"'

SQLBackupC.exe -SQL "BACKUP DATABASES EXCLUDE [master, model, Testing] TO DISK = 'C:\Backups\<AUTO>'"

You can combine SYSTEM or USER with EXCLUDE. For example, the following command backs up all online and operational user databases other than AdventureWorksDW and pubs:

EXECUTE master..sqlbackup '-SQL "BACKUP USER DATABASES EXCLUDE [AdventureWorksDW, pubs] TO DISK = ''C:\Backups\<AUTO>''"'

SQLBackupC.exe -SQL "BACKUP USER DATABASES EXCLUDE [AdventureWorksDW, pubs] TO DISK = 'C:\Backups\<AUTO>'"

Include WITH DIFFERENTIAL to take a differential backup of the specified databases. For example:

EXECUTE master..sqlbackup '-SQL "BACKUP USER DATABASES EXCLUDE [SalesArchive] TO DISK = ''C:\Backups\<AUTO>'' WITH DIFFERENTIAL"'

SQLBackupC.exe -SQL "BACKUP USER DATABASES EXCLUDE [SalesArchive] TO DISK = 'C:\Backups\<AUTO>' WITH DIFFERENTIAL"

A full database backup is required to restore a differential backup. If a full backup is not taken before taking a differential backup, the differential backup may fail with SQL Backup error VDI 1010 and SQL error 3035. You may encounter this error if you set up a differential backup job using *, ALL, SYSTEM or USER and later add a database to the instance and do not take a full backup before the job runs.

To avoid this error, include WITH FULLIFREQUIRED to take a full backup of any databases that require one, before the differential backup is taken. For example:

EXECUTE master..sqlbackup '-SQL "BACKUP USER DATABASES TO DISK = ''C:\Backups\<AUTO>'' WITH DIFFERENTIAL, FULLIFREQUIRED"'

SQLBackupC.exe -SQL "BACKUP USER DATABASES TO DISK = 'C:\Backups\<AUTO>' WITH DIFFERENTIAL, FULLIFREQUIRED"

Any additional options you include in the command, such as compression, encryption, verification and network resilience settings will also be applied to the full backup. However, INIT, FILEOPTIONS and the ERASEFILES options will not be applied to the full backup.

Note that FULLIFREQUIRED is not available when backing up databases on SQL Server 2005.

Using the BACKUP LOGS command with the extended stored procedure or command line

In the following examples, the extended stored procedure version of the command is given first (beginning EXECUTE master..sqlbackupC) followed by the command line version (beginning SQLBackupC.exe). For more information, see Using the extended stored procedure and Using the command line.

To back up transaction logs of all online and operational databases using the FULL or BULK-LOGGED recovery model on the instance you are connected to, use a single wildcard character. For example:

EXECUTE master..sqlbackup '-SQL "BACKUP LOGS [*] TO DISK = ''C:\Backups\<AUTO>''"'

SQLBackupC.exe -SQL "BACKUP LOGS [*] TO DISK = 'C:\Backups\<AUTO>'"

To back up the transaction logs of all online and operational databases using the FULL or BULK-LOGGED recovery model on the instance you are connected to, apart from certain specified databases, use EXCLUDE. For example:

EXECUTE master..sqlbackup '-SQL "BACKUP LOGS EXCLUDE [SalesArchive] TO DISK = ''C:\Backups\<AUTO>''"'

SQLBackupC.exe -SQL "BACKUP LOGS EXCLUDE [SalesArchive] TO DISK = 'C:\Backups\<AUTO>'"

A full database backup is required to restore transaction log backups. If a full backup has not been taken before taking transaction log backups, the backups may fail with SQL Backup error VDI 1010 and SQL error 4214. You may encounter this error if you set up a transaction log backup job using BACKUP LOGS [*] or BACKUP LOGS EXCLUDE [<database name>], later add a database to the instance or change a database's recovery model from simple to full or bulk-logged, and do not take a full backup before the job runs.

To avoid this error, include WITH FULLIFREQUIRED to take a full backup of any databases that require one, before the transaction log backup is taken. For example:

EXECUTE master..sqlbackup '-SQL "BACKUP LOGS [*] TO DISK = ''C:\Backups\<AUTO>'' WITH FULLIFREQUIRED"'

SQLBackupC.exe -SQL "BACKUP LOGS [*] TO DISK = 'C:\Backups\<AUTO>' WITH FULLIFREQUIRED"

Any additional options you include in the command, such as compression, encryption, verification and network resilience settings will also be applied to the full backup. However, INIT, FILEOPTIONS and the ERASEFILES options will not be applied to the full backup.

Note that FULLIFREQUIRED is only available with SQL Server 2008 and later.