| Author |
 Topic  |
|
|
AskSQLTeam
Ask SQLTeam Question
USA
0 Posts |
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
 Posted - 12/19/2007 : 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 - 12/19/2007 : 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
USA
35 Posts |
Posted - 12/20/2007 : 10:46:39
|
Under "Full database Backup", the SQL comment is misleading
-- Back up the AdventureWorks as differential backup
Otherwise, thank you for an informative article
|
|
|
|
graz
Chief SQLTeam Crack Dealer
USA
4128 Posts |
Posted - 12/20/2007 : 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
Slovenia
11741 Posts |
|
|
R
Constraint Violating Yak Guru
United Kingdom
328 Posts |
Posted - 05/02/2008 : 10:42:24
|
Hi
Will there be a 'next article' ?
Hopefully yes, there will...! |
|
|
|
spirit1
Cybernetic Yak Master
Slovenia
11741 Posts |
|
|
web3media
Starting Member
1 Posts |
Posted - 10/16/2008 : 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
Flowing Fount of Yak Knowledge
South Africa
4507 Posts |
Posted - 10/16/2008 : 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 Shaw
SQL Server MVP |
|
|
|
blindman
Flowing Fount of Yak Knowledge
USA
2365 Posts |
Posted - 10/16/2008 : 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
Flowing Fount of Yak Knowledge
South Africa
4507 Posts |
Posted - 10/16/2008 : 13:54:59
|
Yes. A couple, though no one seems to state it clearly.
http://www.sqlskills.com/blogs/paul/post/BACKUP-LOG-WITH-NO_LOG-use-abuse-and-undocumented-trace-flags-to-stop-it.aspx
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.
http://www.sqlskills.com/blogs/paul/post/Search-Engine-QA-1-Running-out-of-transaction-log-space.aspx
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 Shaw
SQL Server MVP |
|
|
|
joseph
Starting Member
Italy
10 Posts |
Posted - 09/18/2009 : 05:35:38
|
quote:
Tail log backup
There 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 - 08/08/2012 : 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. |
Edited by - Alex_Green on 08/16/2012 04:26:10 |
|
|
| |
Topic |
|
Article: Introduction to SQL Server 2005 Database Backups
