Backup Details for Older Days

How to find details of older backups available in the server for all the databases

Here is the script that can be used to find out the backup details for last 7 days for all the databases in a server.

SELECT    CONVERT(CHAR(100),

SERVERPROPERTY('Servername')) AS Server,  
msdb.dbo.backupset.database_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,   
msdb.dbo.backupmediafamily.logical_device_name,   
msdb.dbo.backupmediafamily.physical_device_name,    
msdb.dbo.backupset.name AS backupset_name, 
msdb.dbo.backupset.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.database_name,    msdb.dbo.backupset.backup_finish_date

Bulk Logged Recovery Model in SQL server

Understanding Bulk Logged Recovery Model

 

I interviewed 2-3 candidates recently and asked them simple question “What is Bulk Logged Recovery Model and How IT WORKS”. I know candidate will be very happy hearing such easy question in the interview. I also think same thing and before I go deep, I ask simple questions so that candidate is very much friendly.

I was surprised after getting answer from all of them. The answer was “Bulk operations never get logged in the transaction log and this way it increases the performance”. I can expect such answer from the person who is fresher or having 2-3 years of experience. But if the person has more than 5 years of experience and answer like this will force me to reject the candidate right away.

This makes me to write something about BULK LOGGED RECOVERY model which can remove some of the confusion about this mode.

 

First of all there is difference between NO LOGGED and MINIMALLY LOGGED. In Bulk Logged Recovery model, bulk operations are minimally logged and not fully logged. This does not mean that bulk operations are never logged in transaction log.

Here are the operations which get minimally logged when you database is in bulk logged recovery model:

·         Bulk import operations (bcp, BULK INSERT, and INSERT... SELECT).

·         SELECT INTO operations

·         WRITETEXT and UPDATETEXT statements when inserting or appending new data into the text, ntext, and image data type columns

·         Partial updates to large value data types, using the .WRITE clause in the UPDATE statement when inserting or appending new data

·         CREATE INDEX operations (including indexed views).

·         ALTER INDEX REBUILD or DBCC DBREINDEX operations.

 

Actually in bulk logged recovery model, allocation of bulk operations are stored in transaction log and not complete data page. Since data pages are not getting logged in the transaction log, this recovery model does not take much space in the log. And this is the reason transaction log does not grow when we do costly bulk operations especially rebuilding the indexes. But when we take the transaction log backup, log backup will have complete data pages as well to provide you the ability to restore bulk operations. This is the reason of having bigger size of transaction log backup compare to transaction log file sometimes. Here the diagram which can give clear picture of this concept.

 

 

Data files are the one which basically holds the data pages of the bulk insert operations. This is the reason if a minimally-logged operation has been performed since the last log backup, and one or more data files were damaged and offline because of the disaster, a tail-of-the-log backup cannot be performed and so all user transactions performed since the last log backup will be lost.

Compared to the full recovery model, which fully logs all transactions, the bulk-logged recovery model minimally logs bulk operations, although fully logging other transactions.

Let me talk something about the advantage and disadvantage of this recovery model in SQL server.

 

Advantage:

Since bulk operations are minimally logged, this increases the performance of the bulk operations. Also it does not allow the log to grow unexpectedly when we do some costly operations like rebuild index, create index etc.

Disadvantage:

If transaction log is damaged, changes since the last backup must be redone. Also if any bulk operations occurred since the most recent log backup, changes since the last backup must be redone.

Remember:

1.    You cannot use point in time restore option if your database is set to bulk logged recovery model.

2.    You have to take the transaction log backups else your log will not be truncated.

3.    If you change recovery model to bulk logged from Full then you do not disturb the log backup chain. But it is always a very good practice to take the log backup before and after making changes of recovery model.

4.    Microsoft recommends using this recovery model when and only when we do bulk operations (Mentioned above). Once the operation(s) is/are completed, switch back to FULL recovery model so that your database can be restored to point in time if disaster happens.

 

Hope this helps you to understand the concept of BULK LOGGED RECOVERY model in SQL server. I feel this recovery model very important for the databases where we do not need point in time recovery and we have so many bulk operations running.

SQL Server Database Backup Getting Skipped

Why some of the Microsoft SQL Server Databases are skipped while performing a backup using T-SQL

