Periodically, databases you have in use are no longer needed, but you’d like to keep a backup of them around. At my company, we’ll take the databases offline for a period of time, and then archive them.
I’ve created a script that will backup each offline user database, archive the BAK file and then drop the database.

— Start Script

— Archive Database
— Specify a database set prefix in the cursor select
— to make a backup of the database and delete them from the server.
— Will set them online (if offline) and remove replication (if being replicated)

use [master]

— Quit if there is an error, so database doesn’t get deleted without being backed up first
set xact_abort on

— Include trailing slash in path
declare @BackupDirectory nvarchar(200) = ‘D:\BAK\DevArchive_20130510\’

declare Cursor_Template cursor local fast_forward

select name, case when state_desc = ‘OFFLINE’ then 1 else 0 end as IsOffline,
is_published as IsReplicated
from sys.databases
where state_desc = ‘OFFLINE’ — All Offline DBs
order by name

declare @DatabaseName varchar(128)
declare @SQL nvarchar(4000)
declare @Offline tinyint
declare @IsReplicated tinyint

open Cursor_Template

fetch next from Cursor_Template into @DatabaseName, @Offline, @IsReplicated

while @@FETCH_STATUS = 0


— Start

— Bring online if not currently online
if @Offline = 1
set @SQL = ‘alter database [‘ + @DatabaseName + ‘] set online;’
exec sp_executesql @SQL

— If replicated, remove replication
if @IsReplicated = 1
set @SQL = ‘exec dbo.sp_removedbreplication ”’ + @DatabaseName + ”’;’
exec sp_executesql @SQL

— Backup database

declare @BackupPath nvarchar(300)
set @BackupPath = @BackupDirectory + @DatabaseName + ‘.bak’
set @SQL = ‘backup database [‘ + @DatabaseName + ‘] to disk = ”’ + @BackupPath +
”’with format, name = N”’ + @DatabaseName + ‘ Backup”’
exec sp_executesql @SQL

— Drop database
set @SQL = ‘alter database [‘ + @DatabaseName + ‘] set single_user with rollback immediate; ‘
set @SQL += ‘drop database [‘ + @DatabaseName + ‘];’
exec sp_executesql @SQL

fetch next from Cursor_Template into @DatabaseName, @Offline, @IsReplicated

close Cursor_Template

deallocate Cursor_Template


— End Script