Fortune cookies are usually junk, but...

Fortune cookie fortunes are more platitude than anything these days. I'm usually put off by them. I usually want something along the lines of "you will be rewarded for your recent hard work" or "they really are out to get you". So I was pleasantly surprised when I got the following:

Those who endure most are rewarded most.

True enough (in bed).

Create a database snapshot

I've been meaning to write this one for a while. The need arose today to be able to create a database snapshot fairly quickly. Here's what I came up with:

Relog.exe for perfmon slicing and dicing and PAL for reporting

So I'm going through a "how do we make the application better" exercise here at work. We ran a load test last night to see how the system would hold up. I always have perfmon counters running, so I wanted to see how they did during the load test window. Enter relog.

Validating one article at one subscriber

Here's a quick script before I nod off:

use [publisher_db]
go
begin tran
go
exec sp_marksubscriptionvalidation 
    @publication = N'publication_name,
    @subscriber = 'subscriber_server',
    @destination_db = 'subscriber_db'
exec sp_article_validation
    @publication = N'publication_name',
    @article = N'article_name',
    @rowcount_only = 2,
    @full_or_fast = 2,
    @shutdown_agent = 0
go
commit

I don't think this is exposed through the GUI at all.

Finding last backups with Powershell

Last weekend, I did a change control that was in the middle of when we normally do our full backups. As a result, some of the databases didn't get backed up via the normal process. Our data center guys gave me the option of just running the whole thing again, but that would have backed up already backed up databases and given that our retention policy is based on time (and not by number), the backup drive isn't sized to accommodate that. I was also without my script repository (since moved to Dropbox so that won't happen again), so I needed a quick way to determine which databases needed to be backed up. Enter powershell:

Getting the TCP port of all instances on a machine

So... we run multiple SQL instances per physical server. There also seems to be a firewall issue between my laptop and the environment that prevents port resolution for those instances. So, I decided to whip up a little Powershell script.

Get me some articles!

So, as I alluded to here, I was doing some work today. Specifically, I was adding articles to a publication. I had an idea that I should be able to come up with a way to add the articles that are missing from the publication. That is, if I have a database with 20 tables and 17 are already articles, then the script should be smart enough to add the 3 that aren't.

---

declare tablez cursor for
    select schema_name([schema_id]), t.name 
    from sys.tables t
    left join sysarticles a
       on t.[object_id] = a.[objid] 
    where OBJECTPROPERTY([object_id], 'IsMSShipped') = 0  --no system objects
       and OBJECTPROPERTY([object_id], 'TableHasPrimaryKey') = 1 --only give me tables that can be replicated, please
       and a.[objid] is null --not already an article
open tablez
declare @schema sysname, @table sysname, @fullname sysname
while (1=1)
begin
    fetch next from tablez into @schema, @table
    if (@@FETCH_STATUS <> 0)
        break
    set @fullname = @schema + '.' + @table
    exec sp_addarticle
    @publication = 'Publication',
        @article = @fullname,
        @source_object = @table,
        @destination_table = @table,
        @type = 'logbased',
        --@schema_option
        @destination_owner = @schema,
        @status = 16, 
        @source_owner = @schema

end
close tablez
deallocate tablez

---

The astute among you might wonder why I named the cursor "tablez". I'll present you with two options.

1. "tables" highlights as a system table in SSMS (a la sys.tables).
2. I'm l33t.

I leave it to the reader to choose. As always, the standard warning applies.