Posts Tagged ‘Backup Automation’

T-SQL Script for user Databases Backup with timestamp

Posted: August 18, 2014 by Virendra Yaduvanshi in Database Administrator
Tags: , , , , , , ,
2

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