backups – DB Cloud TECH

September 14, 2015September 15, 2015 Andrew Jackson 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. Continue reading “An Abstract On SQL Server Backup” →

December 24, 2012December 24, 2012 Mustafa EL-Masry backups, backups

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

Get database job Step history

select * from msdb.dbo.sysjobhistory

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>

<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’;

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

December 24, 2012 Mustafa EL-Masry 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

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

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

Create PROC[dbo].DeletebackupHistory

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

December 23, 2012December 23, 2012 Mustafa EL-Masry 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

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’

March 10, 2012March 10, 2012 Mustafa EL-Masry backups, 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

— 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

— 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

overview

Various Types of SQL Server Backup

Full Backups

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this: