BACKUP SERVICE MASTER KEY with date in file name

A service master key is created when SQL Server instance is run for the first time. And it's regeneratred every time you change service account or its password. You should protect it with backup.

Syntax for BACKUP SERVICE MASTER KEY is described here:
http://msdn.microsoft.com/en-us/library/ms190337.aspx

Important notice is it does not allow to overwrite previous file. So you have to delete it before executing statement or use unique file names for every day backup.

This is my simple script to have BACKUP SERVICE MASTER KEY done with date in file name (so it's unique per day). It's compatible with SQL Server 2008 and above (older releases does not allow to set value while declaring variable).

Feel free to use it!


DECLARE @tsql NVARCHAR(512)
DECLARE @holder NVARCHAR(16) = '{DATE}'
DECLARE @path_to_file NVARCHAR(256) = 'D:\sqlbackup\keys\service_master_key_{DATE}.bak'
DECLARE @password NVARCHAR(16) = 'mysecretpassword'
DECLARE @current_day_string NCHAR(8) = CONVERT(NCHAR(8),GETDATE(),112)

SET @path_to_file = REPLACE(@path_to_file, @holder, @current_day_string)
SET @tsql = 'BACKUP SERVICE MASTER KEY TO FILE = '''+@path_to_file+''' ENCRYPTION BY PASSWORD = '''+@password+''''

EXECUTE sp_executesql @tsql