Hello Friends,


Here I am going to share how can we take the database backup of all the SQL Server databases at once instead of taking it individually.

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.