how to take back up of all sql server databases?

I am using SQL server 2012 right, and I would like to know how can I take back up of all MS SQL server databases using a single script? or you can explain me a simple method.


Asked by:- jaiprakash
: 3216 At:- 7/22/2017 8:27:39 AM
SQL Back-up all-databases SQL-Server

2 Answers
profileImage Answered by:- pika

You can take a look on this article's 3rd option to take back up of full database using SQL server

Another solution to take back up of all databases using SQL server is

  • Create a stored procedure
    Create PROCEDURE [dbo].[AllDataBasesBackUp]
    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
    SET @path = 'D:\All_databases_Backup\'
    SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),104)
    DECLARE db_cursor CURSOR FOR
    SELECT name
    FROM MASTER.dbo.sysdatabases
    WHERE name NOT IN ('master','model','msdb','tempdb','ReportServer','ReportServerTempDB')
    OPEN db_cursor
    FETCH NEXT FROM db_cursor INTO @name
    SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
    BACKUP DATABASE @name TO DISK = @fileName
    FETCH NEXT FROM db_cursor INTO @name
    CLOSE db_cursor
    DEALLOCATE db_cursor
  • Execute the above procedure.

    EXEC [AllDataBasesBackUp]?

Now, you can check D:\All_databases_Backup\ (Location, all database back up file is created there using above stroed procedures

At:- 7/23/2017 3:19:29 PM
Great answer, looking at the above link option 2 is easier provided on this link 0
By : Vinnu - at :- 6/8/2018 12:06:36 PM

profileImage Answered by:- jaya

You can also use powershell to take backup of all sql server database

Get-SqlDatabase -ServerInstance localhost |
Where { $_.Name -ne 'tempdb' } | foreach{$_ |
Backup-SqlDatabase -CompressionOption On -BackupFile "$($_.NAME)_db_$(Get-Date -UFormat %Y%m%d%H%M).bak"};

Source: How to back up SQL Server database ? (Various Ways explained)

Upvote if it helps, thanks.

At:- 6/13/2022 8:24:47 AM

Login/Register to answer
Register directly by posting answer/details

Full Name *

Email *

By posting your answer you agree on privacy policy & terms of use