Mahmoud ElMansy: knowledge meant to be free

T-SQL script to backup all user databases with date time backup name

This simple script to backup all user databases to specific location .

USE master;
GO
DECLARE @DATE VARCHAR(25)

SELECT @DATE= REPLACE( CONVERT(VARCHAR, GETDATE(),111),'/',':')+' '+ CONVERT(VARCHAR, GETDATE(),114);

DECLARE DB_Cursor Cursor
FOR
 
-- Get all user databases
SELECT name AS DatabaseName
FROM sys.sysdatabases
WHERE ([dbid] > 4)
 
OPEN DB_Cursor
DECLARE @DataBaseName VARCHAR(100);
DECLARE @backupPath nvarchar(100);
DECLARE @SqlStatment nvarchar(500);
DECLARE @databaseNum INT=1
 
-- backup folder and all database has a separet folder
SET @backupPath = 'c:\DataBaseBackups\'
 
Fetch NEXT FROM DB_Cursor INTO @DataBaseName
While (@@FETCH_STATUS <> -1)
 
BEGIN

set @SqlStatment =  N'
backup DATABASE ' + @DataBaseName + N' TO disk = ''' +
 @backupPath + @DataBaseName+N'
\'+@DataBaseName + N'_' +@date+ N'.bak''';
EXEC (@SqlStatment);
print convert(varchar, @databaseNum) +N'
-'+ @SqlStatment;
set @databaseNum=@databaseNum+1;
Fetch next from DB_Cursor INTO @DataBaseName
END
 
close DB_Cursor
DEALLOCATE DB_Cursor
GO