April 2023
M	T	W	T	F	S	S
	1	2
3	4	5	6	7	8	9
10	11	12	13	14	15	16
17	18	19	20	21	22	23
24	25	26	27	28	29	30

Posts Tagged ‘backup’

Solution – SQL Server Backup Failing with EXCEPTION_ACCESS_VIOLATION

Posted by blakhani on March 25, 2015

Recently someone posted on Facebook group about a problem. He informed that whenever the backups are taken in SQL Server 2014 instance, it’s failing with error as below.

Msg 3013, Level 16, State 1, Line 4
BACKUP DATABASE is terminating abnormally.

When we open ERRORLOG we saw below

***Stack Dump being sent to D:\MSSQL\LOG\SQLDump0089.txt
SqlDumpExceptionHandler: Process 1144 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
* *******************************************************************************
*
* BEGIN STACK DUMP:
*   01/01/15 18:05:25 spid 1144
* Private server build.
*
*
*   Exception Address = 00007FF85B652D45 Module(sqlmin+00000000009D2D45)
*   Exception Code    = c0000005 EXCEPTION_ACCESS_VIOLATION
*   Access Violation occurred reading address 00007FFA0C208980
* Input Buffer 116 bytes –
*             backup database test to disk=’c:\temp\test.bak’

As we can see that Access Violation is occurring while running backup database command. Whenever there is a dump generated, there is a MDMP file is also generated. Here is the stack when we analyze the dump. Yon can refer http://mssqlwiki.com/2012/10/16/sql-server-exception_access_violation-and-sql-server-assertion link to know more about identifying stack.

sqlmin!PerfmonManager::AddInstance+0x4e0
sqlmin!BackupPerfmonCounter::AddInstance+0x1f
sqlmin!BackupDevicePerfmonCounter::Init+0x13c
sqlmin!BackupFileDesc::InitPerfCounters+0xc <<Performance Counters Initialization.
sqlmin!BackupMediaIo::Initialize+0x29
sqlmin!BackupMedium::CreateDeviceObject+0x224
sqlmin!BackupMedium::Open+0x27
sqlmin!BackupStream::OpenForBackup+0x27
sqlmin!BackupStream::ThreadMainRoutine+0x160
sqlmin!BackupThread::ThreadBase+0x51
sqlmin!SubprocEntrypoint+0xa7f << This is the child thread of main backup thread.
sqldk!SOS_Task::Param::Execute+0x21e
sqldk!SOS_Scheduler::RunTask+0xa8
sqldk!SOS_Scheduler::ProcessTasks+0x279
sqldk!SchedulerManager::WorkerEntryPoint+0x24c
sqldk!SystemThread::RunWorker+0x8f
sqldk!SystemThreadDispatcher::ProcessWorker+0x3ab
sqldk!SchedulerManager::ThreadEntryPoint+0x226
kernel32!BaseThreadInitThunk+0xd
ntdll!RtlUserThreadStart+0x1d

Here is the version of SQL Server.

Microsoft SQL Server 2014 – 12.0.2000.8 (X64)
Feb 20 2014 20:04:26
Copyright (c) Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

This is identified as an issue with SQL Server 2014 RTM version. Here is the KB article – http://support.microsoft.com/en-us/kb/2973444 (FIX: "SQL Server performance counters are disabled" when you move the SQL Server resource in SQL Server 2014)

Fix: Cumulative Update 2 for SQL Server 2014 or any later version of SQL Server 2014.

If you are running higher version and above fix then you need to find out why performance monitor is not showing SQL Server related counters. The way to verify it would be to use DMV

select * from sys.dm_os_performance_counters

You should see many counters specifically look for “Backup/Restore Throughput/sec”. In SQL 2014 this is a new performance counter added to get backup/restore speed and that missing counter (Backup/Restore Throughput/sec) which is causing backup to fail.

Cheers,

Balmukund Lakhani

Twitter @blakhani

Author: SQL Server 2012 AlwaysOn – Paperback, Kindle

Posted in EXCEPTION_ACCESS_VIOLATION, SQL Server, SQL Server 2014 | Tagged: backup, EXCEPTION_ACCESS_VIOLATION, SQL Server 2014 | 13 Comments »

SQL 2014 Learning Series # 3 – New Feature – Backup Encryption

Posted by blakhani on April 3, 2014

Before we jump into the new feature, lets have a look at behavior in previous version. Same simulation can be done on SQL Server 2014 as well.

Create Database
Create table in the database and insert some rows.
Take backup of the database and check if we are able to see those rows in the backup.

Here are the commands which we can run to achieve this.

