SQL Server Maintenance Solution

Preview Versions

Here you can read about upcoming features and download preview versions. Please contact me, if you feel that any of these features could be useful in your environment.

Back up databases in parallel

If you have hundreds of even thousands of databases on the same server, then backup can take a long time.

I am working on a new feature in DatabaseBackup that lets you back up databases in parallel. The idea is to let multiple jobs work together as if it was one job.

Here is how it works: You create multiple identical jobs. The jobs need do to have the parameter @ExecutionMode = 'DATABASES_IN_PARALLEL'.

EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',
@Directory = 'C:\Backup',
@BackupType = 'FULL',
@Compress = 'Y',
@CheckSum = 'Y',
@ExecutionMode = 'DATABASES_IN_PARALLEL',
@DatabaseOrder = 'SIZE_DESC'

This works by using a queue - table. Stored procedure executions that have identical parameter values are getting on the same queue. When a stored procedure execution is done with one database, it will take the next database from the queue.

Download: DatabaseBackup-DatabasesInParallel-CTP.sql, Queue-CTP.sql, and QueueDatabase-CTP.sql

Configure directory structures, file names, and file extensions

One of the most requested features in a long time has been to be able to configure the directory structures. I have also got some requsts to configure the file names, and the file extensions. I have been working on making all this configurable.

I have an early CTP version. Here is how it will look to get the same directory structure and file names as the current defaults. You don't need to specify it then as it will then use the defaults, but it gives you an idea how the parameters work.

EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',
@Directory = 'C:\Backup',
@BackupType = 'FULL',
@DirectoryStructure = '{ServerName}${InstanceName}{DirectorySeparator}{DatabaseName}{DirectorySeparator}{BackupType}_{PartialDatabase}_{CopyOnlyDatabase}',
@AvailabilityGroupDirectoryStructure = '{ClusterName}${AvailabilityGroupName}{DirectorySeparator}{DatabaseName}{DirectorySeparator}{BackupType}_{PartialDatabase}_{CopyOnlyDatabase}',
@FileName = '{ServerName}${InstanceName}_{DatabaseName}_{BackupType}_{PartialDatabase}_{CopyOnlyDatabase}_{Year}{Month}{Day}_{Hour}{Minute}{Second}_{FileNumber}.{FileExtension}',
@AvailabilityGroupFileName = '{ClusterName}${AvailabilityGroupName}_{DatabaseName}_{BackupType}_{PartialDatabase}_{CopyOnlyDatabase}_{Year}{Month}{Day}_{Hour}{Minute}{Second}_{FileNumber}.{FileExtension}'

As you see I am using tokens with {}. Any feedback on the syntax for the tokens?

Any tokens that are not relevant will be removed. E.g. the token {InstanceName} will be removed if it is a default instance.

I am using a token for the directory seperator as that is different on Windows and Linux. It is also different for DISK and URL.

The @DirectoryStructure and @AvailabilityGroupDirectoryStructure parameters applies also when you are doing backup to Azure Blob Storage (the @URL parameter).

To do: Improved input parameter checking

Download: DatabaseBackup-Configurable-CTP.sql