SQL SERVER – FIX: ERROR : Msg 3023, Level 16, State 2 – Backup, file manipulation operations (such as ALTER DATABASE ADD FILE) and encryption changes on a database must be serialized

Errors are the best way to learn how SQL Server works and as DBA’s we are bound to see many of them from time to time. One of the primary functions of a DBA would include creating backups and most importantly trying to automate the same using jobs and maintenance plans.

Here is a typical scenario which a DBAs can encounter. One fine day they notice that some backup jobs are failing for no reason. Normal troubleshooting always starts with an error message. Recently, one of my blog readers sent an email to me which was worth a look.

I am getting below error. What is the cause and solution?

Msg 3023, Level 16, State 2, Line 1
Backup, file manipulation operations (such as ALTER DATABASE ADD FILE) and encryption changes on a database must be serialized. 
Reissue the statement after the current backup or file manipulation operation is completed.

I pinged him on twitter and asked more details. He informed that they have a job which runs and fails with the error described above. I asked him to get more details about the job and post back. I also asked him to check details from my good friend Balmukund’s blog – query to find what is running at the same time when job runs. He didn’t come back to me – that means his issue might be resolved.

But that left me curious to find the possible causes of the error Msg 3023, Level 16, State 2. Reading the message again, it looks like two parallel backups would cause error. So I ran two parallel backup command for a database which was little big in size (100GB). As soon as two full backups started, I could see that only one backup was making progress (session id 57) and another (session id 58) was waiting for first one to finish.

Which means the error is not raised and backup is waiting. But as soon as I cancelled the query (session 58), I got below message.

Another possible reason of the error is that if we perform shrink operation in parallel to backup operation. (Shrink is NOT something which I recommend, but people would never listen)

Here is the text

Msg 3140, Level 16, State 5, Line 1
Could not adjust the space allocation for file 'SQLAuthority'.
Msg 3023, Level 16, State 2, Line 1
Backup, file manipulation operations (such as ALTER DATABASE ADD FILE) and encryption changes on a database must be serialized. 
Reissue the statement after the current backup or file manipulation operation is completed.

Depending on who came first, here is the behavior. If a backup is started when either add or remove file operation is in progress, the backup will wait for a timeout period, then fail. If a backup is running and one of these operations is attempted, the operation fails immediately.

Solution: Find out the conflicting operation and retry your operation after stopping or finishing conflicting operation.

Learning using error messages is a great way to understand what happens inside SQL Server. Do let me know in the recent past, what have you learnt from error messages in your environments.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Unable to Restore From Backup After Detach Operation

While doing community work I travel a lot, speak at various conferences and get a chance to meet many new faces. The best part is that I get a chance to hear a variety of issues which people face while using SQL Server. This blog post is an outcome of one such interaction with a DBA from one of the organizations I had to meet.

After the conference a young guy came to me and said – “I found a bug in SQL Server Restore”. I was amazed with his confidence and asked him to tell more before concluding. He said that to restore a database from backup, you need to have same database created before restore. I told that there is something which is not right with the test which he is performing because that doesn’t sound correct. I gave him my email address and asked to contact me to find more. I was eagerly waiting for his mail as this was on top of my mind and I was restless for two days. Finally the mail landed-

He sent an email repro steps.

  1. Create new database.
  2. Take a backup of the database.
  3. Detach the database.
  4. Restore from backup taken in step 2. This step would fail.

I followed the same steps

CREATE DATABASE SQLAuth
GO
BACKUP DATABASE SQLAuth TO DISK = 'C:\Temp\SQLAuth.bak' WITH FORMAT
GO
sp_detach_db 'SQLAuth'
GO
RESTORE DATABASE SQLAuth FROM DISK = 'C:\Temp\SQLAuth.bak'
GO

As soon as I run the last command of restore, I get below error

Msg 3142, Level 16, State 1, Line 7
File "SQLAuth" cannot be restored over the existing "E:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\SQLAuth.mdf". 
Reissue the RESTORE statement using WITH REPLACE to overwrite pre-existing files, or WITH MOVE to identify an alternate location.
Msg 3142, Level 16, State 1, Line 7
File "SQLAuth_log" cannot be restored over the existing "E:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\SQLAuth_log.ldf". 
Reissue the RESTORE statement using WITH REPLACE to overwrite pre-existing files, or WITH MOVE to identify an alternate location.
Msg 3119, Level 16, State 1, Line 7
Problems were identified while planning for the RESTORE statement. Previous messages provide details.

