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

This is simple example of taking database backup from the 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
In above code, all the database backup will get stored on  'C:\Backup\' location on execution of the above script.

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

Angular User Session Timeout example step by step

Add Comma automatically while entering amounts in textbox using Javascript

Implement Logging in CSV file using Nlog in .net core MVC application- part 2

Implement Nlog in .Net core MVC application part 1

Clone a generic list in C# using extension method

Send bulk Email in asp.net using excel file

Devexpress Datebox date formatting in angular 6 with example