One SQL Script to take Backup of all DataBases [ MS SQL SERVER]

 MS SQL Backup all databases: Here in this article will see how to take backup of all database from SQL Server step by step. Using SQL Server Management Studio we can make our databases backup .i.e using user interface we can easily get the .bak file of our database, which we can restore later anywhere.

But what if we have a lot of databases on our SQL Server, and want to take backup all the database to our local drive. i.e., save all the .bak file of our databases in our local C drive or D drive respectively.
In this case, using SQL Server Management Studio and taking manually database backup is not a good option. So to solve this, we make a simple SQL Script .i.e( Create StoredProcedure) which do the excellent job for us, in short, it takes backup of all the database in SQL SERVER.

Steps to Taking Backup of all Databases in MQ Sqlserver.

  1. Create a Backup folder (where all database backup .bak file would be saved ).
  2. Create a STORED PROCEDURE (which takes backup of databases)

#1 Create a Backup folder i.e.(directory)

First, we create a folder in our Local C drive, and name it as MyBackup. The newly created folder MyBackup is used to save all our databases .bak.


#2 Create STORED PROCEDURE which takes backup of databases.

After creating the folder, now we make a stored procedure which does the job. Let go in depth of the stored procedure. With the below-written query, we get the list all databases from our SQL server.

SELECT name
FROM master.dbo.sysdatabases

 

You might have noticed the above query returns all the database including default database. If you want to exclude default databases or specific database while taking backup then with where condition we make our custom selection of database. Our query look like this

SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb') -- exclude these databases

 

Right now we are done with the database selection next we want to take backup of these databases. By using MS SQL  BACKUP DATABASE statement, we can easily take the full backup of the database. Simple example of using backup database statement look like as written below

DECLARE @fileName varchar(250);
SET @filePath_Name ='C:\MyBackup\tableName.bak';
BACKUP DATABASE @dbName TO DISK [email protected]_Name;

Also Read: Are parentheses (brackets) necessary in SQL while using condition?

We almost complete of taking database backup. As we want to take backup of all the database we need to make a loop on all the database and take backup of each. Here we need to use CURSOR which loops over each database and take backup of each.

Our Final Stored Procedure looks like as written below.

CREATE PROCEDURE GET_BACKUP(
@backupPath VARCHAR(256) -- specify database backup file directory
)
AS BEGIN

DECLARE @dbName VARCHAR(50) -- database name
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name

-- specify filename format
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb') -- exclude these databases

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbName

WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @backupPath + @dbName + '_' + @fileDate + '.BAK'
BACKUP DATABASE @dbName TO DISK = @fileName

FETCH NEXT FROM db_cursor INTO @dbName
END
CLOSE db_cursor
DEALLOCATE db_cursor

END

Also Read: How to Get Time part from Datetime in MS SQL server?

The last step is to execute this stored procedure and get all the databases; pls check your backup folder you will be able to see all .bak files are generating one by one.

EXEC GET_BACKUP 'C:\MyBackup\'

Conclusion:

This article cover a complete tutorial on how to take a full database backup of all the database or specific databases from SQL SERVER 2008 by using above SQL Script .i.e. ( Stored Procedure). Hope you like this tutorial. If you have any recommendations, please let us know what you think in the comment section below! See you again next time!

Share:    
Satinder Singh

Hi all, am Founder and Author of Codepedia.info, also atypical polyglot programmer from Bombay, India. I have a passion to create, solve, and deploy software applications.
       ForEach ( minute in MyLife ) { myExperience++ ; }


5 comments on “One SQL Script to take Backup of all DataBases [ MS SQL SERVER]”

  1. faizan

    hi thanks for sharing.
    i can apply this procedure on my godaddy’s dedecated servers db it shows me
    “Processed 4248 pages for database ‘dbmydb’, file ‘dbmydb’ on file 4.
    Processed 2 pages for database ‘dbmydb’, file ‘dbmydb_log’ on file 4.
    BACKUP DATABASE successfully processed 4250 pages in 0.993 seconds (33.429 MB/sec).

    but the folder is empty.
    can you please help me.

  2. Shashwat

    Hey, Sorry I resolved my query regarding GET_BACKUP… Just add restore part
    RESTORE DATABASE @dbName FROM DISK = @filePath

  3. Shashwat

    Hey,
    Nice article! But I have one doubt. I couldn’t understand your last query,
    EXEC GET_BACKUP ‘C:\MyBackup\’
    What will it do? As SQL server does not have such stored procedure.
    And it will be more helpful if you share the same type of script file to restore this “.back” file.


Post Comment


Your email address will not be published. Required fields are marked *