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)

About these ads

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)

SQL SERVER – Answer to Puzzle – ISNUMERIC and Unexpected Results

Earlier I blogged about Puzzle – ISNUMERIC and Unexpected Results – SQL in Sixty Seconds #076 and it is one of the most popular blog posts. Lots of people answered the question correctly.

In the blog post I had a video puzzle. You can watch the video over here.

Now the puzzle was what was the value which I had passed in the second select statement which was returning the value of the SELECT statement as 1. The answer is I have passed TAB in the ISNUMERIC function.

The reason given by many of you is that as an ASCII value of the TAB is 9, it is returning the 1 in the ISNUMERIC function. Well, I am personally not sure if that is the valid reason or not. I will let you all decide the answer for the same.

However, MSDN’s official documentation says – ISNUMERIC returns 1 when the input expression evaluates to a valid numeric data type; otherwise it returns 0. That explains that it works with more than one data type.

If any of the blog readers have more details about the same, would you please post it in a comment and I will update the blog post with reference.

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

SQL SERVER – Beginning Table Valued Constructors – Notes from the Field #052

[Note from Pinal]: This is a 52th episode of Notes from the Field series. I am very happy that the journey which we started one year ago is a amazing milestone. In this 52 episode in the entire year we have learned a lot of new things from industry experts of LinchPin People. They are amazing sets of people who know what they are doing on the field and in the real world. I have received so many notes from blog readers that they have benefited from the experience shared by LinchPin Team.

In this episode of the Notes from the Field series database expert Kathi Kellenberger explains Table Valued Constructors. Kathi is an amazing instructor, she was the SQL author I have read in my early career. The reason, I love SQL Server because her writing has instigated love for this technology in me. Today she brings a relatively unknown topic for database experts. Read the experience of  Kathi in his own words.


Table Valued Constructors, also called Row Constructors, were introduced with SQL Server 2008, but many SQL Server professionals haven’t heard about them. They make it easy to insert multiple rows of hard-coded values into a table with just one insert statement.  NOTE: In this post, I’ll abbreviate Table Valued Constructors as TVCs.

I’ll often see people using older techniques to populate sample tables when asking for T-SQL help on the forums. The following example demonstrates two techniques.

CREATE TABLE #test(Col1 INT, Col2 VARCHAR(10));
--Method 1, using UNION
INSERT INTO #test(Col1, Col2)
SELECT 1,'a'
UNION ALL
SELECT 2,'b'
UNION ALL
SELECT 3,'c';

--Method 2, multiple select statements
INSERT INTO #test(Col1, Col2)
SELECT 4, 'd';
INSERT INTO #test(Col1, Col2)
VALUES( 5, 'e');

The first method takes advantage of the UNION ALL operator to combine three SELECT queries into one set of results.  The second example uses a separate INSERT statement for each row to be inserted. One statement uses a SELECT with hard-coded values, while the second uses the VALUES keyword.

Beginning with the 2008 version, you can specify multiple rows with the VALUES syntax which is now called TVC. Each set of values must be within parentheses and each set must be separated by a comma. Example 2 demonstrates this technique.

CREATE TABLE #testTVC(Col1 INT, Col2 VARCHAR(10));
INSERT INTO #testTVC(Col1, Col2)
VALUES(1,'A'),(2,'B'),(3,'C'),(4,'D'),(5,'E');

The big advantage of TVCs is that you save typing. Of course, looking cool on the forums is a bonus.

While using the TVC to insert multiple rows with one insert statement is pretty fantastic, there are some other interesting uses. You can create a set of results using a TVC within a derived table. Take a look at this example:

SELECT *
FROM
(VALUES ('January'),('February'),
(
'March'),('April'),('May'),
(
'June'),('July'),('August'),
(
'September'),('October'),
(
'November'),('December')
)
AS Months ([Month]);

 

In the months example above, my TVC is part of the FROM clause. Notice that I must alias the TVC just like a derived table. I also have to provide column names right after the alias.

