RSS

Category Archives: backups

An Abstract On SQL Server Backup

overview

SQL Server application is integrated with several High-Availability elucidations like Fail-over clustering, Database mirroring, or Always-on. These solutions make sure that the databases are available for maximum up time. However, in order to make sure that the databases are not lost or there is no data loss with any type of failure, users can create SQL Server backups. This segment will discuss about the backup and restore strategies and methodologies of the same.

Various Types of SQL Server Backup

  • Full backups
  • Differential backups
  • Transaction log backups

Full Backups

This type of backup is the most common type of backup, and is known as database backup. It comprises the backup of databases along with part of the transactional log files. It provides simplest forms of database “backup and restore” method. This backup can be taken using T-SQL or SQL Server Management Studio. Read the rest of this entry »

 
1 Comment

Posted by on September 14, 2015 in backups

 

Tags:

Get all Database Job History then Send Email

***************************************************Introduction********************************************************

Today i will Dessecus how to Get all Database job history then Send Email to The Technical Support “job History Report” then i will delete this log history

Get Daatabase job history

select * from msdb.dbo.sysjobs

Job history

Get database job Step history

select * from msdb.dbo.sysjobhistory

Hob Step History

Now i will make join between msdb.dbo.sysjobhistory and msdb.dbo.sysjobs by Job_ID  and i will  Convert the job tun Date time because if you select it without convert you will sedd the Data Formate “20121224” so i will convert it , also i have column name “Run_Status” this Column return 0 or 1 { 0 = the job is Failed , 1 = The job is Success } so i will make Case when on this Column this Script you can use it for to ways

1- job Failed history report

2- job Success history Report

but i will write now ho to get job all history Failed and Success

select J.job_id,J.nameas[Job Name],S.step_name,S.step_id,

CONVET (DATETIME,RTRIM(S.run_date))+(S.run_time* 9 +S.run_time% 10000 * 6 +S.run_time% 100 * 10)/ 216e4 asRun_Date_Time,

RIGHT(‘000000’+CONVERT(varchar(6),S.run_duration), 6)as[Run Duration],

CASE S.run_status

WHEN 0 THEN‘JOB FAIL’

WHEN 1 THEN‘JOB Success’

END

,S.message,S.server,  CONVERT(DATETIME,RTRIM(S.run_date))+(S.run_time* 9 +S.run_time% 10000 * 6 +S.run_time% 100 * 10)/ 216e4 asRun_Date_Time

from  msdb.dbo.sysjobhistory as S innerjoin msdb.dbo.sysjobs as J

on s.job_id=J.job_id

where Step_id<> 0

/*

************************************ Send Email With Database job History Report**************************************

If you need to know hot Configure your database mail See this post in the First http://wp.me/p1Oidq-9A

*/

DECLARE @xmlNVARCHAR(MAX)

DECLARE @bodyNVARCHAR(MAX)

SET @xml=CAST((SELECTJ.nameAS‘td’,,S.step_nameAS‘td’,,S.step_idAS‘td’,”,CASES.run_status

WHEN 0 THEN‘JOB FAIL’

WHEN 1 THEN‘JOB Success’

END AS‘td’,

,CONVERT(DATETIME,RTRIM(S.run_date))+(S.run_time* 9 +S.run_time% 10000 * 6 +S.run_time% 100 * 10)/ 216e4  AS‘td’,”,S.messageAS‘td’

from msdb.dbo.sysjobhistory as S innerjoin msdb.dbo.sysjobs as J

on s.job_id=J.job_id

where Step_id<> 0

FOR XMLPATH(‘tr’),ELEMENTS)ASNVARCHAR(MAX))

SET @body=‘<html><body><H3>Database job history</H3>

<table border = 1>

<tr><font color=blue size=5 >

<td> Job Name </td> <td> Step Name </td> <td> Step ID </td> <td> Job Status </td> <td> Job_Run_Time </td> <td> message </td></font></tr>’

