Er. alokpandey's Blog

Backup ALL your SQL Server 2005 databases using ONE script

Posted in Uncategorized by Alok Kumar Pandey on July 26, 2010

DECLARE @DBName varchar(255)

DECLARE @DATABASES_Fetch int

DECLARE DATABASES_CURSOR CURSOR FOR
select
DATABASE_NAME = db_name(s_mf.database_id)
from
sys.master_files s_mf
where
— ONLINE
s_mf.state = 0

— Only look at databases to which we have access
and has_dbaccess(db_name(s_mf.database_id)) = 1

— Not master, tempdb or model
and db_name(s_mf.database_id) not in (‘Master’,’tempdb’,’model’)
group by s_mf.database_id
order by 1

OPEN DATABASES_CURSOR

FETCH NEXT FROM DATABASES_CURSOR INTO @DBName

WHILE @@FETCH_STATUS = 0
BEGIN
declare @DBFileName varchar(256)
set @DBFileName = datename(dw, getdate()) + ‘ – ‘ +
replace(replace(@DBName,’:’,’_’),’\’,’_’)

exec (‘BACKUP DATABASE [‘ + @DBName + ‘] TO DISK = N”c:\db backup\’ +
@DBFileName + ”’ WITH NOFORMAT, INIT, NAME = N”’ +
@DBName + ‘-Full Database Backup”, SKIP, NOREWIND, NOUNLOAD, STATS = 100’)

FETCH NEXT FROM DATABASES_CURSOR INTO @DBName
END

CLOSE DATABASES_CURSOR
DEALLOCATE DATABASES_CURSOR

Advertisements

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

%d bloggers like this: