Blog Post

Script: Backup All your Databases

,

We all know the importance of taking regular backups of our SQL Server databases. This is a useful little script that I use to take a full backup of my databases on an instance. It can be used in combination with the SQL Agent to schedule:



DECLARE @DBName varchar(255)
DECLARE @DS VARCHAR(50)
DECLARE @Path VARCHAR(255)



DECLARE Full_Backup CURSOR FOR

SELECT name from sys.databases
WHERE name NOT IN ('TEMPDB')
AND state = 0 --Exclude offline databases, they won't backup if they offline anyway
AND Source_database_id is null -- Removes snapshots from the databases returned, these can'
t be backed up eith

OPEN Full_Backup


FETCH NEXT FROM Full_Backup
INTO @DBName

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
--Set the filename values of the backup files
SET @DS = REPLACE(CONVERT(VARCHAR(10), GETDATE(), 111), '/', '') + '_'
+ REPLACE(CONVERT(VARCHAR(8), GETDATE(), 108), ':', '')
SET @Path = 'C:\DATA\Backup\'
SET @Path = @path + @DBNAME + '_' + @DS + '.bak'
--Take the backup

BACKUP DATABASE @DBNAME
TO DISK = @Path
WITH
FORMAT, INIT, SKIP, NOREWIND,
NOUNLOAD, STATS = 10

FETCH NEXT FROM Full_Backup
INTO @DBName


END

CLOSE Full_Backup
DEALLOCATE Full_Backup
GO

 

Your backups are only good if you can run a restore from them, it is important to regularly test this process.

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating