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.