SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Introduction to SQL Server 2005 Database Backups
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 12/19/2007 :  08:12:15  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote

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

Sweden
30218 Posts

Posted - 12/19/2007 :  08:40:03  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Looking forward to read next chapter!



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

dbird
Starting Member

4 Posts

Posted - 12/19/2007 :  11:23:32  Show Profile  Visit dbird's Homepage  Reply with Quote
Great article. It reminded me of some backups useful backup options that I forgot about. Definitely worth keeping as a quick reference.

Go to Top of Page

talleyrand
Starting Member

USA
35 Posts

Posted - 12/20/2007 :  10:46:39  Show Profile  Visit talleyrand's Homepage  Click to see talleyrand's MSN Messenger address  Send talleyrand a Yahoo! Message  Reply with Quote
Under "Full database Backup", the SQL comment is misleading
-- Back up the AdventureWorks as differential backup

Otherwise, thank you for an informative article


Go to Top of Page

graz
Chief SQLTeam Crack Dealer

USA
4137 Posts

Posted - 12/20/2007 :  11:20:12  Show Profile  Visit graz's Homepage  Reply with Quote
Yes it is. So much for my proof reading skills. It's fixed.

=================================================
Creating tomorrow's legacy systems today. One crisis at a time.
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11750 Posts

Posted - 12/20/2007 :  11:28:51  Show Profile  Visit spirit1's Homepage  Reply with Quote
and mine...

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out
Go to Top of Page

R
Constraint Violating Yak Guru

United Kingdom
328 Posts

Posted - 05/02/2008 :  10:42:24  Show Profile  Reply with Quote
Hi

Will there be a 'next article' ?

Hopefully yes, there will...!
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11750 Posts

Posted - 05/04/2008 :  06:48:48  Show Profile  Visit spirit1's Homepage  Reply with Quote
yes there will be. real life sometimes gets in the way of things

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com
Go to Top of Page

web3media
Starting Member

1 Posts

Posted - 10/16/2008 :  00:06:11  Show Profile  Reply with Quote
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.
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 10/16/2008 :  04:05:50  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
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
Go to Top of Page

blindman
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 10/16/2008 :  10:30:22  Show Profile  Reply with Quote
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.
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 10/16/2008 :  13:54:59  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
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
Go to Top of Page

joseph
Starting Member

Italy
10 Posts

Posted - 09/18/2009 :  05:35:38  Show Profile  Reply with Quote
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.
Go to Top of Page

Alex_Green
Starting Member

3 Posts

Posted - 08/08/2012 :  05:54:20  Show Profile  Reply with Quote
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
Go to Top of Page

elliswhite
Starting Member

36 Posts

Posted - 05/03/2014 :  01:39:13  Show Profile  Reply with Quote
I could have take my database backup very smoothly, thanks for sharing article.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000