1) Create a text file and name it Backup_All_Databases.sql (or what ever you want).

2) Paste the below script in it:
Code:

DECLARE @BackupFile varchar(255), @DB varchar(30), @Description varchar(255), @LogFile

varchar(50)
DECLARE @Name varchar(30), @MediaName varchar(30), @BackupDirectory nvarchar(200)
SET @BackupDirectory = ‘C:\Backuped_SQL_DB\’
–Add a list of all databases you don’t want to backup to this.
DECLARE Database_CURSOR CURSOR FOR SELECT name FROM sysdatabases WHERE name <> ‘tempdb’ AND

CURRENT_TIMESTAMP) + ‘.’
END
BACKUP DATABASE @DB TO DISK = @BackupFile
WITH NAME = @Name, DESCRIPTION = @Description ,
MEDIANAME = @MediaName, MEDIADESCRIPTION = @Description ,
STATS = 10
FETCH next FROM Database_CURSOR INTO @DB
END
CLOSE Database_Cursor
DEALLOCATE Database_Cursor

name <> ‘model’ AND name <> ‘Northwind’
OPEN Database_Cursor
FETCH next FROM Database_CURSOR INTO @DB
WHILE @@fetch_status = 0

BEGIN
SET @Name = @DB + ‘( Daily BACKUP )’
SET @MediaName = @DB + ‘_Dump’ + CONVERT(varchar, CURRENT_TIMESTAMP , 112)
SET @BackupFile = @BackupDirectory + + @DB + ‘_’ + ‘Full’ + ‘_’ +
CONVERT(varchar, CURRENT_TIMESTAMP , 112) + ‘.bak’
SET @Description = ‘Normal’ + ‘ BACKUP at ‘ + CONVERT(varchar, CURRENT_TIMESTAMP) +

‘.’

IF (SELECT COUNT(*) FROM msdb.dbo.backupset WHERE database_name = @DB) > 0 OR @DB =

‘master’
BEGIN
SET @BackupFile = @BackupDirectory + @DB + ‘_’ + ‘Full’ + ‘_’ +
CONVERT(varchar, CURRENT_TIMESTAMP , 112) + ‘.bak’
–SET some more pretty stuff for sql server.
SET @Description = ‘Full’ + ‘ BACKUP at ‘ + CONVERT(varchar,

CURRENT_TIMESTAMP) + ‘.’
END
ELSE
BEGIN
SET @BackupFile = @BackupDirectory + @DB + ‘_’ + ‘Full’ + ‘_’ +
CONVERT(varchar, CURRENT_TIMESTAMP , 112) + ‘.bak’
–SET some more pretty stuff for sql server.
SET @Description = ‘Full’ + ‘ BACKUP at ‘ + CONVERT(varchar,

3) Open scheduler and create a new task that calls the below command line:

Code:
sqlcmd -S . -i “C:\Backups\BackupSettings\Backup_All_Databases.sql”

Note: I also made a batch file that had the same command line but added a pause so I can

test it before the scheduler runs it.
EXAMPLE:

Code:
sqlcmd -S . -i “C:\Backups\BackupSettings\Backup_All_Databases.sql”
pause

4) Just run it every night in a scheduler and you are done.

Clean up Old Backup Files.

If you are running Windows Server 2003 you can also run a command utility to delete any

files older then x number of days. This helps keep it cleaned up. Just paste this in a

batch file and schedule the batch file.

Code:

echo on

rem First Delete old SQL Backup Files

FORFILES /p C:\Backuped_SQL_DB /s /m *.* /d -3 /c “CMD /C del /Q @FILE”

This will also work on SQL Express

rem pause