SQL Stuff

Bring Offline Databases Online Before Backup

This script will Set all Offline Databases Online, and do a backup after the switch to online. I’ve also taken the liberty of adding some extra logic to use a network backup share, and add a time stamp to the file name.

use master;
set nocount on
declare @timestamp varchar(50)
declare @servername varchar(50)
declare @backup_offlinedb varchar(max)
declare @set_databases_online varchar(max)
set @servername = ( select @@servername )
set @timestamp = ( select rtrim(replace(replace(replace(convert(char, getdate(), 9), ‘:’, ‘-‘), ‘AM’, ‘ am’), ‘PM’, ‘ pm’)) ) set @backup_offlinedb = ”
set @set_databases_online = ”

select @backup_offlinedb = @backup_offlinedb +
‘backup database [‘ + name + ‘] to disk = ”\\MyBackupShare\’ + @servername + ‘\’ + name + ‘\Full\’ + name + ‘ Full ‘ + @timestamp + ‘.bkp” with format; ‘ + char(10) from sys.databases where name like ‘EDDS%’ and state_desc = ‘offline’

select @set_databases_online = @set_databases_online +
‘alter database [‘ + name + ‘] set online; ‘ + char(10)
from sys.databases where /*name like ‘%wildcard here%’ and*/ state_desc = ‘offline’

exec (@set_databases_online);
waitfor delay ’00:00:30′ — wait 30 seconds for online operation to complete. you should be fine, but doesn’t hurt to add some delay. exec (@backup_offlinedb);

Advertisements

Categories: SQL Stuff

Tagged as: , , , , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s