Hey guys!
All very well ?
In this article I will share with you a solution that can save lives in desperate cases when someone deletes an object from the database (View, Stored Procedure, View or Function), you don't have a versioning routine for database objects and you need to try to recover that object quickly, without having to go back to a backup (which can take a certain amount of time, depending on the size of the database).
If you want to avoid this type of situation (where it is not always possible to recover the object's code), I suggest implementing version control for database objects, available in the post How to create an Audit trigger to log object manipulation in SQL Server.
If you just need to know if a specific object was deleted and who deleted it, you can use SQL Server's standard Trace (which is enabled by default) to obtain this information. Find out more in the post Monitoring DDL and DCL operations using SQL Server's fn_trace_gettable.
When you do not have any version control over your database objects (View, Stored Procedure, Function and Trigger) and someone accidentally deletes this object, a big problem may have been created for the DBA, especially when this object does not exist or is not updated in other environments (DEV, QA, etc.), as it will be necessary to upload a parallel database with the last backup to recover the source code of this object, which can require a lot of time and effort.
The purpose of this article is to provide some alternatives that can help you recover the source of these objects, even without version control (which is not ideal) and without having to go back to a backup.
Testing environment
For the tests in this article, I will create a new database, set the Recovery model to FULL and create some objects. After that, I will use these objects and we will try to recover them again.
Script used to create the bank:
CREATE DATABASE [Teste]
ON PRIMARY
(
NAME = N'Teste',
FILENAME = N'C:\Dados\Teste.mdf',
SIZE = 100MB,
FILEGROWTH = 500MB
)
LOG ON
(
NAME = N'Teste_log',
FILENAME = N'C:\Dados\Teste_log.ldf',
SIZE = 100MB,
FILEGROWTH = 500MB
);
GO
ALTER DATABASE [Teste] SET RECOVERY FULL
GO
Script used to create objects:
USE [Teste]
GO
CREATE FUNCTION fncRecupera_Objeto_ID(@Nome sysname)
RETURNS INT
AS BEGIN
RETURN (SELECT TOP(1) [object_id] FROM sys.objects WHERE [name] = @Nome)
END
GO
SELECT dbo.fncRecupera_Objeto_ID('sysallocunits')
SELECT dbo.fncRecupera_Objeto_ID('fncRecupera_Objeto_ID')
GO
CREATE PROCEDURE stpRecupera_Informacoes_Objeto(@Nome sysname)
AS
BEGIN
SELECT *
FROM sys.objects
WHERE [name] = @Nome
END
GO
EXEC stpRecupera_Informacoes_Objeto @Nome = 'fncRecupera_Objeto_ID'
GO
CREATE VIEW vwObjetos
AS
SELECT *
FROM sys.objects
WHERE is_ms_shipped = 0
GO
SELECT * FROM dbo.vwObjetos
Now, I'm going to delete the objects:
DROP FUNCTION fncRecupera_Objeto_ID
GO
DROP PROCEDURE stpRecupera_Informacoes_Objeto
GO
DROP VIEW vwObjetos
GO
How to recover deleted objects by reading T-log
One of the alternatives to recover the source code of deleted objects is to read transactions and data from the Transaction Log of SQL Server, on a tip I received from MVP Rafael Santos in a Whatsapp group.
In case you don't know, Transaction Log is a critical database component that stores all changes made to a database, both structure and data. When the CHECKPOINT command is executed (either manually or automatically), the Transaction-Log data is stored in the database data file and is removed from the transaction log. To better understand how the CHECKPOINT process works, read the excellent post The CHECKPOINT process, by Vitor Fava.
That said, it is already clear that the useful life of the data stored in the Transaction Log is limited and this leaves this solution a little lacking in terms of the reliability of recovering the desired data. Furthermore, in order to recover transaction log data, the base's Recovery Model must be set to FULL.
That said, let's try to recover the objects deleted at the beginning of this post. To do this, we will use the undocumented function sys.fn_dblog in the Stored Procedure below:
USE [master]
GO
CREATE PROCEDURE [dbo].[stpRecupera_Objetos_Excluidos]
@Database NVARCHAR(MAX),
@Dt_Inicial DATETIME,
@Dt_Final DATETIME
AS
BEGIN
DECLARE @Compatibility_Level INT = (SELECT A.[compatibility_level] FROM master.sys.databases AS A WHERE A.name = @Database)
IF (ISNULL(@Compatibility_Level, 0) < = 80) BEGIN
RAISERROR('O modo de compatibilidade deve ser igual ou maior que 90 (SQL SERVER 2005)', 16, 1);
RETURN
END
DECLARE @Query VARCHAR(MAX) = '
USE [' + @Database + '];
SELECT
''' + @Database + ''' AS [DatabaseName],
A.AllocUnitName,
A.[Page ID],
A.[Slot ID],
CONVERT(VARCHAR(MAX), SUBSTRING([RowLog Contents 0], 33, LEN([RowLog Contents 0]))) AS [Script]
FROM
sys.fn_dblog(NULL, NULL) A
WHERE
[AllocUnitName] = ''sys.sysobjvalues.clst''
AND A.Operation = ''LOP_DELETE_ROWS''
AND A.Context = ''LCX_MARK_AS_GHOST''
AND EXISTS (
SELECT
NULL
FROM
sys.fn_dblog(NULL, NULL) X
WHERE
[Transaction Name] IN (''CREATE/ALTER PROCEDURE'', ''CREATE/ALTER FUNCTION'', ''CREATE TABLE'', ''DROPOBJ'')
AND (CONVERT(DATETIME, [Begin Time]) BETWEEN ''' + CONVERT(VARCHAR(19), @Dt_Inicial, 121) + ''' AND ''' + CONVERT(VARCHAR(19), @Dt_Final, 121) + ''')
AND A.[Transaction ID] = X.[Transaction ID]
)
AND SUBSTRING([RowLog Contents 0], 33, LEN([RowLog Contents 0])) <> 0'
EXEC(@Query)
END
Usage example:
As you can see, we were able to easily recover the code of the 3 objects that we deleted. This was only possible because the recovery model was set to FULL and the data was still stored in the Transaction Log.
In environments that have frequent log backups (I imagine this is the majority), the time you have between deleting the data and trying to recover it can be very short, since when making a log backup and/or running a CHECKPOINT, this data is no longer accessible using this function.
How to recover deleted objects by reading log backups
Using a solution slightly different from the one presented previously, I will demonstrate how to recover deleted objects by reading log backups, which is when you make a backup of the transaction log and the data that was available using the undocumented function sys.fn_dblog is no longer accessible, as the transaction log data is stored in the log backup files and the transaction log is truncated.
To overcome this situation, Microsoft provides us with the fn_dump_dblog function, where we can read log backup files and extract information regarding transactions carried out on the instance.
To do this, I will delete the created objects, perform a full backup of the base, recreate the objects, delete them again and perform a log backup:
USE [Teste]
GO
IF (OBJECT_ID('dbo.fncRecupera_Objeto_ID') IS NOT NULL) DROP FUNCTION fncRecupera_Objeto_ID
GO
IF (OBJECT_ID('dbo.stpRecupera_Informacoes_Objeto') IS NOT NULL) DROP PROCEDURE stpRecupera_Informacoes_Objeto
GO
IF (OBJECT_ID('dbo.vwObjetos') IS NOT NULL) DROP VIEW vwObjetos
GO
-------------------------------------------------------------------------------
BACKUP DATABASE [Teste] TO DISK ='C:\Backups\SQL\sql2016\Teste_20180707_231600.bak'
GO
-------------------------------------------------------------------------------
CREATE FUNCTION fncRecupera_Objeto_ID(@Nome sysname)
RETURNS INT
AS BEGIN
RETURN (SELECT TOP(1) [object_id] FROM sys.objects WHERE [name] = @Nome)
END
GO
SELECT dbo.fncRecupera_Objeto_ID('sysallocunits')
SELECT dbo.fncRecupera_Objeto_ID('fncRecupera_Objeto_ID')
GO
CREATE PROCEDURE stpRecupera_Informacoes_Objeto(@Nome sysname)
AS
BEGIN
SELECT *
FROM sys.objects
WHERE [name] = @Nome
END
GO
EXEC stpRecupera_Informacoes_Objeto @Nome = 'fncRecupera_Objeto_ID'
GO
CREATE VIEW vwObjetos
AS
SELECT *
FROM sys.objects
WHERE is_ms_shipped = 0
GO
SELECT * FROM dbo.vwObjetos
-------------------------------------------------------------------------------
IF (OBJECT_ID('dbo.fncRecupera_Objeto_ID') IS NOT NULL) DROP FUNCTION fncRecupera_Objeto_ID
GO
IF (OBJECT_ID('dbo.stpRecupera_Informacoes_Objeto') IS NOT NULL) DROP PROCEDURE stpRecupera_Informacoes_Objeto
GO
IF (OBJECT_ID('dbo.vwObjetos') IS NOT NULL) DROP VIEW vwObjetos
GO
-------------------------------------------------------------------------------
CHECKPOINT
GO
BACKUP LOG [Teste] TO DISK ='C:\Backups\SQL\sql2016\Teste_20180707231600.trn'
GONow, let's read the log backup data:
USE [master]
GO
CREATE PROCEDURE [dbo].[stpRecupera_Objetos_Excluidos_Backup]
@Ds_Arquivo NVARCHAR(MAX)
AS
BEGIN
DECLARE @Query VARCHAR(MAX) = '
SELECT
A.[Page ID],
A.[Slot ID],
CONVERT(VARCHAR(MAX), SUBSTRING([RowLog Contents 0], 33, LEN([RowLog Contents 0]))) AS [Script]
FROM
sys.fn_dump_dblog(NULL, NULL, N''DISK'', 1, ''' + @Ds_Arquivo + ''', DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT) A
WHERE
A.Operation = ''LOP_DELETE_ROWS''
AND A.Context = ''LCX_MARK_AS_GHOST''
AND SUBSTRING([RowLog Contents 0], 33, LEN([RowLog Contents 0])) <> 0'
EXEC(@Query)
END
Result:
As you could see above, as I made the log backup, stpRecupera_Objetos_Excluidos (sys.fn_dblog) no longer returned the commands to create the objects we wanted to recover.
However, using stpRecupera_Objetos_Excluidos_Backup (sys.fn_dump_dblog) and specifying the log backup file that we created, it was possible to recover the object creation commands.
Using ApexSQL Log
Another way to recover the source code of deleted objects and even view the modified/deleted data in tables is by using the paid tool ApexSQL Log. This tool's distinguishing feature is that it is easy to use, intuitive and allows you to analyze Transaction Log data and can also recover information from Transaction-Log backups.
Operations subject to recovery:
ApexSQL Log Demo:
How to retrieve queries by reading the plan cache
Another alternative to trying to retrieve object and query code is by consulting the execution plan cache. When a query or object is executed, SQL Server analyzes whether to cache this execution (for performance purposes) or not.
Although it is not very applicable to the scenario proposed in this post (since when changing/deleting an object, its cache execution is generally deleted as well), the query below can be useful to retrieve previously performed queries:
USE [Teste]
GO
SELECT
[cp].[refcounts],
[cp].[usecounts],
[cp].[objtype],
[st].[dbid],
[st].[objectid],
[st].[text],
[qp].[query_plan]
FROM
sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp;Result:
It is worth mentioning that the useful life of a cache execution is very limited and short, where the cache can be cleared as queries are executed or even if the SQL service is restarted.
How to retrieve queries using SQL Prompt
As already demonstrated in the post SQL Server – Write T-SQL like a Ninja using Redgate SQL Prompt, SQL Prompt has a really cool feature called “Tab History”, which stores all the queries or objects that you have opened since you installed SQL Prompt.
If you recently opened/changed this object that was deleted, there is a good chance that you will be able to recover it using Tab History.
This feature is really very practical, useful and sometimes it can even save lives... lol... In my previous company, my Tab History reached almost 2 GB, with all the queries/objects that I had already opened/viewed the code for in the last 4 years (and it already saved me A LOT of time).
Well, that's it, folks!
I hope you liked this post.
A hug and see you next time.
Comentários (0)
Carregando comentários…