Generate All Database Backup From Sql Server Using Query

In this blog, we will generate all the database backup from the SQL Server using the Query.

Steps

1. Open Your Microsoft Sql Server
2. Click on New Query
3. Copy and Paste This Query
4. Change Your Database Path (Where You Can Save Your Backup File)
5. Execute Query

----Copy This Query----

 

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

DECLARE @filedate1 varchar(100)

SET @path = 'D:\KunalDatabase\'

 

SELECT @fileDate = CONVERT(varchar(50),GETDATE(),103)

 

SELECT @filedate1= REPLACE(@fileDate,'/','-')

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 + '_' + @filedate1 + '.BAK'

       BACKUP DATABASE @name TO DISK = @fileName

 

       FETCH NEXT FROM db_cursor INTO @name 

END 

 

CLOSE db_cursor 

DEALLOCATE db_cursor

 

 

Output