Restoring & Renaming Single Database

Owned by Andrew Paydon
Last updated: Jan 23, 2020 by John Micallef (Deactivated)

When a single DB (v68+) requires renaming, documented process/issues to be listed here

Scenarios

  1. Restore database as a different name on the same server as existing database, eg. Synergetic_AUVIC_CLIENTNAME_PRD to Synergetic_AUVIC_CLIENTNAME_TST on prod/test system
  2. Restore database to a different server, eg. eg. Synergetic_AUVIC_CLIENTNAME_PRD from production server to Synergetic_AUVIC_CLIENTNAME_TST on test server

SQL Agent Job

See v68 SQL Agent Restore Job which is a basic script to create a Restore Job using the below process and modify the Database Name (TODO: Replace Config values for things like CoreAPI)

Manual Process

  1. Restore the database using spSynRestoreDB

    Example of spSynRestoreDB
    USE master
GO
exec spSynRestoreDB
 @BackupFolder = 'E:\SQLBackup\Synergetic_AUVIC_CLIENTCODE_PRD\FULL',
 @RestoreDataFolder = 'E:\MSSQL13.MSSQLSERVER\MSSQL\DATA',
 @RestoreLogFolder = 'E:\MSSQL13.MSSQLSERVER\MSSQL\DATA',
 @RestoreIndividualDatabaseFromName = 'Synergetic_AUVIC_CLIENTCODE_PRD',
 @RestoreIndividualDatabaseToName = 'Synergetic_AUVIC_CLIENTCODE_TST', 
 @SetRecoveryModel = 'FULL',  -- or 'SIMPLE'
 @RestoreTransactionLogFlag = 0,
 @ForceOverwriteExistingDBFlag = 1

2. Script out all SQL agent jobs for the database from the prod instance

  • Synergetic Daily Maintenance : Synergetic_AUVIC_CLIENTCODE_PRD
  • Synergetic Email Processor : Synergetic_AUVIC_CLIENTCODE_PRD
  • Synergetic Message Queue Processor : Synergetic_AUVIC_CLIENTCODE_PRD
  • Synergetic Portal Detail Change Notification : Synergetic_AUVIC_CLIENTCODE_PRD
  • Synergetic Receipts Online Processor : Synergetic_AUVIC_CLIENTCODE_PRD

Replace all instances of "_CLIENTCODE_PRD" with "_CLIENTCODE_TST" then execute the script on the destination server

3. Rebuild Synonyms

  1. Delete the synonyms 'SynergyOne...'
  2. Rebuild the synonyms using 'exec spuV68UpgradeCreateSynonymsAll'
  3. Update the 'DatabaseName' field in the syndatabases table (if required) to reflect the name of the single database

Known Issues

If restoring via Scenario 1 then the "Server Login" user name will continue to match the original database name. 

For example, the database "Synergetic_AUVIC_CLIENTNAME_TST" will be restored with the PRD user account:

zSynergetic_AUVIC_CLIENTCODE_PRD_ServerLogin

It is not possible to rename this account, as it is still used by the PRD system/database. There may be issues running the DB patcher on the test instance in this case, unless a new user account is provisioned for "zSynergetic_AUVIC_CLIENTCODE_PRD_ServerLogin", which is done via the CreateZUsersApp (available to the Synergetic Systems team only). This will also require reconfiguration of the applications, which is not desirable for an automated test server restore routine.

Synonyms will not automatically point to the new database after renaming - make sure to rebuild them with step 3 above.

If restoring for Scenario 2 then the user login and user name can be renamed (scripted) as an addition to the restore process. There may be some additional msdb etc dependencies required for this account on a test system.

Conclusion

Further testing on these scenarios and step-by-step solution is required in order to support renaming of databases.