Archive

Archive for the ‘Backup and Restore’ Category

Procedure to make the Backup SLA document

December 1, 2013 Leave a comment

 

Procedure to make the Backup SLA document

 

As a DBA I have to document the Critical backup information so we can prepare the SLA document for our client. The below information is required to make the SLA document so we can estimate the data loss hours. I have to make the SLA document for my client.

 

Backup Information

•           Types of Backups Performed (Full, Differential, Log):

•           Last Full Database Backup:

•           Last Differential Database Backup:

•           Last Transaction Log Backup:

•           How Often are Transaction Logs Backed Up:

•           Average Database Full Backup Time:

•           Database Full Backup Size:

•           Average Transaction Log Backup Size:

•           Number of Full Database Backup Copies Retained:

•           Backups Encrypted:

•           Backups Compressed:

•           Backup To Location:

•           Offsite Backup Location:

•           Backup Software/Agent Used:

 

 

Script reference: Book SQL Server Backup and Restore By Shawn McGehee

 

SELECT  d.name ,

MAX(d.recovery_model) ,

is_Password_Protected , –Backups Encrypted:

–Last Full Database Backup:

MAX(CASE WHEN type = ‘D’ THEN backup_start_date

ELSE NULL

END) AS [Last Full Database Backup] ,

–Last Transaction Log Backup:

MAX(CASE WHEN type = ‘L’ THEN backup_start_date

ELSE NULL

END) AS [Last Transaction Log Backup] ,

–Last Differential Log Backup:

MAX(CASE WHEN type = ‘I’ THEN backup_start_date

ELSE NULL

END) AS [Last Differential Backup] ,

–How Often are Transaction Logs Backed Up:

DATEDIFF(Day, MIN(CASE WHEN type = ‘L’ THEN backup_start_date

ELSE 0

END),

MAX(CASE WHEN type = ‘L’ THEN backup_start_date

ELSE 0

END)) / NULLIF(SUM(CASE WHEN type = ‘I’ THEN 1

ELSE 0

END), 0) [Logs BackUp count] ,

–Average backup times:

SUM(CASE WHEN type = ‘D’

THEN DATEDIFF(second, backup_start_date,

Backup_finish_date)

ELSE 0

END) / NULLIF(SUM(CASE WHEN type = ‘D’ THEN 1

ELSE 0

END), 0) AS [Average Database

Full Backup Time] ,

SUM(CASE WHEN type = ‘I’

THEN DATEDIFF(second, backup_start_date,

Backup_finish_date)

ELSE 0

END) / NULLIF(SUM(CASE WHEN type = ‘I’ THEN 1

 

ELSE 0

END), 0) AS [Average Differential

Backup Time] ,

SUM(CASE WHEN type = ‘L’

THEN DATEDIFF(second, backup_start_date,

Backup_finish_date)

ELSE 0

END) / NULLIF(SUM(CASE WHEN type = ‘L’ THEN 1

ELSE 0

END), 0) AS [Average Log Backup Time] ,

SUM(CASE WHEN type = ‘F’

THEN DATEDIFF(second, backup_start_date,

Backup_finish_date)

ELSE 0

END) / NULLIF(SUM(CASE WHEN type = ‘F’ THEN 1

ELSE 0

END), 0) AS [Average file/Filegroup

Backup Time] ,

SUM(CASE WHEN type = ‘G’

THEN DATEDIFF(second, backup_start_date,

Backup_finish_date)

ELSE 0

END) / NULLIF(SUM(CASE WHEN type = ‘G’ THEN 1

ELSE 0

END), 0) AS [Average Differential

file Backup Time] ,

SUM(CASE WHEN type = ‘P’

THEN DATEDIFF(second, backup_start_date,

Backup_finish_date)

ELSE 0

END) / NULLIF(SUM(CASE WHEN type = ‘P’ THEN 1

ELSE 0

END), 0) AS [Average partial Backup Time] ,

SUM(CASE WHEN type = ‘Q’

THEN DATEDIFF(second, backup_start_date,

Backup_finish_date)

ELSE 0

END) / NULLIF(SUM(CASE WHEN type = ‘Q’ THEN 1

ELSE 0

END), 0) AS [Average Differential

partial Backup Time] ,

