Feeds:
Posts
Comments

Archive for May, 2015

Sometimes, it is very important to know when your database has been dropped as well who dropped it. Obviously, if you setup the database backup properly, you can easily recover it from the last backup but how to find who dropped/ deleted the database ? Today, I came across this issue and I started my research and found some solutions to recover this info using trace, however, I developed a script that will help you find who dropped database, at what time, by using SQL Server Log.

Note : Please do not use this script for any negative purpose.

Script :

--This script is compatible with SQL Server 2005 and above.
USE master
GO
DROP PROCEDURE Recover_Dropped_Database_Detail_Proc
GO
CREATE PROCEDURE Recover_Dropped_Database_Detail_Proc
@Date_From DATETIME='1900/01/01',
@Date_To DATETIME ='9999/12/31'
AS
;WITH CTE AS (
Select REPLACE(SUBSTRING(A.[RowLog Contents 0],9
,LEN(A.[RowLog Contents 0])),0x00,0x) AS [Database Name]
,[Transaction ID]
FROM fn_dblog(NULL,NULL) A
WHERE A.[AllocUnitName] ='sys.sysdbreg.nc1'AND
A.[Transaction ID] IN (
SELECT DISTINCT [TRANSACTION ID] FROM  sys.fn_dblog(NULL, NULL)
WHERE Context IN ('LCX_NULL') AND Operation IN ('LOP_BEGIN_XACT')
AND [Transaction Name] LIKE '%dbdestroy%'
AND CONVERT(NVARCHAR(11),[Begin Time]) BETWEEN @Date_From AND @Date_To))

SELECT
 A.[Database Name]
,B.[Begin Time] AS [Dropped Date & Time]
,C.[name] AS [Dropped By User Name]
FROM CTE A
INNER JOIN fn_dblog(NULL,NULL) B
ON A.[Transaction ID] =B.[Transaction ID]
AND Context IN ('LCX_NULL') AND Operation IN ('LOP_BEGIN_XACT')
AND [Transaction Name] LIKE '%dbdestroy%'
INNER JOIN sys.sysusers C ON B.[Transaction SID]=C.[Sid]

GO
EXEC Recover_Dropped_Database_Detail_Proc
GO

1

Let me know if you came across this issue and its solution as well.

Read Full Post »

On 26th May, we had a very informative session presented by Mr. Mohammed Owais (CTO at CAZAR) in SQL Server User Group meetup about Backups – not as simple as you think. He covered almost each and every aspect from full backup till tail log backup, however, a very nice question has been raised by an audience – ‘how to check the status of the backup / recovery along with the percentage via TSQL ?’. Because in most cases we have more than one DBA in an organization and sometimes they are geographically dispersed and if one of them takes backup / restore, how the others will come to know that he is performing any backup / restore using T-SQL.

Given below is the script which will give you the backup / restore progress along with the exact percentage and the user name (who is taking the backup).

USE master
GO
SELECT
  A.session_id As [Session ID]
, login_name As [Login Name]
, [command] As [Command]
, [text] AS [Script]
, [start_time] As [Start Time]
, [percent_complete] AS [Percentage]
, DATEADD(SECOND,estimated_completion_time/1000, GETDATE())
as [Estimated Completion time]
, [program_name] As [Program Name]
FROM sys.dm_exec_requests A
CROSS APPLY sys.dm_exec_sql_text(A.sql_handle) B
INNER JOIN sys.dm_exec_sessions C ON A.session_id=C.session_id
WHERE A.command in ('BACKUP DATABASE','RESTORE DATABASE')
GO

1 : While Taking Backup

USE master;
GO
BACKUP DATABASE AdventureWorks2012
TO DISK = 'C:\Data\AdventureWorks2012.Bak'
   WITH FORMAT,
      MEDIANAME = 'SQLServerBackups',
      NAME = 'Full Backup of AdventureWorks2012';
GO

raresql-Backup-Restore.1.2

2 : While Restoring Backup

USE master;
GO
RESTORE DATABASE AdventureWorks2012
FROM DISK = 'C:\Data\AdventureWorks2012.BAK'
WITH NORECOVERY
GO

raresql-Backup-Restore.1.1

Let me know if you came across this issue and its solution as well.

Read Full Post »

Yet again I had another opportunity to present Introduction to Policy Based Management yesterday (26 May 2015) at SQL Server User Group meet-up @ Microsoft Office, Dubai, UAE. As mentioned earlier, this event is normally held every last Tuesday of the month. Meet-up was organised by SQL Server User group (www.uaessug.com) and usually consists of two comprehensive and informative sessions that focus on the SQL Server latest technology, best practice, user experience, tips & tricks. This is a good platform to network with SQL Server experts face to face. I was the speaker for yesterday’s meet up along with Mr. Mohammed Owais (CTO at CAZAR).

Following topics were covered in yesterday’s session:

  • Backups – not as simple as you think presented by Mr. Mohammed Owais
  • Introduction to policy based management was by me

We focused on how you can make your restore strategy instead of backup strategy 🙂 and discussed different types of backup and how can it be handy in case of disaster. Also we discussed, how can you manage your policies across the servers by just click of a button.

I hope yesterday’s session was factual as usual. Our next meetup will be held in June 2015. Hope the coming sessions would witness more participants.

Some glimpses of the session:

Mr. Owais busy explaining Backup :

IMG_9815_U

He explained us why tail log backup is important :

IMG_9816_U

Further he explained, how to boast performance of backup :

IMG_9818_U

Me presenting Introduction to policy based management :

IMG_9820_U

Explaining PBM with the help of demos :

IMG_9821_U

IMG_9826_U

Last but not least, lunch 🙂

IMG_9828_U

IMG_9829_U

IMG_9831_U

Thank you guys. See you next month.

Read Full Post »