Showing posts with label Backup/Restore. Show all posts
Showing posts with label Backup/Restore. Show all posts

Tuesday 20 February 2018

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.

Friday 12 January 2018

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.

Friday 13 October 2017

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] 
CREATE PROCEDURE [dbo].[sp_DBA_Backup_FilesCleanup] (
@DatabaseName VARCHAR(200),
@BackupType VARCHAR(100),
@DriveName VARCHAR(1), 
@RetentionDays VARCHAR(4))

    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 

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] 
EXEC dbo.sp_DBA_Backup_FilesCleanup  @DatabaseName='MyDB' , @BackupType='FULL',@DriveName='G', @RetentionDays='7'
EXEC dbo.sp_DBA_Backup_FilesCleanup  @DatabaseName='MyDB' , @BackupType='Diff',@DriveName='G', @RetentionDays='7'
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.

Monday 3 July 2017

Getting important information of SQL Server Backups

Regardless of the method that is used to take SQL Server backups, they need to be carefully monitored 24x7 hours especially in critical database environments whose data is changing rapidly. Keeping the backups up to date is crucial as they can save us a lot of time when it comes to recovering databases in the likely event of unwitting incidents. On the top of that, in my experience it was shameful to see many environments without a backup strategy in place, even more, nobody was in charge of them.  In that context, it is clear that those businesses were surely at high risk of losing a lot of data and jaw-dropping amounts of money. Sometimes it is believed that having backups of the whole virtual machine is good enough, nevertheless, this sort of backup has a different purpose from a database backup. Thus, at no time can we replace SQL Server database backups with virtual machine backups which are time-consuming, inadequate, far-fetched for databases. In this sense, having implemented a suitable database backup strategy (Full + Diff + Log Backups) and carefully monitoring them are fundamental tasks to serve the needs of ensuring the recovery database process.
Broadly, it is of paramount importance to have a script at hand to find out whether or not specific sorts of backups were taken properly as well as how long they last, the size of the backup file, who took the backup, or if the backup was taken using CHECK_SUM option, etc. Today I am sharing that script which returns important information of backups for all databases in a SQL Server instance. In the following script, it filters out info of Full Backups (type='D'). You can use 'L' for Log Backup and 'I' for Differential Backup. So, try it out and make the most out of the info.

SELECT isnull(,'') DatabaseName,
CASE is_read_only WHEN 1 THEN 'YES' ELSE 'NO' END as IsReadOnly, 
cast(sum(size)/128.0/1024.0 as decimal(8,2) ) as DBSize_InGB ,  
isnull( last_backup.recovery_model,'')RecoveryModel,isnull(,'') as BackupName ,
isnull(cast(last_backup.backup_start_date as varchar(24)),'(No Taken)') as BackupStartDate,  
isnull(case when [backup_size]>1024 then  cast( cast([backup_size]/1024.0 as decimal(8,2)) as varchar(12)) 
     + 'GB' else cast([backup_size] as varchar(12)) + 'MB' end,0) as BackupSize,
isnull(case when compressed_backup_size>1024 then  cast( cast(compressed_backup_size/1024.0 as decimal(8,2)) as varchar(12)) +
     'GB' else cast(compressed_backup_size as varchar(12)) + 'MB' end,0) as CompressedBackupSize ,
 isnull([duration],'') as Duration, isnull(is_copy_only,'') as IsCopyOnly, isnull(is_damaged,'') as IsDamaged,  
 isnull(has_backup_checksums,'') as HasBackupCheckSUM, isnull([user_name],'')as UserName
FROM master.sys.sysaltfiles  f
    inner join sys.databases d on f.dbid=d.database_id
    inner join (select database_name,backup_start_date, name, is_damaged,is_password_protected,
                has_incomplete_metadata,is_copy_only, cast(backup_size/1024.0/1024.0 as decimal(8,2))  as [backup_size], 
                cast(compressed_backup_size/1024.0/1024.0 as decimal(8,2))  as compressed_backup_size,
                isnull(cast(  CAST((DATEDIFF(second, bs.backup_start_date, bs.backup_finish_date))/3600 AS varchar) + ' hours ' 
                     + CAST(((DATEDIFF(second, bs.backup_start_date, bs.backup_finish_date))%3600 )/60 AS varchar)+ ' min'    as varchar(40) 
                ), '(No Available)') as [duration],bs.backup_finish_date, has_backup_checksums , [user_name], recovery_model
              FROM msdb.dbo.backupset bs
              WHERE bs.backup_set_id IN (SELECT MAX(backup_set_id) FROM msdb.dbo.backupset
                                                      where    type = 'D'
                                                     GROUP BY database_name) 
                AND bs.database_name IN (SELECT name FROM master.dbo.sysdatabases  )
                                ) as last_backup   on = last_backup.database_name
                             where fileid<>2    
                             group by dbid, , d.is_read_only,d.recovery_model_desc, last_backup.database_name, 
last_backup.backup_start_date,last_backup.[backup_size] ,last_backup.compressed_backup_size,, last_backup.duration,
 last_backup.backup_finish_date,is_copy_only,is_damaged,has_backup_checksums , [user_name], last_backup.recovery_model
ORDER BY DATEDIFF(minute,  last_backup.backup_start_date,  last_backup.backup_finish_date) desc

That is all for now, let me know any remarks you may have.

Friday 19 August 2016

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'):

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!

Monday 8 February 2016

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:

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:

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

EXEC [sys].[sp_cdc_help_jobs] 

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

Monday 18 October 2010

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!
HELLO, I'M PERCY REYES! — a book lover, healthy lifestyle lover... I've been working as a senior SQL Server Database Administrator (DBA) for over 20 years; I'm a three-time awarded Microsoft Data Platform MVP. I'm currently doing a PhD in Computer Science (cryptography) at Loughborough University, England — working on cryptographic Boolean functions, algorithmic cryptanalysis, number theory, and other algebraic aspects of cryptography. READ MORE