MAX(CASE WHEN type = ‘D’ THEN backup_size

ELSE 0

END) AS [Database Full Backup Size] ,

 

SUM(CASE WHEN type = ‘L’ THEN backup_size

ELSE 0

END) / NULLIF(SUM(CASE WHEN type = ‘L’ THEN 1

ELSE 0

END), 0) AS [Average Transaction Log Backup Size] ,

–Backup compression?:

CASE WHEN SUM(backup_size – compressed_backup_size) <> 0 THEN ‘yes’

ELSE ‘no’

END AS [Backups Compressed]

FROM    master.sys.databases d

LEFT OUTER JOIN msdb.dbo.backupset b ON d.name = b.database_name

WHERE   d.database_id NOT IN ( 2, 3 )

GROUP BY d.name ,

is_Password_Protected

–HAVING  MAX(b.backup_finish_date) <= DATEADD(dd, -7, GETDATE()) ;

 

 

Categories: Backup and Restore

Rectified the Page corruption issue by page restore option

January 26, 2013 1 comment

Rectified the Page corruption issue by page restore option

The database corruption is very common issue in DBA world but we can restore the specific page which is corrupted by page restore option. We have implemented successfully in our project and written the below article If the DBCC CHECKDB  job has failed and as an output  we have received the below output.

Object ID 2088535921, index ID 0, partition ID 72345201021503994, alloc unit ID 72345201051571606 (type In-row data): Page (1:94299) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 2088535921, index ID 0, partition ID 72345201021503994, alloc unit ID 72345201051571606 (type In-row data), page (1:94299). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed.
CHECKDB found 0 allocation errors and 2 consistency errors in table ‘ DimSalesReason’ (object ID 2088535921).
CHECKDB found 0 allocation errors and 2 consistency errors in database ‘ AdventureWorksLT2008’.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (AdventureWorksLT2008).

 

Work around:

From this you can see what page is corrupted (1:94299)

The first thing to do is check if it is data in a heap, in a clustered index, or in a non-clustered index. In the above text you can see it is index ID 0. You could also examine the page (1:94299 in database ‘yourdb’) as follows:

DBCC TRACEON (3604, -1)
GO
DBCC PAGE(‘ AdventureWorksLT2008’, 1, 94299, 3)
GO
In the output you will see something like:

Metadata: IndexId = n

Note: If n is greater than 1 it is a non-clustered index and can safely be dropped and recreated. If n is 0 or 1 you have data corruption and need to perform one of the options described below.
Option  1: Restoring specific corrupted  page  from a backup
If the recovery model is FULL (or BULK_LOGGED, with some limitations), you can back up the tail of the log, perform a restore (with no recovery) from the last clean full backup, followed by subsequent log backups and finally the tail of the log.

If only a few pages are affected you have the option of selectively restoring only the bad pages, as follows:

RESTORE DATABASE AdventureWorksLT2008 PAGE = ‘1:94299’
FROM DISK = ‘E:\ AdventureWorksLT2008.bak’
WITH NORECOVERY
Option 2: If the recovery model is simple you don’t have that option, and have to accept that a restore from the last full backup will result in subsequent transactions being lost. In this case, or if you have no backups at all, you may decide that an automatic repair is the only option.

 

ALTER DATABASE AdventureWorksLT2008 SET EMERGENCY;
GO

