Script to take backups of all databases at a time in SQL Server

You can use below script to take the backup of all databases in an SQL Server.

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 (‘tempdb’)
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,’:’,’_’),’\’,’_’)+’.bak’

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

FETCH NEXT FROM DATABASES_CURSOR INTO @DBName
END

CLOSE DATABASES_CURSOR
DEALLOCATE DATABASES_CURSOR

Posted by Sheikvara
+919840688822, +919003270444
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