Search results matching tags 'SQL Server', 'DBA', and 'Tips'

Back up a single table in SQL Server

BuckWoody — Thu, 03 Jun 2010 12:51:34 GMT

SQL Server doesn’t have an easy way to take a table backup, so I often use the bcp (Bulk Copy Program) to accomplish the same goal. I’ve mentioned this before, and someone told me when they tried it they couldn’t restore the table – ah the dangers of telling people half the information! I should have mentioned that you need to have a “format file” ready if the table does not exist at the destination. In my case I already had the table, in this person’s case they did not. The format file can be used to rebuild that table structure before the data is bcp’d in, and you can read more about it here: http://msdn.microsoft.com/en-us/library/ms191516.aspx

There’s another way to back up a table, and that’s to create a Filegroup and place the table there. Then you can take a Filegroup backup to back up a single table.

Of course, there are other methods of moving a single table’s data in an out, including SQL Server Integration Services and even the older Data Transformation Services, or simply by using hte SQLCMD or PowerShell utilities to run a query and just save the output to a file. In fact, these days I’m using a PowerShell script to build INSERT statements from that query. That could also easily be modified to create the table structure (or modify one if needed) quite easily.

Backup those keys, citizen

BuckWoody — Tue, 20 Apr 2010 12:14:50 GMT

Periodically I back up the keys within my servers and databases, and when I do, I blog a reminder here. This should be part of your standard backup rotation – the keys should be backed up often enough to have at hand and again when they change.

The first key you need to back up is the Service Master Key, which each Instance already has built-in. You do that with the BACKUP SERVICE MASTER KEY command, which you can read more about here.

The second set of keys are the Database Master Keys, stored per database, if you’ve created one. You can back those up with the BACKUP MASTER KEY command, which you can read more about here.

Finally, you can use the keys to create certificates and other keys – those should also be backed up. Read more about those here.

Anyway, the important part here is the backup. Make sure you keep those keys safe!

PowerShell PowerPack Download

BuckWoody — Wed, 07 Apr 2010 13:30:20 GMT

I read Jeffery Hicks’ article in this month’s Redmond Magazine on a new add-in for Windows PowerShell 2.0. It’s called the PowerShell Pack and it has a some great new features that I plan to put into place on my production systems as soon as I finished learning and testing them.

You can download the pack here if you have PowerShell 2.0. I’m having a lot of fun with it, and I’ll blog about what I’m learning here in the near future, but you should check it out. The only issue I have with it right now is that you have to load a module and then use get-help to find out what it does, because I haven’t found a lot of other documentation so far.

The most interesting modules for me are the ones that can run a command elevated (in PSUserTools), the task scheduling commands (in TaskScheduler) and the file system checks and tools (in FileSystem). There’s also a way to create simple Graphical User Interface panels (in ). I plan to string all these together to install a management set of tools on my SQL Server Express Instances, giving the user “task buttons” to backup or restore a database, add or delete users and so on. Yes, I’ll be careful, and yes, I’ll make sure the user is allowed to do that.

For now, I’m testing the download, but I thought I would share what I’m up to. If you have PowerShell 2.0 and you download the pack, let me know how you use it.

Script Disclaimer, for people who need to be told this sort of thing:

Never trust any script, including those that you find here, until you understand exactly what it does and how it will act on your systems. Always check the script on a test system or Virtual Machine, not a production system. Yes, there are always multiple ways to do things, and this script may not work in every situation, for everything. It’s just a script, people. All scripts on this site are performed by a professional stunt driver on a closed course. Your mileage may vary. Void where prohibited. Offer good for a limited time only. Keep out of reach of small children. Do not operate heavy machinery while using this script. If you experience blurry vision, indigestion or diarrhea during the operation of this script, see a physician immediately.

Process Improvement and the Data Professional

BuckWoody — Tue, 06 Apr 2010 13:23:48 GMT

Don’t be afraid of that title – I’m not talking about Six Sigma or anything super-formal here. In many organizations, there are more folks in other IT roles than in the Data Professional area. In other words, there are more developers, system administrators and so on than there are the “DBA” role.

That means we often have more to do than the time we need to do it. And, oddly enough, the first thing that is sacrificed is process improvement – the little things we need to do to make the day go faster in the first place. Then we get even more behind, the work piles up and…well, you know all about that.

Earlier I challenged you to find 10-30 minutes a day to study. Some folks wrote back and asked “where do I start”? Well, why not be super-efficient and combine that time with learning how to make yourself more efficient? Try out a new scripting language, learn a new tool that automates things or find out ways others have automated their systems. In general, find out what you’re doing and how, and then see if that can be improved. It’s kind of like doing a performance tuning gig on yourself!

If you’re pressed for time, look for bite-sized articles (like the ones I’ve done here for PowerShell and SQL Server) that you can follow in a “serial” fashion. In a short time you’ll have a new set of knowledge you can use to make your day faster.

System Variables, Stored Procedures or Functions for Meta Data

BuckWoody — Thu, 11 Mar 2010 13:42:22 GMT

Whenever you want to know something about SQL Server’s configuration, whether that’s the Instance itself or a database, you have a few options.

If you want to know “dynamic” data, such as how much memory or CPU is consumed or what a particular query is doing, you should be using the Dynamic Management Views (DMVs) that you can read about here: http://msdn.microsoft.com/en-us/library/ms188754.aspx

But if you’re looking for how much memory is installed on the server, the version of the Instance, the drive letters of the backups and so on, you have other choices.

The first of these are system variables. You access these with a SELECT statement, and they are useful when you need a discrete value for use, say in another query or to put into a table. You can read more about those here: http://msdn.microsoft.com/en-us/library/ms173823.aspx

You also have a few stored procedures you can use. These often bring back a lot more data, pre-formatted for the screen. You access these with the EXECUTE syntax. It is a bit more difficult to take the data they return and get a single value or place the results in another table, but it is possible. You can read more about those here: http://msdn.microsoft.com/en-us/library/ms187961.aspx

Yet another option is to use a system function, which you access with a SELECT statement, which also brings back a discrete value that you can use in a test or to place in another table. You can read about those here: http://msdn.microsoft.com/en-us/library/ms187812.aspx

By the way, many of these constructs simply query from tables in the master or msdb databases for the Instance or the system tables in a user database. You can get much of the information there as well, and there are even system views in each database to show you the meta-data dealing with structure – more on that here: http://msdn.microsoft.com/en-us/library/ms186778.aspx

Some of these choices are the only way to get at a certain piece of data. But others overlap – you can use one or the other, they both come back with the same data. So, like many Microsoft products, you have multiple ways to do the same thing. And that’s OK – just research what each is used for and how it’s intended to be used, and you’ll be able to select (pun intended) the right choice.

Have you backed up your keys lately?

BuckWoody — Mon, 01 Mar 2010 14:06:04 GMT

Did you know that you already have a Server Master Key (SMK) generated for your system? That’s right – while a Database Master Key (DMK) is generated when you encrypt a certificate or Asymmetric Key with code, the Server Master Key is generated automatically when you start the Instance.

So you should back all of those keys up periodically, and then store that backup AWAY from the server itself.

There are two reasons for this – first, if the drives get stolen and you’re storing the key backup there, well, that should be obvious why that’s bad. Second, you want to protect the keys in case the system is destroyed or you can’t recover the drives. You will need those keys if you have encrypted anything in the database to get the data back.

More here: http://technet.microsoft.com/en-us/library/bb964742.aspx

No, the standard Maintenance Wizards don’t get this data. And no, I haven’t seen it addressed in most of the maintenance scripts out there anyway – sometimes for good reason, but this means you need to take care of it manually, and then document where you put that backup.

Tools and Processes for “Fitting it all in”

BuckWoody — Mon, 18 Jan 2010 14:42:05 GMT