Msg 3013, Level 16, State 1, Line 7
RESTORE DATABASE is terminating abnormally.

Error Message is very clear about the cause of restore failure. Since we detached the database, the mdf and ldf files are still available at the location when the database was created. It’s good that SQL is not over writing the files by itself unless we specify explicitly.

If you want to over write the files then we can use the “WITH REPLACE” clause to the command as shown below.

RESTORE DATABASE SQLAuth FROM DISK = 'C:\Temp\SQLAuth.bak' WITH REPLACE

If we don’t detach the database and perform restore on top of existing database like below

CREATE DATABASE SQLAuth
GO
BACKUP DATABASE SQLAuth TO DISK = 'C:\Temp\SQLAuth.bak' WITH FORMAT
GO
RESTORE DATABASE SQLAuth FROM DISK = 'C:\Temp\SQLAuth.bak'
GO

Then we will get a slightly different message as shown below:

Msg 3159, Level 16, State 1, Line 5
The tail of the log for the database "SQLAuth" has not been backed up. Use BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE statement to just overwrite the contents of the log.
Msg 3013, Level 16, State 1, Line 5
RESTORE DATABASE is terminating abnormally.

Again, this is a safety mechanism where a user has to confirm their actions. Recall the situation when you have an existing file in windows and you paste same file at same location – you always get warning. SQL Server is no different and I was pleasantly relived with the fact that this was not a bug inside SQL Server. I am glad the DBA did send me this information because it made me revalidate and play around with backups with SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Why my Database is in Restoring Mode After Taking Backup?

Once upon a time there was a SharePoint consultant named Pepper. She wanted to learn about SQL Server Administration/DBA work. While she was a master SharePoint consultant at her office in Bethel she grew curious to learn SQL Server. She wanted to be independent when dealing with SQL server chores like creating maintaining database, taking backups, creating jobs and things alike. Being busy as she is at her current task on working with SharePoint she would sometimes squeeze in some time to poke around on SQL server and learn about it. Our coffee discussions would turn out to be SQL discussions and how SharePoint uses SQL as its backend.

Last week, I got a call from Pepper at noon timeframe Now that does not happen and I was sure there was something fishy…

Pepper: Tony, is it normal for database to get in RESTORING state after backup?
I said in my usual tone of ‘romcasim

Me: depends on what state you wanted it to be in

Pepper: I am in no mood to play ‘take-my-hint’ I need to get back to testing something please tell me what did I do wrong.

Me: OK OK.. hummmm tell me what all you did so I can help

Pepper: Let me ping you.

Here is what I learnt from her chat messages. Pepper wanted to test a few things which involved her taking database backup and transaction log backup. So she scrambled on the net and got the syntax quick without bothering to read much.  She did her test runs or code and ran the backup.

BACKUP DATABASE [BKP] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\full_backup.bak'
WITH NOFORMAT, NOINIT, NAME = N'BKP-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

Then again some testing from SharePoint and now Ran below to take Tlog backup

BACKUP LOG [BKP] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\tlog_backup.trn'
WITH NO_TRUNCATE, NOFORMAT, NOINIT, NAME = N'BKP-TLog Backup', SKIP, NOREWIND, NOUNLOAD, NORECOVERY,   STATS = 10
GO

Now this is where I figured the mistake the “NORECOVERY” clause. As per MSDN documentation

NORECOVERY

Backs up the tail of the log and leaves the database in the RESTORING state. NORECOVERY is useful when failing over to a secondary database or when saving the tail of the log before a RESTORE operation.

To perform a best-effort log backup that skips log truncation and then take the database into the RESTORING state atomically, use the NO_TRUNCATE and NORECOVERY options together.

After having explained what that clause does, the obvious question pops out.

Pepper: Tony, all that is nice to know. How do I get out of the RESTORING state?

I gave her the following command, which she happily executed and voila DB was back online.

RESTORE DATABASE BKP WITH RECOVERY

Pepper: I owe you a coffee. Starbucks @ 5?

I was thinking in my head – One more midday coffee is going to be over the Tail Log backup discussion. As expected, she asked questions and I decided to share them here.

Question #1: Can this happen from user interface also?

Answer #1: Yes. Here is the option in SQL Server Management Studio (SSMS)

Question #2: But why would someone do that? Why would someone take production database into restoring state?

Answer #2: These kind of log backups are called as tail log backups. Imagine a situation where DBA has configured log shipping. As a part of DR drill, application team wants to move production workload to secondary server. One DR drill is complete, the old primary should again take primary role. If we don’t want to reinitialize the log-shipping via full backup then here are the steps.

  1. Disable all log shipping jobs (on primary and secondary)
  2. Restore all pending transaction logs which are not yet applied on secondary with norecovery.
  3. Take tail log backup with norecovery. This would leave primary database in restoring state.
  4. Restore this tail log backups on secondary database using “with recovery” clause.
  5. This would bring secondary open for read/write activities and testing.
  6. Once testing completes, take a tail log back-up from current primary (secondary initially)
  7. Restore that backup with recovery on current secondary (primary initially)
  8. Enabled all log shipping jobs which were disabled in first step.

That was an eye-opener for Pepper and at least, she paid for my coffee.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Database Stuck in Restoring State

Here is a one of the very common question I keep on getting via email.

“I just restored all the backup files of my database, however, my database is still stuck in restoring state. How do I fix it?”

Well, if you have already restored all the database, you can execute the following code and it will bring your database from recovery to operational state.

RESTORE DATABASE NameofDatabase
WITH RECOVERY

If due to any reason, above query returns error about restoring log files or any other file and you do not have that file in your hand, you can run following command.

Remember above command will roll forward your database and you will be not able to restore any other database after that.

RESTORE DATABASE NameofDatabase
WITH RECOVERY,REPLACE

If you are not sure what to do, leave a comment and I will help you out with your situation.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – A Practical Use of Backup Encryption

 Backup is extremely important for any DBA. Think of any disaster and backup will come to rescue users in adverse situation. Similarly, it is very critical that we keep our backup safe as well. If your backup fall in the hands of bad people, it is quite possible that it will be misused and become serious data integrity issue. Well, in this blog post we will see a practical scenario where we will see how we can use Backup Encryption to improve security of the bakcup.

Feature description

Database Backup Encryption is a brand new and long expected feature that is available now in SQL Server 2014. You can create an encrypted backup file by specifying the encryption algorithm and the encryptor (either a Certificate or Asymmetric Key).

The ability to protect a backup file with the password has been existing for many years. If you use SQL Server for a long time, you might remember the WITH PASSWORD option for the BACKUP command. The option prevented unauthorized access to the backup file.

However this approach did not provide reliable protection. In that regard, Greg Robidoux noted on MSSQLTIPS: “Although this does add a level of security if someone really wants to crack the passwords they will find a way, so look for additional ways to secure your data.

To protect a backup file, SQL Server 2008 introduced the transparent data encryption (TDE) feature. Thus, a database had to be transparently encrypted before backup. Therefore, start with SQL Server 2012 the PASSWORD and MEDIAPASSWORD parameters are not used while creating backups. Even so, data encryption and backup files encryption are two different scenarios.

In case a database is stored locally, there is no need to encrypt it before backup. Fortunately in SQL Server 2014 there are two independent processes. Along with data encryption it is possible to encrypt a backup file based on a certificate or an asynchronous key. Supported encryption algorithms are:

  • AES 128
  • AES 192
  • AES 256
  • Triple DES

Practical use

To illustrate above mentioned, I will create an encrypted backup of the Adventureworks database. Also, you can back up directly to Azure. If needed, you may restore the encrypted back up file on Azure.

I will use dbForge Studio for SQL Server to create the encrypted backup file.

To protect the backup file we need to create an encryptor: either a Certificate or Asymmetric Key. Then, we need to pass this encryptor to the target SQL Server to restore the backup. For this, the encryptor must be exported from the source SQL Server and imported to the target SQL Server. There are no problems with the certificate in this regard. It is more complicated with asymmetric keys.

Taking into account that the BACKUP ASYMMETRIC KEY command is not available, and we can not just create a duplicate for an asymmetric key (compared to symmetric key), the only approach is to create the asymmetric key outside the SQL Server. Then we can use the sn.exe utility to transfer it inside SQL Server (CREATE ASYMMETRIC KEYkeynameFROM FILE = ‘filename.snk‘). After that we can use this asymmetric key to encrypt the backup file on the source instance. Further we need to use the same *.snk file to create the asymmetric key on the target instance (and restore the backup file).

In our example we will not use asymmetric keys. We will use a certificate. Moreover the certificate (behind the scene) is the pair of open/closed keys.

Let’s create the server certificate and use it to encrypt the backup file.

The certificate will be protected with the database master key, because we didn’t specify the ENCRYPTION BY statement.

This is exactly what we need. Only certificates signed with the database master-key can be used for the encryption purposes. Otherwise, If we for instance, protect the certificate with the password ENCRYPTION BY PASSWORD = ‘strongpassword‘, the following error appears while attempting to encrypt the backup file:

“Cannot use certificate ‘CertName’, because its private key is not present or it is not protected by the database master key.”

Encrypted backups (along with usual backups) can be traditionally created locally on the hard drive and in Azure Storage.

Instead of writing tons of SQL code I will use the convenient dbForge Studio for SQL Server Back Up wizard. The wizard allows to create the database backup in several clicks.

Step 1: Setup the DB Connection and the backup file location.

Step2: Setup mediaset

Step 3: Select the encryption algorithm and certificate.

In case you don’t want to pay extra attention to transferring the backup file to the Windows Azure, you can backup directly to Azure.

After the script execution in the required container the blob (with the backup) appears.

In case you had already created a backup with the same name in the same container, you can get the following error: There is currently a lease on the blob and no lease ID was specified in the request.

Further, you can restore the back up file on the Windows Azure.

Summary: 

Obviously, it is a good practice to encrypt a backup file while transferring. This, for instance, allows to avoid data leak while transferring backups from one DPC to another.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – SSMS: Backup and Restore Events Report

A DBA wears multiple hats and in fact does more than what an eye can see. One of the core task of a DBA is to take backups. This looks so trivial that most developers shrug this off as the only activity a DBA might be doing. I have huge respect for DBA’s all around the world because even if they seem cool with all the scripting, automation, maintenance works round the clock to keep the business working almost 365 days 24×7, their worth is knowing that one day when the systems / HDD crashes and you have an important delivery to make. So these backup tasks / maintenance jobs that have been done come handy and are no more trivial as they might seem to be as considered by many. So the important question like: “When was the last backup taken?”, “How much time did the last backup take?”, “What type of backup was taken last?” etc are tricky questions and this report lands answers to the same in a jiffy.

So the SSMS report, we are talking can be used to find backups and restore operation done for the selected database. Whenever we perform any backup or restore operation, the information is stored in the msdb database. This report can utilize that information and provide information about the size, time taken and also the file location for those operations.

Here is how this report can be launched.

 

Once we launch this report, we can see 4 major sections shown as listed below.

  • Average Time Taken For Backup Operations
  • Successful Backup Operations
  • Backup Operation Errors
  • Successful Restore Operations

Let us look at each section next.

Average Time Taken For Backup Operations

Information shown in “Average Time Taken For Backup Operations” section is taken from a backupset table in the msdb database. Here is the query and the expanded version of that particular section

USE msdb;
SELECT (ROW_NUMBER() OVER (ORDER BY t1.TYPE))%2 AS l1
,       1 AS l2
,       1 AS l3
,       t1.TYPE AS [type]
,       (AVG(DATEDIFF(ss,backup_start_date, backup_finish_date)))/60.0 AS AverageBackupDuration
FROM backupset t1
INNER JOIN sys.databases t3 ON ( t1.database_name = t3.name)
WHERE t3.name = N'AdventureWorks2014'
GROUP BY t1.TYPE
ORDER BY
t1.TYPE

On my small database the time taken for differential backup was less than a minute, hence the value of zero is displayed. This is an important piece of backup operation which might help you in planning maintenance windows.

Successful Backup Operations

Here is the expanded version of this section.

 

This information is derived from various backup tracking tables from msdb database.  Here is the simplified version of the query which can be used separately as well.

