Backup strategy

I've recently inherited a position where I am responsible for the well-being
of some DBs.
2 (much) more important than others.
The current recovery model, from what I can tell, is to do a full db/log
backup overnight.
This .bak file is then written to tape as well as saved on the disk for 2
days.

Both these dbs are used fairly extensively 8-5pm and losing data would not
be good.
The db sizes are approx 5gb and 3gb.
This doesn't seem like the ideal situation to me. Everything I read tells
me... full backup periodically, differential nightly and transaction hourly.
Agreed?
If so then I have 2 questions:
1. Is the best way to do this via a maintenance plan or by scripting and
scheduling?
2. What, if any, overhead can be expected with regular transaction backups
during work hours?

A bit of a pointer to #1 would be appreciated also.
Thanks.Both the databases are quite small, so using full, differential and log
backups may be overkill - you might find that a full backup once or
twice a day with log backups every hour (or whatever) is fine. On the
other hand, if the databases are growing fast, then you may want to
allow for that from the beginning.

In my opinion, maintenance plans are useful for quickly getting backups
running in a new environment, but their functionality and flexibility
can be limited. So I would probably use them as a starting point while
I worked out what the longer term backup plan should be. For example, I
don't believe that maintenance plans support differential backups, so
if you need them then you would have to create your own jobs anyway.
See "Reducing Recovery Time" in Books Online for details of using
different backup types together.

The overhead of a transaction log backup is likely to be minimal, but
as always, it's best to test it yourself in your own environment. It
would depend on the number of transactions, the disk layout and the
destination for the backups etc. But even if there is some slight
impact, it's probably a small price to pay for having the backups
available in the case of an outage.

Simon|||Jay,

The only thing I use the maintenance jobs for are two (2) tasks.
1. checking the consistency of the database (usually at 11pm on Sat)
2. doing the reindex of the database (usally at 1 am on Fri)

The rest of the backups I script manually. I create a backup device for
each database.
Job #1 - Full Backup of the database (usually at 6pm each night)
Job #2 - Transaction log backup every hour from 7am to 5pm.
Job #3 - Full backup of the database to a separate device at 12 noon (not
all databases)

Oscar...

"Jay" <jazemail@.gmail.com> wrote in message
news:Hqepe.5753$U4.811596@.news.xtra.co.nz...
> I've recently inherited a position where I am responsible for the
well-being
> of some DBs.
> 2 (much) more important than others.
> The current recovery model, from what I can tell, is to do a full db/log
> backup overnight.
> This .bak file is then written to tape as well as saved on the disk for 2
> days.
> Both these dbs are used fairly extensively 8-5pm and losing data would not
> be good.
> The db sizes are approx 5gb and 3gb.
> This doesn't seem like the ideal situation to me. Everything I read tells
> me... full backup periodically, differential nightly and transaction
hourly.
> Agreed?
> If so then I have 2 questions:
> 1. Is the best way to do this via a maintenance plan or by scripting and
> scheduling?
> 2. What, if any, overhead can be expected with regular transaction backups
> during work hours?
> A bit of a pointer to #1 would be appreciated also.
> Thanks.|||"Oscar Santiesteban Jr." <oscarsantiesteban@.worldnet.att.net> wrote in
message news:Qgtpe.912601$w62.11314@.bgtnsc05-news.ops.worldnet.att.net...
> Jay,
> The only thing I use the maintenance jobs for are two (2) tasks.
> 1. checking the consistency of the database (usually at 11pm on Sat)
> 2. doing the reindex of the database (usally at 1 am on Fri)

Forgive me if I don't follow your practise. Those hours are the only hours
I'm *not* thinking about DBs :)
I've learned that the transaction log is written to 2 HDD (raid) so it isn't
as bad as I thought.
As a precaution I now back up the transaction log to a USB drive each
lunchtime.

Cheers
Jay

> The rest of the backups I script manually. I create a backup device for
> each database.
> Job #1 - Full Backup of the database (usually at 6pm each night)
> Job #2 - Transaction log backup every hour from 7am to 5pm.
> Job #3 - Full backup of the database to a separate device at 12 noon (not
> all databases)
> Oscar...
> "Jay" <jazemail@.gmail.com> wrote in message
> news:Hqepe.5753$U4.811596@.news.xtra.co.nz...
>> I've recently inherited a position where I am responsible for the
> well-being
>> of some DBs.
>> 2 (much) more important than others.
>> The current recovery model, from what I can tell, is to do a full db/log
>> backup overnight.
>> This .bak file is then written to tape as well as saved on the disk for 2
>> days.
>>
>> Both these dbs are used fairly extensively 8-5pm and losing data would
>> not
>> be good.
>> The db sizes are approx 5gb and 3gb.
>> This doesn't seem like the ideal situation to me. Everything I read tells
>> me... full backup periodically, differential nightly and transaction
> hourly.
>> Agreed?
>> If so then I have 2 questions:
>> 1. Is the best way to do this via a maintenance plan or by scripting and
>> scheduling?
>> 2. What, if any, overhead can be expected with regular transaction
>> backups
>> during work hours?
>>
>> A bit of a pointer to #1 would be appreciated also.
>> Thanks.
>>
>>|||"Jay" <jazemail@.gmail.com> wrote in message
news:Hqepe.5753$U4.811596@.news.xtra.co.nz...
> I've recently inherited a position where I am responsible for the
well-being
> of some DBs.
> 2 (much) more important than others.
> The current recovery model, from what I can tell, is to do a full db/log
> backup overnight.
> This .bak file is then written to tape as well as saved on the disk for 2
> days.
> Both these dbs are used fairly extensively 8-5pm and losing data would not
> be good.
> The db sizes are approx 5gb and 3gb.
> This doesn't seem like the ideal situation to me. Everything I read tells
> me... full backup periodically, differential nightly and transaction
hourly.
> Agreed?
> If so then I have 2 questions:
> 1. Is the best way to do this via a maintenance plan or by scripting and
> scheduling?

For this I'd use a simple maintenance plan to back up your logs every X
minutes.

> 2. What, if any, overhead can be expected with regular transaction backups
> during work hours?

VERY little.

We run our transaction log backups every 20 minutes and notice no impact.

(in fact, ironically in some cases, running them MORE often can be better
since they'll be smaller when you do run them.)

> A bit of a pointer to #1 would be appreciated also.
> Thanks.

Backup Software

Hi

We are getting ready to rollout a new system in the next few months and I have started to look at backup and recovery strategies for SQL Server 2005 on which this new system will be housed. The proposal is to by Veritas Backup Exec with the server this system will be housed on however my knowledge is "limited" to the backup and restore options available directly from SQL. I have been unable to find any opinion on using third party software to backup and restore SQL Server 2005 databases.

Can anyone help to give me an objective opinion on this subject. Should I simply use SQL Server and the tools it ships with or do these third-party applications make it easier?

Many thanks in advance of your help

Cheers

Danny

I find that using SQL's built in backup facility, backing up to a local drive, and then having a third party network backup product to move the backup to other locations/media is often the best choice.

|||I NEVER use backup agents to directly backup my SQL Server databases. The closest that I come is using SQL Litespeed at several customers so that we can take advantage of the compression and encryption features. Beyond that, I backup to disk and then use a utilty like Backup Exec to archive my backups to tape. I've simply had way too many very bad experiences in live disasters in the past to ever do backups any other way.|||

I have used redgate sql backup. this has more compresiion ratio , passowrd protected and backup will be encrypted.

However, SQL Server native backup also work for me lot of time. it will take more time than other tools but u can work with that

|||

I find SQL backup still the easiest (well, it could use lots of improvements) to use

We use Maintainence Plan to backup onto network file server on RAID5 (say we keep a weekly rotation)

and future plan is to use Veritas Backup Exec to backup those .BAK files (not sure how long the rotation will be, or if it's even necessary to backup SQL BAK files onto tape)

We did try Veritas SQL Agent as well, but due to network issues that's been halted for now

It wasn't too bad to use

|||

Backing-up to disk, using either SQL Server's native backups or a third-party backup tool, then backing-up from disk to tape is a great way to go if you have a sufficient time window. If you plan your backup regime properly you can keep several days' backups on disk and backup only the most recent backups to tape on a daily basis. This approach is beneficial when someone asks for a copy of a particular database from several days ago as you don't have to mess around recalling tapes etc...

Whether to use SQL Server's native backups or, instead, a third-party tool, such as Quest's SQL Litespeed or Red-Gate's SQL Backup, depends on whether you need to minimise the storage requirements of your backups and/or if you need to reduce the time taken to create the backup files. For instance, if you have only a couple of databases each of, say, 1GB in size then it probably isn't going to be worth investing in a third-party tool. If, on the otherhand, you have databases of, say, 200GB in size and you're backing-up to disk across a network then you'll probably see significant reductions in both backup file sizes and backup times if you use a third-party product that is capable of compression.

We use either SQL Server backups or SQL Litespeed backups, scheduled by SQL Agent, depending on the sizes of the databases on our servers - the decision is made on a server-by-server basis. We then use Backup Exec to backup the resultant backup files to tape. I must admit that I've never used Backup Exec to backup data straight from SQL Server, however to me it just doesn't 'feel' right doing it that way - anyway we're happy with the solution that we use so there's no need to change it. One other thing is that we've been using SQL Litespeed for around three years and performed literally hundreds of test restores and we have never had problems restoring databases from the compressed files (and, no, I don't work for Quest... ;) ).