Most data professionals I’ve met work in two modes: we plan for our day, and we react to the situations around us. I’m staring at my list of things that I need to do today right now, which is my planned work. Of course, I have no idea how much of that will really get done – it’s optimistic to be sure. On the other hand I have several systems I manage, and at any moment one of them or the people that interface with them may “change state” such that I need to give them some attention.

So how do I meld the two? Sometimes it can be quite difficult. I’m constantly working through my list in my mind, re-arranging what I’m focusing on based on what I perceive as the highest need. There are, however, some tools that I use each day to help me manage the workflow.

I use Outlook for tracking everything, since it has a task list (my primary tracking), a calendar, mail and so on. Also I can share the information, it’s on-line so I can see it anywhere, and I can even take it offline onto the plane this week when I fly out of town.

For the “ad-hoc” work, I rely on a script library, which I keep as SQL Server Management Studio projects. I keep those scripts and projects backed using Microsoft Live Mesh, which synchronizes those files (along with a few other critical files and my IE Favorites) across not only my laptop and primary systems, but even with my Virtual Machines.

Also for my SQL Server systems I use the Standard Reports I’ve blogged about here. I also use Greg Larsen’s Database Dashboard, and a series of PowerShell scripts that work across my systems, alerting me to any problems. Of course I’m using SQL Server Agent Jobs quite a bit, and I also use Alerts and some Perfmon automation for my monthly baselining.

So – is this your experience as well? Do you get driven by both planned and unplanned work? What tools and processes do you use to keep it all straight with your SQL Server Instances?

Plan and Prepare or Just Do It? How about Both!

BuckWoody — Thu, 07 Jan 2010 15:56:00 GMT

I'm kind of a type "A" person. OK, I'm a VERY type "A" person. I even cook by setting things up ahead of time. I'm definitely more in the "Plan and Prepare" camp than the "Just Do It" camp.

But I do realize that there are times when you just can't stop and prepare. Sure, it would be great to know that server is going to melt down just now, but it happened and you have to deal with it. Now is not the time to open the plastic on that "Troubleshooting SQL Server" DVD course you bought! You just have to dive in and get the thing fixed.

Hopefully you're not operating like that all the time. If you are, you probably need to get some help with your systems, at least temporarily, until you can get them stabilized.

So which is it? Should you be like me, a type "A" who has to have everything planned out, or a reactionary agent, fixing things as they happen? How about - Both!

I think you should aim to plan and prepare as much as you can. Your life will be more stress-free, and you'll be happier in your job. But you can't lose your head when things go wrong and demand time to plan and prepare. You just have to jump in there and fix the problem.

But I think there's a happy medium in there somewhere. I ten to plan and prepare for the times I have to "just do it". I have my scripts ready for things like backups, DBCC repairs, restores, web site links for "how to" articles and so on. I have those right by my desk so that I don't have to panic when panic hits. So in effect, I'm doing both - I'm planning to just do it.

SQL Server Chargeback Strategies

BuckWoody — Tue, 29 Dec 2009 14:47:37 GMT

It seems the more things change the more they stay the same. One of the things I used to create on the mainframe system when I started years ago was a “charge-back” system. It tracked the time and resources used by the employees so that we could charge their department money for the time they spent on the (very expensive) mainframe.

When LAN systems came out, IT departments were just charged as a general expense. But these days, with shrinking budgets and deeper scrutiny on how resources are used, this idea has come back around. IT and Data Systems managers are keen to show the organization that it isn’t free to operate a database system.

From time to time I get asked if it is possible to do a chargeback system for SQL Server. And it is! In fact, I wrote a couple of articles on how to do that right here:

http://www.informit.com/guides/content.aspx?g=sqlserver&seqNum=311

http://www.informit.com/guides/content.aspx?g=sqlserver&seqNum=312

Code that Writes Code

BuckWoody — Wed, 25 Nov 2009 14:01:40 GMT

I have scripts that re-create my databases for testing and development purposes. But sometimes I want to take the data from a set of tables and move it as well – I could use SSIS, or a SELECT INTO statement, but what if I want to “re-set” the data to a point in time? In other words, load it with some “base data”?

I thought this might be a good place to demonstrate “code that writes code”. No, it isn’t that big of a deal – most DBAs know how to do this, but in the interest of those who don’t, I thought I would share.

So the end result I want in this case is to have a bunch of INSERT statements that contain my base data from a table where that data already exists. I could script out the table from SSMS and use the replacement parameter feature to fill out each record individually:

I have a table in one of my databases that I right-clicked to show the INSERT statement:

-- Person

INSERT

INTO [WAVS].[dbo].[Person]

([PersonName]

,[Street]

,[CityStateZip]

,[PersonType])

VALUES

(<PersonName, varchar(150),>

,<PersonStreet, varchar(100),>

,<PersonCityStateZip, varchar(255),>

,<PersonPersonType, varchar(100),>)

I can use the CTRL-SHIFT-M keys to replace the VALUES part with the values I want.

But I already had a copy of that “base” data before I turned the database over to the testers. Before I give it to them, I run code like this on each table – this one is for the Person table above (note that there should only be two lines here):

SELECT

'INSERT INTO [WAVS].[dbo].[Person] ([PersonID], [PersonName], [Street], [CityStateZip], [PersonType]) VALUES (' + CAST([PersonID] AS VARCHAR(5))+ ', ''' + [PersonName]+ ''', ''' + [Street]+ ''', ''' + [CityStateZip] + ''', ''' + [PersonType] + ''')'

FROM

[WAVS].[dbo].[Person]

When I run this code, I get these statements:

INSERT INTO [WAVS].[dbo].[Person] ([PersonID], [PersonName], [Street], [CityStateZip], [PersonType]) VALUES (1, 'Buck Woody', '123 Here Street', 'Covington, WA 98042', 'Vet')
INSERT INTO [WAVS].[dbo].[Person] ([PersonID], [PersonName], [Street], [CityStateZip], [PersonType]) VALUES (2, 'Jane Doe', '231 Function Ave', 'Redmond, WA 98052', 'Vet')
INSERT INTO [WAVS].[dbo].[Person] ([PersonID], [PersonName], [Street], [CityStateZip], [PersonType]) VALUES (3, 'Diane Wilson', '34251 Appt 3 7th Street', 'Seattle, WA 98061', 'Vet')
INSERT INTO [WAVS].[dbo].[Person] ([PersonID], [PersonName], [Street], [CityStateZip], [PersonType]) VALUES (4, 'John Kelso', '89734 Country Lane', 'Covington, WA 98042', 'Farmer')
INSERT INTO [WAVS].[dbo].[Person] ([PersonID], [PersonName], [Street], [CityStateZip], [PersonType]) VALUES (5, 'Marjorie Christian', '9893452 Changein Lane', 'Maple Valley, WA 98072', 'Farmer')
INSERT INTO [WAVS].[dbo].[Person] ([PersonID], [PersonName], [Street], [CityStateZip], [PersonType]) VALUES (6, 'Joanne Lister', '98904 Mapelwood Drive', 'Spokanne, WA 98045', 'Zoo Worker')

All I have to do is copy those statements into my “initialization” scripts just after the table creations, and then I can run them at will to create my new database (I know I can take a backup – I have reasons for not doing that).

There are two keys for writing code that writes code: The first is to simply use the single tick (') to write out what the end code should say (INSERT INTO, in my case) and then to use multiple ticks (''') when you want the code to contain the ticks themselves. This “escapes” the characters so that you can embed them. If you use SSMS to write your queries, the color coding will look as mine does above. You’ll also need to remember to CAST or CONVERT any data types that don’t concatenate (+) together well. In my case I had to CAST an integer value – but that’s OK, since it is only printing to the screen and I don’t include the ticks, the INSERT works fine.

You can use this method not only with INSERTS, but with lots of operations. And you can also even fire off the code once you create it – but that’s another post :)