Another interesting use of TVCs involves the APPLY operator. A feature of APPLY is that columns from the outer query are visible inside the APPLY. Not only can you have hard-coded values as in the previous example, you can use columns from the outer query as well. The following example demonstrates this technique.

SELECT SOD.SalesOrderID, Dates.*
FROM Sales.SalesOrderHeader AS SOD
CROSS APPLY(VALUES('Order Date',OrderDate),
(
'Due Date',DueDate),
(
'Ship Date',ShipDate)) AS Dates(TypeOfDate,TheDate);

In this example, using the AdventureWorks database, each row from the SalesOrderHeader table is returned three times, once for each kind of date. Instead of displaying each date in its own column. This example “unpivots” the dates.

Table Valued Constructors can save you some typing and make your code look more organized. And, as you have seen here, they are also capable of some neat little tricks.

If you want to get started with BIML with the help of experts, read more over at Fix Your SQL Server.

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

SQL SERVER – Puzzle – ISNUMERIC and Unexpected Results – SQL in Sixty Seconds #076

It has been a long time since I have asked, puzzled on this blog so let us have fun time together with ISNUMERIC function. If you get the correct answer to this question, I will give you one month free subscription to Pluralsight.

The question is in the form of video which is displayed here. Watch the video and answer correct question in the comment on the this blog. You have 48 hours from the time of publishing this blog to answer it correctly.

If you due to any reason, you can’t watch the video, here is the image taken from the video. I strongly suggest you watch this video to get a clear idea of the puzzle.

In the above image, when I execute first select statement, it returns me answer as 0 but when I execute the second function, it gives me answer as 1. What is passed in the second select statement in ISNUMERIC function which is producing answer as 1, even though it is not visible.

Please leave your answer as a comment to this blog post.

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

SQL SERVER – Exploring SQL Auditing with SQL Compliance Manager

As database administrators, we are constantly quizzed by our superiors to answer questions such as: how secure is our network, how secure is our data, is data secure at rest, is data secure in transition, who is accessing my data? All these are basic yet compelling queries businesses ask. In this age of competitive businesses, organizations are becoming tech savvy in building a secure fort for their critical data. Having worked on many projects in the past, these security measures are something I know are non-negotiable from an implementation point of view for administrators. When on this topic, I am reminded of a conversation I had with a junior DBA named Siva.

Siva: Hi Pinal!

Pinal: Hi Siva, how are you dude?

Siva: I am rocking and have been put onto a spot now from my internal auditing team.

Pinal: Wow, nice. Please shoot your question. Let me see what can be done.

Siva: Our internal auditing team wants to do auditing for our mission-critical sensitive database.

Pinal: Well, it’s easy and as simple as the question you asked.

Siva: Really, are you sure?

Pinal: Yes. Use the out-of-box capability of SQL Server auditing and it is something to play with too.

Siva: Oh, yeah. That is true. But there is a twist. They do want to log everything, but want to make sure our administration/maintenance activities are not logged per se.

Pinal: That is an interesting requirement for sure. But, it is not difficult either – trust me.

Siva: Now you are getting me interested. Please guide me.

Pinal: Well, start looking for filtered audit. Have you looked into it?

Siva: Oh yeah. I do need to look into it. Thanks for the pointer. If you get a chance, please write about these on your blog someday.

Pinal: Sure.

This conversation has been on my mind for quite some time and I have been wanting to write what Siva asked since then.

Simple Auditing Filtering with SQL Server

In the above conversation, I am making an assumption that the administrative tasks are done by the local administrator account like “sa.” The first step is to know the “principal id” of our administrator account. Use the following DMV to know the same:

SELECT name, principal_id FROM sys.server_principals

In our example, the “sa” account will have the principal_id of 1. So that is what we will use in our filter section of audit definition. The next is the audit definition we will create using the filter condition. A typical TSQL construct would look like:

CREATE SERVER AUDIT [Filtered-Audit-2014-10]
TO FILE
(  FILEPATH = N'C:\Temp\'
,MAXSIZE = 0 MB
,MAX_ROLLOVER_FILES = 2147483647
,RESERVE_DISK_SPACE = OFF
)
WITH
(  QUEUE_DELAY = 1000
,ON_FAILURE = CONTINUE
)
-- Change server_principal_id as needed
WHERE ([server_principal_id]<>(1))
GO

This is an awesome capability introduced with SQL Server 2012 and is worth a mention today. Now we can go ahead and create a server audit for specific events. Once the audit is enabled, we can go ahead and attach our events to this using the TSQL like below:

CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpecification-20141020]
FOR SERVER AUDIT [Filtered-Audit-2014-10]
ADD (FAILED_LOGIN_GROUP)
GO

In this example, we have gone ahead and created an audit for failed logins. The wish list was to automate for a broader auditing capability with SQL Server.

Advanced Auditing with SQL Compliance Manager

On the topic of auditing I would like to talk a little bit about SQL Compliance Manager from Idera. As much as we would like to customize and use the out-of-box T-SQL functionality, it is sometimes necessary to do the same functionality of auditing in an automated fashion. This is where SQL Compliance Manager comes into play. There are a number of things we can start auditing using this tool. Various configuration options you can do with this tool include:

  1. Logins
  2. Failed Logins
  3. Security Changes
  4. Database Definition
  5. Administrative Activities
  6. User Defined Events

In addition to these, we can audit based on filtered access or we can audit all the events by unchecking the checkbox too. I don’t think we can get an easier configurable option for auditing logins, DDL, admin tasks in such a user friendly format. This in my opinion is the fastest way to configure audits on important tasks.

If you are a savy DBA, then the chances are you can enable all these auditing capabilities using a similar T-SQL script. But this is where I felt there are nice additions to the tool that make it worth a second look.

I particularly want to call out the auditing threshold, which can be used to track abnormal activity and proactively alert a DBA of any suspicious activity that has been identified based on thresholds set by the organization around access, login failures, DDL activity and many more. In addition to those activities, there is also a laundry list of standard reports they generate after the data is collected. This list is pretty exhaustive to say the least.

The more I play with the product, there are more pleasant surprises with each additional screen.

Wrap up

As I wrap up, feel free to use the filtered auditing capability with SQL Server and explore how these can be controlled at a granular level. If you want a quick solution that will enable auditing, keeping security and compliance in mind, then it is worth looking into SQL Compliance Manager too. It is worthwhile to trial SQL Compliance Manager for your environment. I still look forward for plugs to the current SQL Server enhancements like filtered auditing, audit resilience capabilities in the future. I am sure these are present in some shape or form today and may be explored.

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

SQL Authority News – Embarcadero DB PowerStudio Promotion & Savings

If you are regular readers of this blog, you may be aware of that I use many different tools in my daily routine. One of them is DB PowerStudio for Embarcadero. The team has recently announced special saving for up to  51% when you buy the DB PowerStudio bundle. With DB PowerStudio, you get a database administration and development tool suite that enables you to work seamlessly across database platforms. DBAs and Developers need easy-to-implement and easy-to-use tools that enable them to create and maintain mission-critical databases, expedite code development, streamline database change management processes, and quickly optimize performance bottlenecks.

Here are some details about DB PowerStudio’s Bundle. It contains four products.

DBArtisan DBArtisan

  • Database administration and management
  • Space, data, security and performance management and insight
  • Schema and data migrations

Rapid SQL

  • The Intelligent IDE for SQL development
  • Query building
  • Debugging SQL code, function and stored procedures

DB Optimizer

  • Automated SQL profiling, tuning, optimization
  • Proactive identification of performance issues and bottlenecks
  • Maintain database uptime and availability

DB Change Manager

  • Essential schema and data synchronization
  • Quickly roll-out database changes
  • Visibility and reporting on DB changes
  • Compliance with database audit and reporting requirements

You can click here to avail a 51 % discount.

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