Create a folder a directory. Before executing SQL command the cursor we need to specify the path in cursor. Below query will return a listing of all backup files in all databases on a SQL instance.
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
-- specify database backup directory
SET @path = 'C:\Backup\'
-- specify filename format
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
DECLARE db_cursor CURSOR READ_ONLY FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb') -- exclude these databases
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
Just Copy the above code and paste it in th Query Explorer and specify the path where you want to backup the databases and then Execute. It will automatically backups the databases into the specified Location.
This cursor did it very smart. for me it saved lot of time.
The video explains in detail about how to take backup all databases using SQL Command.