Mitch's Pitch

In my second quick tip, I wrote about SQL 2012 IntelliSense Code Snippets in the intelliSense menu. In case you didn’t notice in the drop down for the Code Snippets, there was another option for ‘Surround with…’ just below it. What is this you might ask? Well, it is exactly what it sounds like. This option will allow you to create code that would sound a statement, like the typical Begin/End or While statements.

You would create these the same way you would the Code Snippets. When you are in SSMS (SQL Server management Studio) and open a New Query window, you can simply right-click in that window and click on Surround With… (You can also type Crtl+K, Ctrl+S). The third way is to click Edit on the main menu, chose IntelliSense > Surround With…

Once you click Surround With… it will display 3 different choices. They are the Begin Statement, the If Statement or the While Statement.

Once you click on one, it will write the basic T-SQL code for you. All you have to do is fill in the Condition and what you wish to execute during that condition.

Is this a huge time saver? Not really, but for people new to T-SQL coding, it will set them up with the basic format.

In my last quick tip, I wrote about SQL 2012 Code Snippets in the intelliSense menu and how easy they are to use. What I didn’t mention is that you also have the ability to create custom code snippets. Why would you want to do this? The biggest reason is to create consistency in development of these types of objects across your environment. What if your company has a specific header or code block format for stored procedures that they have a hard time enforcing because it is a pain to keep cutting and pasting for developers. Wouldn’t it be easier for a developer to right-click and add a snippet that already has all the headers and formats included? It would be very easy to create a central repository of custom code snippets for all developers in your shop to use when building new objects.

So how do we do this? In this case, you will need to open the Code Snippets Manager. To do this, go to Tools -> Code Snippets Manager… or Ctrl+K, Ctrl+B.

This will open up all of the Code Snippets that come canned in SQL 2012. What is important to recognize is that all of these code snippets are actually xml files. In this example, I am going to create a custom code snippet for a stored procedure with a comment block at the top. The easiest way is to just copy the canned ‘Create Procedure  Basic Template’ and modify it. From the image below, you can see I identified where the xml files currently reside.

I created a folder called Custom_Snippets on my C: Drive, copied the xml file and renamed it ‘SP_wCmntBlk.snippet’.Once that xml file was copied to my new folder, I can open it with Visual Studio or notepad update it to look like this.

Once I finish updating the xml code, I save it. Then, back in the Code Snippets Manager, I click Add, drill down to the folder I created called Custom_Snippets and click Select Folder. This folder will now show up in the Code Snippets Manager. In fact, any .snippet file you place in the folder will now automatically be visable.

Now if I open up SSMS, Open a New Query, right-click in the query editor and click Insert Snippet, I will see my Custom Snippet folder and can select the snippet I just created.

You don’t need to be an expert with xml to do this as I just showed you. It is rather simple and can be very useful.  Try it our and let me know what your thoughts are!

How often do you forget the syntax for simple things like creating roles, logins or triggers. You might not have to create them very often, so simple tasks like these might occasionally get truncated from your brain memory. Well, SQL 2012 has made life much easier for us by adding what they call Code Snippets. These snippets will allow us to simple create a script shell for all sorts of objects and they are super simple to use. Here is how you use them.

When you are in SSMS (SQL Server management Studio) and open a New Query window, you can simply right-click in that window and click on Insert Snippet… (You can also type Crtl+K, Ctrl+X)

This will display server different object in which you can create a generic code snippet for. In this case, we will look at creating a basic Stored Procedure shell. Once you choose the Create a single stored procedure option, generic code is created for you to use.

All you have to do is fill in the highlighted sections.

The same is true for all of the other objects listed in the snippets. You might already know the syntax to all of this, most of us DBA’s do, but this is also a no brainer and time saver in the sense that it is very easy to click n’ create. This can help new developers and old developers alike.

Take a minute and check it out.

Have you ever written a long T_SQL statement that you are really proud of only to execute it and get an error on line 250? You then have to count from the first line down 250 or copy all of the code into an editor like UltraEdit to find the line the error occurred on? Wouldn’t it be easier if SSMS had a built-in line numbers to the Query editor? Well, the funny thing is they did, it is just not enabled by default. So how do we turn it on, let me show you quick.

Within the SQL 2012 SSMS window, click on Tools -> Options…

Once the Options screen opens up, you will navigate down to find the Display settings.