Create database [SQLServerHelp]
go
use [SQLServerHelp]
go
Create Table MyData (i int, j char(100));
go
insert into MyData values (1, 'See this data')
go
backup database SQLServerHelp to Disk = 'E:\Tools\SQLServerHelp.bak'
go

I have opened the backup file with one of the text editor and I can search for the row which I inserted. Note that opening big file in text editors may take a lot of time and file may become unusable for SQL Server. PLEASE DON’T TRY THIS ON PRODCUTION DATABASE BACKUP. I have highlighted the row which was inserted via above script. (1, ‘See this data’)

The way to avoid above situation is that encrypt the database use TDE or encrypt the column using column encryption. Please read below

http://technet.microsoft.com/en-us/library/cc278098(v=SQL.100).aspx (Database Encryption in SQL Server 2008 Enterprise Edition)

There are some overhead involved with above techniques and you may want to make sure that only backup is encrypted and you are OK if MDF file has data in readable format. To solve this, SQL Server 2014 has new feature called backup encryption. You can read more on books online

Let’s look at steps involved.

Create database master key for the master database.

USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'SQLServer-Help@123';
GO

Create a Backup Certificate

Use Master
GO
CREATE CERTIFICATE BackupEncryptCert
   WITH SUBJECT = 'SQL Backup Encryption Certificate';
GO

Take a backup: This can be done by SSMS UI or T-SQL

T-SQL

BACKUP DATABASE [SQLServerHelp] TO  DISK = N'E:\Tools\Encrypted_SQLServerHelp.bak' WITH NOFORMAT, NOINIT,  
NAME = N'SQLServerHelp-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10, ENCRYPTION(ALGORITHM = AES_128, SERVER CERTIFICATE = [BackupEncryptCert])
GO

Here is the message which we can see as output in SSMS when we run T-SQL.

Here is the complete text of highlighted warning.

“Warning: The certificate used for encrypting the database encryption key has not been backed up. You should immediately back up the certificate and the private key associated with the certificate. If the certificate ever becomes unavailable or if you must restore or attach the database on another server, you must have backups of both the certificate and the private key or you will not be able to open the database.”

It’s clear that to restore this backup, we need to backup certificate. Going back to original problem, I tried searching for the text in new backup file but can’t find it. If you look at screenshot, it’s clear that it’s more unreadable.

As Vinod (b|t) mentioned in his blog, we can see complete message in ERRORLOG (this is also an enhancement in SQL Server 2014)

2014-04-03 06:36:42.780 Backup       Database backed up. Database: SQLServerHelp, creation date(time): 2014/04/01(10:15:50), pages dumped: 307, first LSN: 34:24:37, last LSN: 34:56:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {‘E:\Tools\SQLServerHelp.bak’}). This is a

2014-04-03 06:36:42.810 Backup       BACKUP DATABASE successfully processed 298 pages in 0.212 seconds (10.981 MB/sec).

2014-04-03 07:25:49.220 Backup       Database backed up. Database: SQLServerHelp, creation date(time): 2014/04/01(10:15:50), pages dumped: 307, first LSN: 34:168:37, last LSN: 34:200:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {‘E:\Tools\Encrypted_SQLServerHelp.bak’}

2014-04-03 07:25:49.240 Backup       BACKUP DATABASE successfully processed 298 pages in 0.228 seconds (10.211 MB/sec).

It’s clear that second one took little more time. I am guessing this is because of encryption as all other factors are same.

Hope you have learned something new today!

Edit (9 April 2014) – Vinod (b|t) wrote a blog on restoring such backups. http://blogs.extremeexperts.com/2014/04/09/sql-server-2014-restoring-encrypted-backups/

Cheers,

Balmukund Lakhani

Twitter @blakhani

Author: SQL Server 2012 AlwaysOn – Paperback, Kindle

Posted in SQL 2014 Learning Series, SQL Server 2014 | Tagged: backup, encryption, security, SQL 2014 Learning Series, SQL Server 2014, what's new | 1 Comment »

Troubleshooting : Msg 3201 – Cannot open backup device .. Operating system error 3(The system cannot find the path specified.)

Posted by blakhani on March 13, 2014

Data recovery is a time consuming and expensive process. Taking regular backups can save a lot of time as well as make sure that the data can be restored in the case of disaster. There could be many situation where backup of the database taken in SQL Server might fail with 3201 error. The text of error id as below (you can use sys.messages catalog view to get it)

Cannot open backup device ‘%ls’. Operating system error %ls.

Same message is applicable for restore as well, it’s all about unable to open device. If we notice %ls is placeholder which would be inserted when message is raised. Some sample errors are as below.

