I had a requirement to script full backups and this was the simplest code I could find …

--backupAll.sql
DECLARE @dbname VARCHAR(50)
DECLARE @fileName VARCHAR(256)
DECLARE @path VARCHAR(256)

SET @path = N'\somebackupserverbackupshareservernameinstancename'

DECLARE db_cursor CURSOR FOR
SELECT name FROM master.dbo.sysdatabases
WHERE name != 'tempdb'

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbname

WHILE @@FETCH_STATUS = 0
BEGIN
       SET @fileName = @path + @dbname + '_full.Bak'
       BACKUP DATABASE @dbname TO DISK = @fileName with init, noskip, noformat, nounload, stats=10, checksum
       FETCH NEXT FROM db_cursor INTO @dbname
END

CLOSE db_cursor
DEALLOCATE db_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