SELECT *
FROM sys.databases t1
INNER JOIN backupset t3 ON (t3.database_name = t1.name)
LEFT OUTER JOIN backupmediaset t5 ON ( t3.media_set_id = t5.media_set_id)
LEFT OUTER JOIN backupmediafamily t6 ON ( t6.media_set_id = t5.media_set_id)
WHERE (t1.name = N'AdventureWorks2014')
ORDER BY backup_start_date DESC,t3.backup_set_id,t6.physical_device_name;

The report does some calculations to show the data in a more readable format. For example, the backup size is shown in KB, MB or GB. I have expanded first row by clicking on (+) on “Device type” column. That has shown me the path of the physical backup file.

Personally looking at this section, the Backup Size, Device Type and Backup Name are critical and are worth a note. As mentioned in the previous section, this section also has the Duration embedded inside it.

Backup Operation Errors

This section of the report gets data from default trace. You might wonder how. One of the event which is tracked by default trace is “ErrorLog”. This means that whatever message is written to errorlog gets written to default trace file as well. Interestingly, whenever there is a backup failure, an error message is written to ERRORLOG and hence default trace. This section takes advantage of that and shows the information. We can read below message under this section, which confirms above logic.

No backup operations errors occurred for (AdventureWorks2014) database in the recent past or default trace is not enabled.

Successful Restore Operations

This section may not be very useful in production server (do you perform a restore of database?) but might be useful in the development and log shipping secondary environment, where we might be interested to see restore operations for a particular database. Here is the expanded version of the section. To fill this section of the report, I have restored the same backups which were taken to populate earlier sections.

Here is the simplified version of the query used to populate this output.

USE msdb;
SELECT *
FROM restorehistory t1
LEFT OUTER JOIN restorefile t2 ON ( t1.restore_history_id = t2.restore_history_id)
LEFT OUTER JOIN backupset t3 ON ( t1.backup_set_id = t3.backup_set_id)
WHERE t1.destination_database_name = N'AdventureWorks2014'
ORDER BY restore_date DESC,  t1.restore_history_id,t2.destination_phys_name

Have you ever looked at the backup strategy of your key databases? Are they in sync and do we have scope for improvements? Then this is the report to analyze after a week or month of maintenance plans running in your database. Do chime in with what are the strategies you are using in your environments.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Backup to Azure Blob using SQL Server 2014 Management Studio

Recently, while working on a demo about backup, I realized that in management studio, now there is a new option in the backup screen called a URL.  This feature is called as “backup to URL”.

I checked earlier version of SSMS and found that this was not there in the SQL 2012 management studio. There the options were “disk” and “tape” in SSMS 2012.

As soon as the URL is selected, it would ask for various options about backup destination in Azure.

Let’s go step by step and take a database backup to Microsoft Azure Blob storage.

To use this feature, you need to have is a Microsoft Azure account. If you do not have a Windows Azure account, visit Windows Azure 3-Month free trial. After logging it to the portal, I have created a “storage” called sqlauthority.

Under sqlauthority, I have created a container called “backup”.  This container was created as “Public Blob”.

If we closely look at URL it is http://<StorageAccount>.blob.core.windows.net/<ContainerName> Going back to SSMS, this is what we have to provide. But wait, how would SQL Server connect to Azure Blob storage? Well, that’s where the credential comes into the picture. Note that authentication to the storage account is required for SQL Server backup and restore even if we choose to create a public container. There are multiple ways to connect to the storage – publishing profile, certificate or SQL Credential. To use SQL credential, we need to get the secret key from the portal using below the screen. The screen would appear once we click on “Manage Access Key” in the third image of this blog.

Once we have the secret key, we can go to SSMS and right click on “Credential” under “Logins” to choose “New Credential”.

Once this is done, we are all set. Go back to UI and choose “backup-cred” in the drop down. Here is the final screen.

Once the backup is complete, we should see the backup on the portal. Notice URL http://sqlauthority.blob.core.windows.net/backup/Sales_backup_2014_06_04_101547.bak

Once the backup is available, we can also restore it. We can use management studio as well as T-SQL to restore the backup taken on the URL. While choosing the device, we need to use “URL” option, browse to storage container and choose correct file.

Go ahead and play with this feature.Let me know what you think of the same.

Reference: Pinal Dave (http://blog.sqlauthority.com)