Database Solutions

Sunday, September 25, 2011

SQL Script to get Backup Information ...

SQL Script to get Backup Information - SQL Script to know when all was last backup was being performed ? SQL Script ... http://ping.fm/Q5vHy

SQL Server Partial Data backup, a new backup type in SQL Server 2005

One of my training participant was asked discussing about refresh of large databases where I suggested him a partial backup strategy but I was shocked when he misunderstood the things and understood that partial backup is same filegroup backup.

So let's understand,

What is SQL Server Partial Database Backup ?

Partial backups were introduced for SQL Server 2005. Partial backups will backup the primary filegroup, any read/write file groups, and any read-only files that are specified during the backup. The partial backup is designed to not backup any data that is placed in a read-only file group. So, you can use a partial backup to backup your entire database – except for the read only data. Partial backups are meant to be used in a simple recovery model situation, although they can be used in a full recovery model situation as well.

Data Loss:

The partial backup provides bare minimum protection from data loss. The partial backup serves as a base for differential partial backups – which can be applied to the partial backup to bring the database closer to a point in time near the actual database failure. A full recovery model with transaction logs provides the best chance to restore the database up to the point of failure.

Size:

A key advantage of using a partial backup as opposed to a full database backup is the reduced file size. The read only filegroups only have to be saved once, therefore the partial backups will be smaller than what would be required for a full database backup.

Resources Required:

The strain on the database will likely be much less for a partial database backup than it is for a full database backup since specified read only filegroups won’t have to be backed up.

How to take a Partial Database Backup ?

Backup Type Partial and Differential partial are newly introduced in SQL Server 2005 and still not supported by SSMS and Maintenance Plans. To take this sort of backup we need to manually write SQL statement like below

BACKUP DATABASE [ANKUR] READ_WRITE_FILEGROUPS TO DISK = N'c:\ANKUR-25Sep2011.BAK'  -- Full Partial  backup 
BACKUP DATABASE [ANKUR] READ_WRITE_FILEGROUPS TO DISK = N'c:\ANKUR-25Sep2011.BAK' WITH  DIFFERENTIAL  -- Differential Partial backup

For More Details check BOL.

SQL Script for getting Backup Information

SQL Script to know when all was last backup was being performed ?

SQL Script to know when all was last backup was being performed and what type of backup was taken ?

SQL Script to what type of backup is being configured and when it was last run?

Backuping all databases is the most important maintenance part and a DBA should know, what type of backup and when was it taken last time.

SQL Script to get last backup date and time and type of backup

/*-------------------------------------------------------------------------------------------------------------------------------Description    :     This stored procedure will send out alert email if there is a blocking which lasted more than specified duration)
-- Copyright 2011 - DBATAG

-- Author        :    DBATAG
-- Created on    :    20/09/2011
-- Version       :    1.0
-- Dependencies  :
--                Table                            Procedure
-- Description      : Get backup date and time of last backup and type of backup and till now, how many backups has been performed on aparticular DB.
----------------------------------------------------------------------------------------------------------------------------*/
SELECT sDB.name AS DatabaseName 
      ,MAX(sDB.recovery_model_desc) AS RecModel 
      ,MAX(BS.backup_start_date) AS LastBackup 
      ,MAX(CASE WHEN BS.type = 'D' 
                THEN BS.backup_start_date END) 
       AS LastFull 
      ,SUM(CASE WHEN BS.type = 'D' 
                THEN 1 END) 
       AS CountFull 
      ,MAX(CASE WHEN BS.type = 'L' 
                THEN BS.backup_start_date END) 
       AS LastLog 
      ,SUM(CASE WHEN BS.type = 'L' 
                THEN 1 END) 
       AS CountLog 
      ,MAX(CASE WHEN BS.type = 'I' 
                THEN BS.backup_start_date END) 
       AS LastDiff 
      ,SUM(CASE WHEN BS.type = 'I' 
                THEN 1 END) 
       AS CountDiff 
      ,MAX(CASE WHEN BS.type = 'F' 
                THEN BS.backup_start_date END) 
       AS LastFile 
      ,SUM(CASE WHEN BS.type = 'F' 
                THEN 1 END) 
       AS CountFile 
      ,MAX(CASE WHEN BS.type = 'G' 
                THEN BS.backup_start_date END) 
       AS LastFileDiff 
      ,SUM(CASE WHEN BS.type = 'G' 
                THEN 1 END) 
       AS CountFileDiff 
      ,MAX(CASE WHEN BS.type = 'P' 
                THEN BS.backup_start_date END) 
       AS LastPart 
      ,SUM(CASE WHEN BS.type = 'P' 
                THEN 1 END) 
       AS CountPart 
      ,MAX(CASE WHEN BS.type = 'Q' 
                THEN BS.backup_start_date END) 
       AS LastPartDiff 
      ,SUM(CASE WHEN BS.type = 'Q' 
                THEN 1 END) 
       AS CountPartDiff 
FROM sys.databases AS sDB 
     LEFT JOIN 
     msdb.dbo.backupset AS BS 
         ON BS.database_name = sDB.name 
WHERE ISNULL(BS.is_damaged, 0) = 0 -- exclude damaged backups          
GROUP BY sDB.name 
ORDER BY sDB.name;

Note : In backupset table, we get the following backups status which means

D = Database

I = Differential database

L = Log

F = File or filegroup

G =Differential file

P = Partial

Q = Differential partial

NULL - not specified

Backup Type Partial and Differential partial are newly introduced in SQL Server 2005 and still not supported by SSMS and Maintenance Plans. To take this sort of backup we need to manually write SQL statement like below

BACKUP DATABASE [ANKUR] READ_WRITE_FILEGROUPS TO DISK = N'c:\ANKUR-25Sep2011.BAK'  -- Full Partial  backup 
BACKUP DATABASE [ANKUR] READ_WRITE_FILEGROUPS TO DISK = N'c:\ANKUR-25Sep2011.BAK' WITH  DIFFERENTIAL  -- Differential Partial backup

Thursday, September 22, 2011

Schedule Analysis Services Database B...

Schedule Analysis Services Database Back Up - Can we schedule a Analysis Service (OLAP)Database backup ? How to Back... http://ping.fm/VO4u4

Backing Up an Analysis Services Datab...

Backing Up an Analysis Services Database - How to Backup Analysis Services Database ? Backing up Analysis Services d... http://ping.fm/OXKgF

Monday, September 19, 2011

Max Memory setting for SQL Server Ana...

Max Memory setting for SQL Server Analysis Services - Can we set a MAX Memory setting for Microsoft SQL Server Analy... http://ping.fm/Olj0G

SQL Server Analysis Services Authenti...

SQL Server Analysis Services Authentication Modes - Question : Can I have SQL Server Authentication Modes for SQL Se... http://ping.fm/b0C58