Chris

|||Thank you all for your updates, most helpful!!!|||

The easiest way for me is to use a sql server backup program. The one I reccomend is www.4backuponline.com it will let you have a local copy and an online copy of you backup and it's really simple to use. You set it up once and choose when to run backups and it will run automatically and send daily emails with backup status.

I hope it helps,

|||I always do a quick backup of my project using Developer's Backup.|||

I do know that sqlbase a tape is not "allowed" to be ran over the database.

This is not the same situation for sql is it?

Backup Software

Hi

We are getting ready to rollout a new system in the next few months and I have started to look at backup and recovery strategies for SQL Server 2005 on which this new system will be housed. The proposal is to by Veritas Backup Exec with the server this system will be housed on however my knowledge is "limited" to the backup and restore options available directly from SQL. I have been unable to find any opinion on using third party software to backup and restore SQL Server 2005 databases.

Can anyone help to give me an objective opinion on this subject. Should I simply use SQL Server and the tools it ships with or do these third-party applications make it easier?

Many thanks in advance of your help

Cheers

Danny

I find that using SQL's built in backup facility, backing up to a local drive, and then having a third party network backup product to move the backup to other locations/media is often the best choice.

|||I NEVER use backup agents to directly backup my SQL Server databases. The closest that I come is using SQL Litespeed at several customers so that we can take advantage of the compression and encryption features. Beyond that, I backup to disk and then use a utilty like Backup Exec to archive my backups to tape. I've simply had way too many very bad experiences in live disasters in the past to ever do backups any other way.|||

I have used redgate sql backup. this has more compresiion ratio , passowrd protected and backup will be encrypted.

However, SQL Server native backup also work for me lot of time. it will take more time than other tools but u can work with that

|||

I find SQL backup still the easiest (well, it could use lots of improvements) to use

We use Maintainence Plan to backup onto network file server on RAID5 (say we keep a weekly rotation)

and future plan is to use Veritas Backup Exec to backup those .BAK files (not sure how long the rotation will be, or if it's even necessary to backup SQL BAK files onto tape)

We did try Veritas SQL Agent as well, but due to network issues that's been halted for now

It wasn't too bad to use

|||

Backing-up to disk, using either SQL Server's native backups or a third-party backup tool, then backing-up from disk to tape is a great way to go if you have a sufficient time window. If you plan your backup regime properly you can keep several days' backups on disk and backup only the most recent backups to tape on a daily basis. This approach is beneficial when someone asks for a copy of a particular database from several days ago as you don't have to mess around recalling tapes etc...

Whether to use SQL Server's native backups or, instead, a third-party tool, such as Quest's SQL Litespeed or Red-Gate's SQL Backup, depends on whether you need to minimise the storage requirements of your backups and/or if you need to reduce the time taken to create the backup files. For instance, if you have only a couple of databases each of, say, 1GB in size then it probably isn't going to be worth investing in a third-party tool. If, on the otherhand, you have databases of, say, 200GB in size and you're backing-up to disk across a network then you'll probably see significant reductions in both backup file sizes and backup times if you use a third-party product that is capable of compression.

We use either SQL Server backups or SQL Litespeed backups, scheduled by SQL Agent, depending on the sizes of the databases on our servers - the decision is made on a server-by-server basis. We then use Backup Exec to backup the resultant backup files to tape. I must admit that I've never used Backup Exec to backup data straight from SQL Server, however to me it just doesn't 'feel' right doing it that way - anyway we're happy with the solution that we use so there's no need to change it. One other thing is that we've been using SQL Litespeed for around three years and performed literally hundreds of test restores and we have never had problems restoring databases from the compressed files (and, no, I don't work for Quest... ;) ).

Chris

|||Thank you all for your updates, most helpful!!!|||

The easiest way for me is to use a sql server backup program. The one I reccomend is www.4backuponline.com it will let you have a local copy and an online copy of you backup and it's really simple to use. You set it up once and choose when to run backups and it will run automatically and send daily emails with backup status.

I hope it helps,

|||I always do a quick backup of my project using Developer's Backup.|||

I do know that sqlbase a tape is not "allowed" to be ran over the database.

This is not the same situation for sql is it?

Backup Software

Hi

We are getting ready to rollout a new system in the next few months and I have started to look at backup and recovery strategies for SQL Server 2005 on which this new system will be housed. The proposal is to by Veritas Backup Exec with the server this system will be housed on however my knowledge is "limited" to the backup and restore options available directly from SQL. I have been unable to find any opinion on using third party software to backup and restore SQL Server 2005 databases.

Can anyone help to give me an objective opinion on this subject. Should I simply use SQL Server and the tools it ships with or do these third-party applications make it easier?

Many thanks in advance of your help

Cheers

Danny

I find that using SQL's built in backup facility, backing up to a local drive, and then having a third party network backup product to move the backup to other locations/media is often the best choice.

|||I NEVER use backup agents to directly backup my SQL Server databases. The closest that I come is using SQL Litespeed at several customers so that we can take advantage of the compression and encryption features. Beyond that, I backup to disk and then use a utilty like Backup Exec to archive my backups to tape. I've simply had way too many very bad experiences in live disasters in the past to ever do backups any other way.|||

I have used redgate sql backup. this has more compresiion ratio , passowrd protected and backup will be encrypted.

However, SQL Server native backup also work for me lot of time. it will take more time than other tools but u can work with that

|||

I find SQL backup still the easiest (well, it could use lots of improvements) to use

We use Maintainence Plan to backup onto network file server on RAID5 (say we keep a weekly rotation)

and future plan is to use Veritas Backup Exec to backup those .BAK files (not sure how long the rotation will be, or if it's even necessary to backup SQL BAK files onto tape)

We did try Veritas SQL Agent as well, but due to network issues that's been halted for now

It wasn't too bad to use

|||

Backing-up to disk, using either SQL Server's native backups or a third-party backup tool, then backing-up from disk to tape is a great way to go if you have a sufficient time window. If you plan your backup regime properly you can keep several days' backups on disk and backup only the most recent backups to tape on a daily basis. This approach is beneficial when someone asks for a copy of a particular database from several days ago as you don't have to mess around recalling tapes etc...

Whether to use SQL Server's native backups or, instead, a third-party tool, such as Quest's SQL Litespeed or Red-Gate's SQL Backup, depends on whether you need to minimise the storage requirements of your backups and/or if you need to reduce the time taken to create the backup files. For instance, if you have only a couple of databases each of, say, 1GB in size then it probably isn't going to be worth investing in a third-party tool. If, on the otherhand, you have databases of, say, 200GB in size and you're backing-up to disk across a network then you'll probably see significant reductions in both backup file sizes and backup times if you use a third-party product that is capable of compression.

We use either SQL Server backups or SQL Litespeed backups, scheduled by SQL Agent, depending on the sizes of the databases on our servers - the decision is made on a server-by-server basis. We then use Backup Exec to backup the resultant backup files to tape. I must admit that I've never used Backup Exec to backup data straight from SQL Server, however to me it just doesn't 'feel' right doing it that way - anyway we're happy with the solution that we use so there's no need to change it. One other thing is that we've been using SQL Litespeed for around three years and performed literally hundreds of test restores and we have never had problems restoring databases from the compressed files (and, no, I don't work for Quest... ;) ).

Chris

|||Thank you all for your updates, most helpful!!!|||

The easiest way for me is to use a sql server backup program. The one I reccomend is www.4backuponline.com it will let you have a local copy and an online copy of you backup and it's really simple to use. You set it up once and choose when to run backups and it will run automatically and send daily emails with backup status.

I hope it helps,

|||I always do a quick backup of my project using Developer's Backup.|||

I do know that sqlbase a tape is not "allowed" to be ran over the database.

This is not the same situation for sql is it?

sql

Backup Software

Hi

We are getting ready to rollout a new system in the next few months and I have started to look at backup and recovery strategies for SQL Server 2005 on which this new system will be housed. The proposal is to by Veritas Backup Exec with the server this system will be housed on however my knowledge is "limited" to the backup and restore options available directly from SQL. I have been unable to find any opinion on using third party software to backup and restore SQL Server 2005 databases.

Can anyone help to give me an objective opinion on this subject. Should I simply use SQL Server and the tools it ships with or do these third-party applications make it easier?

Many thanks in advance of your help

Cheers

Danny

I find that using SQL's built in backup facility, backing up to a local drive, and then having a third party network backup product to move the backup to other locations/media is often the best choice.

|||I NEVER use backup agents to directly backup my SQL Server databases. The closest that I come is using SQL Litespeed at several customers so that we can take advantage of the compression and encryption features. Beyond that, I backup to disk and then use a utilty like Backup Exec to archive my backups to tape. I've simply had way too many very bad experiences in live disasters in the past to ever do backups any other way.|||

I have used redgate sql backup. this has more compresiion ratio , passowrd protected and backup will be encrypted.

However, SQL Server native backup also work for me lot of time. it will take more time than other tools but u can work with that

|||

I find SQL backup still the easiest (well, it could use lots of improvements) to use

We use Maintainence Plan to backup onto network file server on RAID5 (say we keep a weekly rotation)

and future plan is to use Veritas Backup Exec to backup those .BAK files (not sure how long the rotation will be, or if it's even necessary to backup SQL BAK files onto tape)

We did try Veritas SQL Agent as well, but due to network issues that's been halted for now

It wasn't too bad to use

|||

Backing-up to disk, using either SQL Server's native backups or a third-party backup tool, then backing-up from disk to tape is a great way to go if you have a sufficient time window. If you plan your backup regime properly you can keep several days' backups on disk and backup only the most recent backups to tape on a daily basis. This approach is beneficial when someone asks for a copy of a particular database from several days ago as you don't have to mess around recalling tapes etc...

Whether to use SQL Server's native backups or, instead, a third-party tool, such as Quest's SQL Litespeed or Red-Gate's SQL Backup, depends on whether you need to minimise the storage requirements of your backups and/or if you need to reduce the time taken to create the backup files. For instance, if you have only a couple of databases each of, say, 1GB in size then it probably isn't going to be worth investing in a third-party tool. If, on the otherhand, you have databases of, say, 200GB in size and you're backing-up to disk across a network then you'll probably see significant reductions in both backup file sizes and backup times if you use a third-party product that is capable of compression.

We use either SQL Server backups or SQL Litespeed backups, scheduled by SQL Agent, depending on the sizes of the databases on our servers - the decision is made on a server-by-server basis. We then use Backup Exec to backup the resultant backup files to tape. I must admit that I've never used Backup Exec to backup data straight from SQL Server, however to me it just doesn't 'feel' right doing it that way - anyway we're happy with the solution that we use so there's no need to change it. One other thing is that we've been using SQL Litespeed for around three years and performed literally hundreds of test restores and we have never had problems restoring databases from the compressed files (and, no, I don't work for Quest... ;) ).

Chris

|||Thank you all for your updates, most helpful!!!|||

The easiest way for me is to use a sql server backup program. The one I reccomend is www.4backuponline.com it will let you have a local copy and an online copy of you backup and it's really simple to use. You set it up once and choose when to run backups and it will run automatically and send daily emails with backup status.

I hope it helps,

|||I always do a quick backup of my project using Developer's Backup.|||

I do know that sqlbase a tape is not "allowed" to be ran over the database.

This is not the same situation for sql is it?

Backup Recovery Philosophy... pull up a chair

(Be forewarned... this is long, but I think you'll find the information interesting)

When it comes to SQL Server backup/restore/recovery, my approach is based on a two-part philosophy. I'd like to share this with you and get you opinion and feedback...

First, I approach recovery from the instance level, not the individual databases. While a SQL Server instance is made up of system- and user-databases, I make no distinctions between them in terms of recovery - the instance is the whole "database". I'll later explain why.

Second, I approach it so that it will ultimately allow me to do a "displace" recovery... not just "in-place" restore. In other words, be able to restore the database on the current host ("in-place"), such in a case of a media or other failures where the host is intact - which is usually the common, easy case. But above that, be able to recover the database onto another host, such in a case of a catastrophic failure of the original host.

At any rate, the two part approach equates to what I consider provisions for doing "displace SQL Server instance recovery" - the ability to restore the whole SQL Server Instance, including the original system-databases, onto another host.

I come from an Oracle background... in the world where there's only one "database instance"... no such thing as "individual" databases. And really, the term "database" in Microsoft equates to what Oracle calls a "tablespace". Oracle's "system tablespace" equates to Microsoft's "system databases"... and so forth. It's a common case of one vendor calling it, "TOE-MAY-TOE", and the other calling it, "TOE-MAH-TOE". And in Oracle... I'm not the habit of making distinction between these tablespaces - system or non-system - when it comes to backing up... not many are (I would think). Our backup appoach views backing up the WHOLE database instance... therefore if needed... we can restore the WHOLE database instance. I can't find any other logical way to approach database backup.

But as I've found... in the world of SQL Server, there's seems to be a disconnect between how user-databases should be backed up (and restored) from the the way system-databases should be backed up (and restored). Most people make distinction between these database when it comes to backup and recovery.

Let me it explain it this way...

In my shop, my backup strategy provides the capability to restore both system- and user-databases. Some people might be surprised, but all this can actually be done using only the built-in SQL Server backup and restore functions. What I've also found is SQL DBAs find it almost "un-natural" to restore system-databases. I've heard and read some say is this... "just restore the user-databases on the new server and "re-create" the objects (that were in the previous system database) into the new system databases on the new host". Some also suggests using scripts to make "re-creating" these objects easier.

I think that people approach it this way because restoring system database onto another server (a different host name, a different file directory structure, etc) is not as simple as restoring user-databases.

But it is doable... and doable using only the built-in SQL Server functions.
My previous post on this matter can be found here (http://sqlforums.windowsitpro...22923&highlight_key=y)

I long time ago, I found a way to do this. And since then, my backup strategy has remained fairly the same... backup the whole sql server instance... and have the ability to restore the whole instance (if/when needed). And in the end, we not only have a great backup strategy, but a strategy for disaster recovery.

I agree that the approach to [simply] restore user-database, and then "re-creating" system-database objects is viable, but it's not "absolute". And here's why...

You'll always have to make provisions around when system-database objects are created/changed... for the life of the instance... such as logins, for example.
I've seen some application create extended procedures (in the master database) and create jobs and plans (in msdb).
You'll have to consistently ensure you update your "re-create scripts" to ensure you capture these objects and changes... through the life of the instance.
You'll have to be that much more intimate with the applicatons in order to understand all the components it may be adding outside it's user-database.

There a huge potential risk that you'll miss something.

Bottom line... restoring the whole SQL Server instance avoids any of the above caveats, guaranteeing a "physical restore" [of the original instance] versus one where parts of it had to be "logically" recreated and/or potentially be missing.

I admit, I also use scripts to complete a full SQL Server instance recovery. For me, it is a set of batch and sql scripts, but I only use it to expedite the process. And more importantly... the scripts are created once for an instance and doesn't have to be updated...
regardless of what occurs within the instance...
regardless if new logins or extended procedures are created/updated (in the master)...
regardless if new/updatess to jobs/maintenance plans/etc occur (in msdb)...
and I don't even really know what goes on in model... but again... it doesn't matter...
all of it gets restored.

The process... scripts or no scripts... is the same for all SQL Server instance I support... regardless of the idiosyncrasies of the application running on them. I have to make NO special provisions for any of them.

It's a solution where at the end of the recovery... I can tell the customer that whatever they originally lost has been restored (from the last backup)... without any doubt. Again it's a "physical restore", not one where parts of it had to be "logically" recreated.

Lastly... If you understand my philosophy... then you could probably understand why I don't consider Log Shipping and Replication to be a thorough disaster recovery solution. The reason being is that these solutions only provision for the recovery of user-databases, right? You cannot use log shipping or replication for system databases, right? And as with the other... you'll have to provision for re-creating system-database objects. Again, I admit log shipping and replication are viable solution, but again, with the idea of re-creating system database objects... it's not absolute... not for system recovery.

I'm looking for good arguments and feedbacks to really put this approach to the test. Thanks.... this is long, but I think you'll find the information interestingOne out of two ain't bad...|||Thanks alot!
I was looking for "constructive" feedback... I guess that's best you can do.|||I'll expand on what I think Blindman was driving at with his comment, and add a few thoughts of my own.

Your system is good, and a notch better than database backups for some problems, but it is not foolproof for many reasons.

What you are looking to create is something like an Oracle dump combined with a full system backup, with the intent of being able to reload a full image of everything that either affects the databse or is affected by the database. The first part of this is easy to do with either Oracle or Microsoft SQL, the second part is out of scope for both products.

If an absolute, byte-for-byte one hundred percent backup is what you want, you can get there fairly easily but not using strictly Oracle or Microsoft SQL tools. The gist of the idea would go something like:

1) Spin up two or more virtual machines
2) Cluster them to form a single logical machine
3) Periodically halt each virtual machine (one at a time)
4) Backup the data files that represent the virtual machine
5) Restart the virtual machine
6) Loop back to step 3 for any additional virtual machines

