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?
You need to look at the database options to determine if it's in FULL recovery or not. You can use [DATABASEPROPERTYEX] 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] 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. :
Have a look at: [
http://www.sqldbatips.com/showarticle.asp?ID=27 ] :
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] * [SQL Server Backup and Restore] * [SQL Backup and Recovery Joes2Pros] by AskSSC's own Tim Radney :