Sunday, November 19, 2017
 
 
     

SQL Server - Let there be Backups and Backups Happend

By Saleem Hakani on 9/21/2014

Backups are extremely important for the continuity of business. Many companies fire their engineers just because they didn’t have proper backups in place.

Wouldn’t it be great if there was a way that DBA’s and engineers didn’t have to worry about backups and SQL Server was smart enough to perform the backup based on workload pattern (data changes) or based on some defined recovery interval and the backups are auto-magically stored offsite without having to worry about putting them on tapes and then shipping them.

Let’s welcome Managed Backups in SQL Server 2014. The SQL Server product team has done an awesome job in delivering this new feature that takes away the headache of managing and maintaining database backups. SQL Server Managed Backup automatically performs backups of your databases and stores them to Windows Azure Blob Storage Service.

SQL Server Managed Backups can be enabled at the database or Instance level. With the database level backup, you can only perform managed backup per database. However, if you enable instance level managed backups then you can perform managed backups for all user databases and for any new future databases. (Note: System databases are not currently supported with Managed backups) SQL Server Managed backups can be used for both on premise and hosted environments (ex: VM).

The good thing about SQL Server Managed Backups is that you do not have to specify the type of backup or frequency of the backup. You only specify the retention period which will take care of determining both the type and frequency of the database backup. You can also use the new backup encryption feature introduced in SQL Server 2014 along with SQL Server Managed Backups.

What is a Retention Period?

The retention period is used by SQL Server Managed Backup to determine what backup files should be retained (or left) in the storage so that you can perform a point-in-time recovery of the database. (Currently supported values for retention are 1 thru 30 days)

Note: SQL Server Managed Backup is currently only supported using T-SQL.

Important: Only FULL and LOG backups are currently supported by SQL Server Managed Backups.

Full Database Backup is kicked-off automatically if:

1. SQL Server Managed Backup is enabled at the Database or Instance level.

2. T-log has grown >= 1 GB in size since the last full database backup.

3. The log chain is broken

4. T-Log backup file is deleted (by human or system error)

5. Backup files are overwritten with different backup or something else,

6. Etc.

T-Log Backup is Kicked-off automatically if:

1. Database has had >=5 MB of T-Log change

2. >= 2 hours since the last Log backup was taken

3. Anytime the T-Log backup is lagging behind a full database backup

4. T-Log backup history is deleted or is not found,

5. Etc.

Keep in mind:

1. System databases cannot be backed up using SQL Server Managed Backup

2. T-SQL and SSMS are both supported for performing SQL Server Managed Backup

3. Only Windows Azure Blob Storage Service is currently supported as backup storage option

4. Backup size of the database cannot exceed 1 TB in size currently. (you can use backup compression to reduce the backup size)

5. Databases must be in either FULL or BULK-LOGGED Recovery model. SQL Server Managed Backup doesn’t support databases with Simple Recovery model.

6. Enabling SQL Server Managed Backup at Instance level enables managed backups for new databases but doesn’t enable that for existing databases. You must configure each databases specifically.

7. A Windows Azure storage account and a SQL Credential that stores the authentication information to the storage account should both be created before configuring SQL Server Managed Backup to Windows Azure.

Let’s now look at an example on how to perform SQL Server Managed Backup:

Enabling SQL Server Managed Backup at Instance Level:

To Enable Instance Level Managed Backup:

EXEC smart_admin.sp_set_instance_backup

          @retention_days=30

          ,@credential_name='dbClinictoURL'

          ,@encryption_algorithm ='AES_128'

          ,@encryptor_type= 'Certificate'

          ,@encryptor_name='dbClinicBackupCert'

          ,@enable_backup=1;

To Disable Instance Level Managed Backup:

EXEC smart_admin.sp_set_instance_backup

               @enable_backup=0;

 

Enabling SQL Server Managed Backup at Database Level:

To Enable Database Level Managed Backup:

EXEC smart_admin.sp_set_db_backup

                  @database_name='dbClinic'

                  ,@enable_backup=1

                  ,@retention_days =30

                  ,@credential_name ='dbClinictoURL'

                  ,@encryption_algorithm ='AES_256'

                  ,@encryptor_type= 'Certificate'

                  ,@encryptor_name='dbClinicBackupCert';

 

To Disable Database Level Managed Backup:

EXEC smart_admin.sp_set_db_backup

               @database_name='dbClinic'

    ,@enable_backup=0;

 

Alternatively, you can also use SSMS for performing SQL Server Managed Backups as shown in the below screen.

Alternatively, you can also use SSMS for performing SQL Server Managed Backups as shown in the below screen.

Thank you,

Saleem Hakani

Automated Backup Solution
Backups and Restores
Cloud Backups
Managed Backups
SQL Server Backups
Minimize
SQL Article Tags
HOME   |   SQL ARTICLES   |   SCRIPTS   |   DISCUSSIONS   |   SQL TEAMS   |   JOBS   |   ABOUT US
Copyright [2014] by SQLCOMMUNITY.COM