This will give you the 100% control and full image backup that you've envisioned. It will include all of the registry entries, all of the DLL files, and all of the necessary system resource settings to do an "idiot level" reinstall of either type that you've described.

It seems relatively painless to me, although I think that it is overkill for anyone not banking on at least five nines of reliability.

-PatP|||No, what I was driving at was that his post was verbose, specific to his situation, offered little or no practical advice, and asked for no definable assistance.
This is a forum. It is not a blog. It is not a soapbox.|||I'll offer my .02 (and further violate blindman's sensibilities)

I try to create my backup/recovery plans based on the premise that tomorrow, I won't be at the office (won the lottery, got hit by a truck on my m/c, etc).

Keep it simple. Keep it consistent. From one server to the next and to the next beyond that.

Pick a plan. Document it. Test it. Tweak it if you have to (and document it again). Then make each instance the same. Dev. Test. Prod. If you're using third party tools in Prod, then use them in Dev. If Prod is Full Recovery Model, Dev and Test are too. If Prod is dumping to a remote server, test is too. If you're using SAN-based backups in Prod, use them in Test and Dev (okay, that may be a hard sell, but at least Test).

Then validate your plan. Have the junior DBA validate it. Go over it with the system admins and the network admins, too. Everyone should know what the plan is and have confidence that it will work.

By the way, I have a pretty strong Oracle background as well. I understand when you say that an Oracle tablespace is like an MS database, but I disagree with you. MS and Oracle diverged here and that's just all there is to it.

Regards,

hmscott|||Pat/hms... thanks for the feedback (despite the current company... which I address at the very end).

hms... interesting philosophy. There's definitely aspect of that I think I'll keep in mind and borrow.

Pat... It's not that I'm looking for byte-for-byte... but I'm looking for more a "physical" recovery approach than that of a "logical" one.

Let me try to clear my point.

At this stage... after discussing with others the same subject... I think I know how better clarify the point I'm trying to get at in my original post.

In terms of sql server instance recovery... such as in a disaster... or even if you just want to move the sql server instance to another server... and using only sql server tools... there seems to two prominent way to do this.

As I tried to explain in my original post, the one way I've been acquainted with is this:

1.) Restore the user-databases and then "re-create" the system databases objects. These system database objects could potentially include administration objects like backup jobs and devices. But I'm more concerned about the objects that belong to an application... because as I've witnessed it... some application create objects outside there user-database.

The other... that I've come to realize is this. And I know I may be "20 years late" (as implied by others elsewhere) but nonetheless... I realized it on my own:
2.) Restore the sql instance, including the system- and user-database from backup. Restoring the system databases from backup would mean you wouldn't have to "re-create" anything.

If you approach this situation via #1, here's my view on that. You have to know exactly what objects in the system database you need to re-create. And when it comes to potential objects that belong to the application... how can you ever be 100% sure you know all of them... and if, over time, they change, how would know about the changes if it is triggered/made by the application.

However, if you approach it via #2, then it doesn't matter what and where any of these objects are... restoring system databases ensures your recovery is complete of these objects.

So I guess all I'm trying to get is that for those you still prefer to do it via #1 (in the context I've described... restoring the whole instance to another server... whether for disaster recovery or simply moving the instance), I'd like to understand your rational... because there could be some things I am not realizing.

For me... doing via #2 means I can have a single procedure [in recovering an instance to another server] that can apply to all my sql server instance. I would view this as having a very consistent and standard way of performing instance recovery, which would help simplify my administration.

#1 would almost always have the need for me to fully understand an application to determine the objects that would need to be "re-created". This isn't necessarily impossible, but what make it even more unfavorable (for me) is that not only do I need to know what and where these objects are... but I'll also have to know when these objects gets updated by the application. Now, I admit, this circumstance may not be common enough... but I think it's probable enough to warrant the argument. Therefore any recovery of the instance would always have to be open the any potential idiosyncrasies of an application. This would complicate my administration... having me stray more into the realm of the application that I'd prefer.

#2 on the other hand... would simply restore the system databases and thus all/any application objects that are there will be restored "automatically" and be current up to version since the last backup of the system databases. This method removes any burden related to identifying what/where/when this objects are and the need to identify and manage an application potential idiosyncrasies.

I hope that's made my point a little clearer that you can address.
Thanks again for the feedbacks.

To Blindman:
I mean only to pose a discussion topic... something we can dialog about.
I honestly don't have any agenda other than trying to find some truth about how we do our jobs. I'm interested in what you think and also in how you think (your philosophy) on the subject. Is that so horrible??
Why can't that be enough?? Why does it gotta meet some set of criteria (all the time)?

And what BUGS me is that it wouldn't kill you to leave it alone. If it doesn't interest you... then don't reply! I even "warn" potential readers right from the start... "This is long."

"... this is a forum, not a blog" What the hell does that mean??
No one's forced to read anything... I'm not shoving my post down anyone's throat...and whatever I post, doesn't hinder anyone else for using the system... so what's the big deal.

Give me a break, man...
You're like angry... or something, man. It's just post.

Backup Question 100234987492874

Hi all,
The database in question uses Full Recovery Model but my maintenance plan
dictates to only keep 2 days of transaction log files. This has always
worked great.
For no obvious reason the backup no longer removes transaction files older
than 2 days so I am quickly running out of disk space. Again, nothing has
changed on this server, we keep detailed change management logs. I am
assuming that if I just blast the old .TRN files I will be fine but am still
puzzled why this behavior started and how to correctly stop it.
ThanksHi,
I have noticed this if the backup/transaction backup job in the schedule
doesn't complete successfully
--
I hope this helps
regards
Greg O MCSD
http://www.ag-software.com/ags_scribe_index.aspx. SQL Scribe Documentation
Builder, the quickest way to document your database
http://www.ag-software.com/ags_SSEPE_index.aspx. AGS SQL Server Extended
Property Extended properties manager for SQL 2000
http://www.ag-software.com/IconExtractionProgram.aspx. Free icon extraction
program
http://www.ag-software.com. Free programming tools
"A" <agarrettbNOSPAM@.hotmail.com> wrote in message
news:O106PHfuDHA.1060@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> The database in question uses Full Recovery Model but my maintenance plan
> dictates to only keep 2 days of transaction log files. This has always
> worked great.
> For no obvious reason the backup no longer removes transaction files older
> than 2 days so I am quickly running out of disk space. Again, nothing has
> changed on this server, we keep detailed change management logs. I am
> assuming that if I just blast the old .TRN files I will be fine but am
still
> puzzled why this behavior started and how to correctly stop it.
> Thanks
>|||Below KB might help:
http://support.microsoft.com/default.aspx?scid=kb;en-us;303292&Product=sql2k
Also, check out below great troubleshooting suggestions from Bill H at MS:
-- Log files don't delete --
This is likely to be either a permissions problem or a sharing violation
problem. The maintenance plan is run as a job, and jobs are run by the
SQLServerAgent service.
Permissions:
1. Determine the startup account for the SQLServerAgent service
(Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
account is the security context for jobs, and thus the maintenance plan.
2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
account) then skip step 3.
3. On that box, log onto NT as that account. Using Explorer, attempt to
delete an expired backup. If that succeeds then go to Sharing Violation
section.
4. Log onto NT with an account that is an administrator and use Explorer to
look at the Properties|Security of the folder (where the backups reside)
and ensure the SQLServerAgent startup account has Full Control. If the
SQLServerAgent startup account is LocalSystem, then the account to consider
is SYSTEM.
5. In NT, if an account is a member of an NT group, and if that group has
Access is Denied, then that account will have Access is Denied, even if
that account is also a member of the Administrators group. Thus you may
need to check group permissions (if the Startup Account is a member of a
group).
6. Keep in mind that permissions (by default) are inherited from a parent
folder. Thus, if the backups are stored in C:\bak, and if someone had
denied permission to the SQLServerAgent startup account for C:\, then
C:\bak will inherit access is denied.
Sharing violation:
This is likely to be rooted in a timing issue, with the most likely cause
being another scheduled process (such as NT Backup or Anti-Virus software)
having the backup file open at the time when the SQLServerAgent (i.e., the
maintenance plan job) tried to delete it.
1. Download filemon and handle from www.sysinternals.com.
2. I am not sure whether filemon can be scheduled, or you might be able to
use NT scheduling services to start filemon just before the maintenance
plan job is started, but the filemon log can become very large, so it would
be best to start it some short time before the maintenance plan starts.
3. Inspect the filemon log for another process that has that backup file
open (if your lucky enough to have started filemon before this other
process grabs the backup folder), and inspect the log for the results when
the SQLServerAgent agent attempts to open that same file.
4. Schedule the job or that other process to do their work at different
times.
5. You can use the handle utility if you are around at the time when the
job is scheduled to run.
If the backup files are going to a \\share or a mapped drive (as opposed to
local drive), then you will need to modify the above (with respect to where
the tests and utilities are run).
Finally, inspection of the maintenance plan's history report might be
useful.
Thanks,
Bill Hollinshead
Microsoft, SQL Server
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"A" <agarrettbNOSPAM@.hotmail.com> wrote in message news:O106PHfuDHA.1060@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> The database in question uses Full Recovery Model but my maintenance plan
> dictates to only keep 2 days of transaction log files. This has always
> worked great.
> For no obvious reason the backup no longer removes transaction files older
> than 2 days so I am quickly running out of disk space. Again, nothing has
> changed on this server, we keep detailed change management logs. I am
> assuming that if I just blast the old .TRN files I will be fine but am still
> puzzled why this behavior started and how to correctly stop it.
> Thanks
>

Backup Question

