SQL Server 2012 – Page 8

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

SP Help you to take FULL,DIFF,LOG backup from all DB

Introducation

Database backup we have 3 typr from backup Full backup , Diffrential backup , log transaction backup now i write stored procedure make koop on all database on SQl Server instance then take backup from it this Stored procedure take 2 paramter

@BackupPath :- this the path of your hard disk

@backupType :- your database backup typy this parmater allow Number Value { 1 = Full backup , 2 = Diff backup , 3 = log Trans backup } any thing not like this 3 value Stored will give you Error.

Now you can Create this proc under your database master then you can call it

Note : if you want to put your database backup for starday in folder and Sunday in folder ……ETC please Check this Post http://wp.me/p1Oidq-1D

Create Stored Procedure TakeDBsBackupDaily

USE MASTER

set ANSI_NULLSON

set QUOTED_IDENTIFIERON

Create PROCEDURE[dbo].[TakeDBsBackupDaily]

@BackupPathnvarchar(500),@backupTypeint

BEGIN

declare @dnvarchar(2)

,@mnvarchar(2)

,@ynvarchar(4)

,@CurDatenvarchar(8)

,@hnvarchar(2)

,@minnvarchar(2)

,@secnvarchar(2)

,@CurTimenvarchar(8)

,@FNamenvarchar(500)

,@DbNamenvarchar(100)

,@BackupTypeNamenvarchar(200)

Set @d=DATEPART(“day”,(GETDATE()))

Set @m=DATEPART(“month”,(GETDATE()))

Set @y=DATEPART(“year”,(GETDATE()))

set @CurDate=replicate(‘0’,2–len(@d))+@d+replicate(‘0’,2–len(@m))+@m+replicate(‘0’,4–len(@y))+@y

Set @h=DATEPART(“hour”,(GETDATE()))

Set @min=DATEPART(“minute”,(GETDATE()))

Set @sec=DATEPART(“second”,(GETDATE()))

set @CurTime=replicate(‘0’,2–len(@h))+@h+replicate(‘0’,2–len(@min))+@min+replicate(‘0’,2–len(@sec))+@sec

DECLARE bK CURSORFOR

SELECT name FROM sys.databases WHERE Database_ID> 4 andstate_desc=‘Online’

OPENbk

FETCH NEXT FROM bk

INTO @DbName

WHILE @@FETCH_STATUS=0

BEGIN

—–FULL backup

IF @backupType= 1

begin

SET @FName= @BackupPath+‘\’+@DbName +‘_D_’+@curdate+‘_FULL’+‘.bak’

print‘Backing up Database : ‘+@DbName+‘ …’

set @BackupTypeName= @DbName+‘-FULL Database Backup’

PRINT @FName

BACKUP DATABASE @DbNameTO DISK=@FName WITH NOFORMAT,INIT, NAME = @BackupTypeName , SKIP, NOREWIND,NOUNLOAD,STATS= 10

End

—DIFF backup

Else IF @backupType = 2

begin

SET @FName = @BackupPath+‘\’+@DbName +‘_D_’+@curdate+‘_DIFF’+‘.bak’

print‘Backing up Database : ‘+@DbName+‘ …’

set @BackupTypeName = @DbName+‘-DIFF Database Backup’

PRINT@FName

BACKUP DATABASE @DbNameTO DISK = @FName WITH DIFFERENTIAL,NOFORMAT,NOINIT, NAME = @BackupTypeName,SKIP,NOREWIND,NOUNLOAD, STATS= 10

End

—Log backup

Else IF@backupType= 3

begin

SET @FName = @BackupPath+‘\’+@DbName +‘_D_’+@curdate+‘Log’+‘.bak’

print‘Backing up Database : ‘+@DbName+‘ …’

set @BackupTypeName = @DbName+‘-Log_Trans Database Backup’

PRINT@FName

BACKUP LOG @DbNameTO DISK = @FName WITH NOFORMAT,NOINIT, NAME = @BackupTypeName,SKIP,NOREWIND,NOUNLOAD, STATS = 10

End

Else

RAISERROR(‘Invalid Backup Type value.

Must be Write your Database backup type

1 = FULL backup

2 = Diffrential backup

3 = log Transaction backup

Or Contact the Adminstrator Mostafa Elmasry 0593205711′, 16, 1);

FETCH NEXT FROM bk

INTO @DbName

END

CLOSE bk

DEALLOCATE bk

END

####################################################################################

now try to Execute your Stored Procedure

Full backup

exec [TakeDBsBackupDaily] ‘E:\backups\Log’,1

DIFF backup

exec [TakeDBsBackupDaily] ‘E:\backups\Log’,2

Log backup

exec [TakeDBsBackupDaily] ‘E:\backups\Log’,3

October 31, 2012June 4, 2013 Mustafa EL-Masry Script

Create comma-separated list

1- Create Table Emp

SET ANSI_NULLSON

SET QUOTED_IDENTIFIERON

CREATE TABLE [dbo].[Emp](

[Emp_Code] [int] NULL,

[Emp_Name] [nvarchar] (50)NULL)

ON [PRIMARY]

2- Insert Data

Insert into Emp values (1,‘Mostafa’),(1,‘Moahmed’),(2,‘Elsayed’),(2,‘Elmasry’)

3- Create comma-separated list

WITH CTE AS (

SELECT DISTINCTEmp_Code FROM Emp )

SELECT Emp_Code, CommaList =STUFF((SELECT‘,’+ Emp_name FROM Emp

WHERE Emp_Code = CTE.Emp_Code ORDERBY Emp_name

FORXMLPATH(”),TYPE).value(‘.’,‘varchar(max)’),1,1,”)FROM CTE

ORDERBY Emp_Code;

October 12, 2012June 4, 2013 Mustafa EL-Masry Script

Drop All Databases in SQL Server

EXEC sp_MSforeachdb ‘
IF DB_ID(”?”) > 4
BEGIN
ALTER DATABASE ? SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE ?
END’

Share this:

Share this:

Share this:

Share this:

Share this: