Tuesday, March 22, 2011

[Replication] Nasty bug and reinitializing without a snapshot

So, there's a bug in SQL Server 2005 SP2 _and_ SQL Server 2005 SP3 (fixed in SP2 CU12 and SP3 CU3), where a commonly-used replication SP can inadvertently reset your subscription, and you get the dread "The initial snapshot for publication...is not yet available" error. In my case I couldn't easily snapshot the data across, so a workaround is to sync the data, then recreate the subscription, specifying that it does not need to be initialized.

Bug: MS KB967192


Essentially, what you need to do is drop the subscription, sync the data, then recreate the subscription, making sure to uncheck "Initialize" (if using SSMS).

Thursday, March 10, 2011

[Backups] Verify your backups physically exist

We've been using Rodney Landrum's SSIS package to monitor our environment (As Seen In SQL Server Mag). We're running the old version, which doesn't deal AT ALL with servers not being available. (I assume the latest version does, but haven't had time to check.)

And I came across an issue recently - missing backups. Because of the various retentions set via our backup jobs, we would occasionally have a file vanish. Eek!

So, cue this code. It'll grab the most-recent backup for each server/database, and make sure the file physically exists. It doesn't check the veracity of the backup, just that there's a file there. It also uses xp_fileexists, an undocumented (and therefore it can change - though it's been the same since SQL Server 2000) SP.

If you don't use Rodney's code, you can still use this, but it'll be a _little_ more work. Take the below code, have it run on each machine and dump into a central table (I'll leave those details up to you), then run the second set of code against it.

SELECT server_name,
'FULL' as backup_type
from msdb.dbo.backupmediafamily
inner join msdb.dbo.backupset
on backupset.media_set_id = backupmediafamily.media_set_id
where backup_start_date > getdate()-14
and physical_device_name NOT LIKE 'VDI_%'
and physical_device_name like '%BAK' --or whatever your backups are named.

And here's the full code:

if object_id('tempdb..#backup_list') is not null
drop table #backup_list;
CREATE TABLE #backup_list (id int IDENTITY, server sysname, database_name sysname, physical_device_name VARCHAR(520), backup_start_date DATETIME, file_exists BIT)
DECLARE @minid INT, @maxid int
DECLARE @does_it_exist INT
DECLARE @filename VARCHAR(500)

--using dba_rep's copy that Rodney Landrum's SSIS code pulls, get a list of the most recent backup for each db in past 2 weeks
INSERT INTO #backup_list
( server ,
database_name ,
SELECT Backup_History.server,
FROM Backup_History
SELECT server, database_name,
MAX(backup_start_date) AS max_start_date
FROM Backup_History
WHERE backup_type <>'LOG'
AND backup_start_date > GETDATE()-14
GROUP BY server, database_name
ON most_recent.SERVER = Backup_History.Server
AND most_recent.database_name = Backup_History.database_name
AND most_recent.max_start_date = Backup_History.backup_start_date
AND Backup_History.physical_device_name NOT LIKE 'SQLsafe%'
AND Backup_History.backup_type <> 'LOG'

--Fixing the names of local backups so that we can get them over the network.
UPDATE #backup_list
SET physical_device_name = REPLACE(physical_device_name,LEFT(physical_device_name,2), '\\' + LTRIM(RTRIM(server)) + '\' + LEFT(physical_device_name,1) + '$')
WHERE physical_device_name LIKE '%:%'

SELECT @minid = MIN(id) , @maxid = MAX(id) FROM #backup_list

--Walk the list, checking each file and updating the table
WHILE @minid < @maxid
SET @does_it_exist = 0

SELECT @filename = physical_device_name
FROM #backup_list
WHERE id = @minid

EXEC Master.dbo.xp_fileexist @filename, @does_it_exist OUTPUT
UPDATE #backup_list
SET file_exists = @does_it_exist
WHERE id = @minid

IF @minid % 10 = 0 PRINT @minid

SET @minid = @minid+1

SELECT * FROM #backup_list WHERE file_exists = 0

Thursday, March 3, 2011

[Tools] Convert PDF to text

Courtesy of the interwebs:

"That depends. If it is an image in the PDF, you're out of luck.
Otherwise (if it is a PDF containing text) you can do the following, all in one line, assuming osx has the strings command and a perl interpreter:"

strings filename.pdf | perl -ne '$line=$_; $s=$line; $w=""; while ($s =~ m/(\w+)(.*)/){print $line if ($w eq $1); $w=$1; $s=$2;}'