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
WikiDBA 