SQLAuthority News – Get My Two Books FREE at #SQLPASS

Every year when I visit SQLPASS, I do book signing where I give away my books for free. This year there are two opportunities when I am going to give away my books for free.

Though the books are in great numbers they always run away early. I strongly suggest that you arrive early and secure your copy. I will be extremely delighted to shake your hands and talk SQL.

Book Signing will be at the booth of Embarcadero and here are the timings for the same.

Book: SQL Wait Stats - Thursday, November  6th from 1 PM to 2PM.

Book Secret Tool Box of Successful Bloggers – Friday, November 7th from 11 AM to 12 PM.

Do not miss this opportunity.

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

About these ads

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

sp_detach_db 'SQLAuth'

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.


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


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 – PowerShell way of Restarting SQL Server Service

I learn a lot when I go for conferences all around the world. There are styles, content and cultural differences that I get a chance to learn and enjoy as part of my trips. Most of these trips I make are geared towards at least one learning experience I can get end of the day. With these travels, I also attend and speak at local user groups to get a flavour of what is the neat and coolest way to work with SQL Server. Recently at our local user group (SQL Server Bangalore UG) UG meeting, I was vividly stumped by a question from one of the speakers. Here he was talking about various ways to shut down or start SQL Server.

This is was an interesting quiz and I was pleasantly surprised by the most common methods and wanted to participate in this quiz. The answers were flowing all over and here are some that I would like to share which were part of audience interactions:

  1. Object Explorer in SSMS (SQL server Management Studio)
  2. Net Start Command.
  3. SQL Server Configuration Manager.
  4. Windows Services.
  5. Using the Sqlservr.exe executable

I thought I had known most of these and the usage of sqlservr.exe was something I wanted to check as it had been ages. For complete list of startup parameters refer book online.

Getting back to UG meet, I couldn’t hold my curiosity back and I was quick to raise my hand to give what I thought could be yet another answer. I shouted out loud the “SHUTDOWN” command. This was something I thought would fit the bill too. The speaker was understanding to say, this was one of the partially correct answer. Taken aback, I eagerly waited for the explanation.

Though the answer was correct, it was to shut down the service and didn’t allow us to restart our SQL Server service. And the speaker, waited for more answers. This made me curious of the fact that there was more to what I knew.

Welcome PowerShell

The speaker was quick to say, now that we have exhausted all the options available – let me talk about Windows PowerShell and it can be yet another way to restart SQL Server Service. It completely slipped my mind about this option. PowerShell is a powerful way to script and do activities with SQL Server. If you haven’t played around, then I strongly suggest to have a look into the same.

Now, how can I restart SQL Server Service? In this example below, I have used the PowerShell ISE IDE to execute the command as identified below:

Restart-Service -Force MSSQLSERVER


Since there was no feedback from the console, I was curious to know if the SQL Server service did restart properly. On examining the Task manager I could confirm that the service did restart because the PID values were completely different after the command got executed.

Sometimes, we carry of knowledge that needs a refresher. Hence taking a student approach to everything we do can help a lot in this learning experience.

Do let me know if you found any other way to restart SQL Server and I would be more than happy to learn it from you.

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

SQL Authority News – Presenting Session at #SQLPASS on November 5, 2014

I will be attending SQLPASS 2014 this year in Seattle. I have attended SQLPASS 5 times before and this is my sixth times for the event. I love this event and that is why every single year I attend the event.

This time I will be presenting on session Secrets of SQL Server: Database Worst Practices as well. Here is the details for my session.

Secrets of SQL Server: Database Worst Practices


“Oh my God! What did I do?” Chances are you have heard, or even uttered, this expression. This demo-oriented session will show many examples where database professionals were dumbfounded by their own mistakes, and could even bring back memories of your own early DBA days. The goal of this session is to expose the small details that can be dangerous to the production environment and SQL Server as a whole, as well as talk about worst practices and how to avoid them.

Shedding light on some of these perils and the tricks to avoid them may even save your current job. After attending this session, developers will only need 60 seconds to improve performance of their database server in their SharePoint implementation. We will have a quiz during the session to keep the conversation alive. Developers will walk out with scripts and knowledge that can be applied to their servers, immediately after the session.

Date: November 05, 2014

Time: 10:15 AM – 11:30 AM

Room: 6B

Why should you attend my session?

When I want to attend any session, I try to reason myself about why should I attend any particular session. Well, if you are wondering the same for me, here are my reasons for you.

  • We will have lots of FUN
  • You will LEARN something while you are having FUN
  • FUN and LEARN