I have set up my server to use the simple recovery model. I understood this
to mean that the Transaction Log is not used. So why is it that my
Transaction Log is still being used and is growing?Simple does not mean that your T log is not used.
It means that your T-Log is truncated each time a transaction is
successfully comitted (i'm not sure about the truncation criteria).
But, unless I missed a point, this does not mean your T-Log is shrunk. You
still have to do it or by a maintenance plan, of by DBCC SHRINKDATASE on a
regular basis,
Chris
"Hoof Hearted" <HoofHearted@.discussions.microsoft.com> wrote in message
news:3FEABB58-6782-4980-8A02-1287A1E8D841@.microsoft.com...
> I have set up my server to use the simple recovery model. I understood
this
> to mean that the Transaction Log is not used. So why is it that my
> Transaction Log is still being used and is growing?|||simple recovery does not mean that your transaction will not be used.
Transaction logs will be used and this is an integral part of databases.
please note that when simple recovery model is set this means that the
in-active portion transaction(ie. commited transactions) are truncated at
every checkpoint(this is dictated by the configuration of your recovery
interval) and over written by new active , or uncommitted transactions. this
however does not mean that the transaction logfile size will reduce,
for example if you ran a procedure which grew your transaction log to 10Gb!
the fact that you have set a recovery model of simple means that as long as
all protions of this transaction log is active the physical log file will
grow to 10Gb!
once the transaction has been completed and committed the percentage use of
the transaction log file will be close to 0% check this with dbcc
sqlperf(logspace)
the file will still remain at 10Gb until you perform a dbcc shrinkfile which
will reduce the size of the physical file (with truncateonly option returns
space acquired back to sqlserver - see BOL for more info)
there is no reason to shrink the logfiles - especially if it will only grow
to it's original size, shrinking it will just put uneccessary IO overhead in
the middle of a transaction (ie. while sql server tries to grow log file to
accomodate the transaction)
HTH
"Hoof Hearted" wrote:
> I have set up my server to use the simple recovery model. I understood this
> to mean that the Transaction Log is not used. So why is it that my
> Transaction Log is still being used and is growing?

Backup Question

I have set up my server to use the simple recovery model. I understood this
to mean that the Transaction Log is not used. So why is it that my
Transaction Log is still being used and is growing?Simple does not mean that your T log is not used.
It means that your T-Log is truncated each time a transaction is
successfully comitted (i'm not sure about the truncation criteria).
But, unless I missed a point, this does not mean your T-Log is shrunk. You
still have to do it or by a maintenance plan, of by DBCC SHRINKDATASE on a
regular basis,
Chris
"Hoof Hearted" <HoofHearted@.discussions.microsoft.com> wrote in message
news:3FEABB58-6782-4980-8A02-1287A1E8D841@.microsoft.com...
> I have set up my server to use the simple recovery model. I understood
this
> to mean that the Transaction Log is not used. So why is it that my
> Transaction Log is still being used and is growing?|||simple recovery does not mean that your transaction will not be used.
Transaction logs will be used and this is an integral part of databases.
please note that when simple recovery model is set this means that the
in-active portion transaction(ie. commited transactions) are truncated at
every checkpoint(this is dictated by the configuration of your recovery
interval) and over written by new active , or uncommitted transactions. this
however does not mean that the transaction logfile size will reduce,
for example if you ran a procedure which grew your transaction log to 10Gb!
the fact that you have set a recovery model of simple means that as long as
all protions of this transaction log is active the physical log file will
grow to 10Gb!
once the transaction has been completed and committed the percentage use of
the transaction log file will be close to 0% check this with dbcc
sqlperf(logspace)
the file will still remain at 10Gb until you perform a dbcc shrinkfile which
will reduce the size of the physical file (with truncateonly option returns
space acquired back to sqlserver - see BOL for more info)
there is no reason to shrink the logfiles - especially if it will only grow
to it's original size, shrinking it will just put uneccessary IO overhead in
the middle of a transaction (ie. while sql server tries to grow log file to
accomodate the transaction)
HTH
"Hoof Hearted" wrote:

> I have set up my server to use the simple recovery model. I understood thi
s
> to mean that the Transaction Log is not used. So why is it that my
> Transaction Log is still being used and is growing?

backup question

Does point-in-time restore only for full recovery model?
and if i use a full - backup then can i restore in any time or just the
backup time?
or if use transaction log , it can only to restore to the time of
transaction log, can i restore one minutes before the transaction logHi
Yes, Point in time only works when a database is in full recovery mode, the
database is backed up, and the transaction log is backed up on a regular
basis.
You can do a point in time restore anywhere between the time the backup is
completed and the last transaction log backup is completed.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:F0B43886-5D63-4F9B-A3E3-223CA20A831D@.microsoft.com...
> Does point-in-time restore only for full recovery model?
> and if i use a full - backup then can i restore in any time or just the
> backup time?
> or if use transaction log , it can only to restore to the time of
> transaction log, can i restore one minutes before the transaction log

Backup Question

I have set up my server to use the simple recovery model. I understood this
to mean that the Transaction Log is not used. So why is it that my
Transaction Log is still being used and is growing?
Simple does not mean that your T log is not used.
It means that your T-Log is truncated each time a transaction is
successfully comitted (i'm not sure about the truncation criteria).
But, unless I missed a point, this does not mean your T-Log is shrunk. You
still have to do it or by a maintenance plan, of by DBCC SHRINKDATASE on a
regular basis,
Chris
"Hoof Hearted" <HoofHearted@.discussions.microsoft.com> wrote in message
news:3FEABB58-6782-4980-8A02-1287A1E8D841@.microsoft.com...
> I have set up my server to use the simple recovery model. I understood
this
> to mean that the Transaction Log is not used. So why is it that my
> Transaction Log is still being used and is growing?
|||simple recovery does not mean that your transaction will not be used.
Transaction logs will be used and this is an integral part of databases.
please note that when simple recovery model is set this means that the
in-active portion transaction(ie. commited transactions) are truncated at
every checkpoint(this is dictated by the configuration of your recovery
interval) and over written by new active , or uncommitted transactions. this
however does not mean that the transaction logfile size will reduce,
for example if you ran a procedure which grew your transaction log to 10Gb!
the fact that you have set a recovery model of simple means that as long as
all protions of this transaction log is active the physical log file will
grow to 10Gb!
once the transaction has been completed and committed the percentage use of
the transaction log file will be close to 0% check this with dbcc
sqlperf(logspace)
the file will still remain at 10Gb until you perform a dbcc shrinkfile which
will reduce the size of the physical file (with truncateonly option returns
space acquired back to sqlserver - see BOL for more info)
there is no reason to shrink the logfiles - especially if it will only grow
to it's original size, shrinking it will just put uneccessary IO overhead in
the middle of a transaction (ie. while sql server tries to grow log file to
accomodate the transaction)
HTH
"Hoof Hearted" wrote:

> I have set up my server to use the simple recovery model. I understood this
> to mean that the Transaction Log is not used. So why is it that my
> Transaction Log is still being used and is growing?

backup question

Does point-in-time restore only for full recovery model?
and if i use a full - backup then can i restore in any time or just the
backup time?
or if use transaction log , it can only to restore to the time of
transaction log, can i restore one minutes before the transaction log
Hi
Yes, Point in time only works when a database is in full recovery mode, the
database is backed up, and the transaction log is backed up on a regular
basis.
You can do a point in time restore anywhere between the time the backup is
completed and the last transaction log backup is completed.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:F0B43886-5D63-4F9B-A3E3-223CA20A831D@.microsoft.com...
> Does point-in-time restore only for full recovery model?
> and if i use a full - backup then can i restore in any time or just the
> backup time?
> or if use transaction log , it can only to restore to the time of
> transaction log, can i restore one minutes before the transaction log

backup question

Does point-in-time restore only for full recovery model?
and if i use a full - backup then can i restore in any time or just the
backup time?
or if use transaction log , it can only to restore to the time of
transaction log, can i restore one minutes before the transaction logHi
Yes, Point in time only works when a database is in full recovery mode, the
database is backed up, and the transaction log is backed up on a regular
basis.
You can do a point in time restore anywhere between the time the backup is
completed and the last transaction log backup is completed.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:F0B43886-5D63-4F9B-A3E3-223CA20A831D@.microsoft.com...
> Does point-in-time restore only for full recovery model?
> and if i use a full - backup then can i restore in any time or just the
> backup time?
> or if use transaction log , it can only to restore to the time of
> transaction log, can i restore one minutes before the transaction log

backup of transaction log

Hello all,
Disaster recovery scenario ques: I am taking full backups of my SQL Server database each midnight on tape and moving it off-site. I then take differential backups and also want to backup the transaction log. Assuming that my production database server is
on site and the site is burned down, how do I recover my transaction logs from? Do companies usually move transaction logs off-site as well?
Where are transaction logs generally stored to facilitate in disaster recovery? Thanks in advance for the help!
- Bill
Hi,
Normally you have to setup a disaster location away from your production
server location. In our case we have got the Disaster recovery server 1000
Miles from our production server. We have set a Logshipping betwen the
production server and Disater recovery server.
How to setup the DR server:-
1. Install the DR server with same Hardware configuration / OS and Patches /
SQL server edition and service packs
2. COnfigure a database identical to production
3. Make the database Readonly
4. Take a Full database backup from production and load it in Disaster
server
5. After that you can perform the transaction log backup, copy to DR server
and load it in DR server. You can fix the interval based on ur data growth
(Preferably 30 minutes)
6. This will ensure that in UR DR side data is available.
NOte:
1. SQL 2000 Enterprise edition has the Logshipping automated feature
http://www.microsoft.com/technet/pro.../logship1.mspx
2. You can Transactional replication also for this to set the Stand by
server.
Thanks
Hari
MCDBA
In SQL 2000 Enterprise edition you have got a
"Bill" <anonymous@.discussions.microsoft.com> wrote in message
news:C53B9957-8D1D-44CC-8023-EACF730AC1EB@.microsoft.com...
> Hello all,
> Disaster recovery scenario ques: I am taking full backups of my SQL Server
database each midnight on tape and moving it off-site. I then take
differential backups and also want to backup the transaction log. Assuming
that my production database server is on site and the site is burned down,
how do I recover my transaction logs from? Do companies usually move
transaction logs off-site as well?
> Where are transaction logs generally stored to facilitate in disaster
recovery? Thanks in advance for the help!
> - Bill
|||HI
Create Disaster recovery Plan
Create job for disaster recovery solution as disaster recovery plan describe.
Step 1.
Backup the database(s) to backup device based on current time and weekday
(Physical file located on file server, this file backed up to tape)
Step 2
Copy backup file(s) to off site server
Step 3 (Optional)
Restore database(s) on off site server
Decrease the backup time:
-Full backup saturday or sunday only, another weekdays create differential backup.
-Create the backup devices:
Use backup devices with INIT (overwrite) option (Tape backups from file server store old versions as need)
DB_Name_full
DB_Name_diff
DB_Name_log1
DB_Name_log2
...
Create Stored Srocedures for Disaster Recovery for all cases.
You can on remote server restore the database manually, or from job (You must create SP-s).
BOL: BACKUP and RESTORE
JBandi
|||Hari and Andras, Thank you for the insights!
-- Hari wrote: --
Hi,
Normally you have to setup a disaster location away from your production
server location. In our case we have got the Disaster recovery server 1000
Miles from our production server. We have set a Logshipping betwen the
production server and Disater recovery server.
How to setup the DR server:-
1. Install the DR server with same Hardware configuration / OS and Patches /
SQL server edition and service packs
2. COnfigure a database identical to production
3. Make the database Readonly
4. Take a Full database backup from production and load it in Disaster
server
5. After that you can perform the transaction log backup, copy to DR server
and load it in DR server. You can fix the interval based on ur data growth
(Preferably 30 minutes)
6. This will ensure that in UR DR side data is available.
NOte:
1. SQL 2000 Enterprise edition has the Logshipping automated feature
http://www.microsoft.com/technet/pro.../logship1.mspx
2. You can Transactional replication also for this to set the Stand by
server.
Thanks
Hari
MCDBA
In SQL 2000 Enterprise edition you have got a
"Bill" <anonymous@.discussions.microsoft.com> wrote in message
news:C53B9957-8D1D-44CC-8023-EACF730AC1EB@.microsoft.com...
> Hello all,
database each midnight on tape and moving it off-site. I then take
differential backups and also want to backup the transaction log. Assuming
that my production database server is on site and the site is burned down,
how do I recover my transaction logs from? Do companies usually move
transaction logs off-site as well?
recovery? Thanks in advance for the help!
> - Bill

backup of transaction log

Hello all,
Disaster recovery scenario ques: I am taking full backups of my SQL Server d
atabase each midnight on tape and moving it off-site. I then take differenti
al backups and also want to backup the transaction log. Assuming that my pro
duction database server is
on site and the site is burned down, how do I recover my transaction logs fr
om? Do companies usually move transaction logs off-site as well?
Where are transaction logs generally stored to facilitate in disaster recove
ry? Thanks in advance for the help!
- BillHi,
Normally you have to setup a disaster location away from your production
server location. In our case we have got the Disaster recovery server 1000
Miles from our production server. We have set a Logshipping betwen the
production server and Disater recovery server.
How to setup the DR server:-
1. Install the DR server with same hardware configuration / OS and Patches /
SQL server edition and service packs
2. COnfigure a database identical to production
3. Make the database Readonly
4. Take a Full database backup from production and load it in Disaster
server
5. After that you can perform the transaction log backup, copy to DR server
and load it in DR server. You can fix the interval based on ur data growth
(Preferably 30 minutes)
6. This will ensure that in UR DR side data is available.
NOte:
1. SQL 2000 Enterprise edition has the Logshipping automated feature
http://www.microsoft.com/technet/pr...n/logship1.mspx
2. You can Transactional replication also for this to set the Stand by
server.
Thanks
Hari
MCDBA
In SQL 2000 Enterprise edition you have got a
"Bill" <anonymous@.discussions.microsoft.com> wrote in message
news:C53B9957-8D1D-44CC-8023-EACF730AC1EB@.microsoft.com...
> Hello all,
> Disaster recovery scenario ques: I am taking full backups of my SQL Server
database each midnight on tape and moving it off-site. I then take
differential backups and also want to backup the transaction log. Assuming
that my production database server is on site and the site is burned down,
how do I recover my transaction logs from? Do companies usually move
transaction logs off-site as well?
> Where are transaction logs generally stored to facilitate in disaster
recovery? Thanks in advance for the help!
> - Bill|||HI
Create Disaster recovery Plan
Create job for disaster recovery solution as disaster recovery plan describe
.
Step 1.
Backup the database(s) to backup device based on current time and weekday
(Physical file located on file server, this file backed up to tape)
Step 2
Copy backup file(s) to off site server
Step 3 (Optional)
Restore database(s) on off site server
Decrease the backup time:
-Full backup saturday or sunday only, another weekdays create differential b
ackup.
-Create the backup devices:
Use backup devices with INIT (overwrite) option (Tape backups from file serv
er store old versions as need)
DB_Name_full
DB_Name_diff
DB_Name_log1
DB_Name_log2
...
Create Stored Srocedures for Disaster Recovery for all cases.
You can on remote server restore the database manually, or from job (You mus
t create SP-s).
BOL: BACKUP and RESTORE
JBandi|||Hari and Andras, Thank you for the insights!
-- Hari wrote: --
Hi,
Normally you have to setup a disaster location away from your production
server location. In our case we have got the Disaster recovery server 1000
Miles from our production server. We have set a Logshipping betwen the
production server and Disater recovery server.
How to setup the DR server:-
1. Install the DR server with same hardware configuration / OS and Patches /
SQL server edition and service packs
2. COnfigure a database identical to production
3. Make the database Readonly
4. Take a Full database backup from production and load it in Disaster
server
5. After that you can perform the transaction log backup, copy to DR server
and load it in DR server. You can fix the interval based on ur data growth
(Preferably 30 minutes)
6. This will ensure that in UR DR side data is available.
NOte:
1. SQL 2000 Enterprise edition has the Logshipping automated feature
http://www.microsoft.com/technet/pr...n/logship1.mspx
2. You can Transactional replication also for this to set the Stand by
server.
Thanks
Hari
MCDBA
In SQL 2000 Enterprise edition you have got a
"Bill" <anonymous@.discussions.microsoft.com> wrote in message
news:C53B9957-8D1D-44CC-8023-EACF730AC1EB@.microsoft.com...
> Hello all,
database each midnight on tape and moving it off-site. I then take
differential backups and also want to backup the transaction log. Assuming
that my production database server is on site and the site is burned down,
how do I recover my transaction logs from? Do companies usually move
transaction logs off-site as well?
recovery? Thanks in advance for the help!
> - Bill

Backup of Databases

For a static READ Only database with no scheduled job, is there any
databases other than those mentioned below I have to make a backup for
recovery purpose ?
1) User Database
2) Master
3) MSDB
Thanks"Stephen" schrieb:
> For a static READ Only database with no scheduled job, is there any
> databases other than those mentioned below I have to make a backup for
> recovery purpose ?
> 1) User Database
> 2) Master
> 3) MSDB
> Thanks
No. With no scheduled jobs you probably don't even need the msdb (if there's
no alerts, operators, or email support defined).
The master db is only needed to restore the logins that might map to db user
accounts.
The user db is generally restorable without any system db restore (that's
why you can restore a user db on any given SQL Server ...).|||Hi,
You are good enough.
If it is a read only database, then you could backup the database once and
keep it in a safe location. This includes all your system databases
(Master and MSDB) as well as your user databases.
Thanks
Hari
SQL Server MVP
"Stephen" <anonymous@.discussions.microsoft.com> wrote in message
news:%23h5mcSXeFHA.3864@.TK2MSFTNGP10.phx.gbl...
> For a static READ Only database with no scheduled job, is there any
> databases other than those mentioned below I have to make a backup for
> recovery purpose ?
> 1) User Database
> 2) Master
> 3) MSDB
> Thanks
>

