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