How can we take all the SQL SERVER Database Backup at once

First we need to create a folder in any drive to store the backup of database which we are going to take.

Here I have created a folder named “dbBackup” in my drive “C:\”

Copy the below code & paste it in your SQL Server Query Executer page:

DECLARE @DBName varchar(255)

DECLARE @DATABASES_Fetch int

DECLARE DATABASES_CURSOR CURSOR FOR

select

DATABASE_NAME   = db_name(s_mf.database_id)

from

sys.master_files s_mf

where

– ONLINE

s_mf.state = 0

 

– Only look at databases to which we have access

and has_dbaccess(db_name(s_mf.database_id)) = 1

 

– Not master, tempdb or model

and db_name(s_mf.database_id) not in (‘Master’,'tempdb’,'model’)

group by s_mf.database_id

order by 1

 

OPEN DATABASES_CURSOR

 

FETCH NEXT FROM DATABASES_CURSOR INTO @DBName

 

WHILE @@FETCH_STATUS = 0

BEGIN

declare @DBFileName varchar(256)

set @DBFileName = datename(dw, getdate()) + ‘ – ‘ +

replace(replace(@DBName,’:',’_'),’\',’_')

 

exec (‘BACKUP DATABASE [' + @DBName + '] TO  DISK = N”c:\dbBackup\’ +

@DBFileName + ‘.bak’ + ”’ WITH NOFORMAT, INIT,  NAME = N”’ +

@DBName + ‘-Full Database Backup”, SKIP, NOREWIND, NOUNLOAD,  STATS = 100′)

 

FETCH NEXT FROM DATABASES_CURSOR INTO @DBName

END

 

CLOSE DATABASES_CURSOR

DEALLOCATE DATABASES_CURSOR

 

Now execute the page & you are done….!!!

Check your Folder “dbBackup” in Drive “C:/” . You will find all the databases backup in it.

About the Author

avatar