Database restore – standardized

Standardized database restore solution. Doing some pre-restore checks and post-restore configurations with restored database. Is is also possible to restore databases that are part of Availability Group and joining them back. Whole solution is hosted on GitHub so refer to it for most actual version. SQL Server Database Restore on GitHub.

Technical preview #

Whole solution consist of two stored procedures, that can be called directly or from SQL Agent job steps. One procedure is needed for all restore scenarios, and another one is needed for Availability Group secondary replicas to be able to join database to AG.

• RestoreDatabase – perform every restore
• AddDatabaseOnSecondary – only needed on secondary replicas

Both procedures use Ola Halengreen’s CommandLog table for tracking operations done during execution and CommandExecute for executing commands within script. Both table and procedure is created during deployment and you are informed about it.

Deployment #

Only thing you have to do is to copy deplyment script. Copy script to SQL Server Management Studio and run it aganst SQL Server instance you are connected to or use multi-query from Registered Servers. Running script using multi-query is especially beneficial when creating procedures on AG replicas, you will avoid unnecessary clicking when connecting to every replica and running one by one.

Execution of stored procedures #

OK so you are all set now and you can start enjoying new stored procedures. You can use RestoreDatabase procedure to make common restore to standalone SQL Server database or you can use it to refresh database that is part of Availability Group. For detailed description of what is each procedure doing behind the scenes look into documentation file or go through messages after its execution.

Parameters #

Procedure RestoreDatabase

Procedure AddDatabaseOnSecondary (this is executed from RestoreDatabase on primary replica)

Restore database #

EXEC [master].[dbo].[RestoreDatabase]
@BackupFile = N'\\Path\To\BackupFile\Backup.bak',
@Database = N'TestDB_Restored',
@CheckModel = 'Y',
@PreservePermissions = 'Y',
@LogToTable = 'Y'

During restore of database following tasks are done in specified order:

1. Checking – core requirements before any executing
   1. permissions
   2. procedure CommandExecute
   3. table CommandLog
2. Preparing – collect some important data and build restore command
   1. gathering instance info
   2. gathering backup file info
   3. building restore command
3. Restoring database – performing restore from given backup using CommandExecute stored procedure (include logging in CommandLog table)
4. Post configuration – doing some reconfiguration to restored database
   1. set auto-growth values based on model database – since v1.2
   2. shrink log file
   3. rename files
   4. preserve users and roles with permissions from original database – since v1.3
   5. set multi user
   6. set online
5. Informing about successful restore

Restore database in AG #

EXEC [master].[dbo].[RestoreDatabase]
@BackupFile = N'\\Path\To\BackupFile\Backup.bak',
@Database = N'TestDB_Restored',
@AvailabilityGroup = N'AvailabilityGroupName',
@SharedFolder = N'\\SERVER\SharedFolder',
@LogToTable = 'Y'

During restore of database that is already or going to be part of Availability Group there are some prerquisites that have to be met to start data synchronizing across replicas. See this article for more info about all prerequisites. From beginning it is doing the same as restore of common database so only additional steps described here (what continues after restore of database):

1. Take full backup
2. Take backup of transaction log
3. Add database to Availability Group on primary replica
4. Iterate all secondary replicas
   1. Check if linked server for secondary replica exists otherwise create it
   2. Check RPC Out config for linked server
   3. Adding database to Availability Group by calling stored procedure AddDatabaseOnSecondary – see more info in next section
5. Informing about successful joining on all secondaries
6. Informing about successful restore

Procedure AddDatabaseOnSecondary  is executed only on secondary replicas where called by procedure RestoreDatabase from primary replica via linked server. So you only need this procedure if using restores of databases that are part of Availability Group. You can remove it once when planning to use RestoreDatabase only for common databases.

Following command is constructed automatically within RestoreDatabase execution but you can  call procedure directly if you want:

EXEC [master].[dbo].[AddDatabaseOnSecondary]
@FullBackupFile = N'\\Path\To\BackupFile\FullBackup.bak',
@TlogBackupFile = N'\\Path\To\BackupFile\TlogBackup.trn',
@Database = N'TestDB_Restored',
@CheckModel = 'Y',
@AvailabilityGroup = N'AvailabilityGroupName',
@LogToTable = 'Y'

Actions done during execution of this procedure:

1. Checking requirements
   1. permissions
   2. procedure CommandExecute
   3. table CommandLog
   4. availability group
2. Restoring database
   1. restore full backup
   2. restore of transaction log
3. Add database to Availability Group
   1. add on secondary replica
4. Informing about sucessfull joining on secondary replica

Possible problems #

There was testing of the solution ongoing for several weeks for debugging and tuning purposes and all known problems has been fixed already, but as everything also this script can cause some issues in different environments.

I’m assuming only following possible issues:

• problems with accessing secondary replica via linked server – Login failed for User ‘NT AUTHORITY\ANONYMOUS LOGON’
• When executing from SQL Agent job, ensure that account that is used for execution has sufficient permissions, especially in case restoring database into Avaialability Group as there are actions done on all secondary replicas.

And some other possible problems can be related to OH stuff in the solution so, please be so kind and try to check this FAQ https://ola.hallengren.com/frequently-asked-questions.html first before asking me directly.

Reporting issues #

Please report all found issues, here are some channels:

• tomas.rybnicky@wetory.eu
• Use GitHub Issues