T-SQL Script for user Databases Backup with timestamp

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

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

 

Advertisements
Comments
  1. Msg 137, Level 15, State 2, Line 52
    Must declare the scalar variable “@@backupcommand”.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s