If you have a server you would have been presented at one point with the need to backup all databases in one go. Here’s a script that would do just that.
CREATE PROCEDURE Tblbackup
AS
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 = ‘D:SQLBackup’
— specify filename format
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’ )
— exclude these databases
DECLARE @DatabaseFolder NVARCHAR(255)
SET @DatabaseFolder = @path + @fileDate
EXEC master.sys.Xp_create_subdir @DatabaseFolder;
OPEN db_cursor
FETCH next FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @fileDate + ” + @name + ‘.BAK’
BACKUP DATABASE @name TO DISK = @fileName
FETCH next FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
