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 databases from SQL Server step by step. From SQL Server Management Studio we can make our databases backup .i.e By using the 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 databases to our local drive. i.e., save all the .bak files of our databases in our local C drive or D drive respectively. In this case, using SQL Server Management Studio and taking database backup manually one by one is not a good option. So to solve this, we make a simple SQL Script .i.e( StoredProcedure Created ) which does an excellent job for us, in short, it takes backup of all the databases in SQL SERVER.

Steps to Taking Backup of all Databases in MQ Sqlserver.

  1. Create a Backup folder (where all database backup .bak files 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 that does the job. Let go in-depth about the stored procedure. With the below-written query, we get the list of all databases from our SQL server.
FROM master.dbo.sysdatabases
  You might have noticed the above query returns all the databases including the default database. If you want to exclude default databases or specific databases while taking the backup, then using the where condition we make our custom selection of database. Our query looks like this
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 the MS SQL  BACKUP DATABASE statement, we can easily take the full backup of the database. A simple example of using a backup database statement look like as written below
DECLARE @fileName varchar(250);
SET @filePath_Name ='C:MyBackup	ableName.bak';
BACKUP DATABASE @dbName TO DISK =@filePath_Name;
Also Read: Are parentheses (brackets) necessary in SQL while using condition?
We are almost complete with taking database backup. As we want to take backup of all the databases, we need to make a loop on all the databases and take backup of each. Here we need to use CURSOR which loops over each database and takes backup of each. Our Final Stored Procedure looks like as written below.
@backupPath VARCHAR(256) -- specify database backup file directory

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

-- specify filename format
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

SET @fileName = @backupPath + @dbName + '_' + @fileDate + '.BAK'

FETCH NEXT FROM db_cursor INTO @dbName
CLOSE db_cursor
DEALLOCATE db_cursor


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.

Conclusion: This article covers a complete tutorial on how to take a full database backup of all the databases or specific databases from SQL SERVER 2008 by using the above SQL Script .i.e. ( Stored Procedure). 

Thank you for reading, pls keep visiting this blog and share this in your network. Also, I would love to hear your opinions down in the comments.

PS: If you found this content valuable and want to thank me? 👳 Buy Me a Coffee

Subscribe to our newsletter

Get the latest and greatest from Codepedia delivered straight to your inbox.

Post Comment

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