Thursday, 18 March 2010

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.


Related Posts



Transaction Log Backups


Managing You Backup Files

No comments:

Post a Comment

Featured post

SQL Server 2008 and SQL Server 2008 R2 - OUT of SUPPORT July 2019

Both SQL Server 2008 and SQL Server 2008 R2 go out of extended support with Microsoft in July 2019. That’s fast approaching and as we enter ...