I faced an issue during restoration of very large db in our development database server. Issue is restoration takes very long time even though we had latest hardware and recent version of SQL Server (it’s SQL 2008 Ent. Ed). So to troubleshoot it I have used the trace flag 3004 which print all internal things happen during backup and restoration operation.

In this article I will try and cover the concepts in understanding the amount of memory or buffers required for completing a database backup. I would be using a couple of trace flags to get that detail and further discuss two of the parameters MAXTRANSFERSIZE and BUFFERCOUNT in the BACKUP DATABASE TSQL syntax which will help us understand a bit more about memory consumption for a database backup and time involved in completing the backup.

Does the title sounds crazy? You might think that this topic is very simple? Yes you are right however some folks still raise this question in forums. Im seeing this question for for a long time in forums and thought of sharing the solution here, so that the folks can give a search to get this (coz Google is our first techie teacher or instant solution provider ).

You might be wondering what is happening in your SQL Server when you see the error message given below. Its all simple, you need not panic.

Introduction

Backup compression is one of the feature expected by every administrator to minimize the backup size and to squeeze the time taken for backup. In previous versions of SQL Server most of us are going for 3rd party tools like Quests Litespeed, Redgates SQL Backup etc. for which we need to pay additional amount. SQL Server 2008 adds an additional feature of taking compressed backup through native backup command itself.