Using Query in Sql Server Generate All Database Backup

Steps Generate All Database Backup in Sql Server:

1. Open Your  Sql Server
2. Click on New Query window
3. Copy and paste the query below
4. Change your database path (where you want to save your database backup)
5. Execute query

--use 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:\Santosh\Database backup'

 

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

--End of query--

as shown in picture:

that's it