We support Microsoft .NET Framework 2.0 & 1.1, all versions of Access, SQL 2000, SQL 7.0, SQL 2005 Express, SOAP, FrontPage 2002, 2003, Visual Studio 2005, Index Server, XML, UDDI, & Mobile device support. We also offer great third party tools like SmarterMail, Merak Mail, SmarterStats, PHP, Perl, MySql, DeepMetrix Livestats XSP 8.0.   We support Microsoft .NET Framework 2.0 & 1.1, all versions of Access, SQL 2000, SQL 7.0, SQL 2005 Express, SOAP, FrontPage 2002, 2003, Visual Studio 2005, Index Server, XML, UDDI, & Mobile device support. We also offer great third party tools like SmarterMail, Merak Mail, SmarterStats, PHP, Perl, MySql, DeepMetrix Livestats XSP 8.0.
 Sunday, March 11, 2007

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

2) Paste the below script in it:

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 = 'E:\SQLBackup\'
--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 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, 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

Open scheduler and create a new task that calls the below command line:
            sqlcmd -S . -i "E:\Backup.sql"

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.

echo on

rem First Delete old SQL Backup Files

FORFILES /p E:\SQLBackup /s /m *.* /d -3 /c "CMD /C del /Q @FILE"

rem pause

Name
E-mail
Home page

Comment (HTML not allowed)  

Enter the code shown (prevents robots):