SQL: Backup Database and Purge It From SQL Server

/* Make a Final Backup of Database, Purge Its Backup and Restore History, and Remove Database */

/* Make a Final Backup */
BACKUP DATABASE DATABASE_NAME TO DISK = '\\ARCHIVE\Databases\DATABASENAME.bak' WITH COMPRESSION
GO

/* Purge Backup Chain Metadata */
EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'DATABASENAME'
GO

/* Set Exclusive Access of SQL Server Database before Dropping It  */
/* Preempt this error:
Drop failed for Database 'DATABASENAME'. (Microsoft.SqlServer.Smo)
Cannot drop database "DATABASENAME" because it is currently in use. (Microsoft SQL Server, Error: 3702)
Cannot drop database because it is currently in use Microsoft SQL Server Error 3702
*/
USE [master]
GO
ALTER DATABASE [DATABASENAME] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE [DATABASENAME] -- Now Delete the Database
GO

Leave a Reply

Your email address will not be published. Required fields are marked *