Verify Latest Database Backups – T-SQL

A common challenge is to determine when database backups were taken from our databases. As soon as there are more than three user databases, this task is not that easy to accomplish by opening the database properties dialog in SSMS and checking the Last Full/Log Backup lines on the General tab. Scripting is your friend – as always :)

A very simplistic approach is to just get the time of the latest backups taken.

SELECT d.name as DBName, 
      ISNULL(CONVERT(varchar(16), MAX(bs.backup_finish_date), 120),'------ NEVER') as LastBackup
FROM sys.databases d
LEFT OUTER JOIN msdb.dbo.backupset bs ON bs.database_name = d.name
WHERE d.name != 'tempdb'
GROUP BY d.Name
ORDER BY d.Name;

Ok, that was easy – where’s the catch? Well, what kind of backup was taken of your database at that time? Was it a full, a copy-only or just a transaction log? Oops… So, let’s RTFM and find the magic column in the backupset table: type. Typically, we’re interested in the full, incremental and log backups, when applicable. Version 2:

SELECT d.name as DBName, 
      ISNULL(CONVERT(varchar(16), bs_D.LastBackup, 120),'------ NEVER') as LastFullBackup,
      ISNULL(CONVERT(varchar(16), bs_I.LastBackup, 120),'------ NEVER') as LastDiffBackup,
      CASE WHEN d.recovery_model_desc = 'SIMPLE' THEN 'N/A' ELSE ISNULL(CONVERT(varchar(16), bs_L.LastBackup, 120),'------ NEVER') END as LastTLogBackup
FROM sys.databases d
LEFT OUTER JOIN (select database_name, max(backup_finish_date) LastBackup FROM msdb.dbo.backupset WHERE type = 'D' group by database_name) bs_D ON bs_D.database_name = d.name
LEFT OUTER JOIN (select database_name, max(backup_finish_date) LastBackup FROM msdb.dbo.backupset WHERE type = 'I' group by database_name) bs_I ON bs_I.database_name = d.name
LEFT OUTER JOIN (select database_name, max(backup_finish_date) LastBackup FROM msdb.dbo.backupset WHERE type = 'L' group by database_name) bs_L ON bs_L.database_name = d.name
WHERE d.name != 'tempdb'
ORDER BY d.Name;

Looks a bit more fancy – and useful. If you’re really picky, you may want to check the full backups whether they were copy-only backups, by filtering on the is_copy_only column. Enjoy!

SQL objektumok scriptelése Powershellben

Azt már mindenki tudja, h PS-ben lehet kiválóan scriptet generálni a SQL entitásokról a Script() metódus segítségével. Felvetődött egy kérdés vkiben, hogy hogyan lehet csak DB sémát backupolni, és felvetődött erre a Powershell, meg a get-contentitem|%{$_.Script()}, mint alapgondolat. Ja, ez ezt adja vissza egy új táblámra:

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[KeynoteDaily](
        [day] [date] NOT NULL,
        [site] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
        [measurements] [smallint] NULL,
        [failures] [smallint] NULL,
        [contenterrors] [smallint] NULL,
        [availability_wc] [real] NULL,
        [availability_woc] [real] NULL,
        [errors] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [loadtime] [smalldatetime] NULL,
        [comment] [varchar](2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

Szóval jól néz ki, nem? Nem. addig néz ki jól, amíg nem jut eszembe, hogy van primary key is a táblán. Meg még további indexek. Amiket persze megint ki lehet scriptelni: ˘

%{$_.Indexes}|%{$_.script()}

Meg még ki tudja, mi marad ki. Na mindegy, tuti megoldást nem tudok, de ez olyan lámának tűnt, hogy elkezdtem nézegetni a metódusokat, és találtam egy olyat, hogy EnumScript():

PS > gci |%{$_.EnumScript()}
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[KeynoteDaily](
        [day] [date] NOT NULL,
        [site] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
        [measurements] [smallint] NULL,
        [failures] [smallint] NULL,
        [contenterrors] [smallint] NULL,
        [availability_wc] [real] NULL,
        [availability_woc] [real] NULL,
        [errors] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [loadtime] [smalldatetime] NULL,
        [comment] [varchar](2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_KeynoteDaily] PRIMARY KEY CLUSTERED
(
        [site] ASC,
        [day] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [ix_test] ON [dbo].[KeynoteDaily]
(
        [failures] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON,
ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [ix_test2] ON [dbo].[KeynoteDaily]
(
        [measurements] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON,
ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

Vagyis ő megcsinálja nekünk a rekurziót. Szerintem aranyos kényelmi szolgáltatás.
Ja, a fentiekhez a teljes path a következő volt: PS SQLSERVER:\SQL\MYCOMPUTER\DENALI\Databases\MyDatabase\Tables> . Csak a tisztánlátás kedvéért.

Az OUTPUT clause

Ideje, hogy valami szakmait is írjak, mert kezd uncsi lenni a blog. Szóval van egy szép hosszú listám arról, hogy mi mindent nem mondtam el a Millenárisban tartott beszédemben, és ebből mazsolázgatok. Kezdjük is el!

Az első egy kicsi, de hasznos apróság: az OUTPUT clause vagy ahogy Serény tanár úr írná, a klóz. Mire jó? Tipikus jelenet, hogy kell update-elnem rekordokat egy OLTP adatbázisban, azaz az adat változik alattam, de szeretném tudni, hogy pontosan melyeket update-eltem meg. Erre a gyenge megoldás a

select primary_key into #temptabla from tabla
where status = 'NEW'

update tabla where primary_key in (select primary_key from #temptabla)
set status = 'ACTIVE'

drop table #temptabla

Ez egy egész jó megközelítés, két feltétel teljesülése esetén:

  1. Nulla konkurrencia lehetséges, azaz pl. nem akar egyszerre két alkalmazásszerver dolgozni az adatbáziban, ekkor ugyanis ők lelkesen dolgozzák fel a rekordokat – egyszerre. Ezt nem részletezném, hogy miért gáz.
  2. 2008-at vagy kevesebbet írunk. Azóta ugyanis van OUTPUT clause.

A dolog roppant egyszerű: bele van dugva egy belső trigger gyakorlatilag, ami a következőt tudja: ha beletömjük az OUTPUT-ot az INSERT/UPDATE/DELETE utasításba, akkor lesz inserted és/vagy deleted táblánk, és lehet kiszipkázni az adatokat, akár csak kiechózni, akár betenni táblaváltozókba. Az OUTPUT helye a WHERE feltétel előtt van még, erre figyeljetek. Valahogy így:

UPDATE tabla
SET status = 'ACTIVE'
OUTPUT inserted.primary_key
WHERE status = 'NEW'

Valljuk be, hogy jobban néz ki. Ha el akarjuk menteni az outputot későbbi reszeléshez vagy csak nyakonvágni a teljesítményt, akkor pedig a fenti példa így néz ki:

DECLARE @tablacska table (primary_key int);
UPDATE tabla
SET status = 'ACTIVE'
OUTPUT inserted.primary_key INTO @tablacska
WHERE status = 'NEW';
SELECT primary_key FROM @tablacska;

Ez egyébként fokozható, például ha már így előjött a konkurrencia, mint probléma, kiválóan megoldható a sok alkalmazás egy táblát túr probléma ezzel meg egy kis locking hinteléssel:

UPDATE tabla WITH (READPAST)
SET status = 'ACTIVE'
OUTPUT inserted.*
WHERE status = 'NEW'

Így ha több szerver dolgoz fel egy queue-t, hogy szépen mondjam, nem kell nagyon foglalkozniuk a másikkal. Egy konkrét szerver odamegy, kivesz annyi rekordot, amennyit akar (mert lehet TOP-ot is mondani az update-ben), ha valaki más is éppen update-el, akkor az általa lockolt rekordokat átugorja, és keres olyat, ami nincs lockolva. Ez a READPAST ajándéka: átugorja a lockolt rekordokat, így nem akadnak fenn egymáson a párhuzamos processzek.

(disclaimer: épp nincs SQL szerverem, ezért a példákat fejből írtam, ha nem működnek, bocsi, kijavítom :)

Új diszkek hozzáadása az SQL clusterhez

Diszket kellett bővíteni egy SQL clusterben, a lehető legkevesebb leállással, úgyhogy úgy döntöttem, hogy folytatom, amiben jó kezdek lenni: scriptelek. A cluster diszkek hozzáadása önmagában nem nagy szám (most felhördül legalább 28 ember, akinek már voltak rossz élményei a témában – nekem is, de a lekapcsolt többi node majdnem mindig dobott a dolgon), a jó rész az, hogy az SQL csak azokat a diszkeket használja, amiken dependál a service, a dependenciát pedig csak leállított helyzetben lehet állítgatni. Itt jön jól a script, hogy ne kelljen klikkelni, míg megöregszem. Ezúttal a cluster.exe volt a műtéti alany, az eredmény pedig alant látható (a sql12clus clustert megtámogatva):

cluster.exe sql12clus resource "SQL Server" /Offline 
cluster.exe sql12clus resource "SQL Server" /AddDependency:"K: BillingData"
cluster.exe sql12clus resource "SQL Server" /AddDependency:"L: BillingLog"
cluster.exe sql12clus resource "SQL Server" /Online

Azaz leállít, dependenciát hozzáad, elindít. Egy öröm volt.

Backup listázás PowerShellben

Az SQL 2008 R2 előadásomon a Lurdy Házban mutattam egy PS scriptet, ami a következő igényt elégíti ki: egy fájlrendszeren lakó könyvtárban lévő backup fájlokból kinyalja a backup információkat. Íme a script:

function List-Backups(
[string] $filename, ##fájl vagy könyvtárnév, dir parancs számára emészthető input kell
$Html, ## HTML fájl output neve, .\ prefix kell neki lokális esetén
$servername = '.' ## az SQL Server, aki lefuttatja a restore headeronly parancsot
) {

## a tulajdonságok, amik kellenek - teljes lista a BOL-ban
$proplist = ('Position', 'DatabaseName','ServerName','BackupFinishDate','DatabaseVersion')
## mint fent, csak a filename mezővel prefixeltem. igénytelen, de gyors megoldás, 
$proplist2 = ('FileName', 'Position', 'DatabaseName','ServerName','BackupFinishDate','DatabaseVersion')

## ez a tömb tárolja a teljes listát
$allbck = @()

foreach ($file in (dir $filename)) ## ezért kellett dir kompatibilis input
  {
  ## lefut a restore headeronly...
  $backups = invoke-sqlcmd -ServerInstance ".\mysql" -Query "restore headeronly from disk = '$($file.FullName)'"
  $bck = @()
  ## a kiválasztott mezoket eltesszük a $bck változóba. 
  ##   Igazából perzisztált scriptben nem illik olyan shortcutokat használni, mint % (foreach) meg ? (where)
  $backups|select $proplist|%{$bck += $_}
  ## hozzáadjuk a fájlnevet is...
  $bck |add-member noteproperty filename $file.FullName
  ## ... és a kibövitett listát eltesszük az összegzett listába
  $allbck += $bck|select $proplist2
  } #foreach $file
## kiírjuk az összegzett listát a standard outputra - ez a függvény kimenete
$allbck
## ha meg lett adva HTML fájlnév, akkor abba is kitoljuk a táblázatot, és meg is nyitjuk. Itt lehetne kezelni a .\ hiányát, de nem tettem, lustaságból
if ($Html) 
  {
  $allbck|ConvertTo-Html|Out-file $Html
  ## itt nyitjuk meg, az alkalmazás a default app a megadott kiterjesztéshez, tehát a böngészőnk htm(l)-nél
  invoke-expression $Html
  } #if $html
}  #function List-Backups

## És nézzünk néhány példát is:

## nézzük meg a D:\SQLData\Backups könyvtár tartalmát, HTML-ben is
List-Backups D:\SQLData\Backups .\lista.html
## nézzünk meg egyetlen fájlt a konzolon 
List-Backups D:\SQLData\Backups\Full.BAK
##keressük meg a Billing adatbázis legfrissebb full backupját tartalmazó fájlt és a backup pozícióját a megadott könyvtárban
List-Backups D:\SQLData\Backups | ?{$_.DatabaseName -eq 'Billing' -and $_.BackupTypeDescription -eq 'DATABASE'} | `
 Sort -prop BackupFinishDate -desc| select Filename, Position -first 1

Az utolsó parancsba beletoltam több mindent is. A ? (where) a szűrést hivatott bemutatni, a -eq és az -and a vicces operátorokat, amit el is rontottam a demóban, a backtick a többsoros parancs sorvégét jelzi, a többi meg magától értetődő, de azért jó látni, hogy a lehetőségek szinte korlátlanok.