I created one stored procedure to take the backups of all my databases. I executed the same through SQL job and it was executing. Since total databases size is around 300 GB, I just left this running and was monitoring the status of the job. Once the job got executed successfully I thought my backups are completed. I went to the location where I took the backup and I found backups are not available for 3 of my databases. Strange!!!!.

 

I looked into my stored procedure again and again and could not find anything wrong there. Just I was asking SQL server to loop through my all user databases through CURSOR and back up all of them to a network location.

Here is the Stored Procedure script that I used.

 

CREATE PROCEDURE [dbo].[DBA_DBs_Copy_Only_Backup]

      -- Add the parameters for the stored procedure here

AS

BEGIN

      SET NOCOUNT ON;

      DECLARE @name VARCHAR(50) -- database name 

      DECLARE @path VARCHAR(256) -- path for backup files 
      DECLARE @fileName VARCHAR(256) -- filename for backup 
      DECLARE @fileDate VARCHAR(20) -- used for file name

      -- specify database backup directory
      SET @path = 'Specify your local/network path here' 

       -- specify filename format

      SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) + REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),':','')

      DECLARE db_cursor CURSOR FOR 

      SELECT name
      FROM master.dbo.sysdatabases
      WHERE name NOT IN ('tempdb')  -- exclude these databases

      OPEN db_cursor  

      FETCH NEXT FROM db_cursor INTO @name  

      WHILE @@FETCH_STATUS = 0  

      BEGIN  
            SET @fileName = @path + @name + '_' + @fileDate + '.BAK'

            BACKUP DATABASE @name TO DISK = @fileName 

            WITH COPY_ONLY, SKIP, NOINIT
            FETCH NEXT FROM db_cursor INTO @name  

      END  

      CLOSE db_cursor  

      DEALLOCATE db_cursor

END

 

Finally I found there is problem with the cursor. To resolve this issue I made the CURSOR STATIC in my code and after that no databases were getting skipped from backup. This is what I did to make the CURSOR static.

 

DECLARE db_cursor CURSOR STATIC FOR

 

Remember by default cursor is dynamic and it may change the value dynamically when your database state changes to any state. By making cursor STATIC we make sure that the data retrieved from the table gets stored in tempdb as reference and it never change till the completion of your script.

 

 

Recovering a SQL Server Database From SUSPECT Mode

How to Repair Database which is in SUSPECT mode in SQL Server 2005/2008

Morning you come to your office and you see your one of the critical database in SUSPECT mode and you are not able to do any action against that database. You need to repair the database at the earliest.

Why Database Is In SUSPECT Mode

There could be so many reasons for this but here are the main reasons that I can think of.

1.    Data files or log files are corrupt.

      2.    Database server was shut down improperly.
      3.    SQL cannot complete a rollback or rollforward operation.

If you want to find the exact reason why your database is in SUSPECT mode, you can use below query. This should give you the exact error message.

DBCC CHECKDB ('<Database Name>') WITH NO_INFOMSGS, ALL_ERRORMSGS

How to Repair the database and bring it online

Follow below steps to bring the database ONLINE and accessible.

1.    Change the status of your database by running following query

          EXEC sp_resetstatus '<Database Name>';

2.    Set the database in EMERGENCY mode by running following query

ALTER DATABASE <Database Name> SET EMERGENCY;

3.    Check the database for any inconsistency by executing below query

DBCC CHECKDB('<Database Name>');

4.    If you get any error after executing DBCC CHECKDB then immediately bring the database in SINGLE USER MODE by running following query. If no error found then you are done.

          ALTER DATABASE <Database Name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

5.    Take the backup of the database just to be in safe side.

6.    Run the following query as next step. Remember while using the below query, that uses REPAIR_ALLOW_DATA_LOSS, is a one way operation that is once the database is repaired all the actions performed by these queries can’t be undone. There is no way to go back to the previous state of the database. So as a precautionary step you should take backup of your database in step 5 mentioned above.

DBCC CHECKDB ('<Database Name>', REPAIR_ALLOW_DATA_LOSS);

7.    Finally bring the database in MULTI USER mode by running following query.

ALTER DATABASE <Database Name> SET MULTI_USER;

8.    At last step you verify the connectivity from your database. Now users should be able to connect to the database properly. If any data loss, you may need to get from the backup taken in step 5.

Now your database is ONLINE and accessible. Now Rock and have the coffee to RELAX.