Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

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.

Comments

Posted by Dukagjin Maloku on 21 March 2010

Not bad, nice script! I use maintenance plan for Backup SYS & Users DBs. I agree, without testing restore, it is not properly backup.  

Leave a Comment

Please register or log in to leave a comment.