Month: June 2008

I feel like I keep finding reasons to use SQL Server 2008 Management Studio with SQL 2005 instances, and here’s another – the COPY_ONLY option now appears on the Backup dialog box (others include the DROP & CREATE scripting option, multi-server queries and the Select Top 1000 Rows menu entry).

It’s not a new option (it just wasn’t part of the dialog box), but there’s an option when backing up a database called COPY_ONLY. It goes in the WITH section. It means that the backup you’re taking won’t be included in your ordinary backup/restore sequence. Let’s paint a scenario.

Your system takes a full database backup on a Sunday morning. Monday, Wednesday and Friday you do differential backups. Everything’s great, each of the differential backups only looks at changes since Sunday, when the last full backup was taken.

Except that one Thursday, one of the developers wants to refresh the test environment with a recent copy of the production system. Instead of finding Sunday’s backup and using that, they make a new backup, and restore that onto test. Everything works, and that new backup gets deleted.

Which means that Friday’s differential backup is based on the Thursday backup instead of Sunday. When a disaster occurs on Saturday, you’re in a bit of strife.

If the developer had used the COPY_ONLY option, then Friday’s differential would still’ve been based on Sunday’s backup. That’s the whole point of COPY_ONLY – it doesn’t reset all the flags that indicate which data should be included in the differential.

Like I said earlier, it’s not new – it’s was introduced in SQL 2005. I don’t know why it didn’t appear in the SQL 2005 dialog box – it didn’t even turn up in a service-pack. Since so many people learn about features through the user-interface, it really could’ve helped educate users about this useful option.

The hairy Irishman (his description – I just call him Sacha) is presenting to the Adelaide SQL Server User Group today, talking about improvements in SQL Server 2008 Integration Services. Right now he’s talking about the No Match output of the Lookup Component, and I’m wondering why I had always just accepted that in SQL 2005, rows that didn’t match a lookup would get pumped out to the error output.

For those of you who are saying “Sorry, what?”, let me explain…

If you have a data flow in Integration Services which is missing a particular piece of information, and you need to get that information from another source, you are likely to do a Lookup transformation, which can perform a query to find the values to be inserted into each row in the flow. Of course if the data is just in two tables, you’d just do a join in your original source query, but as soon as you’re talking about files, you don’t have that luxury in quite the same way. Sure you could populate a table and then read it back, but a Lookup may end up being much quicker.

Now, in SQL Server 2005, if a row couldn’t find the looked up value, it would be considered an error, along with truncation errors and errors in connection to the lookup source. I just accepted this as okay before, but the more I come across the No Match output (which can be used as well as the error output), the more I like it. So I can much more easily separate the rows that have caused errors from the rows that just couldn’t be found in the lookup source.

Regular readers of my blog will remember my post about how to get PowerShell for SQL into your regular PowerShell window. Except that if you’ve just installed SQL Server 2008 RC0, you may have noticed it doesn’t work any more.

Never fear, they’ve just changed the name a bit. They’ve put 100 on the end of both. So just change your console file to list the two snapins as SqlServerProviderSnapin100 and SqlServerCmdletSnapin100. Calling PowerShell with the -PSConsoleFile option still works as before of course.

On a lighter note, they’ve fixed the case sensitivity. I can now “cd databases” just fine, without it needing me to use a capital D.