---
title: Backup and restore database using SQL stored procedure
description: Read the blog on how to backup and restore database using SQL stored procedure
image: https://blog.e-zest.com/hubfs/backup-and-restore-database-using-sql-stored-procedure.jpg
---

[![e-Zest logo\_An accion lab company\_Colour](https://blog.e-zest.com/hubfs/images/Logos%20with%20Tagline/Corporate/SVG/e-Zest%20logo_An%20accion%20lab%20company_Colour.svg "e-Zest logo_An accion lab company_Colour")](https://www.e-zest.com/)

- [WORK](https://www.e-zest.com/our-work)
- [SERVICES](https://www.e-zest.com/our-services) 
    - [Product Engineering](https://www.e-zest.com/digital-product-engineering-services)
    - [Enterprise Data Services](https://www.e-zest.com/enterprise-data-services)
    - [Digital Commerce](https://www.e-zest.com/digital-commerce-services)
    - [Digital Operations](https://www.e-zest.com/digital-operations)
    - [Digital Consulting](https://www.e-zest.com/digital-transformation-consulting-services)
    - [Digital Experience Design](https://www.e-zest.com/digital-experience-design)
- [INSIGHTS](https://blog.e-zest.com/)
- [CAREERS](https://www.e-zest.com/careers)
- [ENTERPRISE DATA SERVICES](https://www.e-zest.com/enterprise-data-services)
- [GENERATIVE AI](https://www.e-zest.com/generative-ai)
- [Healthcare](https://healthcare.e-zest.com)
- [Contact Us](https://www.e-zest.com/contact-us)

- ![Elevating Product Engineering with AI and ML](https://blog.e-zest.com/hubfs/Elevating%20Product%20Engineering%20with%20AI%20and%20ML.jpg)

  [BLOGS](https://blog.e-zest.com)
  
  #### Elevating Product Engineering with AI and ML

  As technology evolves and customer choices broaden, there is an increasing ...

  [Read more](https://blog.e-zest.com/elevating-product-engineering-with-ai-and-ml)
- ![e-Zest – An Accion Labs Company gets Recognition as a Contender in the ISG Provider Lens™ Study on Digital Engineering Services 2024](https://blog.e-zest.com/hubfs/e-Zest%20%E2%80%93%20An%20Accion%20Labs%20Company%20-%20Gets%20Recognition%20as%20a%20Contender%20in%20the%20ISG%20Provider%20LensTM%20Study%20on%20Digital%20Engineering%20Services%202024.jpg)

  [NEWS](https://blog.e-zest.com/news)
  
  #### e-Zest – An Accion Labs Company gets Recognition as a Contender in the ISG Provider Lens™ Study on Digital Engineering Services 2024

  We are delighted to announce that e-Zest – An Accion Labs Company has been ...

  [Read more](https://blog.e-zest.com/news/e-zest-an-accion-labs-company-gets-recognition-as-a-contender-in-the-isg-provider-lens-study-on-digital-engineering-services-2024)
- ![e-Zest Solutions supports Digital Pune Mission](https://blog.e-zest.com/hubfs/blog-files/Digital%20Literacy%20Bus%20e-Zest.png)

  [CSR](https://blog.e-zest.com/csr)
  
  #### e-Zest Solutions supports Digital Pune Mission

  **Pune, India:** e-Zest Solutions, a leading digital technology innovation ...

  [Read more](https://blog.e-zest.com/csr/e-zest-solutions-supports-digital-pune-mission)

- [Work](https://www.e-zest.com/our-work)
- [Services](https://www.e-zest.com/our-services)
- [Insights](https://blog.e-zest.com)
- [Careers](https://www.e-zest.com/careers)
- [Enterprise Data](https://www.e-zest.com/enterprise-data-services)
- [Generative AI](https://www.e-zest.com/generative-ai)
- [Innovations](https://www.e-zest.com/innovations)
- [Healthcare](https://healthcare.e-zest.com)
- [About](https://www.e-zest.com/about-us)
- [Contact](https://www.e-zest.com/contact-us)

- [Blog](https://blog.e-zest.com/)
- [News](https://blog.e-zest.com/news)
- [CSR](https://blog.e-zest.com/csr)

- [![Linkedin Icon](https://blog.e-zest.com/hubfs/icons/linkedin_icon_white.svg)](https://www.linkedin.com/company/e-zest-solutions/)
- [![Twitter Icon](https://blog.e-zest.com/hubfs/twitter-logo-silhouette%20(5).svg)](https://twitter.com/ezest)
- [![Facebook Icon](https://blog.e-zest.com/hubfs/facebook-app-logo%20(4).svg)](https://www.facebook.com/61259256638)
- [![Instagram Icon](https://blog.e-zest.com/hubfs/instagram-logo%20(4).svg)](https://www.instagram.com/ezestsolutions/)
- [![Youtube Icon](https://blog.e-zest.com/hubfs/icons/youtube_icon_white.svg)](https://www.youtube.com/user/ezestcorporate)

[![Go to top icon](https://blog.e-zest.com/hubfs/AA%20Assets/top%20button.svg)](https://blog.e-zest.com/backup-and-restore-database-using-sql-stored-procedure#top)

Hit enter to search or ESC to close

Insights

e-Zest members share technology ideas to foster digital transformation.

- [Blog](https://blog.e-zest.com)
- [News](https://blog.e-zest.com/news)
- [Podcasts](https://www.e-zest.com/podcasts)
- [Healthcare](https://blog.e-zest.com/healthcare)
- [CSR](https://blog.e-zest.com/csr)
- [Whitepaper](https://blog.e-zest.com/whitepaper)
- [Playbook](https://blog.e-zest.com/playbook)
- [Media Kit](https://www.e-zest.com/media-kit)

# Backup and restore database using SQL stored procedure

[Pranita Pendharkar](https://blog.e-zest.com/author/pranita-pendharkar)  Dec 05, 2017

[SQL](https://blog.e-zest.com/topic/sql) [SQL Server database](https://blog.e-zest.com/topic/sql-server-database)

- <https://www.linkedin.com/shareArticle?mini=true&url=https://blog.e-zest.com/backup-and-restore-database-using-sql-stored-procedure&title=&summary=&source=>
- <https://twitter.com/intent/tweet?source=https://blog.e-zest.com/backup-and-restore-database-using-sql-stored-procedure&text=Backup%20and%20restore%20database%20using%20SQL%20stored%20procedure>
- <https://www.facebook.com/sharer/sharer.php?u=https://blog.e-zest.com/backup-and-restore-database-using-sql-stored-procedure>
- <https://www.instagram.com/ezestsolutions/?u=https://blog.e-zest.com/backup-and-restore-database-using-sql-stored-procedure>

![backup-and-restore-database-using-sql-stored-procedure.jpg](https://blog.e-zest.com/hs-fs/hubfs/backup-and-restore-database-using-sql-stored-procedure.jpg?width=754&name=backup-and-restore-database-using-sql-stored-procedure.jpg)

Many times we need to backup and restore our databases on daily basis. We do this all process manually which increases our repetitive work. For the same, here we have SQL stored procedures to make it automated and easy which is helpful for doing database backup and restore. We can also make SQL job to run it automatically. Here is a sample code:

- **To Backup databases**

USE \[master\]  
GO  
/\*\*\*\*\*\* Object: StoredProcedure \[dbo\].\[DBbackup\] Script Date: 11/03/2017 7:35:54 AM \*\*\*\*\*\*/  
SET ANSI\_NULLS ON  
GO  
SET QUOTED\_IDENTIFIER ON  
GO  
-- =============================================  
-- Author: \<Pranita Pendharkar\>  
-- Create date: \<11-03-2017\>  
-- Description: \<To create database backups \>  
-- =============================================  
/\*  
EXEC \[DBbackup\] 'DBbackup'  
\*/  
Alter PROCEDURE DBbackup

 @name VARCHAR(MAX) = '' -- DB NAME TO CREATE BACKUP  
AS  
BEGIN

DECLARE @path VARCHAR(256) -- path of backup files  
DECLARE @fileName VARCHAR(256) -- filename for backup  
DECLARE @fileDate VARCHAR(20) -- used for file name

SET @path = 'E:\\Data\\FTP\\Backup\\'

-- specify filename format  
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

 BEGIN  
 SET @fileName = @path + @name + '\_' + @fileDate + '.BAK'  
 BACKUP DATABASE @name TO DISK = @fileName  
 END  
END  
GO

Likewise just pass names of db’s to stored procedure to create backup.  
EXEC \[DBbackup\] 'Database1'  
EXEC \[DBbackup\] 'Database2'

**Also there are different database backup types**

**Full Backup**

This is one of the simple SQL server backup type. It does not depend on recovery model. It contains all data for a particular database. Also contain enough log to recover the database.  
Syntax:-

BACKUP DATABASE Dbname TO DISK = 'fileName.bak'

**Differential Backup**

It is related to the last full backup. It contains all changes that have been made since the last full backup.  
Syntax:-

BACKUP DATABASE Dbname TO DISK = 'diff.bak' WITH DIFFERENTIAL

**Transaction log backup**

This is only possible with full or bulked logged recovery model. It contains all log records that have not been included in the last transaction log backup.  
Syntax:-

BACKUP LOG Dbname TO DISK = 'log.bak'

**Copy-only backup**

If you need to make an additional full or transaction log backups which will occur beyond the regular sequence of SQL Server backups  
Syntax:-

BACKUP DATABASE Dbname TO DISK = 'full.bak' WITH COPY\_ONLY

**File and File-groups backup**

These backup types allow you to backup one or more database files or filegroups.  
Syntax:-

BACKUP DATABASE Dbname FILE = 'File' TO DISK = 'File.bck'

 To perform filegroup backup:  
 BACKUP DATABASE Dbname FILEGROUP = 'Group' TO DISK = 'Group.bck'

**Partial Database backups**

Partial backups are used in simple recovery model to make backups of very large databases that have one or mode read-only filegroups.  
Syntax:-

BACKUP DATABASE Dbname READ\_WRITE\_FILEGROUPS TO DISK = 'partial\_backup.bak'

- **To Restore Database backups**

 USE \[master\]  
GO  
/\*\*\*\*\*\* Object: StoredProcedure \[dbo\].\[DBRestore\] Script Date: 11/03/2017 7:23:54 AM \*\*\*\*\*\*/  
SET ANSI\_NULLS ON  
GO  
SET QUOTED\_IDENTIFIER ON  
GO  
-- =============================================  
-- Author: \<Pranita Pendharkar\>  
-- Create date: \<11-03-2017\>  
-- Description: \<To Restore backup \>  
-- =============================================  
/\*  
EXEC \[DBRestore\] 'DBRestore'  
\*/  
Alter PROCEDURE DBRestore

 @name VARCHAR(MAX) = '' -- DB NAME TO Restore  
AS  
BEGIN

 DECLARE @SQL VARCHAR(MAX)  
 DECLARE @NewDBName VARCHAR(MAX) -- NEW DB NAME TO RESTORE THE BACKED UP DB.  
 DECLARE @FileNameOrgBackup VARCHAR(MAX) -- Backup file name to restore  
 DECLARE @PathOrgignalBackup VARCHAR(MAX) -- Path of backup file to restore

SET @NewDBName = @name  
 SET @PathOrgignalBackup = 'E:\\Data\\FTP\\'  
 SET @FileNameOrgBackup = @PathOrgignalBackup + @name + '.bak'  
 SET @SQL = ''

SET @SQL ='RESTORE DATABASE ' + @NewDBName  
 SET @SQL = @SQL + ' FROM DISK = ''' + @FileNameOrgBackup + ''''

EXECUTE(@SQL)  
END  
GO

Likewise just pass names of db’s to stored procedure to restore database.  
EXEC \[DBRestore\] 'Database1'  
EXEC \[DBRestore\] 'Database2'

This is a very straight forward process to backup and restore which will reduce our repetitive work.  
Hope this will help you!!!!!!!

- <https://twitter.com/intent/tweet?source=https://blog.e-zest.com/backup-and-restore-database-using-sql-stored-procedure&text=Backup%20and%20restore%20database%20using%20SQL%20stored%20procedure>
- <https://www.facebook.com/sharer/sharer.php?u=https://blog.e-zest.com/backup-and-restore-database-using-sql-stored-procedure>
- <https://www.linkedin.com/shareArticle?mini=true&url=https://blog.e-zest.com/backup-and-restore-database-using-sql-stored-procedure&title=&summary=&source=>

### Similar Blog

[![SQL Server or MongoDB](https://blog.e-zest.com/hubfs/MongoDB.jfif) SQL Server or MongoDB When it comes to deciding between a relational (SQL) or non-relational (NoSQL) data structure, the user must consider the ... By Aarti Shinde Mar 04, 2020](https://blog.e-zest.com/sql-server-or-mongodb)

[![Basics of stored procedure in SQL Server Management Studio](https://blog.e-zest.com/hubfs/Basics%20of%20Stored%20Procedure%20in%20SQL%20Server%20Management%20Studio-191118.png) Basics of stored procedure in SQL Server Management Studio **Definition of Stored Procedure** A Stored Procedure is pre-compiled collection of SQL statements and SQL command logic in stored ... By Aarti Shinde Nov 17, 2019](https://blog.e-zest.com/basics-of-stored-procedure-in-sql-server-management-studio)

[![Snowflake – The Data Warehouse built for the Cloud](https://blog.e-zest.com/hubfs/blog-files/Snowflake%20%E2%80%93%20The%20Data%20Warehouse%20built%20for%20the%20Cloud-190909.png) Snowflake – The Data Warehouse built for the Cloud *“The goal is to turn data into information, and information into insight.” - Carly Fiorina* By Vijay Kumbhoje Sep 10, 2019](https://blog.e-zest.com/snowflake-the-data-warehouse-built-for-the-cloud)

[![Inserting multiple records simultaneously in a table in SQL Server](https://blog.e-zest.com/hubfs/Sql-server.png) Inserting multiple records simultaneously in a table in SQL Server While developing applications, we may come across the situations where we need to insert/update multiple records in a database ... By Sagar Damle Dec 20, 2017](https://blog.e-zest.com/inserting-multiple-records-simultaneously-in-a-table-in-sql-server)

[![Compression and Decompression of files using Ionic DLL](https://blog.e-zest.com/hubfs/Compression%20and%20Decompression%20of%20files%20using%20Ionic%20DLL.jpg) Compression and Decompression of files using Ionic DLL There are many scenarios in which we need to deal with zip and unzip. This will decrease our manual work of zipping and ... By Pranita Pendharkar Nov 06, 2017](https://blog.e-zest.com/compression-and-decompression-of-files-using-ionic-dll)

## Helping Organizations Thrive in the Digital Age

[Contact Us](https://www.e-zest.com/contact-us)

- [About](https://www.e-zest.com/about-us)
- [Services](https://www.e-zest.com/our-services)
- [Work](https://www.e-zest.com/our-work)
- [Insights](https://blog.e-zest.com/)
- [Healthcare](https://healthcare.e-zest.com/)
- [Accion Labs](https://www.accionlabs.com/)

- <https://www.linkedin.com/company/e-zest-solutions/>
- <https://twitter.com/ezest>
- <https://www.facebook.com/eZest/>
- <https://www.instagram.com/ezestsolutions/>
- <https://www.youtube.com/user/ezestcorporate>

- Dallas
- Detroit
- Hannover
- Vienna
- London
- Pune

 © 2025 e-Zest Solutions

- [Privacy Policy](https://www.e-zest.com/privacypolicy)
- [Cookie Policy](https://www.e-zest.com/cookie-policy)

 Contact us

 Contact us

e-Zest is a leading digital innovation partner for enterprises and technology companies that utilizes emerging technologies for creating engaging customers experiences. Being a customer-focused and technology-driven company, it always helps clients in crafting holistic business value for their software development efforts. It offers software development and consulting services for cloud computing, enterprise mobility, big data and analytics, user experience and digital commerce.