Lista backupów bazy MsSQL

Poniższe zapytanie wyświetla wykonane w ostatnim czasie backupy baz naszej instancji

SELECT
substring(CONVERT(CHAR(100), 'TEST-Server'),1,20) AS Server,
substring(msdb.dbo.backupset.database_name,1,25) as datatabse_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
msdb.dbo.backupset.expiration_date,
CASE msdb..backupset.type
WHEN 'D' THEN 'Database'
WHEN 'L' THEN 'Log'
END AS
backup_type,
msdb.dbo.backupset.backup_size,
substring(msdb.dbo.backupmediafamily.logical_device_name,1,20) as logical_dev_name,
substring(msdb.dbo.backupmediafamily.physical_device_name,1,40) as physical_dev_name,
substring(msdb.dbo.backupset.name,1,20) AS backupset_name,
substring(msdb.dbo.backupset.description,1,20) as description
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id =
msdb.dbo.backupset.media_set_id
WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date,
102) >= GETDATE() - 7)
ORDER BY
msdb.dbo.backupset.backup_finish_date desc

Server               datatabse_name            backup_start_date       backup_finish_date      expiration_date         backup_type backup_size                             logical_dev_name     physical_dev_name                        backupset_name       description
-------------------- ------------------------- ----------------------- ----------------------- ----------------------- ----------- --------------------------------------- -------------------- ---------------------------------------- -------------------- --------------------
TEST-Server          ZWR                       2017-11-15 20:24:22.000 2017-11-15 20:26:04.000 NULL                    Database    5582769152                              NULL                 VNBU0-14676-276-1510773845               NULL                 NULL
TEST-Server          TELP                      2017-11-15 20:23:59.000 2017-11-15 20:24:00.000 NULL                    Database    15148032                                NULL                 VNBU0-14676-18152-1510773822             NULL                 NULL
TEST-Server          SCRE                      2017-11-15 20:22:34.000 2017-11-15 20:23:37.000 NULL                    Database    3311795200                              NULL                 VNBU0-14676-15376-1510773738             NULL                 NULL
...
TEST-Server          AVA_PTK                   2017-11-09 18:06:16.000 2017-11-09 18:06:17.000 NULL                    Database    51852288                                NULL                 VNBU0-13596-9328-1510247160              NULL                 NULL
TEST-Server          AVA_DPD                   2017-11-09 18:05:35.000 2017-11-09 18:05:55.000 NULL                    Database    342338560                               NULL                 VNBU0-13596-5340-1510247119              NULL                 NULL
TEST-Server          AVA                       2017-11-09 18:00:42.000 2017-11-09 18:05:13.000 NULL                    Database    8517797888                              NULL                 VNBU0-13596-428-1510246826               NULL                 NULL

(210 row(s) affected)

Jeśli byśmy chcieli sprawdzić kiedy był wykonany ostatni backup pełny, różnicowy lub backup logów uruchamiamy zapytanie:

use msdb
go
-- D = Full, I = Differential and L = Log.
SELECT bks.database_name ,
adb.recovery_model_desc,
MAX(CASE WHEN bks.type = 'D' THEN bks.backup_finish_date ELSE NULL END) AS LastFullBackup,
MAX(CASE WHEN bks.type = 'I' THEN bks.backup_finish_date ELSE NULL END) AS LastDifferential,
MAX(CASE WHEN bks.type = 'L' THEN bks.backup_finish_date ELSE NULL END) AS LastLog
FROM backupset as bks
JOIN sys.databases as adb ON bks.database_name = adb.name
GROUP BY bks.database_name, adb.recovery_model_desc
ORDER BY bks.database_name ASC

database_name			recovery_model		LastFullBackup          LastDifferential        LastLog
-----------------------------------------------------------------------------------------------------------
AdventureWorks2012		SIMPLE			2020-11-25 20:40:46.000 	NULL		NULL
AdventureWorksDW2012		SIMPLE			2020-11-25 20:40:35.000 	NULL		NULL
AdventureWorksLT2012		SIMPLE			2020-11-25 20:40:10.000 	NULL		NULL
Database1			SIMPLE			2020-12-07 17:01:38.000 	NULL		NULL
model				SIMPLE			2020-01-19 14:38:07.000 	NULL		NULL
Nauka_CSharp			FULL			2020-12-03 08:34:46.000 	NULL		2020-12-01 20:16:39.000
Nauka_SSIS			FULL			2020-12-03 08:34:56.000 	NULL		2020-12-03 08:33:31.000
Northwind			FULL			2020-11-22 13:14:14.000 	NULL		2019-02-07 23:13:49.000
SSISDB				SIMPLE			2020-11-22 13:11:53.000 	NULL		NULL
TSQL2012			SIMPLE			2020-11-25 20:39:57.000 	NULL		NULL
Warning: Null value is eliminated by an aggregate or other SET operation.

(10 rows affected)
Zaszufladkowano do kategorii Microsoft SQL | Otagowano | Dodaj komentarz