SET @body=@body+@xml+‘</table></body></html>’

EXEC msdb.dbo.sp_send_dbmail

@profile_name =‘DBA’,— replace with your SQL Database Mail Profile

@body =@body,

@body_format =‘HTML’,

@recipients =‘Mostafa@Safeerp.com’,— replace with your email address

@subject =‘Database job History’;

Report in Email

/*

************************************ Cleare job history**************************************************

If you need to know hot Configure your database mail See this post in the First

*/

EXEC MSDB.dbo.sp_purge_jobhistory

@job_name=N’testsenemail’; — JOB Nmar

GO

 
3 Comments

Posted by on December 24, 2012 in backups, backups

 

How to Check backup log /and How to Delete backup log

introduction

Today i will explain how to monitor your backup is Saved on or no also How to Delete the old backup history from the log

How to Check your Backup log or your backup history

we can Check the backup history by this way but before this step i need from you to Create Databae “backupTest” and take backup from it with 3 types Full,DIFF,LOG

select Database_name,backup_start_date,backup_finish_date,type from msdb.dbo.backupset

you can select all Column (*) to see all information

but i will customize it to help you how to check the FULL backup , DiFF backup , Log backup

********************************Check FULL abckup history************************************

SELECT database_name,backup_start_date,type,recovery_model,server_name,machine_name,is_password_protected

FROM msdb.dbo.backupset where Type=‘D’ ORDER BY backup_set_id DESC

GO

********************************Check DIFF abckup history************************************

SELECT database_name,backup_start_date,type,recovery_model,server_name,machine_name,is_password_protected

FROM msdb.dbo.backupset where Type=‘i’

ORDER BY backup_set_id DESC

********************************Check LOG abckup history************************************

SELECT

database_name,backup_start_date,type,recovery_model,server_name,machine_name,is_password_protected

FROM msdb.dbo.backupset where Type=‘L’

ORDER BY backup_set_id DESC

GO

How to Delete your backup history:-

we can make delete for all backup history for any database by this Code

exec MSDB.DBO.sp_delete_database_backuphistory ‘Database name’

So this Code Delete history to the backup but for one database one by one so i will make Cursore to loop on all Server to get all database name then Delete the backup history for all Database

set ANSI_NULLSON

set QUOTED_IDENTIFIERON

go

Create PROC[dbo].DeletebackupHistory

AS

BEGIN

DECLARE @DB Nvarchar(50)

DECLARE GetDB CURSOR FOR

Select Name from Sys.Databases Where database_id> 4 and state_desc=‘ONLINE’

OPEN GetDB

FETCH NEXT FROM GetDB INTO @DB

WHILE @@FETCH_STATUS= 0

BEGIN

—-Delete backup history

exec MSDB.DBO.sp_delete_database_backuphistory @db

——————————-

FETCH NEXT FROM GetDB

INTO @DB

END— FETCH WHILE

CLOSE GetDB

DEALLOCATE GetDB

END

 

 
Leave a comment

Posted by on December 24, 2012 in backups, backups

 

How to Change your Database Backup path After installation

After i install SQL Server i want to Change my backup path so leat’s See What i will do to change this path and Check it Change correct ot now

1- Open Start menu

2- open Run

3- Write RegEdit

4 – HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012\MSSQLServer

Note : After this path {HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\} you will select your SQL Server version i try this Example on SQL Server 2012 so i Select {MSSQL11.SQL2012}

5- in you write panal you will See BackupDirectory Write Click on it then modify then Write your new backup path

backup path

Check your backup path or See your backup path

DECLARE @BackupDirectoryVARCHAR(100)

EXEC master..xp_regread@rootkey=‘HKEY_LOCAL_MACHINE’,

@key=‘SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012\MSSQLServer’,

@value_name=‘BackupDirectory’,

@BackupDirectory=@BackupDirectoryOUTPUT

SELECT @BackupDirectory

