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