SQL

Database backup easily using DB SQL command

Database backup easily using DB SQL command, someone asked me to explain?

Backup sql server databases using SQL Query-Cursor

Create a folder  a directory. Before executing SQL command the  cursor we need to specify the path in cursor. Below query will return a listing of all backup files in all databases on a SQL instance.

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
-- specify database backup directory
SET @path = 'C:\Backup\' 
-- specify filename format
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
DECLARE db_cursor CURSOR READ_ONLY FOR 
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')  -- exclude these databases
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  

Just Copy the above code and paste it in th Query Explorer and specify the path where you want to backup the databases and then Execute. It will automatically backups the databases into the specified Location.

This cursor did it very smart. for me it saved lot of time.

The video explains in detail about how to take backup all databases using SQL Command. 

Post your comments / questions