Now if you want to return your datbase bakup path run this Script

EXEC  master..xp_regwrite

@rootkey=‘HKEY_LOCAL_MACHINE’,

@key=‘SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012\MSSQLServer’,

@value_name=‘BackupDirectory’,

@type=‘REG_SZ’,

@value=‘C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\Backup’

 
Leave a comment

Posted by on December 23, 2012 in backups

 

list down all SQL Server backups

Dwenload Script : http://hotfile.com/dl/149375389/c747113/List_Dowen_SQL_Server_Backups.rar.html

/*——————————————————————————————————————————-

— Description    :     This SQL Script will list down all SQL Server backups which were not backup in last 2 days

— Copyright 2012 – DBATAG

— Author        :    DBATAG

— Created on    :    02/06/2012

— Version       :    1.1

— Dependencies  :

— Compatibility  : This will work on SQL Server 2000, 2005, 2008, 2008 R2 and even in SQL Server 2012

—————————————————————————————————————————-*/

SELECT

   CONVERT(CHAR(100), SERVERPROPERTY(‘Servername’)) AS Server,

   bs.database_name,

   MAX(bs.backup_finish_date) AS last_db_backup_date,

   DATEDIFF(hh, MAX(bs.backup_finish_date), GETDATE()) AS [Backup Age (Hours)]

FROM    msdb.dbo.backupset bs

WHERE     bs.type = ‘D’

GROUP BY bs.database_name

HAVING      (MAX(bs.backup_finish_date) < DATEADD(hh, – 48, GETDATE()))

UNION

–Databases without any backup history

SELECT

   CONVERT(CHAR(100), SERVERPROPERTY(‘Servername’)) AS Server,

   master.dbo.sysdatabases.NAME AS database_name,

   NULL AS [Last Data Backup Date],

   9999 AS [Backup Age (Hours)]

FROM

   master.dbo.sysdatabases LEFT JOIN msdb.dbo.backupset bs

       ON master.dbo.sysdatabases.name  = bs.database_name

WHERE bs.database_name IS NULL AND master.dbo.sysdatabases.name <> ‘tempdb’

ORDER By bs.database_name

————————————————————————————————————————————————

/*——————————————————————————————————————————-

Description    :     This SQL Script will list down all SQL Server backups which were taken in last 10 days with details

— Copyright 2012 – DBATAG

— Author        :    DBATAG

— Created on    :    02/06/2012

— Version       :    1.0

— Dependencies  :

— Compatibility  : This will work on SQL Server 2000, 2005, 2008, 2008 R2 and even in SQL Server 2012

—————————————————————————————————————————-*/

SELECT

   CONVERT(CHAR(100), SERVERPROPERTY(‘Servername’)) AS Server,

   bs.database_name,

   bs.backup_start_date,

   bs.backup_finish_date,

   bs.expiration_date,

   CASE bs.type

       WHEN ‘D’ THEN ‘Full Database’

       WHEN ‘L’ THEN ‘Log’

       WHEN ‘I’ THEN ‘Differential’

       WHEN ‘F’ THEN ‘File Level’

       WHEN ‘G’ THEN ‘File Level Differential’

       WHEN ‘P’ THEN ‘Partial’

       WHEN ‘Q’ THEN ‘Differential partial’

   END AS backup_type,

   convert(varchar,cast(bs.backup_size/1024/1024 as money),10) as ‘Backup Size in MB’,

   bmf.logical_device_name,

   bmf.physical_device_name,

   bs.name AS backupset_name,

   bs.description

FROM   msdb.dbo.backupmediafamily  bmf

   INNER JOIN msdb.dbo.backupset bs ON bmf.media_set_id = bs.media_set_id

WHERE  (CONVERT(datetime, bs.backup_start_date, 102) >= GETDATE() – 10)

ORDER BY

   bs.database_name,bs.backup_finish_date

 
Leave a comment

Posted by on March 10, 2012 in backups, backups