Restore issues

 Media Errors during Backup and Restore Operation 

A backup or restore may fail on encountering media errors, like 3241, 3242, or 3243. These errors usually occur when restoring a corrupted backup file. The backup might get corrupt due to a faulty hardware or a malware attack. 

To resolve media errors, do the following: 

• Check Windows Event Viewer logs for any reported (hardware) errors. If any errors are found, take an appropriate action to get them fixed. For instance, upgrade firmware, fix network connectivity issue, etc. 

• Verify that backups are valid and not corrupted by enabling the Backup CHECKSUM option. For more information, read this: Possible Media Errors During Backup and Restore (SQL Server). 

Note: If you want the backup and restore operation to continue after errors, ensure that the ‘CONTINUE ON ERROR’ option is enabled. 

 

Database Restoration Failed Due to Incompatible SQL Server Version? 

Restoring data from a backup created using a recent SQL Server version to an earlier version of the server may fail with 3169 error. The complete error message is as follows: 

“Error 3169: The database was backed up on a server running version %ls. That version is incompatible with this server, which is running version %ls. Either restore the database on a server that supports the backup or use a backup that is compatible with this server.” 

To resolve this issue, use bulk copy program utility (bcp) to copy the database to an earlier version of SQL Server and import the data into a recent version. The detailed steps are as follows: 

Note: For these steps, we will be using two SQL Server instances. Let’s assume instances named as Instance 1 and Instance 2. Also, ensure to download the latest version of SQL Server Management Studio (SSMS) on both the server instances. 

Step 1 – Copy Data from SQL Server Instance 1 

Step 2 – Import the Copied Data into SQL Server Instance 2 

 

Backup Failed Due to Incorrect Permissions? 

Missing Read and Write permissions to the database folder where the backups are stored may cause the backup to fail. Also, you may receive any of the errors on running backup operations: 

• Backup failed for SQL Server. Operating system error 5 (Access is denied). 

• An error message is generated and logged in the backup history showing a failed backup job. 

To avoid these errors, make sure that the SQL Server service account on the folder where the backups are stored has the necessary privileges. For further details, see Permissions for backup. 

 

Some Other Factors to Consider 

• Check cumulative updates for SQL Server to see if any backup or restore related issues are fixed. 

• Ensure that the disk has enough space to accommodate database backups. 

• Restore may fail if you have set up a db for query notification subscriptions. To work around this issue, set trace flag 9109 before performing a restore operation. 

Still can’t Restore your SQL Database? 

If SQL database restore fails, you may switch to a Disaster Recovery (DR) solution for database recovery and business continuity. However, situations may arise when DR procedure won’t be tested, causing the restore process to fail. In such a case, running DBCC CHECKDB command with repair options, like ‘REPAIR_REBUILD’ or ‘REPAIR_ALLOW_DATA_LOSS’ may help fix database corruption. But, it may cause data loss and integrity issues. In that case, using a comprehensive utility, such as Stellar Repair for MS SQL can help. Designed with parallel processing technique, the tool helps repair and save multiple tables simultaneously – ensuring faster recovery of database components. It also helps extract data from corrupt backup (BAK) file in just a few clicks. You can even use the software to reset lost or forgotten SQL Server master database file password.  

 

When restoring the SQL Server database from backup, you may encounter the “Restore of database failed, Database is in use” error message. This happens if someone is running queries or if you have an active connection or active windows running on the database to be restored. 

Below, we will mention different methods to solve this error. 

• Method 1: Close the Connections 

• Method 2: Set the Database to Single-User Mode 

• Method 3: Bring the Database Offline 

• Method 4: Restart the SQL Service 

 

Queries for latches

 To look at the system in real-time run the following query on a system to join the sys.dm_os_wait_stats, sys.dm_exec_sessions and sys.dm_exec_requests DMVs. The results can be used to determine the current wait type for sessions executing on the server. 

SELECT wt.session_id, wt.wait_type 

, er.last_wait_type AS last_wait_type 

, wt.wait_duration_ms 

, wt.blocking_session_id, wt.blocking_exec_context_id, resource_description 

FROM sys.dm_os_waiting_tasks wt 

JOIN sys.dm_exec_sessions es ON wt.session_id = es.session_id 

JOIN sys.dm_exec_requests er ON wt.session_id = er.session_id 

WHERE es.is_user_process = 1 

AND wt.wait_type <> 'SLEEP_TASK' 

ORDER BY wt.wait_duration_ms desc 

 

The following query will return information for all non-buffer latches: 

select * from sys.dm_os_latch_stats where latch_class <> 'BUFFER' order by wait_time_ms desc; 

 

 

The following script uses the resource_description column to isolate which index was causing the PAGELATCH_EX contention: 

 

SELECT wt.session_id, wt.wait_type, wt.wait_duration_ms            

, s.name AS schema_name            

, o.name AS object_name            

, i.name AS index_name            

FROM sys.dm_os_buffer_descriptors bd  

