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)
Ten wpis został opublikowany w kategorii Microsoft SQL i oznaczony tagami . Dodaj zakładkę do bezpośredniego odnośnika.

Dodaj komentarz