#msdb database

Know more about SQL Server MSDB log store for database backups, restores, and recovery: Q& A – Part 3

In this article, we’ll see the how the backup-and-restore meta-data tables store the information in the MSDB database.

How do you delete six months old data to reduce the size of the backup and restore history tables?

How do you get the Backup History for a specific database including the size, location, and LSN?

How do you create and restore a marked transaction?

How do you find the RESTORE…

New Post has been published on http://www.varindersandhu.in/2014/03/07/sql-server-2012-get-backup-history-database/

SQL Server 2012 - Get Backup History of the database

The msdb database is system database used by SQL Server Agent for scheduling alerts and jobs.

msdb.dbo.backupset – Contain the information concerning the most-granular details of the backup process msdb.dbo.backupmediafamily – Contains the metadata for the physical backup files as they relate to backup sets

<!-- google_ad_client = "pub-2404605494811633"; google_alternate_color = "FFFFFF"; google_ad_width = 468; google_ad_height = 60; google_ad_format = "468x60_as"; google_ad_type = "text_image"; google_ad_channel =""; google_color_border = "FFFFFF"; google_color_link = "0000FF"; google_color_bg = "FFFFFF"; google_color_text = "000000"; google_color_url = "008000"; google_ui_features = "rc:6"; //-->

Using the above mentioned tables, we will find the backup history of database

Script:

SELECT s.database_name, m.physical_device_name, CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS Backup_Size, CAST(DATEDIFF(second, s.backup_start_date,s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken, s.backup_start_date, CASE s.[type] WHEN 'D' THEN 'Full' WHEN 'I' THEN 'Differential' WHEN 'L' THEN 'Transaction Log' END AS BackupType, s.server_name, s.recovery_model FROM msdb.dbo.backupset s INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id WHERE s.database_name = DB_NAME() -- Remove this where clause for all database ORDER BY backup_start_date DESC, backup_finish_date GO

Post has been published on http://www.varindersandhu.in/2011/04/02/sql-server-understanding-the-system-database-of-sql-server/

SQL Server – Understanding the System Databases of SQL Server

<!-- google_ad_client = "pub-2404605494811633"; google_alternate_color = "FFFFFF"; google_ad_width = 468; google_ad_height = 60; google_ad_format = "468x60_as"; google_ad_type = "text_image"; google_ad_channel =""; google_color_border = "FFFFFF"; google_color_link = "0000FF"; google_color_bg = "FFFFFF"; google_color_text = "000000"; google_color_url = "008000"; google_ui_features = "rc:6"; //-->

We all know there are some system databases available in SQL Server. But we should have understanding about system databases. Here just try to give you understanding of system databases very briefly.

There are four system databases available in SQL Server 2008

Master

Model

msdb

tempdb

Master Database

The master database records all the system-level information for a SQL Server system.

The master is the database that records the existence of all other databases and the location of those database files and records the initialization information for SQL Server. Therefore, SQL Server cannot start if the master database is unavailable. In short, Master database is the logical repository for the system objects residing in the sys schema.

Model Database

The model database is used as the template for all databases created on the instance of SQL Server. Template can include specific settings and all sorts of useful stuff.

Msdb Database

The msdb database is used by SQL Server Agent for scheduling alerts and jobs. If the MSDB database is corrupted or damaged then scheduling information used by SQL Server Agent will be lost.

Tempdb Database

The tempdb database is a workspace for holding temporary objects or intermediate result sets, temporary table creation/processing. The TempDB is recreated every time when ever SQL Server restarts

For more Detail go to below URL

New Post has been published on http://www.varindersandhu.in/2014/03/07/sql-server-2012-get-backup-history-database/

SQL Server 2012 - Get Backup History of the database

The msdb database is system database used by SQL Server Agent for scheduling alerts and jobs.

<!-- google_ad_client = "pub-2404605494811633"; google_alternate_color = "FFFFFF"; google_ad_width = 468; google_ad_height = 60; google_ad_format = "468x60_as"; google_ad_type = "text_image"; google_ad_channel =""; google_color_border = "FFFFFF"; google_color_link = "0000FF"; google_color_bg = "FFFFFF"; google_color_text = "000000"; google_color_url = "008000"; google_ui_features = "rc:6"; //-->

msdb.dbo.backupset – Contain the information concerning the most-granular details of the backup process msdb.dbo.backupmediafamily – Contains the metadata for the physical backup files as they relate to backup sets

Using the above mentioned tables, we will find the backup history of database

Script:

SELECT s.database_name, m.physical_device_name, CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS Backup_Size, CAST(DATEDIFF(second, s.backup_start_date,s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken, s.backup_start_date, CASE s.[type] WHEN 'D' THEN 'Full' WHEN 'I' THEN 'Differential' WHEN 'L' THEN 'Transaction Log' END AS BackupType, s.server_name, s.recovery_model FROM msdb.dbo.backupset s INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id WHERE s.database_name = DB_NAME() -- Remove this where clause for all database ORDER BY backup_start_date DESC, backup_finish_date GO