Three recovery models for backing up your SQL Server

By Ed Martin, Builder.com, 1 May, 2003 16:53

Backup tips
Use the following checklist to make sure you don't forget crucial steps during your weekly database backups.

Back up the master database weekly. Run a manual backup if you create, alter, or drop a database; add new SQL Server messages; add or drop linked servers; or add log devices.
Back up the msdb database daily. It's usually pretty small, but it's important because it contains all SQL Server jobs, operators, and scheduled tasks.
Back up the model database only if you've modified it.
Use SQL Server Agent to schedule your backup jobs.
If you have the resources available in your production environment, back up production databases to disk either locally or on a network server on the same switch.
Then, copy the backup files/devices to tape. With many hardware failures (especially with RAID systems), the disk is usually intact. Recovery will go much faster if the backup files are on disk.
Back up development and test databases using, at a minimum, the SIMPLE recovery model.
In addition to regularly scheduled backups, back up user databases after nonlogged bulk operations (e.g., bulk copies), creating indexes, or changing the recovery model.
If you're not using the SIMPLE recovery model, remember to back up your database AFTER you've truncated the transaction log.
Document your recovery steps. At the very least, outline the steps, noting where all the important files are.

All of this information remains in the transaction log until the log is truncated -- that is, until all committed transactions are purged from the log. In the SIMPLE recovery model, the log is truncated during a CHECKPOINT, when the SQL Server's memory cache is written to disk, which happens automatically but can be performed manually. That is why the SIMPLE recovery model doesn't support point-in-time recovery. With the FULL and BULK_LOGGED recovery models, the transaction log is truncated when the transaction log is backed up, unless you've explicitly specified not to truncate. To back up a transaction log, use the BACKUP LOG command. The basic syntax is very similar to the BACKUP command: BACKUP LOG { database } TO Here is an example of how to back up the transaction log to a logical device called LogBackupDevice: BACKUP TRANSACTION Northwind TO LogBackupDevice To prevent the truncation of the transaction log, use the NO_TRUNCATE option, like so: BACKUP TRANSACTION Northwind TO LogBackupDevice WITH NO_TRUNCATE Just the basics Although I just covered the basics of database recovery in this article, you can move in the right direction by following these tips. So save yourself some panic by backing up the master weekly and the msdb daily.

More enterprise IT news in ZDNet UK's Tech Update Channel.

For a weekly round-up of the enterprise IT news, sign up for the Tech Update newsletter. Let the editors know what you think in the Mailroom.

« Previous
1
2
3
Next »

Talkback

Hello I am quite new to SQL server and when I try to run "SELECT dbpropertyex("database", "recovery")" against the Master Database I get the error: "Server: Msg 195, Level 15, State 10, Line 1
'dbpropertyex' is not a recognized function name."

Why is this and how can i get a listing of which recovery mode each of my databases are in?

via Facebook 30 September, 2004 09:45

USE the following, the above will not work.

SELECT [Name] AS DatabaseName, DatabasePropertyEx([Name],'recovery') AS RecoveryModel
from SysDatabases ORDER BY [Name]

via Facebook 4 October, 2005 08:38

SELECT DATABASEPROPERTYEX('tempdb','recovery')

via Facebook 22 June, 2006 08:34

Post your comment

In order to post a comment you need to be registered and logged in.

You can also log in with Facebook. Log in or create your ZDNet UK account below

By signing up for this service, you indicate that you agree to our Terms and Conditions and have read and understood our Privacy Policy. Questions about membership? Find the answers in the Community FAQ

ZDNet UK Live

ike fuelband is great for every healthminded person ! to work out! theres this website called textme4free.com that you can use to text anywhere in...

7 hours ago by bordero on Nike's FuelBand wristband gamifies exercise

> I'm told it's somewhat annoying when people have their Macs stolen > and Apple stores treat the thief as the owner, but there you go. Ouch,...

9 hours ago by BrownieBoy on AMD Ultrathins to challenge Intel Ultrabooks

@kevinmchapman. OK, I acknowledge that 'most' was a gratuitous throwaway comment as an afterthought and too presumptuous. As to proof, as you...

14 hours ago by Moley on A tale of two distros: Ubuntu and Linux Mint

@BrownieBoy > Works really well for thieves.... >> Nice attempt to deflect the argument by tossing in a point that's totally >> irrelevant, even...

15 hours ago by Jack Schofield on AMD Ultrathins to challenge Intel Ultrabooks

fantastic that the so called piracy bills have been withdrawn. however, these anti-democracy supporters are still in the shadows so lets be alert...

15 hours ago by raskolnikof on SOPA, Protect IP support wavers in face of online protest

Please God no; teach them anything you like - thinking rationally, the uses and misuses of data, what data is and what it's not - but leave the...

18 hours ago by Tony Douglas via Facebook on Kids are the future. Teach ’em to code.

@Jack, > Works really well for thieves.... Nice attempt to deflect the argument by tossing in a point that's totally irrelevant, even it were...

1 day ago by BrownieBoy on AMD Ultrathins to challenge Intel Ultrabooks

Make that 13 people now - I got refused today at Manchester airport. I thought I was up to date on this legislation - I knew of the EU ruling from...

1 day ago by bootlegger on UK airport body scans will not be opt out

Don't forget to check out apps like GoodReader or SlideShark either, they're indispensible for people on the go in presentation situations. Best...

2 days ago by tinycg on Four top iPad apps for people on the move

Well it seems there is something a number of us agree on. Why is the Ubuntu Unity launcher so ugly? I thought perhaps it was something to do with...

2 days ago by TerryRK on A tale of two distros: Ubuntu and Linux Mint

Duplicate comments are not made intentionally. Its very good to know that now you are keeping check on this problem because sometimes a commenter...

2 days ago by Freebies202 on Microsoft fixes blog comments, speeds up blogs with open source

"the very significant number of users" and "many (most) of us" - you have no evidence for these statements. It is a fact that most users are saying...

2 days ago by kevinmchapman on A tale of two distros: Ubuntu and Linux Mint