Zdublowane wiersze w tabeli

Czasami się zdarza że w tabeli jest sporo takich samych wiersz które niepotrzebnie zaśmiecają tabelę, a czasami powodują „błędy” podczas odczytu danych. Dziś w pracy się spotkałem z taką tabelą gdzie przez pomyłkę został uruchomiony skrypt z insertami do tabeli wrzucający kilkaset wierszy. Dziś pokażę jak w prosty sposób ich się pozbyć pozostawiając unikalne niezdublowane wiersze.

Na początek stworzymy sobie prostą tabelę zawierającą trzy kolumny.

USE [Database1]
GO
CREATE TABLE [dbo].[duplikaty](
[randomID] [int] NULL,
[nazwa] [nchar](10) NULL,
[region] [nchar](10) NULL
) ON [PRIMARY];
GO

W pierwszej kolumnie będzie wpisana losowa liczba w zakresie 0-10. W drugiej i trzeciej kolumnie będzie string połączony w losową liczbą od 1 do 10. A więc napełniamy tabelę 10.000 wierszami

DECLARE @intCounter as INT = 1;
DECLARE @naz1 as INT = 1;
DECLARE @reg1 as INT = 1;
WHILE @intCounter <= 10000 BEGIN SET @naz1 = ROUND(rand()*10,0); SET @reg1 = ROUND(rand()*10,0); --PRINT 'Bieżąca wartość licznika to: '+ CAST(@intCounter AS VARCHAR); INSERT INTO [dbo].[duplikaty]([randomID],[nazwa],[region]) VALUES (ROUND(rand()*10,0),'nazwa'+CAST(@naz1 as nvarchar(10)),'region'+CAST(@reg1 as nvarchar(10))) SET @IntCounter = @IntCounter + 1; END;

Sprawdzamy czy dane się zapisały

select * from [Database1].[dbo].[duplikaty] order by randomID, nazwa;

randomID    nazwa      region
----------- ---------- ----------
0           nazwa0     region3   
0           nazwa0     region7   
0           nazwa0     region0   
0           nazwa0     region4     
...
10          nazwa9     region3   
10          nazwa9     region6   
10          nazwa9     region8   
10          nazwa9     region4   

(10000 rows affected)

W tabeli jest 10.000 wierszy ale z pewnością wiele wierszy się powtarza. Sprawdzamy ile jest unikalnych wierszy

select DISTINCT * from [Database1].[dbo].[duplikaty] order by randomID, nazwa;

randomID    nazwa      region
----------- ---------- ----------
0           nazwa0     region3   
0           nazwa0     region7   
0           nazwa0     region0   
0           nazwa0     region4     
...
10          nazwa9     region3   
10          nazwa9     region6   
10          nazwa9     region8   
10          nazwa9     region4   

(1316 rows affected)

Tak więc czeka nas usunięcie ponad 8.684 wierszy

Możemy jeszcze sobie sprawdzić ile jest wierszy dla każdego randomID

SELECT randomID, COUNT(*) as count
FROM [Database1].[dbo].[duplikaty]
GROUP BY randomID
order by randomID;

randomID    count
----------- -----------
0           488
1           993
2           1024
3           949
4           982
5           1028
6           984
7           1055
8           1004
9           1030
10          463

(11 rows affected)

Poniższe zapytanie przypisuje każdemu wierszowi kolejny numer. W przypadku zdublowanych wierszy będą to kolejne liczby np

10          nazwa9     region9    1
10          nazwa9     region9    2
10          nazwa9     region9    3 

SELECT randomID,nazwa,region,
ROW_NUMBER() OVER(PARTITION BY randomID,nazwa,region
ORDER BY randomID) AS DuplicateCount
FROM [Database1].[dbo].[duplikaty];

randomID    nazwa      region     DuplicateCount
----------- ---------- ---------- --------------------
0           nazwa0     region0    1
0           nazwa0     region1    1
0           nazwa0     region1    2
0           nazwa0     region10   1
0           nazwa0     region10   2
0           nazwa0     region2    1
...
10          nazwa9     region8    3
10          nazwa9     region8    4
10          nazwa9     region8    5
10          nazwa9     region9    1
10          nazwa9     region9    2
10          nazwa9     region9    3

(10000 rows affected)

naszym zadaniem będzie teraz usunięcie wszystkich wierszy z DuplicateCount większym niż 1. Przed uruchomieniem DELETE najpierw sprawdzamy co nam wykona polecenie (dobra praktyka!)

WITH CTE(randomID,nazwa,region,duplicatecount)
AS (SELECT randomID,nazwa,region,
ROW_NUMBER() OVER(PARTITION BY randomID,nazwa,region
ORDER BY randomID) AS DuplicateCount
FROM [Database1].[dbo].[duplikaty])
SELECT * FROM CTE WHERE DuplicateCount > 1;

