Initialization

Initialization is the process of recovering the databases on your target SQL instance so we can begin the process or restoring log files. This tool can help with the initialization. It can also automatically initialize new databases. The following initialization options are supported:

Manual

You can always restore the databases manually on your target instance.

MSDB

The MSDB backup history on the source SQL instance can be used to initialize new databases. You can initialize new databases created on the primary instance by specifying a SourceConnectionString.

LogShippingService.exe --SourceConnectionString "Data Source=PRIMARY1;Integrated Security=True;Encrypt=True;Trust Server Certificate=True"

  "Config": {
      "SourceConnectionString": "Data Source=PRIMARY1;Integrated Security=True;Encrypt=True;Trust Server Certificate=True",  

To be initialized, the database should be online with FULL or BULK LOGGED recovery model. The database needs a FULL backup and the backup location must be accessible on the target server. If you use Ola Hallengren's backup solution, the @ChangeBackupType parameter can be used to create a FULL backup for new databases when the LOG backup job runs.

If you are backing up to a local path, it's possible to do a string find/replace to convert it into a UNC path that is accessible on the secondary node:

LogShippingService.exe --SourceConnectionString "Data Source=PRIMARY1;Integrated Security=True;Encrypt=True;Trust Server Certificate=True" --MSDBPathFind "B:\Backup" --MSDBPathReplace "\\SERVERNAME\Backup"

  "Config": {
      "SourceConnectionString": "Data Source=PRIMARY1;Integrated Security=True;Encrypt=True;Trust Server Certificate=True",  
      "MSDBPathFind": "B:\\Backup",
      "MSDBPathReplace": "\\\\SERVERNAME\\Backup",

Disk

To initialize from folder you also need to specify the folder locations for your FULL/DIFF backups. Use the {DatabaseName} token in place of the database name.

LogShippingService.exe --FullFilePath "\\BACKUPSERVER\Backups\SERVERNAME\{DatabaseName}\FULL" --DiffFilePath "\\BACKUPSERVER\Backups\SERVERNAME\{DatabaseName}\DIFF"

  "Config": {
    "FullFilePath": "\\\\BACKUPSERVER\\Backups\\SERVERNAME\\{DatabaseName}\\FULL",
    "DiffFilePath": "\\\\BACKUPSERVER\\Backups\\SERVERNAME\\{DatabaseName}\\DIFF",

In the example above, the service enumerates all the folders in the path \\BACKUPSERVER\Backups\SERVERNAME\ (Taken from \\BACKUPSERVER\Backups\SERVERNAME\{DatabaseName}\FULL). If processes each folder/database in parallel. If the database doesn't exist it will restore the database from the last FULL/DIFF backup.

If you backup to multiple files, files with the same modified date are treated as part of the same backup set. This avoids having to parse the file name and allows for different file naming formats.

URL

To initialize from azure blob you need to specify FullFilePath and DiffFilePath using {DatabaseName} token in place of the database name. If ContainerUrl and SASToken are specified these paths will be treated as azure blob paths rather than unc paths.

LogShippingService.exe --ContainerUrl "https://your_storage_account.blob.core.windows.net/your_container_name" --FullFilePath "FULL/SERVERNAME/{DatabaseName}/" --DiffFilePath "DIFF/SERVERNAME/{DatabaseName}/" --SASToken "?sp=..." --LogFilePath "LOG/SERVERNAME/{DatabaseName}/"

  "Config": {
    "ContainerUrl": "https://your_storage_account.blob.core.windows.net/your_container_name",
    "SASToken": "?sp=...",
    "FullFilePath": "FULL/SERVERNAME/{DatabaseName}/",
    "DiffFilePath": "DIFF/SERVERNAME/{DatabaseName}/",

If you backup to multiple files, files with the same modified date are treated as part of the same backup set. This avoids having to parse the file name and allows for different file naming formats.

⚠️ Note: S3 isn't currently supported

Other initialization Options

Partial Backups and READONLY filegroups

Log Shipping can be initialized from PARTIAL backups. Partial backups are backups created using the READ_WRITE_FILEGROUPS option. This excludes any READONLY filegroups which you need to create separate backups for. Specify the location of your READONLY backups using ReadOnlyFilePath. By default all filegroups will be required before a restore operation is attempted. If you want the RESTORE to proceed without all the READONLY filegroups, set RecoverPartialBackupWithoutReadOnly option to true (ReadOnlyFilePath can be omitted if true).

LogShippingService.exe --FullFilePath "\\BACKUPSERVER\Backups\SERVERNAME\{DatabaseName}\FULL" --DiffFilePath "\\BACKUPSERVER\Backups\SERVERNAME\{DatabaseName}\DIFF" --LogFilePath "\\BACKUPSERVER\Backups\SERVERNAME\{DatabaseName}\LOG" --ReadOnlyFilePath "\\BACKUPSERVER\Backups\SERVERNAME\{DatabaseName}\READONLY" --RecoverPartialBackupWithoutReadOnly false

  "Config": {
    "LogFilePath": "\\\\BACKUPSERVER\\Backups\\SERVERNAME\\{DatabaseName}\\LOG",
    "FullFilePath": "\\\\BACKUPSERVER\\Backups\\SERVERNAME\\{DatabaseName}\\FULL",
    "DiffFilePath": "\\\\BACKUPSERVER\\Backups\\SERVERNAME\\{DatabaseName}\\DIFF",
    "ReadOnlyFilePath": "\\\\BACKUPSERVER\\Backups\\SERVERNAME\\{DatabaseName}\\READONLY",
    "RecoverPartialBackupWithoutReadOnly": false,

Note: If you are using PARTIAL backups, check sys.master_files for any files in the RECOVERY_PENDING state. These files will need to be recovered.

Polling Frequency for new DBs

You can adjust the frequency it polls for new databases using PollForNewDatabasesFrequency (Specify a time in minutes. Default 10min). Or you can use PollForNewDatabasesCron instead if you want to use a cron expression for more advanced scheduling.

Initialize Simple recovery DBs

It doesn't make sense to include SIMPLE recovery model DBs for log shipping so they are excluded by default. It could be useful to include them in a disaster recovery scenario though by specifying InitializeSimple.

Max Backup Age for Initialization

The log shipping service doesn't consider backups older than 14 days by default. If you are initializing from disk this will prevent restoring a backup for an old database that was deleted. It also prevents initializing from an old backup that will need a large amount of log files applied to bring it up-to-date. If you need to restore from an older backup you can adjust MaxBackupAgeForInitialization.

Moving Files

If you need to adjust the file paths for the initial restore the MoveDataFolder, MoveLogFolder and MoveFileStreamFolder can be specified. If possible it's best to keep the drive configuration and file paths identical between the log shipping primary and secondary.

Example

LogShippingService.exe --PollForNewDatabasesFrequency 10 --InitializeSimple false --MaxBackupAgeForInitialization 30 --MoveDataFolder "D:\Data" --MoveLogFolder "L:\Log" --MoveFileStreamFolder "F:\FileStream"

  "Config": {
      "PollForNewDatabasesFrequency" : 10,
      "InitializeSimple": true,
      "MaxBackupAgeForInitialization": 30,
      "MoveDataFolder": "D:\\Data",
      "MoveLogFolder": "L:\\Log",
      "MoveFileStreamFolder": "F:\\FileStream",

Include/Exclude Databases

You can also explicitly include or exclude databases in the config if required.

Only log ship DB1, DB2, DB3 and DB4:

LogShippingService.exe --IncludedDatabases DB1 DB2 DB3 DB4

  "Config": {
      "IncludedDatabases": ["DB1", "DB2", "DB3", "DB4"],

Add DB5 to the existing list:

LogShippingService.exe --IncludeDatabase DB5

Log ship everything EXCEPT DB1, DB2, DB3 and DB4.

LogShippingService.exe --ExcludedDatabases DB1 DB2 DB3 DB4

  "Config": {
      "ExcludedDatabases": ["DB1", "DB2", "DB3", "DB4"],

Add DB5 to the existing list:

LogShippingService.exe --ExcludeDatabase DB5