Text Editor -> Transact-SQL -> General 

Once you get here, enable the Line numbers check box and click ok.

Now when you open a new query in SSMS, you will see the line numbers! Super easy!

If you don’t, you need to head to Mountain View for a whole day of free training on everything related to Microsoft SQL Server. Did I mention it is free? Well, it is! There is going to be some of the biggest names in the SQL community presenting on all sorts of subjects including Administration, Reporting services, HA/DR, Analysis Services and self promotion. These events are nothing short of amazing and everyone walks away with at least a few new items learned.

You can sign up here.
See the line up of speakers here.

Make sure to keep your 4PM session open for myself and Dilip Nayak (t) called Get TurnedOn with AlwaysOn. We will be presenting on SQL 2012 AlwaysON (HA\DR). This is going to be a great introduction session for anyone interested in learning about AlwaysOn, the features associated with it and how to set it up.

If you have never been to a SQL Saturday, you are really missing out on some top notch training as well as a great opportunity to network with others with the same interests.

In case you haven’t heard, Sacramento is having its first ever SQL Saturday. No, you didn’t misread that. The Cow-Town of California, where our Mayor is an ex-NBA star, our airport is considered international because it flies direct to Mexico and the biggest attraction we have here is a toss-up between Old Town and the State Capital Building.

You got plans on July 28, 2012? Hopefully your answer is ‘No’. The Sacramento SQL Server User Group is going to be putting on a great day of FREE SQL learning. In case you haven’t seen the speaker list, we have already gotten submissions from some great folks both local and national on topics from Database Administration to Business Intelligence and SQL 2012. Maybe you have heard of some of them!

We have a really great planning team who is working their tails off to try and put together a truly outstanding event. We will be having the event at a very lush local venue that is close to downtown Sacramento and Cisco will be hosting an after party at a restaurant on the Sacramento River (more details to come). Check out the SQL Sat 144 website to sign up now and get more information. If you are interested in presenting, we would love to hear from you! We are always looking for people to speak at out local user group meetings as well as events like this.

Can’t wait to see everyone on July 28th!

The copy-only backup is actually a very important tool in all production DBA’s arsenal. A copy only backup is essentially a ‘snapshot’ of the database at that point in time that is completely independent from any sequencing of backups used for a restore.

Why is it important some might ask, well, let me explain. Almost all production SQL environments have some sort of back up strategy which includes some combination of full backups, differential backups and/or Transactional log backups. For instance, a full backup is taken on a database every weekend. This creates a starting reference point for a restore.  After the full backup, differential backups are taken nightly with additional transaction log (TLog) backups taken at specified intervals throughout the day to create what is called a log chain. All of these differential and TLog backups are tied directly to the preceding full backup. A Log Sequence Number (LSN) is created to keep all the backups in order. When a restore is required, the full backup must be restored with all the proper differential and TLog backups. If one is missing, the restore will fail. The important thing here is that after a full backup, all of the differential and TLog backups following it are tied directly to that Full backup.

So, assuming we are all very organized and keep all of our backups automated and documented properly, we should be able to perform a point in time restore without issue. But what happens if one of your co-workers takes an AdHoc full backup of a database to restore to a development environment without telling anyone. Later that day you need to restore the database to a point in time after the AdHoc back up was taken by your co-worker? The restore will fail since the Log Chain was broke by the AdHoc full backup and the LSN’s are no longer in sync with the original full backup. Basically, any TLog backup taken after the AdHoc backup will now be associated to that backup.  In this case, had the person simply taken the AdHoc backup using the Copy-Only option, there wouldn’t have been any issues with the restore since the chain of backups wouldn’t have been broken.

So how do we take a copy-only backup of a database? It is as simple as just adding a ‘with’  statement to the backup command as seen below.

– Create full backup with Copy Only option
BACKUP DATABASE MyDataBase
TO DISK = ‘D:MyDatabase_CopyOnly.bak’
WITH COPY_ONLY
GO

The copy-only feature has been available since SQL 2005, but had to be written in the T-SQL backup statement. In SQL2008, the Copy-Only feature is available in the GUI for ease of use.

Keep in mind that the copy only feature is not available for Differential backups and the option in the GUI will actually be ‘greyed’ out.  The copy-only feature should always
be considered for any AdHoc backup that is not part of the normal backup schedule in your environment.