Raghav Mehra

Raghav Mehra

  • NA
  • 51
  • 1k

SQl to Backup all user databases with timestamp

Aug 26 2014 2:42 AM



use master;
go
DECLARE UserDB_Cursor Cursor
FOR 
-- Selecting user database names.
select name as DatabaseName
from sys.sysdatabases
where ([dbid] > 4) and ([name] not like '$')
OPEN UserDB_Cursor
DECLARE @dbName varchar(100);
DECLARE @dbbackupPath varchar(100);
DECLARE @dbbackupQry varchar(500);
-- make sure that the below path exists
set @dbbackupPath = 'c:\SQLBackupFolder\'
Fetch NEXT FROM UserDB_Cursor INTO @dbName
While (@@FETCH_STATUS <> -1) 
BEGIN
-- Backup SQL statement
set @dbbackupQry =  'backup database ' + @dbName + ' to disk = ''' + @dbbackupPath + @dbName + '_[' + REPLACE( convert(varchar, getdate(), 109), ':', '-') + '].bak''' 
-- Print SQL statement
print @dbbackupQry 
-- Execute backup script
EXEC (@dbbackupQry) 
-- Get next database
Fetch NEXT FROM UserDB_Cursor INTO @dbName
END 
CLOSE UserDB_Cursor
DEALLOCATE UserDB_Cursor
GO

Answers (1)