Posts Tagged ‘Database backup script’

The below script will backup all user databases. Every database backup file will be generate as Database name with timestamp.

USE MASTER
GO

— Declaring a cursor named as DBName for all User database having DBID>4

DECLARE DBName Cursor FOR
Select Name as DatabaseName from sys.sysdatabases 
where dbid >4
OPEN DBName
DECLARE @dbName varchar(100);
DECLARE @backupFolder varchar(100);
DECLARE @backupcommand varchar(500);
Set @backupFolder ‘F:\DBBackup\Full\’
FETCH NEXT FROM DBName INTO @dbName
While (@@FETCH_STATUS <>1)
BEGIN
Set @backupcommand =‘Backup Database ‘+ @dbName +‘ to Disk = ”’+ @backupFolder + @dbName +‘_[‘+REPLACE(Convert(varchar,Getdate(), 109),‘:’, ‘-‘)+ ‘].Bak”’
–Print @backupcommand
EXEC (@backupcommand)
Fetch NEXT FROM DBName INTO @dbName
END
CLOSE DBName
DEALLOCATE DBName
GO