| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2007-12-19 : 08:12:15
|
Backing up a database is one of the most important things you need to do when having a database driven application. It 's only all of your data in there, right? But often developers and management don't realize the importance of backups and overall proper backup strategy for the most important side of the business – data and it's consistency. Read Introduction to SQL Server 2005 Database Backups |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-12-19 : 08:40:03
|
Looking forward to read next chapter!
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
dbird
Starting Member
4 Posts |
Posted - 2007-12-19 : 11:23:32
|
| Great article. It reminded me of some backups useful backup options that I forgot about. Definitely worth keeping as a quick reference. |
|
|
|
talleyrand
Starting Member
35 Posts |
Posted - 2007-12-20 : 10:46:39
|
| Under "Full database Backup", the SQL comment is misleading-- Back up the AdventureWorks as differential backupOtherwise, thank you for an informative article |
|
|
|
graz
Chief SQLTeam Crack Dealer
4149 Posts |
Posted - 2007-12-20 : 11:20:12
|
| Yes it is. So much for my proof reading skills. It's fixed.=================================================Creating tomorrow's legacy systems today. One crisis at a time. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-12-20 : 11:28:51
|
and mine...  _______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out |
|
|
|
R
Constraint Violating Yak Guru
328 Posts |
Posted - 2008-05-02 : 10:42:24
|
| HiWill there be a 'next article' ?Hopefully yes, there will...! |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-05-04 : 06:48:48
|
yes there will be. real life sometimes gets in the way of things  _______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
|
|
|
web3media
Starting Member
1 Post |
Posted - 2008-10-16 : 00:06:11
|
Yes, very good article. Thank you. Can you answer a question for me, though? Does the FULL recovery apply or help if you haven't run a backup? I need to restore a 2005 db to a recent point in time, but have no backups  . Any to take advantage of the info in the transaction logs and get the db back to the desired point in time  Thanks in advance. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2008-10-16 : 04:05:50
|
| No. If there are no backups at all, then the log will be getting auto-truncated, just as it it were simple recovery. Besides, you always need a to restore a full backup first and then apply log backups.--Gail ShawSQL Server MVP |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2008-10-16 : 10:30:22
|
quote:
Originally posted by GilaMonster
No. If there are no backups at all, then the log will be getting auto-truncated, just as it it were simple recovery.
First I've heard of that. It was my understanding that the log file would grow, regardless of whether a backup had ever been taken.Do you have any references to support that?If it is not practically useful, then it is practically useless. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2008-10-16 : 13:54:59
|
Yes. A couple, though no one seems to state it clearly.[url]http://www.sqlskills.com/blogs/paul/post/BACKUP-LOG-WITH-NO_LOG-use-abuse-and-undocumented-trace-flags-to-stop-it.aspx[/url]quote:
The common use is when the transaction log grows to be inordinately large on a database that's in FULL (or BULK_LOGGED) recovery mode. This happens after a database backup has been taken, which switches the log into a mode where it won't truncate until its been backed up. In these circumstances, if you don't take a transaction log backup, the log will continue to grow until it runs out of disk space.
[url]http://www.sqlskills.com/blogs/paul/post/Search-Engine-QA-1-Running-out-of-transaction-log-space.aspx[/url]quote:
So what causes the transaction log to fill up in the first place? It could be a number of different things. The two most common ones I've seen are: * The database is in full recovery mode with normal processing, a full database backup has been taken but no log backups have been taken. This commonly happens when an application goes into production and someone decides to take a backup, without realizing the consequences. Log truncation is not automatic in such a case - a log backup is required to allow the log to truncate.
Books Online: (SQL 2008 BoL)quote:
A continuous sequence of log backups is called a log chain. A log chain starts with a full backup of the database. Usually, a new log chain is only started when the database is backed up for the first time, or after the recovery model is switched from simple recovery to full or bulk-logged recovery. (topic: Working with Transaction Log Backups )
quote:
If you must switch from the simple recovery model to the full recovery model, we recommend that you:Immediately after you complete the switch to the full recovery model or bulk-logged recovery model, take a full or differential database backup to start the log chain. The switch to the full or bulk-logged recovery model takes effect only after the first data backup.Schedule regular log backups and update your restore plan accordingly. Log backups are an integral and fundamental aspect of the full and bulk-logged recovery models. Log backups allow the transaction log to be truncated. If you do not back up the log frequently enough, the transaction log can expand until it runs out of disk space. (Topic: Considerations for Switching from the Simple Recovery Model )
There has to be a full backup to base the logs off. If the database has never ever been backed up, then there is no base for log backups to be restored against. Until a full backup is taken, the DB behaves like it's in simple. The same thing happens if the log chain is broken (backup log.. with truncate/switch to simple and back), until a base for the log chain exists, the log records aren't retained.It's easy to test. Create a DB, put it in full recovery. Check the log space used. Do a large transaction (insert a few million rows into a table. Check the log space used. Checkpoint. Check the log space again.--Gail ShawSQL Server MVP |
|
|
|
joseph
Starting Member
10 Posts |
Posted - 2009-09-18 : 05:35:38
|
quote:
Tail log backupThere seems to be a lot of confusion about this one since it's a new term in SQL Server 2005 (I haven't heard it being used in SS2k)
As far as I know,the tail log backup were existed in SQL2K. |
|
|
|
Alex_Green
Starting Member
3 Posts |
Posted - 2012-08-08 : 05:54:20
|
| Very useful article. Thank you so much!There are a lot of simple third party tools for SQL backup like "SQL Backup and Replication", SQLBackupAndFtp, "Simple SQL Backup", etc. In most standard cases such tools usage is more preferred.I would to recommend SQLBackupAndFTP which allows you to:- Save your backups locally, on your LAN or an FTP site.- Schedule your backups as a Windows task or a Service. In both cases, the tool saves the password for you.- Differential and Transaction Log backups are also available.- The tool creates different backup files for each backup, letting you classify them as you wish. |
|
|
|
elliswhite
Starting Member
36 Posts |
Posted - 2014-05-03 : 01:39:13
|
| I could have take my database backup very smoothly, thanks for sharing article. |
|
|
|
|
Article: Introduction to SQL Server 2005 Database Backups