randomID    nazwa      region     duplicatecount
----------- ---------- ---------- --------------------
0           nazwa0     region1    2
0           nazwa0     region10   2
0           nazwa0     region2    2
0           nazwa0     region2    3
0           nazwa0     region3    2
...
10          nazwa9     region8    4
10          nazwa9     region8    5
10          nazwa9     region9    2
10          nazwa9     region9    3

(8684 rows affected)

Select wzrócił nam 8683 wiersze do usunięcia. Zamieniamy SELECT na DELETE i uruchamiamy polecenia

WITH CTE(randomID,nazwa,region,duplicatecount)
AS (SELECT randomID,nazwa,region,
ROW_NUMBER() OVER(PARTITION BY randomID,nazwa,region
ORDER BY randomID) AS DuplicateCount
FROM [Database1].[dbo].[duplikaty])
DELETE FROM CTE WHERE DuplicateCount > 1;

(8684 rows affected)

Sprawdzamy ile wierszy zostało w tabeli:

select COUNT(*) as [REKORDY] from [Database1].[dbo].[duplikaty];

REKORDY
-----------
1316

(1 row affected)

Sprawdzamy czy nie ma dwóch takich samych wierszy (powinno zwrócić tyle samo co powyższe zapytanie):

select DISTINCT * from [Database1].[dbo].[duplikaty];

randomID    nazwa      region
----------- ---------- ----------
0           nazwa0     region0   
0           nazwa0     region1   
0           nazwa0     region10  
0           nazwa0     region2   
0           nazwa0     region3
...
10          nazwa9     region6   
10          nazwa9     region7   
10          nazwa9     region8   
10          nazwa9     region9   

(1316 rows affected)

Czyszczenie tabeli zostało zakończone poprawnie. Oczywiście można to samo zrobić na kilka innych sposobów m.in. wykorzystując wspomniane wcześniej narzędzia Integration Services (SSIS).

Nie będę tu już opisywał jak stworzyć za pomocą Visual Studio taki projekt. Możecie to znaleźć na wcześniejszych wpisach na mojej stronie.
Do naszego projektu wrzucamy DataFlowTask.

W środku tworzymy OLEDBSource

Konfigurujemy połączenie do naszej bazy i tabeli ze zdublowanymi wierszami

W projekcie do OLEDBSource (który powinien już nie "świecić" się na czerwono) podłączamy element SORT (on się jeszcze świeci na czerwono bo go jeszcze nie skonfigurowaliśmy).

W konfiguracji SORT'a na dole ekranu zaznaczamy opcję "Remove rows with duplicate sort values"

Podłączamy element Delivered Column

A na ich połączeniu wstawiamy "Data Viewer" który pozwoli nam na podglądnięcie przesyłanych danych.

Uruchamiamy nasz projekt

Pojawia się tabelka z 1316 unikalnymi wierszami naszej tabeli

Taką samą ilość wierszy widzimy na schemacie przepływu w naszym projekcie.

Zaszufladkowano do kategorii Microsoft SQL, SSIS | Dodaj komentarz

Prosty ( i szybki) sposób na sprawdzenia połączenia z bazą MSSQL

Nie zawsze mamy zainstalowanego Management Studio by sprawdzić czy możemy się podłączyć do bazy. Jest na to prosty sposób.
Na komputerze z którego chcemy przetestować nasze połączenie z bazą tworzymy pusty plik tekstowy (może być na pulpicie :))

Następnie zmieniamy nu rozszerzenie pliku z TXT na UDL. System spyta się nas czy na pewno tego chcemy 😉

Plik UDL to nic innego niż plik konfiguracyjny połączenia do bazy za pomocą OLE DB, który wskazuje na zewnętrzną tabelę bazy danych.

Mamy gotowy plik w którego klikamy dwa razy myszką. Otwiera się nam okienko w które wpisujemy nazwę naszego serwer. Wybieramy też sposób autentykacji.

Jeżeli możemy wybrać z listy bazę danych to już świadczy o tym że połączenie się udało.

Mozemy też kliknąć na przycisk „Testuj połączenie”

Jak widać wszystko jest OK i połączenie pomiędzy naszym komputerem a bazą działa prawidłowo.

Jakbyśmy podejrzeli zawartość pliku UDL w notatniku zobaczymy, że to prosty ciąg definiujący połączenie do naszyj bazy.

 [oledb]
; Everything after this line is an OLE DB initstring
Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Nauka_SSIS;Data Source=MICEK1968
Zaszufladkowano do kategorii Microsoft SQL | Dodaj komentarz

Import danych z MSSQL do pliku Excela

Czasami jest potrzeba zaimportowania danych z tabeli w bazie danych do pliku Excel. Można to zrobić na kilka sposobów. Wykorzystujemy do tego m.in. narzędzia Integration Services przedstawione w jednym z artykułów. Ja przedstawię dziś inny sposób polegający na wczytanie danych bezpośrednio do arkusza kalkulacyjnego. Połączenie będzie na stałe zdefiniowane co pozwoli na odczyt zawsze aktualnych danych przy każdorazowym otwarciu arkusza.