JOIN (            

  SELECT * 

    --resource_description           

  , CHARINDEX(':', resource_description) AS file_index             

  , CHARINDEX(':', resource_description, CHARINDEX(':', resource_description)+1) AS page_index   

  , resource_description AS rd            

  FROM sys.dm_os_waiting_tasks wt            

  WHERE wait_type LIKE 'PAGELATCH%'                       

  ) AS wt            

    ON bd.database_id = SUBSTRING(wt.rd, 0, wt.file_index)            

    AND bd.file_id = SUBSTRING(wt.rd, wt.file_index+1, 1) --wt.page_index)            

    AND bd.page_id = SUBSTRING(wt.rd, wt.page_index+1, LEN(wt.rd)) 

JOIN sys.allocation_units au ON bd.allocation_unit_id = au.allocation_unit_id 

JOIN sys.partitions p ON au.container_id = p.partition_id 

JOIN sys.indexes i ON  p.index_id = i.index_id AND p.object_id = i.object_id 

JOIN sys.objects o ON i.object_id = o.object_id  

JOIN sys.schemas s ON o.schema_id = s.schema_id 

order by wt.wait_duration_ms desc; 

 

 

The following sample script will query sys.dm_os_waiting_tasks and return latch waits ordered by wait duration: 

-- WAITING TASKS ordered by wait_duration_ms 

SELECT wt.session_id, wt.wait_type 

, er.last_wait_type AS last_wait_type 

, wt.wait_duration_ms 

, wt.blocking_session_id, wt.blocking_exec_context_id, resource_description 

FROM sys.dm_os_waiting_tasks wt 

JOIN sys.dm_exec_sessions es ON wt.session_id = es.session_id 

JOIN sys.dm_exec_requests er ON wt.session_id = er.session_id 

WHERE es.is_user_process = 1 

AND wt.wait_type <> 'SLEEP_TASK' 

ORDER BY wt.wait_duration_ms desc; 

 

 

The following script queries buffer descriptors to determine which objects are associated with the longest latch wait times. 

 

IF EXISTS (SELECT * FROM tempdb.sys.objects WHERE [name] like '#WaitResources%') DROP TABLE #WaitResources; 

CREATE TABLE #WaitResources (session_id INT, wait_type NVARCHAR(1000), wait_duration_ms INT, 

                             resource_description sysname NULL, db_name NVARCHAR(1000), schema_name NVARCHAR(1000), 

                             object_name NVARCHAR(1000), index_name NVARCHAR(1000)); 

GO 

declare @WaitDelay varchar(16), @Counter INT, @MaxCount INT, @Counter2 INT 

SELECT @Counter = 0, @MaxCount = 600, @WaitDelay = '00:00:00.100'-- 600x.1=60 seconds 

 

SET NOCOUNT ON; 

WHILE @Counter < @MaxCount 

BEGIN 

   INSERT INTO #WaitResources(session_id, wait_type, wait_duration_ms, resource_description)--, db_name, schema_name, object_name, index_name) 

   SELECT   wt.session_id, 

            wt.wait_type, 

            wt.wait_duration_ms, 

            wt.resource_description 

      FROM sys.dm_os_waiting_tasks wt 

      WHERE wt.wait_type LIKE 'PAGELATCH%' AND wt.session_id <> @@SPID 

--select * from sys.dm_os_buffer_descriptors 

   SET @Counter = @Counter + 1; 

   WAITFOR DELAY @WaitDelay; 

END; 

 

--select * from #WaitResources; 

 

   update #WaitResources  

      set db_name = DB_NAME(bd.database_id), 

         schema_name = s.name, 

         object_name = o.name, 

         index_name = i.name 

            FROM #WaitResources wt 

      JOIN sys.dm_os_buffer_descriptors bd 

         ON bd.database_id = SUBSTRING(wt.resource_description, 0, CHARINDEX(':', wt.resource_description)) 

            AND bd.file_id = SUBSTRING(wt.resource_description, CHARINDEX(':', wt.resource_description) + 1, CHARINDEX(':', wt.resource_description, CHARINDEX(':', wt.resource_description) +1 ) - CHARINDEX(':', wt.resource_description) - 1) 

            AND bd.page_id = SUBSTRING(wt.resource_description, CHARINDEX(':', wt.resource_description, CHARINDEX(':', wt.resource_description) +1 ) + 1, LEN(wt.resource_description) + 1) 

            --AND wt.file_index > 0 AND wt.page_index > 0 

      JOIN sys.allocation_units au ON bd.allocation_unit_id = AU.allocation_unit_id 

      JOIN sys.partitions p ON au.container_id = p.partition_id 

      JOIN sys.indexes i ON p.index_id = i.index_id AND p.object_id = i.object_id 

      JOIN sys.objects o ON i.object_id = o.object_id 

      JOIN sys.schemas s ON o.schema_id = s.schema_id; 

 

select * from #WaitResources order by wait_duration_ms desc; 

GO 

 

• Measure average page latch wait time with the Performance Monitor counter MSSQL%InstanceName%\Wait Statistics\Page Latch Waits\Average Wait Time or by running the sys.dm_os_wait_stats DMV.