Database backup from Sql script using cursor in sql server 2008/2010

This is simple example of taking database backup from the T-sql script. In my example i have illustrated the simplest way to take the back up from the database. I have used the cursor for the same. Following is the code of the Backup script.

DECLARE @name VARCHAR(50) -- database name 
DECLARE @path VARCHAR(256) -- path for backup files 
DECLARE @fileName VARCHAR(256) -- filename for backup 
DECLARE @fileDate VARCHAR(20) -- used for file name

SET @path = 'C:\Backup\' 

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE db_cursor CURSOR FOR 
SELECT name 
FROM master.dbo.sysdatabases 
WHERE name NOT IN ('master','model','msdb','tempdb') 

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @name  

WHILE @@FETCH_STATUS = 0  
BEGIN  
       SET @fileName = @path + @name + '_' + @fileDate + '.BAK' 
       BACKUP DATABASE @name TO DISK = @fileName 

       FETCH NEXT FROM db_cursor INTO @name  
END  

CLOSE db_cursor  
DEALLOCATE db_cursor


Use the code and give me your feedback.

Comments

Popular posts from this blog

Restore the lost focus of Auto post back controls in asp.net update Panel control

Disable backspace key using Jquery

Clone a generic list in C# using extension method

Add Comma automatically while entering amounts in textbox using Javascript

Remove Owin from MVC 5 Application and use asp.net custom forms authentication

Step by step migration of ASP.NET Core 2.2 to 3.1

Send bulk Email in asp.net using excel file

Email validation using regular expression in jQuery

How to Import CSV File and bind csv file data to gridview in asp.net using c#