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
1
: 291 At:- 7/22/2017 8:27:39 AM
SQL Back-up all-databases SQL-Server






1 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]
    AS
    
    BEGIN
    SET NOCOUNT ON;
    
    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
    WHILE @@FETCH_STATUS = 0
    BEGIN
    SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
    BACKUP DATABASE @name TO DISK = @fileName
    FETCH NEXT FROM db_cursor INTO @name
    END
    CLOSE db_cursor
    DEALLOCATE db_cursor
    END
  • 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

0
At:- 7/23/2017 3:19:29 PM





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

Full Name *

Email *




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

Subscribe Now

Subscribe to our weekly Newsletter & Keep getting latest article/questions in your inbox weekly