Mini Post: Taking a DB out of 'RESTORING' State

Hey guys, just a quick post here on how to take your database out 'RESTORING' state.

So maybe you stopped Mirroring, maybe you were restoring transaction logs, maybe your database has an issue and curled up into itself or maybe you accidently restored a DB with the "WITH NORECOVERY" option (or heck, maybe you did it on purpose).

The bottom line is your DB is currently in restoring.

To remove it from this state, please run the following:
RESTORE DATABASE YourDBName WITH RECOVERY

Ta-da! Its back online. :-)


DISCLAIMER: As stated, I’m not an expert so please, PLEASE feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)

SQL Server 2012: OFFSET-FETCH

Hello all and welcome back (finally!) to SQL Something. :-)

Been a while I know, but sometimes life steps in your way a little bit. But hey even in those times, life teaches you something new.

Recently I’ve studying for the Microsoft 70-461 Exam (Querying Microsoft SQL Server 2012) so if you find posts in the future seem to be query related then you know why. :-p

A lot of the stuff I’m reading so far seems interchangeable with stuff you can use in SQL Server 2008 queries but I did run into something early that seemed kind of interesting. That thing is OFFSET-FETCH.

Mini Post: Setting Line Numbers In the Query Window

1st Mini Post!!!

I think I’m gonna do these every now and again, in addition to the weekly posts. Stuff that’s a little too small to be a normal post, but stuff that’s still useful.

Alrighty, let’s set line numbers shall we? Honestly I don’t know why a) it’s not on as default or b) it’s so roundabout to set up a simple thing but hey, that’s just me.

SQL Server: Setting Up Transactional Backups

Heya guys, it’s been awhile. Welcome back to the first SQL Something in 2 weeks. I sincerely apologise for this. The first week I came down with an awful fever and then the second week was really busy workwise. 

Sigh, still feeling pretty bad about not updating though. Bleh.

Anyhoo let’s begin without further ado. This week, we’re gonna take a look at setting up Transactional Backups.

SQL Server Email Notifications for Back Ups

Heya everyone and, as usual, a big welcome back to SQL Something. :-)

Today’s post is going to illustrate how to set up Email Notifications for SQL Server jobs, specifically for a simple backup job in this case. A friend of mine indicated that they didn’t know how and that inspired this week’s post. 

It’s going to be divided up into three main parts:

• Setting up the Email Account(s) to Send Notifications
• Setting up the Email Account(s) to Receive Notifications
• Setting up the Job with Email Alerts

This blog post is also going to look like it was hit by a picture nuke.

Remove a Database from Single User Mode

Hey everybody! Welcome back to SQL Something.

It’s been a long week but still trying to stick to my self-imposed rule of having one post per week. Here’s a simple one on how to remove Single User Mode from a DB.

Fig. 1: What a DB looks like in Single User Mode in Management Studio.

 

From my readings and my experience it seems a DB could go into single user mode in order to prevent corruption after some form of incorrect action was taken, whether purposefully or not. This recently happened to me when I tried to purposefully break replication. I tried to delete a DB while it was publishing, it then threw an error and curled up in a fetal position in single user mode. Poor little guy.

So how do we take a DB out of Single User Mode? We can try the following:

SQL Server Error: Cannot drop database because it is being used for replication.

Hey everyone! Welcome (or also hopefully "Welcome back") to SQL Something. :-)

This week’s instalment is going to be a little short and focused on fixing the above error that I ran into. 

The general consensus is that this error occurs after Replication was removed from a database and then you attempt to delete the DB. Apparently there might be some replication metadata that was left behind that would cause SQL Server to believe that replication is still taking place. If you attempt to delete the DB, you’ll get the error and it may go into "single user mode" (Access to the DB is restricted to one user; I may make a post on how to get out of it in the future).

As usual I’m gonna post everything I read on how to fix it as well as what I actually did.