Snapshot backup is beautiful option during releases and deploying changes
specially when the database size is large like few hundreds of GBs. We have a practice to take snapshot backup during the release and delete once testing is completed on a 600+GB database.
Recently I had an opportunity to rollback the change and restore the database using snapshot backup on a database with Always-On configuration. I have a few learning worth sharing!
* Suspend data movement of the database in Availability group.
* Removed the database from Availability group
* Change the status of database to Single user mode
* Restore the database from Snapshot backup
* Add the database back to Availability group
-- Suspend data movement of the database in Availability group. ALTER DATABASE database_name SET HADR SUSPEND -- Remove the database from Availability group ALTER AVAILABILITY GROUP MyAG REMOVE DATABASE database_name; -- Change the status of database to Single user mode ALTER DATABASE [database_name] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; -- Restore the database from Snapshot backup RESTORE DATABASE [database_name] FROM DATABASE_SNAPSHOT = 'database_name_Snapshot'; -- -- Database will be available for testing now -- Check previous blog to add the database back into Availability group --