RSS

Monthly Archives: December 2012

HOW TO CHECK YOUR SQL SERVER MIRROING IS RUNNING OR NOT ?

HOW TO CHECK YOUR MIRROING IS RUNNING OR NOT ?

you can Check :

1-  port number listener : open CMD and write this Command  ” netstat -an ” you will see the 3 port for the 3 Servers Principle – mirroring – Witness also I see the 3 port because I setup the 3 SQL Server Instance in the same  Windows Server

Check port

2-  Event Viewer for Windows Server

Event Viewr

3-  SQL Server Database Log

SQl Server Log

4-  Check Endpoint Status

Check Endpoint

 

 
2 Comments

Posted by on December 31, 2012 in Mirroring SQL Server 2008

 

What is the Stander listener port in Database Mirroring Configuration?

If you Create the 3 SQL Servers in the same PC

Begin

you must use difference port

Principle Server use the Stander port number 5021

Mirroring Server use the Stander port number 5022

Witness Server use the Stander port number 5023

End

Else The 3 SQL in 3 different PC

You can use one port like 5021

Don’t Forget to Disable Firewall or must be open this ports on the Firewall

 
Leave a comment

Posted by on December 29, 2012 in Mirroring SQL Server 2008

 

My blog {Database (SQL Server , Oracle) World }Status

Untitled3

Untitled

 
Leave a comment

Posted by on December 25, 2012 in Uncategorized

 

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

 

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

GO

set ANSI_NULLSON

set QUOTED_IDENTIFIERON

GO

Create PROCEDURE[dbo].[TakeDBsBackupDaily]

@BackupPathnvarchar(500),@backupTypeint

AS

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’,2len(@d))+@d+replicate(‘0’,2len(@m))+@m+replicate(‘0’,4len(@y))+@y

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

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

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

set @CurTime=replicate(‘0’,2len(@h))+@h+replicate(‘0’,2len(@min))+@min+replicate(‘0’,2len(@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

 
Leave a comment

Posted by on December 23, 2012 in backups