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.