Backing up databases

Today I had to figure out why the backup maintenance plan was failing on my client’s server.

I have a t-sql script that runs, that grabs all databases and backs them up.
You can modify as needed… here’s the gist of it. If you need help with the script send me an email.
Loren

DECLARE @name VARCHAR(50) — database name
DECLARE @path VARCHAR(256) — path for backup files
DECLARE @fileName VARCHAR(256) — filename for backup
DECLARE @fileDate VARCHAR(50) — used for file name
DECLARE @backupname varchar(128), @errormessage varchar(256)

SET @path = ‘E:\MSSQL\BACKUP’

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN (‘master’,’model’,’msdb’,’tempdb’)


OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN


SET @fileName = @path + ‘\’ + @name + ‘\’+ @name + ‘_’ + @fileDate + ‘.BAK’
set @backupname = @name + ‘- Full Database Backup’

print ‘filename:’ + @filename

BACKUP DATABASE @name
TO DISK = @fileName
WITH NOFORMAT, NOINIT,
NAME = @backupname
, SKIP, NOREWIND, NOUNLOAD, STATS = 10

declare @backupSetId as int

select @backupSetId = position
from msdb..backupset
where database_name = @name
and backup_set_id=(select max(backup_set_id)
from msdb..backupset
where database_name = @name )

if @backupSetId is null
begin
set @errormessage = ‘Verify failed. Backup information for database ”’ + @name + ”’ not found.’
raiserror(@errormessage, 16, 1)
end

RESTORE
VERIFYONLY
FROM
DISK = @filename
WITH FILE = @backupSetId, NOUNLOAD, NOREWIND



FETCH NEXT FROM db_cursor INTO @name
END

CLOSE db_cursor
DEALLOCATE db_cursor

Comments or questions for Loren?

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s