ALTER DATABASE AdventureWorksLT2008 SET SINGLE_USER;
GO
DBCC CHECKDB (AdventureWorksLT2008, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;
GO

ALTER DATABASE AdventureWorksLT2008 SET MULTI_USER;

Categories: Backup and Restore

Restore the Production Database which is part of replication

December 24, 2012 Leave a comment

Issue Description: One week back we got an urgent request to restore the database from a specific point of time because someone from an application team accidently deleted some rows from the database which stopped 1400 hundred employees from accessing building facilities. They wanted to restore the database from a specific point of time, but concern here is that database being restored  is part of the replication as per the high availability scenario. I had to restore the primary database without destroying the Replication settings.  Here I have mentioned the step to restore the database while preserving the replication settings.

 

We are maintaining daily full backup and every 15 minute Transactional log backup in a database Full recovery model. The issue happened at 7 40. so we have a full backup from 7 and two transactional log backups at 7.15 and 7.30. We can restore the database up to 7.30 and here we have to use Preserve the replication settings option during restoration so replication settings are not destroyed.

 

Categories: Backup and Restore

To know the all the information about the restored databases

December 24, 2012 Leave a comment

As per our day to job we have to restore the database in different server as per the requirement and some time we have to give all the information to business owner to validate the task done by person . The business owner required the information who , when and what backup is used to restore the database . We can use the below script to get to know all the information whatever we are looking for.

 

Script:–

 

SQL 2005 script:

 

DECLARE @dbname sysname, @days int

SET @dbname = NULL –substitute for whatever database name you want

SET @days = -30 –previous number of days, script will default to 30

SELECT

rsh.destination_database_name AS [Database],

rsh.user_name AS [Restored By],

CASE WHEN rsh.restore_type = ‘D’ THEN ‘Database’

WHEN rsh.restore_type = ‘F’ THEN ‘File’

WHEN rsh.restore_type = ‘G’ THEN ‘Filegroup’

WHEN rsh.restore_type = ‘I’ THEN ‘Differential’

WHEN rsh.restore_type = ‘L’ THEN ‘Log’

WHEN rsh.restore_type = ‘V’ THEN ‘Verifyonly’

WHEN rsh.restore_type = ‘R’ THEN ‘Revert’

ELSE rsh.restore_type

END AS [Restore Type],

rsh.restore_date AS [Restore Started],

bmf.physical_device_name AS [Restored From],

rf.destination_phys_name AS [Restored To]

FROM msdb.dbo.restorehistory rsh

INNER JOIN msdb.dbo.backupset bs ON rsh.backup_set_id = bs.backup_set_id

INNER JOIN msdb.dbo.restorefile rf ON rsh.restore_history_id = rf.restore_history_id

INNER JOIN msdb.dbo.backupmediafamily bmf ON bmf.media_set_id = bs.media_set_id

WHERE rsh.restore_date >= DATEADD(dd, ISNULL(@days, -30), GETDATE()) –want to search for previous days

AND destination_database_name = ISNULL(@dbname, destination_database_name) –if no dbname, then return all

ORDER BY rsh.restore_history

 

 

SQL 2008 and 2008R2:–

 

–Script: small change is there

 

DECLARE @dbname sysname, @days int

SET @dbname = NULL –substitute for whatever database name you want

SET @days = -30 –previous number of days, script will default to 30

SELECT

rsh.destination_database_name AS [Database],

rsh.user_name AS [Restored By],

CASE WHEN rsh.restore_type = ‘D’ THEN ‘Database’

WHEN rsh.restore_type = ‘F’ THEN ‘File’

WHEN rsh.restore_type = ‘G’ THEN ‘Filegroup’

WHEN rsh.restore_type = ‘I’ THEN ‘Differential’

WHEN rsh.restore_type = ‘L’ THEN ‘Log’

WHEN rsh.restore_type = ‘V’ THEN ‘Verifyonly’

WHEN rsh.restore_type = ‘R’ THEN ‘Revert’

ELSE rsh.restore_type

END AS [Restore Type],

rsh.restore_date AS [Restore Started],

bmf.physical_device_name AS [Restored From],

rf.destination_phys_name AS [Restored To]

FROM msdb.dbo.restorehistory rsh

INNER JOIN msdb.dbo.backupset bs ON rsh.backup_set_id = bs.backup_set_id

INNER JOIN msdb.dbo.restorefile rf ON rsh.restore_history_id = rf.restore_history_id

INNER JOIN msdb.dbo.backupmediafamily bmf ON bmf.media_set_id = bs.media_set_id

WHERE rsh.restore_date >= DATEADD(dd, ISNULL(@days, -30), GETDATE()) –want to search for previous days

AND destination_database_name = ISNULL(@dbname, destination_database_name) –if no dbname, then return all

ORDER BY rsh.restore_history_id

Categories: Backup and Restore