Script to backup all the databases present in Microsoft SQL Server
Open the Microsoft SQL Management studio and run the following script.
Script will take name from sysdatabases view and run backup command for each database. One by one with cursor help database backup is done. you can also schedule backup of SQL Server in SQL Agent. It is good tool for scheduling the jobs of backup.
script:
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