Backup Maintenence plan question

I have recently installed some maintenace plans to perform database backups for an application database. The database is in full recovery mode so I have setup a full backup to be run once per day and then transaction log backups to run every 2 hours throughout the day. My question pertains to the coordination within SQL Server with respect to the database backup and the transaction log backups.

Let's say the full database backup is in progress and at the same time a trasnaction log backup is fired. What happens? Does SQL Server just skip the tran log backup since it won't be needed? or does it do the backup of the tran log anyway (in case the backup fails).

Another question would be when is it safe to delete the transaction log backup target file? Can it be assumed that after a SUCCESSFUL full backup that the transaction log backup can be deleted?

Since I have seperate maintenance plans in place here I am really trying to find out how/if these tasks are integrated within SQL Server. I'm more familiar with Oracle and the use of RMAN. Specifically for cleanups, RMAN knows what is safe to delete and what isn't and so it will keep a backup on disk if it feels it is needed. Is there similar functionality in SQL Server?

Hello,

You're correct: a full backup will block a tran log backup (until the blocking backup cmd completes), and vice-a-versa.

Following a successful full backup, you can safely delete all tran log backups taken prior to the full backup.

There is no equivelant RMAN functionality provided by SQL Server. There is, however the EXPRIEDATE and RETAINDAYS clause you can make use of in the backup command. Check out BOL for more info, but it may help you...

Cheers,

Rob

|||

Actually, that was the case in SQL 2000.

In SQL 2005, you can concurrently run database backup and tran-log backups. The tran-log backup would simply contain the range of LSNs that span the time of the full backup. It would be used as the starting point for rolling forward from restoring that full backup.

Backup log WITH NO_LOG - change recovery model to SIMPLE?

