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
My name is Mahmoud El Mansy I'm C# Senior solution developer .