Blogs

Backup and restore database using SQL stored procedure

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

Topics: SQL SQL Server database

e-Zest Solutions is digital experience engineering company with facilities in the United States (Detroit & San Jose), Germany (Hannover), United Kingdom (London UK) and India (Pune) with global clientele. Our services include custom software development, offshore software development, UX consulting, BigData, Managed cloud Services (Azure & AWS), SharePoint consulting/Migration, Enterprise Java application development, Automated software testing services.