Hi,
I use FULL recovery model, SQL 2005. Is it possible this type of
backup ""change"" my recovery model to SIMPLE. I noticed when I
executed this:
BACKUP DATABSE db_name
TO DISK = 'path'
BACKUP LOG db_name WITH NO_LOG
DBCC SHRINKFILE ('db_name_log', truncateonly)
Now transact log grow very, very, very slow (this is symptom simple
model). But when executed this (different order):
BACKUP LOG db_name WITH NO_LOG
DBCC SHRINKFILE ('db_name_log', truncateonly)
BACKUP DATABSE db_name
TO DISK = 'path'
transact log grow normally
Would somebody explain me this, and tell me first statement change
(theoretically) my model to SIMPLE?
--
RegardsHi
First , read this article
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
<anxcomp@.gmail.com> wrote in message
news:1187684709.945537.84260@.g4g2000hsf.googlegroups.com...
> Hi,
> I use FULL recovery model, SQL 2005. Is it possible this type of
> backup ""change"" my recovery model to SIMPLE. I noticed when I
> executed this:
> BACKUP DATABSE db_name
> TO DISK = 'path'
> BACKUP LOG db_name WITH NO_LOG
> DBCC SHRINKFILE ('db_name_log', truncateonly)
>
> Now transact log grow very, very, very slow (this is symptom simple
> model). But when executed this (different order):
> BACKUP LOG db_name WITH NO_LOG
> DBCC SHRINKFILE ('db_name_log', truncateonly)
> BACKUP DATABSE db_name
> TO DISK = 'path'
> transact log grow normally
> Would somebody explain me this, and tell me first statement change
> (theoretically) my model to SIMPLE?
> --
> Regards
>|||This is expected. If you are in full mode and empty the log without actually doing a backup (which
is what TRUNCATE_ONLY and NO_LOG does), then subsequent real log backups would be useless. SQL
Server know this and in this situation, the database acts as if it is simple recovery (log is
auto-truncated).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<anxcomp@.gmail.com> wrote in message news:1187684709.945537.84260@.g4g2000hsf.googlegroups.com...
> Hi,
> I use FULL recovery model, SQL 2005. Is it possible this type of
> backup ""change"" my recovery model to SIMPLE. I noticed when I
> executed this:
> BACKUP DATABSE db_name
> TO DISK = 'path'
> BACKUP LOG db_name WITH NO_LOG
> DBCC SHRINKFILE ('db_name_log', truncateonly)
>
> Now transact log grow very, very, very slow (this is symptom simple
> model). But when executed this (different order):
> BACKUP LOG db_name WITH NO_LOG
> DBCC SHRINKFILE ('db_name_log', truncateonly)
> BACKUP DATABSE db_name
> TO DISK = 'path'
> transact log grow normally
> Would somebody explain me this, and tell me first statement change
> (theoretically) my model to SIMPLE?
> --
> Regards
>|||On Aug 21, 2:52 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> This is expected. If you are in full mode and empty the log without actually doing a backup (which
> is what TRUNCATE_ONLY and NO_LOG does), then subsequent real log backups would be useless. SQL
> Server know this and in this situation, the database acts as if it is simple recovery (log is
> auto-truncated).
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
>
> <anxc...@.gmail.com> wrote in messagenews:1187684709.945537.84260@.g4g2000hsf.googlegroups.com...
> > Hi,
> > I use FULL recovery model, SQL 2005. Is it possible this type of
> > backup ""change"" my recovery model to SIMPLE. I noticed when I
> > executed this:
> > BACKUP DATABSE db_name
> > TO DISK = 'path'
> > BACKUP LOG db_name WITH NO_LOG
> > DBCC SHRINKFILE ('db_name_log', truncateonly)
> > Now transact log grow very, very, very slow (this is symptom simple
> > model). But when executed this (different order):
> > BACKUP LOG db_name WITH NO_LOG
> > DBCC SHRINKFILE ('db_name_log', truncateonly)
> > BACKUP DATABSE db_name
> > TO DISK = 'path'
> > transact log grow normally
> > Would somebody explain me this, and tell me first statement change
> > (theoretically) my model to SIMPLE?
> > --
> > Regards- Hide quoted text -
> - Show quoted text -
to change recovery model from full to simple use alter database
command for that database
alter database dbname set recovery=simple
http://msdn2.microsoft.com/en-us/library/aa275464(SQL.80).aspx
setting to simple recovery helps to minimize log file growth ,but note
you will not be able to do point in time recovery incase of failures
Thanks
VS|||On 21 Sie, 11:52, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> This is expected. If you are in full mode and empty the log without actually doing a backup (which
> is what TRUNCATE_ONLY and NO_LOG does), then subsequent real log backups would be useless. SQL
> Server know this and in this situation, the database acts as if it is simple recovery (log is
> auto-truncated).
So it is realy true, it change to SIMPLE mode, thanks.
Tibor I read your article ' Why you want to be restrictive with
shrink of database files', I understand I shouldn't use both
SHRINKFILE and SHRINKDATABASE command.
So, would you help me create good backup plan, which ENSURE me that I
shouldn't USE SHRINK* commands, please.
Main principles:
1.LOG can't grow so match (most important)
2 I can lost information max fifteen minutes back
3. I'd like use only full recovery model not SIMPLE
If you like it can be for example graphical plan - "Maintenance Plans"
on SQL 2005
Thank you
--
Regards|||> So it is realy true, it change to SIMPLE mode, thanks.
No, it doesn't change recovery model to simple. It puts the database in a state where it behaves the
same as in simple model. Important distinction.
> 1.LOG can't grow so match (most important)
OK. You handle this by doing frequent log backups since a lot backup will empty the ldf file(s).
> 2 I can lost information max fifteen minutes back
So you should do log backups at least in 15 minutes intervals. Perhaps every 10 minutes...
> 3. I'd like use only full recovery model not SIMPLE
So just keep the database in full recovery.
Above is very basic. Do a database (full) backup perhaps every day. And do a log backup every 10
minutes. This is easy thing to setup with the maint wizard.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<anxcomp@.gmail.com> wrote in message news:1187727440.811711.299920@.a39g2000hsc.googlegroups.com...
> On 21 Sie, 11:52, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>> This is expected. If you are in full mode and empty the log without actually doing a backup
>> (which
>> is what TRUNCATE_ONLY and NO_LOG does), then subsequent real log backups would be useless. SQL
>> Server know this and in this situation, the database acts as if it is simple recovery (log is
>> auto-truncated).
> So it is realy true, it change to SIMPLE mode, thanks.
> Tibor I read your article ' Why you want to be restrictive with
> shrink of database files', I understand I shouldn't use both
> SHRINKFILE and SHRINKDATABASE command.
> So, would you help me create good backup plan, which ENSURE me that I
> shouldn't USE SHRINK* commands, please.
> Main principles:
> 1.LOG can't grow so match (most important)
> 2 I can lost information max fifteen minutes back
> 3. I'd like use only full recovery model not SIMPLE
> If you like it can be for example graphical plan - "Maintenance Plans"
> on SQL 2005
> Thank you
> --
> Regards
>|||> No, it doesn't change recovery model to simple. It puts the database in a state where it behaves the
> same as in simple model. Important distinction.
OK, I understand now.
> Above is very basic. Do a database (full) backup perhaps every day. And do a log backup every 10
> minutes. This is easy thing to setup with the maint wizard.
I've done this use Wizard and now I'm watching what happen with log :)
Thank you Tibor for all advices and other people too :)
--
Regards

Backup LOG fails when trying to backup database in SIMPLE recovery mode

I'm running a backup like this:

BACKUP LOG @.database_name TO DISK = @.log_location_string WITH INIT , NAME = @.database_name

The log backup fails when my script hits a database that is set to simple. Problem is, it won't skip that database and continue on, it just stops right there, leaving the rest of the database logs not backed up!

How can I make my job skip backing up logs if the database is set to simple?

Thanks
TommyCorrection! It looks like the Log backup job IS skipping the SIMPLE database and backing up the rest. However, in my Transact-SQL Output file, the last line in the log file stops with:

Msg 3013, Sev 16: BACKUP LOG is terminating abnormally. [SQLSTATE 42000]
Msg 4208, Sev 16: The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE. [SQLSTATE 42000]
Msg 4035, Sev 16: Processed 1 pages for database 'DBNAME', file 'DBNAME_log' on file 1. [SQLSTATE 01000]

Why the heck doesn't the log file show the rest of the results?
I have options set:
Overwrite
Append output to step history

Thanks
Tommy|||I'm not really sure I understand what you're talking about, but it sounds like you are using the sql maintenance plan. If you are using maintenance plan, maybe you should consider breaking the database that's using simple recovery off to a separate plan. You could write the log to the same file, and have the first plan overwrite the log, and the second one can append.

Sorry if I have missed your point.|||My point is that I have scheduled a job that outputs it's results to a text file. The job backs up database logs, and writes each successful database to the txt file. However, when it fails on one database (because it's set to simple) it skips it, and continues on, but does not keep writing to the txt file.

Thanks
Tommy

Backup Guidelines for Recovery

Thomas,
the pages on MSDN are more or less identical to BOL
(http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/replsql/replbackup_8enn.asp).
Perhaps you have some specific questions regarding
transactional backups?
Regards,
Paul Ibison
No. thanks
On Wed, 28 Jul 2004 06:21:17 -0700, "Paul Ibison"
<Paul.Ibison@.Pygmalion.Com> wrote:

>Thomas,
>the pages on MSDN are more or less identical to BOL
>(http://msdn.microsoft.com/library/default.asp?
>url=/library/en-us/replsql/replbackup_8enn.asp).
>Perhaps you have some specific questions regarding
>transactional backups?
>Regards,
>Paul Ibison