asked
Backupof DB and transaction logs-Full recovery model
I am trying to implement the backup of DB programmatically. I have few questions.I am using MSSQL 2005 express edition. 1. I am calling a stored proc to take the backup of DB. (BACKUP Database [db_name] To Disk =[path]) 2. Is it a full recovery model? or should i specify something?. 3. What happens to T-log in this case? should i write a seperate command to backup t-log or delete t-log. 4. I also would like to get the the size of this particular DB and check with the Disk Space before taking up the backup. How do i do that? 5. Should i also block all the DB connections until backup is completed or is it handled by the DB automatically ? is there any method to do that?
@jctronicsshiva I guess already enough excellent stuff is posted to help you. I am just letting you know that if you do not want to re-invent the wheel then [Ola Hallengrean's maintenance solution][1] may be ideal. In case you cannot use it, then aleast you can look into it to setup something according to your needs. For instance in the past, where we were not able to use it, with the help of this solution we implemented something on our own. We segregated databases backups in groups. Some of the backups ran in parallel and some of them in a chain (one after another). There were multiple scheduled jobs to run those backups which also notifiy all the stakeholders upon successful completion OR failure with added information as Time taken, size etc. Last but most importantly, do test your backups by restoring them on test server/s. This is the most important part of the backup strategy often missed by the strting DBAs. Faulty backups are of no worth and may give you a horrid time in case of a disaster. [1]:
http://ola.hallengren.com/sql-server-backup.html
You need to look at the database options to determine if it's in FULL recovery or not. You can use [DATABASEPROPERTYEX][1] to determine this through T-SQL. If the database is in SIMPLE recovery, you don't need to worry about backing up the logs. If it's in any other recovery model, yes, you need to run regular log backups. By regular I mean at least once an hour, but that's just a guess. It depends on the number of transactions you support. You may need to run log backups as frequently as once very ten minutes. You can't delete the transaction log. Since a backup is a page-by-page copy of the database, to determine the rough size of the backup, you can use [sp_spaceused][2] to see how big the database is currently. Backups are an online operation. This means that you don't need to close connections or stop transactions while a backup is in progress. SQL Server has mechanisms for handling exactly that situation. [1]:
http://technet.microsoft.com/en-us/library/ms186823.aspx [2]:
http://msdn.microsoft.com/en-us/library/ms188776.aspx
I would absolutely recommend that AUTO_CLOSE be set to OFF. Same thing with AUTO_SHRINK. For the vast majority of production systems, there is no reason to have these things set to ON.
Thanks Grant for the reply. I have considered Simple recovery after all the considerations. When i was checking the log files, i got a few messages like "SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations." Also for SQL plans and Bound trees. I read in some posts that its because of AUTO_CLOSE and AUTO_SHRINK property. But i am not sure about when exactly i should disable it?
@jctronicsshiva Better would have been to ask it as another question. BTW, these are the database properties which can be turned ON and OFF quite easily. You can find the status of these properties by using the same DATABASEPROPERTYEX Grant Fritchey has told you about. Then you can turn them OFF using ALTER DATABASE DBNAME SET AUTO_SHRINK OFF etc.
Have a look at: [
http://www.sqldbatips.com/showarticle.asp?ID=27 ][1] [1]:
http://www.sqldbatips.com/showarticle.asp?ID=27 Since you are asking if you should delete transaction log, block connections while backing up and check for disk space before taking a backup, my suggestion to you is to read up on some theory around SQL Server (you can loose your job if you screw up backups and do some of above). * Full backups vs Transaction log backups * Database recovery models And what ever you do, do not delete the transaction log or put the backup to the same disk as your data/transaction log files (you can backup to network location.)
You need to do, as a minimum for FULL RECOVERY databases, FULL backups *and* Transaction Log backups. Quick summary: * A FULL backup is a backup of all the data pages that have been committed at that point. Its size is (approximately) equal to that of the database data file itself, unless you are running a compressed backup. Disk space requirements for compressed backups are a little more complicated to calculate - some versions require the full space to start off with and then truncate, others start from zero... * A DIFF backup is a backup of all data pages that have changed since the last FULL backup. * A Transaction Log backup is a backup of all transaction log changes since the last TLog backup. How do I restore? * Start with restoring the FULL backup * If you have a DIFF taken after the latest FULL backup, then restore that next * Finally, restore (in order) all TLog backups taken after that DIFF backup. You need them all. How often do you do this? * It depends on what the business wants. You need to find out from them how much data they're prepared to lose in the event of a SQL Server failure, of whatever sort. * It also depends on how much backup management overhead you're prepared to put in * Me? I do weekly FULL backups, Daily DIFFs, and quarter-hourly TLOG backups. * And I test these backups every day. How do you keep these? * In order to restore to a particular point-in-time, you'll need at least FULL & TLog backups. * If you just want to be able to get back a set of data (eg for dev/qa systems), then I would put the databases themselves into SIMPLE recovery mode, and just use FULL (and maybe DIFFs) backups periodically. Some terms to understand: * Recovery Point Objective - this needs to be agreed. It's the granularity of the backup - how much data can the business cope with losing? * Recovery Time Objective - this is more Disaster-Recovery planning - how much time do you have in order to get the system back up & running after an outage? This will inform your backup strategy, but will dictate any HA / DR solutions. Some introductory reading on backups: * From Red-Gate (disclosure: I'm a Friend of Red-Gate) (available free as PDFs, or you can buy hard copy from Amazon) * [Brad's Sure Guide to SQL Server Maintenance Plans][1] * [SQL Server Backup and Restore][2] * [SQL Backup and Recovery Joes2Pros][3] by AskSSC's own Tim Radney [1]:
http://www.red-gate.com/community/books/sql-server-maintenance-plans [2]:
http://www.red-gate.com/community/books/sql-server-backup-and-restore.htm [3]:
http://www.amazon.com/SQL-Backup-Recovery-Joes-Pros%C2%AE/dp/1939666082
16 People are following this question.