Message # 1

Msg 3201, Level 16, State 1, Line 1
Cannot open backup device ‘E:\Backup\SQLServerHelp.bak’. Operating system error 3(The system cannot find the path specified.).

Cause: Backup folder not created on E Drive.

Message # 2

Msg 3201, Level 16, State 1, Line 1
Cannot open backup device ‘C:\SQLServerHelp.bak’. Operating system error 5(Access is denied.).

Cause: SQL Server Service account is not having permission on root of C Drive.

Message # 3

Msg 3201, Level 16, State 2, Line 1
Cannot open backup device ‘\\NetworkShare\Backup\SQLServerHelp.BAK’. Operating system error 53(error not found).

This one is interesting because it doesn’t tell the exact message for OS error 53. Sometime this could happen with any other OS error as well. Refer my earlier post here where I showed how to get text for an operating system error number. 53 = The network path was not found.

Cause: Network Path: \\NetworkShare\Backup was incorrect.

OK. Let me stop here.. I didn’t plan to discuss all the possible errors but this blog is to present one interesting scenario which helped me uncovering an operating system concept.

Let’s create a database using below script.

Create Database [SQL Server Help ]

Once database is created I have created maintenance plan to take full backup of the database.

When I executed above maintenance plan, it failed with error:

TITLE: Execute Maintenance Plan

——————————

Execution failed. See the maintenance plan and SQL Server Agent job history logs for details.

——————————

ADDITIONAL INFORMATION:

Job ‘SQLServerHelp.Subplan_1’ failed. (SqlManagerUI)

——————————

BUTTONS:

OK

——————————

If we look at the folder, the folder got created but backup failed. Looked into ERRORLOG and found below

2014-03-13 06:20:31.66 spid65 Error: 18204, Severity: 16, State: 1.

2014-03-13 06:20:31.66 spid65 BackupDiskFile::CreateMedia: Backup device ‘E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\SQL Server Help \SQL Server Help _backup_2014_03_13_062031_6415729.bak’ failed to create. Operating system error 3(The system cannot find the path specified.).

2014-03-13 06:20:31.66 Backup Error: 3041, Severity: 16, State: 1.

2014-03-13 06:20:31.66 Backup BACKUP failed to complete the command BACKUP DATABASE SQL Server Help . Check the backup application log for detailed messages.

when I looked at folder structure, I found that there is a folder “SQL Server Help” got created under “E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup” but still backup was failing. I ran profiler to see the command being fired.

BACKUP DATABASE [SQL Server Help ] 
TO  DISK = N'E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\SQL Server Help \SQL Server Help _backup_2014_03_13_062920_6305771.bak' 
WITH NOFORMAT, NOINIT,  NAME = N'SQL Server Help _backup_2014_03_13_062920_6305771', 
SKIP, REWIND, NOUNLOAD,  STATS = 10

When I executed the command, it failed with below error.

Msg 3201, Level 16, State 1, Line 1

Cannot open backup device ‘E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\SQL Server Help \SQL Server Help _backup_2014_03_13_062920_6305771.bak’. Operating system error 3(The system cannot find the path specified.).

Msg 3013, Level 16, State 1, Line 1

BACKUP DATABASE is terminating abnormally.

Again, something wrong with the path where I am taking backup. Do you see any problem in above message. Look closer.. there is a space at the end in the folder name where backup is attempted. “SQL Server Help “. If you try to create a folder with the whitespace at the end, operating system would trim the space at the end. This is documented here

The root cause of my problem was name of the database where I have added an space at the end intentionally. Go back and have a look at create database statement which I have used. I also participated in this discussion where same problem was posted long back.

Hope you have learned something new!

Cheers,

Balmukund Lakhani

Twitter @blakhani

Author: SQL Server 2012 AlwaysOn – Paperback, Kindle

Posted in Error, SQL Server | Tagged: backup, cannot open backup device, Error: 3201, failure, Level 16, Line 1", maintenance plan, Msg 3201, SQL, State 1 | 8 Comments »

Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

Blog Stats

Blogroll

Select GETDATE()

Subscribe

Follow Blog via Email

Date < getdate()

Select location from world

Meta

Posts Tagged ‘backup’

Solution – SQL Server Backup Failing with EXCEPTION_ACCESS_VIOLATION

SQL 2014 Learning Series # 3 – New Feature – Backup Encryption

Troubleshooting : Msg 3201 – Cannot open backup device .. Operating system error 3(The system cannot find the path specified.)

Message # 1

Message # 2

Message # 3