Remember, we are going to have lots of fun and while having fun we are going to learn. This session is set at LEVEL 100, so I will be talking about at the beginning level. With that said if you are an expert DBA/Developer and want to have fun, join me in my session. We are going to have a great time together.

Here is one of the questions which I am going to ask in the session: If you get it right, I will give you a small token of appreciation.

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

SQL SERVER – What are my Trace Flags Enabled on SQL Server?

In one of my recent interactions with a DBA friend of mine, I had to dig into one of the simplest yet a capability which is available inside SQL Server. SQL Server from time to time has an uncanny requirement to enable certain Trace Flags. Trace Flags are specifically enabled by administrators to mitigate certain behavior that SQL Server has in specific situations. We need to understand that, these trace flags are special purpose and shouldn’t be used without understanding what the implications are on the server as a whole. So please handle these with care.

Now with this context set let me take a moment about the scenario which my friend was asking. DBA friend of mine was curious to know the trace flags that are enabled on his production servers. He was wanting to know how to get this information easily. I quickly wanted to ask him – “buddy, why are you curious suddenly about this?”. He said, recently he got promoted to as a Senior DBA and the DBA who was managing one of the critical servers had left recently and this server was transferred to him.

My first question to him was, “Have you checked the Server Dashboard reports?”

Just get to the Server node on Object Explorer (SSMS) -> Right Click -> Reports -> Standard Reports -> “Server Dashboard”. Once you are here, you can expand the “Non Default Configuration Options” and there are these Trace Flags that are enabled “Globally” on a given server.

This was a great start for sure. And there was a silence for a moment.

After this, he quickly bounced back to say that, the DBA had told that there were many other Trace flags that are enabled than what the server did show on this report. I was pleasantly surprised that I had given him a half-baked solution and had to revert back soon.

DBCC Tracestatus

The best and fastest way to look at all the Trace Flags is to use the DBCC TraceStatus command. There are a number of ways we can look at the statuses using this command. The simplest way to look at all the trace flags enabled is using the DBCC command directly like shown below:

Here we see there are two trace flags that are enabled, one Globally and other Session specific trace flag. As the name suggests the Global flag is applicable for the whole server node while there can be specific statuses which are applicable just for a session duration.

If you want to check the status of a Trace Flag, then we can call the tracestatus command specifically using the trace number as indicated below.

I immediately called my friend and was quick in letting him know about this command which has been with SQL Server for a while and that it had missed my mind. This is a quick way to find what are the Trace Flags used within SQL Server using T-SQL commands. If you have any other way to do these checks, please let me know.

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'

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'

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


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.


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 Authority News – 8th Year Anniversary – Thank You!

Today is very special day in my life. Exactly 8 years ago today, I had started writing this blog.

When I started writing a blog post, I really had no idea what exactly I am starting and where exactly I was going to end. Today, after 8 years, I am still here and talking about SQL Server. Well, today I asked my two of my friends that what should I write on this milestone blog post where I complete my 8 years of blogging. They suggested I have learned in last 8 years and what I want to learn in next 1 year. Very good suggestion. Feel free to guess my friends in the comments area, let us see if you can name them correctly.

Things I have learned in last 8 years

Let me summarize my learning in 5 words – Be Humble and Keep Changing

I truly learned how to be humble in last 8 years. SQL Authority is not my own blog, but it is our blog. You all who are reading this blog right now, it is YOUR blog. There were moments when I was carried away in different directions or there were moments when I was at the same points for quite a while. Every single time I got inspirations from all of you to either get back on the track or move on from one subject. I can’t truly thank you enough. It is your encouragement who got me started writing on Big Data and MySQL. There were times when I had made mistakes and I have received emails correcting me – thank you to everyone who has helped making this blog a better place. I have learned a lot and I will keep on learning in the future.

Things I am planning to learn in next 1 year

This is difficult to answer at this point of time. However, I will say I want to learn more about Hybrid solutions. Everytime I go out in the real world, I often see Oracle, SQL Server, MySQL and many other solutions used all together in a single application. Those applications are really so big that it justifies the needs of various database platforms as they each offer something new. I believe this is where I want to grow more. I want to know where and when each of the data platform is helpful. I will need your help and I will blog my experience on this blog.

Once again – thank you so much. Here are FREE downloads which I have prepared earlier in my career. Please feel free to download them and use them to your advantage.

If you want to stay connected, sign up for our low frequency newsletter.

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