1st save this code with backup.sql name.
———————————————————————————
DECLARE @name VARCHAR(50) — database name
DECLARE @path VARCHAR(256) — path for backup files
DECLARE @fileName VARCHAR(256) — filename for backup
DECLARE @fileDate VARCHAR(20) — used for file name

SET @path = ‘C:\Backup\’

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN (‘master’,’model’,’msdb’,’tempdb’)

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + ‘_’ + @fileDate + ‘.BAK’
BACKUP DATABASE @name TO DISK = @fileName

FETCH NEXT FROM db_cursor INTO @name
END

CLOSE db_cursor
DEALLOCATE db_cursor

————————————————————————————

Automating the Database Backup
You can automate the backup process by creating two Scheduled Tasks.
SQLCMD Scheduled Task

1. First, create a Scheduled Task to automate the .SQL script
created above. Use the Scheduled Task Wizard and when asked to select
a program browse to use browse to C:\Program Files\Microsoft SQL
Server\90\Tools\binn\ SQLCMD.exe. Define the Schedule Task parameters
accordingly and click “Finish”.
2. Go the properties of the newly created Scheduled Task and edit
the Run command as such.

“C:\Program Files\Microsoft SQL Server\90\Tools\Binn

\SQLCMD.EXE” -S <Sql server name> -i “C:\Backup.sql”

 

Leave a Reply