Otwieramy nowy plik Excela.

Po jego otwarciu z górnego menu przechodzimy do opcji DANE a następnie wybieramy „Z innych źródeł” a następnie „z programu SQLServer”.

Wpisujemy nazwę naszego serwera i wybieramy odpowiedni sposób uwierzytelnienia.

Na następnym ekranie wybieramy bazę i tabelę.

Zapisywanie pliku łączenia danych do pliku ODC.

Importowanie danych. Ustawiamy czy to ma być tabela, raport przestawny, wykres przestawny czy tylko mamy stworzyć plik połączenia do bazy. Wybieramy istniejący arkusz lub możemy wyeksportować dane do nowego arkusza.

Wchodząc we właściwości możemy poustawiać jeszcze parę rzeczy dotyczących naszego połączenia do bazy m.in. co ile ma być odświerzany nasz arkusz oraz czy mają być wczytane dane z bazy podczas otwierania arkusza.

Po zatwierdzeniu dane zostają wczytane do arkusza kalkulacyjnego.

Dane możemy „ręcznie” odświerzyć klikając w przycisk.

Lub otworzyć właściwości naszego połączenia.

I zaznaczyć automatyczne połączenie przy otwieraniu arkusza.

W zakładce Definicja mamy ścieżkę do plików konfiguracyjnych naszych połączeń . Dla programistów np w c# znajdziemy tu Conenction String do połączenia się z naszą bazą (okno „Parametry połączenia”)

Jesli zajrzymy do folderu w folderach naszego użytkownika znajdziemy wspomniane wcześniej pliki konfiguracyjne naszych połączeń do baz.

Zaszufladkowano do kategorii Microsoft SQL | Dodaj komentarz

XACT_ABORT

Stwórzmy bazę TEST, a następnie tabelę TABLE_TEST składają się z dwóch kolumn : ID i nazwy oraz z kluczem publicznym na kolumnie ID.

CREATE DATABASE [TEST]

Commands completed successfully.

