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

Clone a generic list in C# using extension method

Disable backspace key using Jquery

Add Comma automatically while entering amounts in textbox using Javascript

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

Source Code Beautifier And Format Source Code For Blogger and Websites

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

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

Email validation using regular expression in jQuery