Looking deeper into the physical & logical architecture - Transaction Log File

Beyond all doubt, it is essential to have a good understanding of the Transaction Log (T-Log) so that we can diagnose unforeseen performance issues related to it and I am sure that almost everyone had at least one. The T-Log is basically a record of all transactions happening to the database. All these transactions are actually first written to the physical T-Log file, and then after a CHECKPOINT, is written to the Data File via the Lazy Writer process. Some of the uses of T-Log are: as a point in time recovery (full recovery model), to record the Start and End of each transaction, every data modification (insert, update, delete) including system SP's, DDL statements to any table including system tables, every extent and page allocation and de-allocation operation, and creation or drop of tables and indexes.

Getting Important Information of SQL Server Backups

Regardless of the method employed to take SQL Server backups, they must be carefully monitored around the clock, particularly in critical database environments where data changes rapidly. Keeping backups current is vital, as they can save considerable time when it comes to recovering databases in the event of unforeseen incidents. Furthermore, it has been disappointing to observe numerous environments lacking a backup strategy, often with no one designated to oversee them. In this context, it is evident that these businesses were at significant risk of losing vast amounts of data and incurring substantial financial losses.

Sometimes, there is a belief that backing up the entire virtual machine is sufficient; however, this type of backup serves a different purpose than a dedicated database backup. Consequently, SQL Server database backups cannot be substituted with virtual machine backups, which can be time-consuming, inadequate, and impractical for databases. Thus, implementing an appropriate database backup strategy (for example, with Full + Differential + Log Backups) and closely monitoring them are essential tasks for ensuring the recovery process of databases.

MSSQL_ENG003165: An error was encountered while replication was being restored/removed. The database has been left offline

While restoring a replicated database without KEEP_REPLICATION option, SQL Server will remove replication settings by executing sp_restoredbreplication at the end of the process. The 'sp_restoredbreplication' system stored procedure will delete all replication metadata, that is, deletion of 'tr_MStran_alterschemaonly', 'tr_MStran_altertable', 'tr_MStran_altertrigger' and 'tr_MStran_alterview' tiggers (which were created to validate alterations on the replication of tables, triggers, views), disable user tables for replication, and deletion of subscription/publications/articles. Nevertheless, there might be some cases where 'sp_restoredbreplication' cannot be executed successfully and ends up leaving the database OFFLINE. I personally experienced that case and the error was something like this:

Msg 3165, Level 16, State 1, Line 1
Database ‘MyDB’ was restored, however an error was encountered while replication was being restored/removed. The database has been left offline. See the topic MSSQL_ENG003165 in SQL Server Books Online.
Msg 3167, Level 16, State 1, Line 1
RESTORE could not start database ‘MyDB’.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Looking into this case, I could see that the cause was a DDL database trigger which existed inside the database. Let me expand on what I am saying. The database had that trigger to audit some schema changes which were supposed to save into an auditing table. Unfortunately, that auditing table did not exist in the server where the database was being restored, and the deletion of objects of replication settings were not completed, which means that 'sp_restoredbreplication' was not executed correctly. Consequently, the restoration was stopped and SQL Server decided to leave the database OFFLINE.

In order to restore a copy of this database, we need to disable all DDL database triggers before taking its backup. Only then will the database be restored successfully. The other method to deal with this issue is to change the status to ONLINE manually after the restoration finishes unsuccessfully and also execute 'sp_restoredbreplication'.

To sum up, we need to proceed with more cautiousness while working with databases linked to replication. That is all for now. Let me know any remarks you may have. Thanks for reading. Stay tuned.

Purging old backup files by using forfiles windows tool

It is well know that most backup strategies include a step to purge backup files to keep the most recent backups in the database server so that the disk space can be used properly. It is of paramount importance to schedule this task inside a SQL job in order to avoid running out of space. Today I am going to share a script to do that that uses forfiles windows tool via cmdshell. This script is within a stored procedure which has some input paramaters such as the database name, backup type, drive, and retention days.

USE [master] 
GO
CREATE PROCEDURE [dbo].[sp_DBA_Backup_FilesCleanup] (
@DatabaseName VARCHAR(200),
@BackupType VARCHAR(100),
@DriveName VARCHAR(1), 
@RetentionDays VARCHAR(4))
WITH ENCRYPTION
AS 
BEGIN
    SET NOCOUNT ON

    DECLARE @strcmd VARCHAR(4000)
    DECLARE @directory VARCHAR(4000)
    
    SET @directory=@DriveName + ':\SQLBackup\' + @DatabaseName --+ '\'  + @BackupType 
    SET @strcmd='forfiles /p "'+@directory+'" /s /d -'+ @RetentionDays +' /c "cmd /c del /q @path"'
      -- print @strcmd
    EXEC master.dbo.xp_cmdshell   @strcmd 
   
   SET NOCOUNT OFF
END
GO

The logic deletes old backups files located on a path with this pattern '<Drive>:\<BackupDirectory>\<DatabaseName>\<BackupType>'. For instance, if we want to delete Full + Diff + Log Backup Files of the database 'MyDB' older than one week and supposing that those backups files are located on the drive 'G' then the full path would be 'G:\SQLBackup\MyDB\Full' for Full Backups, 'G:\SQLBackup\MyDB\Diff' for Differential Backups, and 'G:\SQLBackup\MyDB\Log' for Log Backups. So, using the following stored procedure and according to the example above, we should execute it with the following parameters:

USE [master] 
GO
EXEC dbo.sp_DBA_Backup_FilesCleanup  @DatabaseName='MyDB' , @BackupType='FULL',@DriveName='G', @RetentionDays='7'
GO
EXEC dbo.sp_DBA_Backup_FilesCleanup  @DatabaseName='MyDB' , @BackupType='Diff',@DriveName='G', @RetentionDays='7'
GO
EXEC dbo.sp_DBA_Backup_FilesCleanup  @DatabaseName='MyDB' , @BackupType='Log',@DriveName='G', @RetentionDays='7'

That is all for now. Let me know any remarks you may have. Stay tuned.

Why is the .txt history detail file of SQL Backup Job not created?

Clearly, we always need to have some kind of history for SQL Backup jobs in order to check whether or not they were executed correctly. I personally use a .txt file to write on it all details of each step executed so that I can use it to diagnose any problem or error behind it. Nevertheless, at times set it up may become something not so easy as the .txt file might not be created during the SQL Backup job execution.


There are some reasons why this may be happening. The first one is because the directory where the .txt file does not exist. It should have been created manually before executing the SQL Backup job. The second reason is because the backup directory is blocked. Surprisingly, you will realise it is with blocked access when you try to open it. One way to get it unblocked is by simply opening the directory and then clicking on “continue” via Windows Explorer.



The third one is because the SQL Agent account does not have permissions on that directory. There must have given Read and Write permission on it and there are some cases where we will need to give explicit permissions to the SQL Agent account on that directory via CMD windows command tool. Undoubtedly, it would be no problem if we are working on only one directory but what would it happen whether we are implementing many SQL Backup jobs? it would become a very tedious job to manage one by one. So, in this case we need some manner to automatize and get them done rapidly. For instance, here I am going to show you a technique to achieve it:

icacls "H:\SQLBackup\FinancialDB\Full" /grant MyDomain\sqlagentAccount:(OI)(CI)F

Now for creating the script to give permission on all necessary directories we can create the code by using this T-SQL (based on reading the directory from Backup Devices whose names are like 'FinancialDB-Full_Database_Backup'):

DECLARE @sn NVARCHAR(128);
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE', 'SYSTEM\CurrentControlSet\services\SQLSERVERAGENT',  'ObjectName',   @sn OUTPUT;
select 'icacls "'+SUBSTRING(physical_name,1, LEN(physical_name)-CHARINDEX('\',REVERSE(physical_name)))+'" /grant '+@sn+':(OI)(CI)F'
from sys.backup_devices where name
ORDER BY name 

All the output results must be executed on CMD tool (as Administrator) and finally after executing the SQL Backup job we will verify that the .txt file was created:

And the history details are inside:


I hope this tip is useful and practical for you. Let me know any remark you may have. Thanks for reading!

Using KEEP_CDC option to keep CDC metadata

Having a database with Change Data Capture (CDC) enabled, we will need to take this recommendation into consideration during the restore process. There is one option to be used as part of RESTORE syntax. This is KEEP_CDC option which allows us to restore the CDC metadata as well when the database is restored to another SQL Server instance (or restore it in the same instance with a different a name, indeed). So, here is the example:

RESTORE DATABASE [TESTDB2] FROM  DISK = N'D:SQLBackupTESTDB.bak' WITH  FILE = 1, KEEP_CDC

While verifying that CDC metadata was restored as well, we will see that not only the database and tables keep CDC option enabled, but also all data inside captured by CDC is still there.

-- Checking if CDC is enabled for database TESTDB2.

select is_cdc_enabled,name from sys.databases where name='TESTDB2'

-- Checking if CDC is enabled for table MyTable.

select is_replicated, is_tracked_by_cdc, * from sys.tables

select * from [cdc].[change_tables]  

-- checking the data tracked for table MyTable.

select * from cdc.dbo_MyTable_CT 

This is not all. Now we must create the CDC jobs by executing:

USE TESTDB2

EXEC sys.sp_cdc_add_job 'capture'

EXEC sys.sp_cdc_add_job 'cleanup' 

Finally, you also can verify the jobs were created for CDC

USE TESTDB2

EXEC [sys].[sp_cdc_help_jobs] 

Having done that, the restore process of database (with CDC included) has been completed successfully. Thanks for reading!

SQL11 (Denali): How to migrate SQL Server 2000 databases to SQL Server 2012?

Having found 'SQL Server 2000 (80)' inside the compatibility levels supported by SQL Server 2012, I need to say that the upcoming version of SQL Server is not going to support it. we do know that each new version only supports compatibility levels of two below versions, which means that it is an error as this is a CTP build.



What is going on if we try to restore a SQL Server 2000 database on SQL Server 2012? well, we will get the following error:

Msg 3169, Level 16, State 1, Line 1
The database was backed up on a server running version 8.00.0760. That version is incompatible with this server, which is running version 11.00.1103. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Attaching the database neither works.

Msg 1813, Level 16, State 2, Line 1
Could not open new database ‘Northwind’. CREATE DATABASE is aborted.
Msg 1813, Level 16, State 2, Line 1
Could not open new database ‘Northwind’. CREATE DATABASE is aborted.
Msg 950, Level 20, State 1, Line 1
Database ‘Northwind’ cannot be upgraded because its non-release version (0) is not supported by this version of SQL Server. You cannot open a database that is incompatible with this version of sqlservr.exe. You must re-create the database.

Therefore, SQL Server 2000 databases have to be migrated to SQL Server 2012 following the next steps: (1) restore SQL Server 2000 databases on SQL Server 2005 or SQL Server 2008; (2) take Full Backups of the new SQL Server 2005/2008 databases, and finally; (3) restore them on SQL Server 2012. In other words, there will not be direct migration from SQL Server 2000 to SQL Server 2012. That is all for now, let me know any remarks you may have. Thanks for reading again!