This is a complete copy, which stores all the objects of the database: Tables, procedures, functions, views, indexes etc. It is the foundation of any kind of backup. There are other backup types available as well:Ī full backup, as the name implies, backs up everything. This understanding would, in turn, help us decide our backup-and-restore strategy.įollowing are the most common types of backups available in SQL Server: In this article, however, we are going to focus on the types of backups that are available to us, and understand how to pick what we need, and what aspects we base that decision on. You can use the BACKUP MASTER KEY statement to create those backups.SQL Server backups, in itself, is a vast subject so vast, there are multiple books written about them. It is therefore important that you have a good backup of each DMK. It potentially can be required to access all your encrypted data. The Database Master Key is the root of the database encryption hierarchy in SQL Server.

And remember to also safe that passphrase, as without it you cannot restore the key. The backup itself should be stored in a secure off-site location. This should, as always when dealing with encryption keys, be a strong and unique passphrase. The second is the password that is used to protect the backup file from prying eyes. This file must not exist, as an existing backup file will cause an error. The first is part of the TO FILE clause and specifies where the backup will be stored. Like the BACKUP SERVICE MASTER KEY statement, the BACKUP MASTER KEY statement has just two 'parameters'. Note that the word 'database' is omitted from this statement to prevent confusion with the BACKUP DATABASE statement.

Sql server backup master