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