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)

Database – Taking Backup of MySQL and MongoDB on Cloud

Here is the story of my latest weekend experiments. Those who know me are familiar that I try to do something new every weekend and post my experience over here. Earlier this week, I published my course on MySQL Backup and Recovery. After doing this course, I realize that everything I had covered in the course was related to native MySQL backup, but there should be some solution outside which caters to the larger need of MySQL Backup. Additionally, I found that taking backup with MySQL is hit and miss as well as there is no systemic reporting there. I decided to do some research on my own on the internet. I tried out various tools, softwares and quite a few third party plug-ins.

Out of all my experiments, there is one product indeed that got my attention. That is BitCan. It is indeed an excellent product. Here are few things I found out about the product:

  • User friendly interface and no need to write scripts or cron jobs
  • There is no need to install any plugin
  • Cloud based solution
  • Storage is very elastic and grows/shrinks with the needs
  • SSH secure communication layer integrated
  • Backup Monitoring Dashboard

I decided to try out the solution on my own and I find it very effective and efficient. I decided to quickly sign up for a FREE account and do the following very quickly.

  • Connect my public database with the BitCan
  • Schedule backup at interval
  • Create an immediate backup
  • Download the backup to install on the local machine
  • Monitor backup health in a single dashboard

Well, I already have a MySQL database which is available on the internet and decided to take backup of the same database. Here are a few images from my experiment.

Let us start how we can take backup with the help of BitCan.

First register yourself for FREE at GoBitCan.com

Step 1: Create Account

Once you login you will see the following screen. It will allow us for 30 day trial. Now click on CREATE BACKUP link.


On the same screen there is a video as well. Please refer the video for additional information. I watched this five minutes video and learned how to get started quickly.

Step 2: New Backup

Over here you will select your backup name and opt for what kind of backup you will go for. You can take backup of file, directory, MongoDB as well as MySQL. I wanted to backup MySQL so I will selected MySQL and click on Save Backup.


Step 3: Configuration

Over here provide all the necessary details for your backup. Remember, I am selecting the public database as MySQL is accessible via internet.


You can also see that I have selected backup time for Sunday, Tuesday, Thursday and Saturday for 12:00 AM. Click on Save and continue to the next step.

Step 4: All Done!

That’s it. We are done. The backup will automatically happen at our scheduled time. If we want we can execute the backup manually and can see the various information related to the same.

 
Dashboard

The dashboard is a very interesting element of BitCan. In my life I have never seen a backup dashboard which is so comprehensive and detailed. I loved it. May be in future blog post we will discuss this in details.

Here is the another view of the dashboard.

Well, that’s it. In future blog posts we will discuss various other aspects of the BitCan.

If you care about your database backup, I suggest you register for a FREE account of BitCan today.

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

MySQL – Video Course – MySQL Backup and Recovery Fundamentals

Data is the one of the most crucial things for any organization and keeping data safe is the biggest challenge for any DBA. This is true for any organizations. Think about the scenario that you have a database which is extremely important and suddenly you accidently delete the most important table from that database. I am sure this is a very difficult time. In times like this people often get stressed or just make even second mistake. In my career of 10 years I have done often this mistake and often got stressed out due to un-availability of the database backup.

In the SQL Server field, we have plenty of the help on this subject, but in MySQL domain there is not enough help. For the same reason I have build this MySQL course on Backup and Recovery.

Course Outline

Data is very important to any application and business. It is very important that every business plan for data safety. Database backup strategies are often discussed after the disaster has already happened. In this introductory course we will explore a few of the basic backup strategies every business should implement for data safely. We will explore how we can recover our server quickly after any unfriendly incident to our MySQL database. Click to View Course

Here are various important aspects which we have discussed in this course.

  • How to take backup of single database?
  • How to take backup of multiple database?
  • How to backup various database objects?
  • How to restore a single database?
  • How to restore multiple databases?
  • How to use MySQL Workbench for Backup and Restore?
  • How to restore Point in Time for any database?
  • What is the best time to backup?
  • How to copy database from one server to another server?

All of the above concepts and many more subjects are covered in the MySQL Backup and Recovery Fundamentals course. It is available on Pluralsight.

Scenarios

As learning about Backup and Recovery can be very much boring, I decided to create two fictitious characters and demonstrate the entire course based on their conversation. The story is about Mike and Rahul. Mike is Sr. Database administrator in USA and Rahul is an intern in India. Rahul aspires to become a senior database administrator and this is a story about his challenges and how he overcomes those challenges. I had a great time to build this course and I have got very good feedback on this course. I encourage all of you to attempt to learn MySQL Backup and Recovery Fundamental course with this innovative effort. It will be very valuable to know your feedback.

You will need a valid Pluralsight subscription to watch this course.

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