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
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 .