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

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!!!!!!!
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.