Taking a full backup of database through Management Studio
is not a hard job, but its not a normal practice, In this blog we will show how
to automate the process and manage backup files.
We will also maintain a log file to get the results of
automated backup scripts.
Defining
Standards:
Defining organization level Standards for all data
operations are always very important when we are dealing with the management of
database, Naming conventions, specified disks for specified operations, privileges,
all are important aspects for database management. Below are some pre-defined
standards for our backup strategy.
- We need a full backup
every mid-night.
- Backup folder path will be
c:\backup1\
- Within above mentioned
folder c:\backup1, we will create new folder for each day backup, folder
naming conventions will be
- <servername>_current
date_day of year i.e (DCS2-PMB_20190610_161)
- We will already create an
empty test file with the name backup_log.txt
Defining
Script
- Below part of script is
checking with c:\backup1 folder
for the current day backup, if its already there then delete the whole
folder with backup, and then recreate new folder for the current day
backup as we have decided in our standards that there will be only one
full backup per day.
- Below part of script is taking full backup of database, first line is
taking backup to the specified folder and last 3 lines are sending message
to the already created log file backup_log.txt.
- Below part of script is verifying the database backup and sending message in log file for successful or un-successful case.
Full Script
EXEC sp_configure 'show advanced options', 1; -- To update the
currently configured value for advanced options.
RECONFIGURE;
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1; -- To update the
currently configured value for this feature.
RECONFIGURE;
--
variable declarations for database backup job
DECLARE @day varchar(3);
DECLARE @date varchar(50);
DECLARE @opr_date varchar(150);
DECLARE @file nvarchar(200);
DECLARE @path varchar(100);
DECLARE @server varchar(50);
DECLARE @prefolder varchar(50);
DECLARE @folder varchar(100);
DECLARE @backup nvarchar(200);
DECLARE @email_message nvarchar(200);
DECLARE @rmdircmd nvarchar(200);
DECLARE @cmd nvarchar(200);
DECLARE @rows numeric;
DECLARE @backup_message varchar(100);
--
assign declared variables with desired backup path
SET @date = (SELECT REPLACE(CONVERT(VARCHAR, GETDATE(),102), '.', ''));
SET @day = RIGHT('000' + CAST(DATEPART(dy, @date) AS varchar(3)),3);
SET @server = (SELECT SUBSTRING(@@SERVERNAME, 0, CHARINDEX('\', @@SERVERNAME)));
SET @prefolder = @server + '_'+ @date + '_' + @day + '\'
SET @folder = 'C:\BACKUP1\' + @prefolder;
SET @file = @server + '_DaddsMgmtCS2_DEV_'
+ @date + '_' + @day + '.bak';
SET @backup = @server + '_DaddsMgmtCS2_DEV_'
+ @date + '_' + @day;
SET @path = @folder + @file;
SET @cmd = 'RMDIR ' + @folder;
SET @opr_date = format(getdate(),'MM/dd/yyyy');
-- creating a temp table to hold all
directories name
IF OBJECT_ID('tempdb..#DirectoryTree')IS NOT NULL
drop table #DirectoryTree
CREATE TABLE
#DirectoryTree (
Directory varchar(200)
);
INSERT INTO
#DirectoryTree
EXEC master.dbo.xp_subdirs 'c:\backup1' -- getting all directories within c:\backup folder
--Check if today's backup is already
there
select * from #DirectoryTree where
SUBSTRING(directory,10,8) = REPLACE(CONVERT(VARCHAR, GETDATE(),102), '.', '')
SET @rows =@@ROWCOUNT
if @rows > 0 -- if
its not first backup for today
BEGIN
EXEC master..xp_cmdshell @cmd --- delete already existing folder
EXECUTE master.dbo.xp_create_subdir @folder
END
else
-- create a new
folder for the backup on nsof-pmb
EXECUTE master..xp_create_subdir @folder;
--
backup the database to the specified name & path
BACKUP DATABASE
[DaddsMgmtCS2_DEV] To DISK=@path WITH CHECKSUM, FORMAT, DESCRIPTION = 'Full backup of Database DaddsMgmtCS2_DEV', NAME=@backup;
exec master..xp_cmdshell 'echo
#############################################################################
>> c:\backup1\backup_log.txt'
SET @backup_message = 'echo Backup Completed on ' +
cast(getdate() as varchar) + ' >> c:\backup1\backup_log.txt'
exec master..xp_cmdshell @backup_message
--
verify
restore verifyonly from disk = @path
If @@error = 0
exec master..xp_cmdshell 'echo Backup verified
successfully >> c:\backup1\backup_log.txt'
--print 'Backup Verified Successfull'
-- SET
@email_message = 'Backup is successfully
verified';
else
exec master..xp_cmdshell 'echo Backup can not
verified >>
c:\backup1\backup_log.txt'
EXEC sp_configure 'xp_cmdshell', 0;
RECONFIGURE;
EXEC sp_configure 'show advanced options', 0;
RECONFIGURE;
Implementation
Script can be run either by
- Running directly from SQL
Server Management Studio
- Can be scheduled through
SQL Server Agent Job on daily
basis
- Saved as stored procedure
and call through windows task
scheduler
windows services maintenance
tips for DBA