USE [TEST]
GO
CREATE TABLE [dbo].[TABLE_TEST](
[id] [int] NOT NULL,
[nazwa] [nchar](10) NULL,
CONSTRAINT [PK_TableTest] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Commands completed successfully.

Następnie spróbujmy wykonać polecenie:

BEGIN TRANSACTION
INSERT INTO TABLE_TEST (ID,nazwa) VALUES(1,'rower');
INSERT INTO TABLE_TEST (ID,nazwa) VALUES(1,'wrotki');
COMMIT TRANSACTION;

(1 row affected)
Msg 2627, Level 14, State 1, Line 22
Violation of PRIMARY KEY constraint 'PK_TableTest'. 
Cannot insert duplicate key in object 'dbo.TABLE_TEST'. 
The duplicate key value is (1).
The statement has been terminated.

Jako że dane w kolumnie ID muszą być unikalne, zwróciło błąd jednak pierwsza operacja INSERT została zapisana do tabeli.

SELECT * FROM [TEST].[dbo].[TABLE_TEST]

id          nazwa
----------- ----------
1           rower     

(1 row affected)

Jest tak ponieważ domyślnie transakcje nie jest anulowane w przypadku wystąpienia błędów w trakcie jej wykonywania, tylko jest kontynuowana. Aby temu zapobiec należy ustawić zmienną XACT_ABORT na wartość ON:

select 16384 & @@OPTIONS as '16384_@OPTIONS'

16384_@OPTIONS
--------------
0

(1 row affected)

Ustawiamy zmienną XACT_ABORT na wartość ON.

SET XACT_ABORT ON;

Commands completed successfully.

Teraz wyczyścimy naszą tabelę z danych

TRUNCATE TABLE TEST.TABLE_TEST

Commands completed successfully.

Ponownie wykonujemy nasze polecenie INSERT

BEGIN TRANSACTION
insert into TABLE_TEST (ID,nazwa) values(1,'rower');
insert into TABLE_TEST (ID,nazwa) values(1,'wrotki');
COMMIT TRANSACTION;

(1 row affected)
Msg 2627, Level 14, State 1, Line 21
Violation of PRIMARY KEY constraint 'PK_TableTest'. 
Cannot insert duplicate key in object 'dbo.TABLE_TEST'. 
The duplicate key value is (1).

Zwróciło nam błąd, ale dane nie zostały zapisane do tabeli

SELECT * FROM [TEST].[dbo].[TABLE_TEST]

id          nazwa
----------- ----------

(0 rows affected)

XACT_ABORT powoduje, że w przypadku wystąpienia błędu cała transakcja jest anulowana (nastąpi rollback).
Oczywiście działa to tylko w przypadku stosowanie opcji transakcji BEGIN/COMMIT.
Jeśli wykonamy INSERT pojedynczo pierwszy zostanie zapisany do tabeli

insert into TABLE_TEST (ID,nazwa) values(1,'rower');
insert into TABLE_TEST (ID,nazwa) values(1,'wrotki');

(1 row affected)
Msg 2627, Level 14, State 1, Line 21
Violation of PRIMARY KEY constraint 'PK_TableTest'. 
Cannot insert duplicate key in object 'dbo.TABLE_TEST'. 
The duplicate key value is (1).

SELECT * FROM [TEST].[dbo].[TABLE_TEST]

id          nazwa
----------- ----------
1           rower     

(1 row affected)
Zaszufladkowano do kategorii Microsoft SQL | Dodaj komentarz

Backup i odtworzenie bazy z wykorzystaniem znacznika (mark)

Odtworzenie bazy możemy wykonać z backupu pełnego lub do punktu w czasie z backupu logów transakcyjnych. Dziś pokaże jak odzyskać do punktu (mark) który „zaznaczymy podczas naszych prac na bazie. Pozwala to np na uruchomienie znacznika a następnie odtworzenie kilku zależnych baz do konkretnego punktu aby bazy były konsystentne między sobą.

Na początku utworzymy nową bazę i stworzymy prostą tabelę.

CREATE DATABASE BACKUP_MARK
CREATE TABLE [BACKUP_MARK].[dbo].[TABELA]([random] [int] NULL)

Commands completed successfully.

Baza powinna być w trybie Full Recovery. Wykonamy pełny backup pustej bazy

BACKUP DATABASE [BACKUP_MARK] TO DISK = N'C:\SQLBackup\BACKUP_MARK.bak'
WITH NOFORMAT, INIT, NAME = N'BACKUP_MARK-Full Database Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

10 percent processed.
20 percent processed.
31 percent processed.
41 percent processed.
50 percent processed.
60 percent processed.
71 percent processed.
81 percent processed.
90 percent processed.
Processed 376 pages for database 'BACKUP_MARK', file 'BACKUP_MARK' on file 1.
100 percent processed.
Processed 5 pages for database 'BACKUP_MARK', file 'BACKUP_MARK_log' on file 1.
BACKUP DATABASE successfully processed 381 pages in 0.082 seconds (36.299 MB/sec).

oraz backup logów

BACKUP LOG [BACKUP_MARK] TO DISK = N'C:\SQLBackup\BACKUP_MARK.bak'
WITH NOFORMAT, NOINIT, NAME = N'BACKUP_MARK-Log Database Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

57 percent processed.
100 percent processed.
Processed 14 pages for database 'BACKUP_MARK', file 'BACKUP_MARK_log' on file 2.
BACKUP LOG successfully processed 14 pages in 0.017 seconds (6.433 MB/sec).

Teraz wykonamy proste inserty do tabeli.

Najpierw uruchamiamy nasz znacznik (transakcję)

USE [BACKUP_MARK]
GO
BEGIN TRANSACTION TableInsert1
WITH MARK 'Insert do tabeli';

Commands completed successfully.

Teraz wykonujemy inserty. W naszym przypadku będzie to 499 losowych liczb.

DECLARE @Counter int;
SET @Counter = 1;
WHILE @Counter < 10 BEGIN Skok: --SELECT @Counter SET @Counter = @Counter + 1 INSERT INTO [BACKUP_MARK].[dbo].[TABELA] ([random]) VALUES (FLOOR(RAND()*100000)) IF @Counter = 500 GOTO Branch_Two goto SKOK END Branch_Two:

Zamykamy naszą transakcję.

COMMIT TRANSACTION TableInsert1;
GO

Commands completed successfully.

Zapisany znacznik możemy sprawdzić zapytaniem

SELECT * FROM msdb.[dbo].[logmarkhistory]

database_name      mark_name        description          user_name              lsn                  mark_time
----------------------------------------------------------------------------------------------------------------------------
BACKUP_MARK        TableInsert1     Insert do tabeli     MICEK1968\micek1968    37000000036000531    2020-01-23 19:46:32.820

(1 row affected)

Aby sprawdzić jaki ostatni LSN został zapisany w backupie bazy

USE [BACKUP_MARK]
SELECT last_log_backup_lsn
FROM sys.database_recovery_status
WHERE database_id = DB_ID()

last_log_backup_lsn
---------------------------------------
37000000032800001

(1 row affected)

Sprawdzamy ile wierszy się zapisało w tabeli

SELECT count(*)
FROM [BACKUP_MARK].[dbo].[TABELA]

-----------
499

(1 row affected)

Wykonujemy następne 499 insertów

DECLARE @Counter int;
SET @Counter = 1;
WHILE @Counter < 10 BEGIN Skok: --SELECT @Counter SET @Counter = @Counter + 1 INSERT INTO [BACKUP_MARK].[dbo].[TABELA] ([random]) VALUES (FLOOR(RAND()*100000)) IF @Counter = 500 GOTO Branch_Two goto SKOK END Branch_Two:

Sprawdzamy ilość wierszy

SELECT count(*)
FROM [BACKUP_MARK].[dbo].[TABELA]

-----------
998

(1 row affected)

Wykonujemy kolejny backup logów naszej bazy

BACKUP LOG [BACKUP_MARK] TO DISK = N'C:\SQLBackup\BACKUP_MARK.bak'
WITH NOFORMAT, NOINIT, NAME = N'BACKUP_MARK-Log Database Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

11 percent processed.
20 percent processed.
31 percent processed.
40 percent processed.
52 percent processed.
60 percent processed.
72 percent processed.
81 percent processed.
92 percent processed.
100 percent processed.
Processed 259 pages for database 'BACKUP_MARK', file 'BACKUP_MARK_log' on file 3.
BACKUP LOG successfully processed 259 pages in 0.038 seconds (53.145 MB/sec).

I następne 499 inserty

DECLARE @Counter int;
SET @Counter = 1;
WHILE @Counter < 10 BEGIN Skok: --SELECT @Counter SET @Counter = @Counter + 1 INSERT INTO [BACKUP_MARK].[dbo].[TABELA] ([random]) VALUES (FLOOR(RAND()*100000)) IF @Counter = 500 GOTO Branch_Two goto SKOK END Branch_Two:

I ponownie sprawdzamy ilość wierszy w tabeli

SELECT count(*)
FROM [BACKUP_MARK].[dbo].[TABELA]

 -----------
1497

(1 row affected)

I kolejny backup logów

BACKUP LOG [BACKUP_MARK] TO DISK = N'C:\SQLBackup\BACKUP_MARK.bak'
WITH NOFORMAT, NOINIT, NAME = N'BACKUP_MARK-Log Database Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

12 percent processed.
22 percent processed.
31 percent processed.
41 percent processed.
50 percent processed.
60 percent processed.
73 percent processed.
82 percent processed.
92 percent processed.
100 percent processed.
Processed 251 pages for database 'BACKUP_MARK', file 'BACKUP_MARK_log' on file 4.
BACKUP LOG successfully processed 251 pages in 0.035 seconds (56.026 MB/sec).

Pora odzyskać naszą bazę. Interesuje nas punkt kiedy oznaczyliśmy znacznikiem (zakończyliśmy transakcję)

Wcześniej poznaliśmy numer LSN odpowiadającemu naszemu znacznikowi (LSN=37000000036000531)

Możemy sprawdzić z których plików backupu będzimy korzystać przy odzyskiwaniu bazy

use msdb
go
select s.backup_set_id,
s.first_lsn,
s.last_lsn,
s.database_name,
s.backup_start_date,
s.backup_finish_date,
s.type,
f.physical_device_name
from backupset s join backupmediafamily f
on s.media_set_id = f.media_set_id
WHERE s.database_name = 'BACKUP_MARK'
and first_lsn < 37000000036000531 order by s.backup_finish_date

backup_set_id   first_lsn             last_lsn              database_name     backup_start_date          backup_finish_date         type   physical_device_name
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1148            37000000021600170     37000000030400001     BACKUP_MARK       2020-01-23 19:46:16.000    2020-01-23 19:46:16.000    D      C:\SQLBackup\BACKUP_MARK.bak
1149            37000000021600170     37000000032800001     BACKUP_MARK       2020-01-23 19:46:20.000    2020-01-23 19:46:20.000    L      C:\SQLBackup\BACKUP_MARK.bak
1150            37000000032800001     38000000051200001     BACKUP_MARK       2020-01-23 19:46:48.000    2020-01-23 19:46:48.000    L      C:\SQLBackup\BACKUP_MARK.bak

(3 rows affected)

Jak widać będziemy potrzebowali pliku z backupem pełnym i dwa backupy logów

Przypominam że wykonaliśmy w trakcie naszych prac:
-pełny backup bazy (backup File=1)
-backup logów (backup File=2) - 0 wierszy w tabeli
-inserty do tabeli (499 wierszy)
-ustawiliśmy znacznik (TableInsert1)
-wykonaliśmy kolejne 499 insertów
-backup logów (backup File=3) - 998 wierszy
-inserty do tabeli (499 wierszy)
-backup logów (backup File=4) - 1497 wierszy

Tak więc aby odzyskać bazę do punktu oznaczenia naszego znacznika musimy odzyskać bazę z pełnego backupu (File=1) a następnie z backupu logów (File=2 i File=3). Nasz znacznik jest zbackupowany w backupie oznaczonym jako File=3

najpierw przestawimy bazę w tryb Single User
USE [master]
ALTER DATABASE [BACKUP_MARK] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Commands completed successfully.

Wykonujemy backup obecnych logów.
BACKUP LOG [BACKUP_MARK]
TO DISK = N'C:\SQLBackup\BACKUP_MARK_LogBackup_2020-01-23_19-26-06.bak'
WITH NOFORMAT, NOINIT, NAME = N'BACKUP_MARK_LogBackup_2020-01-23_19-26-06',
NOSKIP, NOREWIND, NOUNLOAD, NORECOVERY , STATS = 5

100 percent processed.
Processed 4 pages for database 'BACKUP_MARK', file 'BACKUP_MARK_log' on file 2.
BACKUP LOG successfully processed 4 pages in 0.017 seconds (1.838 MB/sec).

Teraz odtwarzamy naszą bazę z pełnego logu

RESTORE DATABASE [BACKUP_MARK] FROM DISK = N'C:\SQLBackup\BACKUP_MARK.bak'
WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5

6 percent processed.
10 percent processed.
16 percent processed.
20 percent processed.
25 percent processed.
31 percent processed.
35 percent processed.
41 percent processed.
46 percent processed.
50 percent processed.
56 percent processed.
60 percent processed.
65 percent processed.
71 percent processed.
75 percent processed.
81 percent processed.
86 percent processed.
90 percent processed.
96 percent processed.
100 percent processed.
Processed 376 pages for database 'BACKUP_MARK', file 'BACKUP_MARK' on file 1.
Processed 5 pages for database 'BACKUP_MARK', file 'BACKUP_MARK_log' on file 1.
RESTORE DATABASE successfully processed 381 pages in 0.774 seconds (3.845 MB/sec).

Spójrzmy co będzie jak będziemy chcieli uruchomić odtwarzanie od razu z ostatniego backupu (File=3) podając opcję STOPMARK i wpisując nazwę naszej transakcji (marka)

RESTORE LOG [BACKUP_MARK]
FROM DISK = N'C:\SQLBackup\BACKUP_MARK.bak'
WITH FILE = 3,
RECOVERY,
STOPATMARK = 'TableInsert1';

Msg 4305, Level 16, State 1, Line 236
The log in this backup set begins at LSN 89000000544800001, 
which is too recent to apply to the database.
 An earlier log backup that includes LSN 89000000542400001 can be restored.
Msg 3013, Level 16, State 1, Line 236
RESTORE LOG is terminating abnormally.

Jak widać SQL zwrócił nam błąd związany z błędnym numerem sekwencji (LSN). Musimy bazę odtworzyć kolejno z naszych backupów logów. Wykonywaliśmy je ręcznie i wiemy na którym pliku backupu logu mamy skończyć ale równie dobrze backup może wykonywać się automatycznie więc będziemy wykonywać odtworzenie do czasu prawidłowego zakończenia procesu.

Zaczynamy od pliku backupu logu File=2
RESTORE LOG [BACKUP_MARK]
FROM DISK = N'C:\SQLBackup\BACKUP_MARK.bak'
WITH FILE = 2,
RECOVERY,
STOPATMARK = 'TableInsert1';

Processed 0 pages for database 'BACKUP_MARK', file 'BACKUP_MARK' on file 2.
Processed 12 pages for database 'BACKUP_MARK', file 'BACKUP_MARK_log' on file 2.
This log file contains records logged before the designated mark. 
The database is being left in the Restoring state so you can apply another log file.
RESTORE LOG successfully processed 12 pages in 0.013 seconds (6.911 MB/sec).

Potrzebny jest kolejny plik backupu logów aby odtworzyć bazę do punktu naszego znacznika. Odtwarzamy kolejny plik backupu logów (File=3)

RESTORE LOG [BACKUP_MARK]
FROM DISK = N'C:\SQLBackup\BACKUP_MARK.bak'
WITH FILE = 3,
RECOVERY,
STOPATMARK = 'TableInsert1';

Processed 0 pages for database 'BACKUP_MARK', file 'BACKUP_MARK' on file 3.
Processed 259 pages for database 'BACKUP_MARK', file 'BACKUP_MARK_log' on file 3.
RESTORE LOG successfully processed 259 pages in 0.034 seconds (59.397 MB/sec).

Odzyskanie się zakończyło. Komunikat zwrócił informację o poprawnym odtworzniu bazy. Próby kolejnego odtwarzania plików backupu logów zwrócą nam błąd

RESTORE LOG [BACKUP_MARK]
FROM DISK = N'C:\SQLBackup\BACKUP_MARK.bak'
WITH FILE = 4,
RECOVERY,
STOPATMARK = 'TableInsert1';

Msg 3117, Level 16, State 1, Line 252
The log or differential backup cannot be restored because no files are ready to rollforward.
Msg 3013, Level 16, State 1, Line 252
RESTORE LOG is terminating abnormally.

Tak więc bazę mamy odtworzoną. Powinno być 499 wierszy.

SELECT count(*)
FROM [BACKUP_MARK].[dbo].[TABELA]

-----------
499

(1 row affected)

Jak widać baza odzyskała się prawidłowo.

Po oznaczeniu znacznikiem bazy sprawdziliśmy numer LSN odpowiadający naszemu znacznikowi. Odtwarzając bazę możemy skorzystać też z tego numeru LSN (zamiast nazwy znacznika).

RESTORE LOG AdventureWorks2 FROM DISK = 'C:\SQLBackup\BACKUP_MARK.bak'
WITH STOPATMARK = 'lsn:37000000036000531'
GO

Na koniec czyścimy bazy...

USE master
EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'A1'
GO
DROP DATABASE A1
GO

Zaszufladkowano do kategorii Microsoft SQL | Dodaj komentarz

PowerShell na usługach SQL – podstawowe funkcje

Sprawdzenie wersji SQL-a

Get-SQLInstance -ServerInstance "localhost"

Lista baz w instancji

Get-SQLDatabase -ServerInstance "localhost"

Lista userów w instancji

Get-SQLLogin -ServerInstance "localhost"

Lista backupów baz danych

Get-SQLBackupHistory -ServerInstance "localhost"

Odczyt widoku

Read-SqlViewData -ServerInstance "localhost" -DatabaseName "AdventureWorks2012" -SchemaName "Sales" -ViewName "vSalesPerson" -ColumnName "FirstName","LastName" -TopN 10

Odczyt tabeli (Person.Person) w bazie danych (AdventureWorks2012)

Read-SqlTableData -ServerInstance "localhost" -DatabaseName "AdventureWorks2012" -SchemaName "Person" -TableName "Person" -TopN 3

Wykonanie T-SQL-a

Invoke-Sqlcmd -Query "SELECT GETDATE() AS TimeOfQuery;" -ServerInstance "localhost"

źródło: https://docs.microsoft.com/en-us/powershell/module/sqlps/Backup-SqlDatabase?view=sqlserver-ps

Zaszufladkowano do kategorii Microsoft SQL, SQL PowerShell | Dodaj komentarz

PowerShell na usługach SQL – konfiguracja

Tak jak do innych celów również do SQL-a możemy wykorzystać powłoki PowerShell.

W management Studio klikamy w nasz serwer i uruchamiamy „Start PowerShell”.

Niestety nie został zainstalowany pakiet SQLServera więc konsolka nam się nie otworzy.

Mozemy kliknąć YES i wtedy uruchomi się nam konsola bez obsługi SQL-a lub „Open PowerShell Gallery” co spowoduje otwarcie strony z dodatkiem SQL do PowerShella oraz krótką instrukcją jak to zainstalować.

Aby zainstalować dodatek należy uruchomić PowerShell’a w poziomu Windowsa jako administrator.
Następnie wydać polecenie

Install-Module -Name SqlServer -AllowClobber

Po zakończeniu instalacji ponownie uruchamiamy PowerShella z SQL Management Studio. Tym razem powinno uruchomić się poprawnie.

Poprawność połączenia możemy sprawdzić wydając np komendę

Get-SQLInstance -ServerInstance "localhost"

Zaszufladkowano do kategorii Microsoft SQL, SQL PowerShell | Dodaj komentarz

Kto usunął tabele w bazie???

W tej części pokażę jak sprawdzić kto i kiedy usunął nam tabelę z bazy danych.
Na początek utworzymy sobie testową tabelę w naszej bazie

USE HURTOWNIA
GO
CREATE TABLE TestTable (ID INT)
GO
ALTER TABLE TestTable
ADD FirstCol VARCHAR(100)
GO

Commands completed successfully.

Następnie ją usuwamy

DROP TABLE TestTable
GO

Commands completed successfully.

Teraz przyszła pora na śledztwo kto jest winny zniknięcia tabeli w bazie Hurtownia. Uruchamiamy Raport.

Jak widać w raporcie widać kto stworzył tabelę i kto ją usunął 😉

Zaszufladkowano do kategorii Microsoft SQL | Dodaj komentarz

SSIS – Loopy czyli pętle SSIS-owe

Poniżej opiszę wykorzystanie komponentu wykonującego proste pętle w naszych paczkach SSIS.
Na początek umieszczamy w naszym nowym projekcie komponent LOOP oraz umieszczamy w nim „Execute SQL Task”. Tworzymy też nowe dwie zmienne potrzebne do działania naszej „pętli”.

Zmienna COUNT to liczba powtórzeń a INC kolejna liczba która będzie stopniowo rosnąć lub maleć osiągając wartość COUNT gdzie nastąpi zatrzymanie pętli.

Klikamy w nasz komponent Loop i go modyfikujemy.

Wpisujemy nasze zmienne tak jak poniżej w odpowiednie okna zakładki FOR LOOP

Teraz klikamy w nasz komponent znajdujący się w środku komponentu LOOP. Będzie on powtarzany kilka razy.

Aby nie zaśmiecać bazy AdventuresWorks2012 tworzymy połączenie do naszej testowej bazy Hurtownia.


Polecenie ma robić prosty insert losowej liczby do tabeli LOSOWA.

Uzupełniamy pozostałe opcję komponentu.

Uruchamiamy naszą paczkę.

Sprawdzamy czy inserty się zapisały w tabeli. Jak widać wykonały się 10 razy więc jest OK

Dla porządku możemy jeszcze dołożyć komponent wykonujący TRUNCATE na naszej tabeli żeby liczba wierszy nam nie rosła z kazdym uruchomieniem naszej paczki.

Zaszufladkowano do kategorii SSIS | Dodaj komentarz