Archive

Archive for the ‘Uncategorized’ Category

Agile Implementation Strategy

March 30, 2017 Leave a comment

Advertisements
Categories: Agile Strategy

Check your backup in SQL server


We all know that Database backups are the bread and butter task of the database administration job. We cannot have any database running without backups. Without proper and prompt backup it is not possible to recover the database in the event of data corruption and failure.

Therefore, it is critical to monitor the backups continuously.

In This scenario we can add one more step in our Backup User Databases job which monitoring to find any databases that have not been backed up in certain number of hour.

In our admin database we can create one store procedure uspMonitorBackups. Below I have mentioned the code of the stored procedure uspMonitorBackups, which queries the msdb.dbo.bacupset table for the last backup.

 

USE [admin]

GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[uspMonitorBackups]’) AND type in (N’P’, N’PC’))

DROP PROCEDURE [dbo].[uspMonitorBackups]

GO

CREATE PROCEDURE uspMonitorBackups @backuptype char(1), @numOfMinutes int

as

–D = Database

–L = Log

–Example: Exec admin.dbo.uspMonitorBackups ‘D’,24

declare @dbname varchar(2000)

select name as Databasename

from master.dbo.sysdatabases

where name not in (‘master’,’model’,’msdb’,’tempdb’,’LiteSpeedLocal’)

and name not in

                (select distinct database_name

                from msdb.dbo.backupset

                group by database_name

                having datediff(minute, max(backup_start_date),getdate()) < @numOfMinutes

)

Job step which need to add in Backup User Databases job

Exec admin.dbo.uspMonitorBackups ‘D’,24

The Stored procedure accepts two parameters. The first parameter is type of database backup and second parameter is number of hours

Categories: Uncategorized

Hello world!


Welcome to WordPress.com. This is your first post. Edit or delete it and start blogging!

Categories: Uncategorized