/* 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
Categories:
