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 strategy

Hi,

In my current organisation they are using SQL Server.

They are using TSM (Tivoli Storage Manager) to back up the server on a nightly base.

However I feel that this is not the correct way. Suppose I need some data back, I call technical support and they would restore the server. If another user made some changes to another database that day, he would lose his changes.

Of course they could restore a file but I am not sure if this is correctly. What will happen to the transaction log for example ?

My idea is that they should backup the database using the normal sql backup commando, dump the data to a folder and backup that folder.

Any suggestions please ?

Constantijn Enders

TSM has it's own way of running backups. It even uses the SQL Server APIs to do so. No need to worry about it. When in doubt, test.|||

Great, thanks a lot

CE

|||

Tivoli is a great product, just make sure you understand how to use it properly. Set up a test lab and practice, practice, practice restores.

backup size and initial database size

SQL server 2000
Hi
How can i find
1. Information about all/current backup files, size of backup file on
server using sql query or a script.
2. Initial size of all databases (when they were installed) and current size
of all databases.
Thanks
ontario, canada
I have information about all database backups for last two years (Including
database size in table Backupset in MSDB.
Does % increase in database backup size reflects the % increase in database
size?
ontario, canada
"db" wrote:
> SQL server 2000
> Hi
> How can i find
> 1. Information about all/current backup files, size of backup file on
> server using sql query or a script.
> 2. Initial size of all databases (when they were installed) and current size
> of all databases.
> Thanks
> ontario, canada
|||I am using
select database_name,database_creation_date,backup_start_ date,backup_size
from backupset order by database_name,backup_size desc
to get the bacup size information. I get the information.
I want to select a subset of records which give me information for backupset
for one day of every month (10-...200?). I.e monthwise backup set
information for 10-jan, 10-feb-10-mar...etc). When I issue FOLLOWING COMMAND
select database_name,database_creation_date,backup_start_ date,backup_size
from backupset WHERE BACKUP_START_DATE LIKE '10-...200?) order by
database_name,backup_size desc
I do not get the desired result. What is correct sql query?
ontario, canada
"db" wrote:
[vbcol=seagreen]
> I have information about all database backups for last two years (Including
> database size in table Backupset in MSDB.
> Does % increase in database backup size reflects the % increase in database
> size?
> --
> ontario, canada
> "db" wrote:
|||Select ...
Where BACKUP_START_DATE >= '20000101' And Day(BACKUP_START_DATE) = 10
Tom
"db" <db@.discussions.microsoft.com> wrote in message
news:BCC341DB-9254-43E5-B4FC-08F90F86A325@.microsoft.com...[vbcol=seagreen]
>I am using
> select database_name,database_creation_date,backup_start_ date,backup_size
> from backupset order by database_name,backup_size desc
> to get the bacup size information. I get the information.
> I want to select a subset of records which give me information for
> backupset
> for one day of every month (10-...200?). I.e monthwise backup set
> information for 10-jan, 10-feb-10-mar...etc). When I issue FOLLOWING
> COMMAND
> select database_name,database_creation_date,backup_start_ date,backup_size
> from backupset WHERE BACKUP_START_DATE LIKE '10-...200?) order by
> database_name,backup_size desc
> I do not get the desired result. What is correct sql query?
>
> --
> ontario, canada
>
> "db" wrote:

backup size and initial database size

SQL server 2000
Hi
How can i find
1. Information about all/current backup files, size of backup file on
server using sql query or a script.
2. Initial size of all databases (when they were installed) and current size
of all databases.
Thanks
--
ontario, canadaI have information about all database backups for last two years (Including
database size in table Backupset in MSDB.
Does % increase in database backup size reflects the % increase in database
size'
--
ontario, canada
"db" wrote:
> SQL server 2000
> Hi
> How can i find
> 1. Information about all/current backup files, size of backup file on
> server using sql query or a script.
> 2. Initial size of all databases (when they were installed) and current size
> of all databases.
> Thanks
> ontario, canada|||I am using
select database_name,database_creation_date,backup_start_date,backup_size
from backupset order by database_name,backup_size desc
to get the bacup size information. I get the information.
I want to select a subset of records which give me information for backupset
for one day of every month (10-...200?). I.e monthwise backup set
information for 10-jan, 10-feb-10-mar...etc). When I issue FOLLOWING COMMAND
select database_name,database_creation_date,backup_start_date,backup_size
from backupset WHERE BACKUP_START_DATE LIKE '10-...200?) order by
database_name,backup_size desc
I do not get the desired result. What is correct sql query?
ontario, canada
"db" wrote:
> I have information about all database backups for last two years (Including
> database size in table Backupset in MSDB.
> Does % increase in database backup size reflects the % increase in database
> size'
> --
> ontario, canada
> "db" wrote:
> > SQL server 2000
> > Hi
> > How can i find
> > 1. Information about all/current backup files, size of backup file on
> > server using sql query or a script.
> > 2. Initial size of all databases (when they were installed) and current size
> > of all databases.
> > Thanks
> > ontario, canada|||Select ...
Where BACKUP_START_DATE >= '20000101' And Day(BACKUP_START_DATE) = 10
Tom
"db" <db@.discussions.microsoft.com> wrote in message
news:BCC341DB-9254-43E5-B4FC-08F90F86A325@.microsoft.com...
>I am using
> select database_name,database_creation_date,backup_start_date,backup_size
> from backupset order by database_name,backup_size desc
> to get the bacup size information. I get the information.
> I want to select a subset of records which give me information for
> backupset
> for one day of every month (10-...200?). I.e monthwise backup set
> information for 10-jan, 10-feb-10-mar...etc). When I issue FOLLOWING
> COMMAND
> select database_name,database_creation_date,backup_start_date,backup_size
> from backupset WHERE BACKUP_START_DATE LIKE '10-...200?) order by
> database_name,backup_size desc
> I do not get the desired result. What is correct sql query?
>
> --
> ontario, canada
>
> "db" wrote:
>> I have information about all database backups for last two years
>> (Including
>> database size in table Backupset in MSDB.
>> Does % increase in database backup size reflects the % increase in
>> database
>> size'
>> --
>> ontario, canada
>> "db" wrote:
>> > SQL server 2000
>> > Hi
>> > How can i find
>> > 1. Information about all/current backup files, size of backup file on
>> > server using sql query or a script.
>> > 2. Initial size of all databases (when they were installed) and current
>> > size
>> > of all databases.
>> > Thanks
>> > ontario, canada

Backup security

Hi,
Our current backup process writes backup to a network drive and that is
transferred to a DVD on a daily basis. How can we prevent people from
installing this backup on the DVD if the DVD is ever stolen? Can the
backups be "password protected" or restricted to prevent un-authorized
restore?
Thanks.From BooksOnLine under BACKUP:
PASSWORD = { password | @.password_variable }
Sets the password for the backup set. PASSWORD is a character string. If a
password is defined for the backup set, the password must be supplied to
perform any restore operation from the backup set.
This may prevent most people from restoring the backup but does nothing to
prevent anyone from reading the file with a simple hex editor. You should
look at one of the 3rd party backup tools that do encryption among other
things.
www.red-gate.com
www.quest.com
Andrew J. Kelly SQL MVP
<RK> wrote in message news:%23Evnr5uIGHA.2628@.TK2MSFTNGP15.phx.gbl...
> Hi,
> Our current backup process writes backup to a network drive and that is
> transferred to a DVD on a daily basis. How can we prevent people from
> installing this backup on the DVD if the DVD is ever stolen? Can the
> backups be "password protected" or restricted to prevent un-authorized
> restore?
> Thanks.
>|||Windows has extensive cryptography capabilities that allow creation of
encryption and decryption software relatively easily. If relevant to your
requirements, there are many programs available at little or no cost that
can encrypt and decrypt the backup file(s) separately.
<RK> wrote in message news:%23Evnr5uIGHA.2628@.TK2MSFTNGP15.phx.gbl...
> Hi,
> Our current backup process writes backup to a network drive and that is
> transferred to a DVD on a daily basis. How can we prevent people from
> installing this backup on the DVD if the DVD is ever stolen? Can the
> backups be "password protected" or restricted to prevent un-authorized
> restore?
> Thanks.
>

Backup Restore question

Hi,
SQL Server 2000 SP3 running on Windows 2000 server
Current Backups:
Daily full backup at 10 PM done by Veritas Netbackup
New Backups that will be setup:
Daily Full backup at 3 AM in the morning
Transaction Log backups every hour.
Now my question is, during a restore from the full backup done at 3 AM how
would the restore from transaction logs be affected by the full backup taking
place at 10 PM?
Thanks in advance.You cannot do a restore to a database that is having a backup done at the
same time - and why would you?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:5C8C825F-D498-48ED-A143-4EAD1FB0066C@.microsoft.com...
Hi,
SQL Server 2000 SP3 running on Windows 2000 server
Current Backups:
Daily full backup at 10 PM done by Veritas Netbackup
New Backups that will be setup:
Daily Full backup at 3 AM in the morning
Transaction Log backups every hour.
Now my question is, during a restore from the full backup done at 3 AM how
would the restore from transaction logs be affected by the full backup
taking
place at 10 PM?
Thanks in advance.|||Also, you will have to the use the previous full backup if you want to
restore the 10 p.m. transaction log backup
Ash
"sharman" wrote:
> Hi,
> SQL Server 2000 SP3 running on Windows 2000 server
> Current Backups:
> Daily full backup at 10 PM done by Veritas Netbackup
> New Backups that will be setup:
> Daily Full backup at 3 AM in the morning
> Transaction Log backups every hour.
> Now my question is, during a restore from the full backup done at 3 AM how
> would the restore from transaction logs be affected by the full backup taking
> place at 10 PM?
> Thanks in advance.
>|||Sorry for not making myself clear. I want to know if I start the new full
backup and the new hourly transaction log backup would I be able to restore
at 11PM with just the 3AM full backup and then restoring the transaction logs
in sequence until 11 PM and just IGNORING the 10 PM full backup.
The reason I ask is because many times during the testing to restore from
the 10 PM backups (that is done by the third party software), I get an error
message and I do not trust that full backup. Therefore I want to set up these
new backups through Enterprise Manager( I have done it this way earlier and I
find them very trustworthy)
Thanks.
"Tom Moreau" wrote:
> You cannot do a restore to a database that is having a backup done at the
> same time - and why would you?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:5C8C825F-D498-48ED-A143-4EAD1FB0066C@.microsoft.com...
> Hi,
> SQL Server 2000 SP3 running on Windows 2000 server
> Current Backups:
> Daily full backup at 10 PM done by Veritas Netbackup
> New Backups that will be setup:
> Daily Full backup at 3 AM in the morning
> Transaction Log backups every hour.
> Now my question is, during a restore from the full backup done at 3 AM how
> would the restore from transaction logs be affected by the full backup
> taking
> place at 10 PM?
> Thanks in advance.
>
>|||Yes, you don't have to use the most recent full backup. You can use a
previous full backup and then all of the logs taken after that point in
time.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:F39EAE37-615B-4AF7-BA67-13C56F34AB0B@.microsoft.com...
Sorry for not making myself clear. I want to know if I start the new full
backup and the new hourly transaction log backup would I be able to restore
at 11PM with just the 3AM full backup and then restoring the transaction
logs
in sequence until 11 PM and just IGNORING the 10 PM full backup.
The reason I ask is because many times during the testing to restore from
the 10 PM backups (that is done by the third party software), I get an error
message and I do not trust that full backup. Therefore I want to set up
these
new backups through Enterprise Manager( I have done it this way earlier and
I
find them very trustworthy)
Thanks.
"Tom Moreau" wrote:
> You cannot do a restore to a database that is having a backup done at the
> same time - and why would you?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:5C8C825F-D498-48ED-A143-4EAD1FB0066C@.microsoft.com...
> Hi,
> SQL Server 2000 SP3 running on Windows 2000 server
> Current Backups:
> Daily full backup at 10 PM done by Veritas Netbackup
> New Backups that will be setup:
> Daily Full backup at 3 AM in the morning
> Transaction Log backups every hour.
> Now my question is, during a restore from the full backup done at 3 AM
> how
> would the restore from transaction logs be affected by the full backup
> taking
> place at 10 PM?
> Thanks in advance.
>
>

Backup query ?

Im currently planning to move a Sql Server Database from one Server to another due to SAN errors on the current server.

What I need to know is...

Im currently restoring from last nights backup.. I then plan to apply all the transaction logs from today and tomorrow.
(Up until downtime)

If the database back's up between now and tomorrow will I loose data at the points between the back and the next transaction log backup ??

Im not truncating the transaction log at backup but I just wanted to make sure that ive got this straight before I start the work.

Thanks.You will not loose data between the full backup and the next transactional log backup. That is, of course, everything was done correctly.

Backup Priority level

I've searched enough to know that this ability probably doesn't exist
in our current setup, thus the reason I'm coming to the community now.
When doing a database backup in Enterprise Manager (SQL Server 2000
sp4), is there a priority level setting so that I can put less load on
the CPU? Our small office uses one server for everything and when I do
a 12:00 pm full backup of our live DB, the server slows to a crawl for
about 4 or 5 minutes. I'd like to see the backup take longer but put
less load on the server so we can to use it. If this setting doesn't
exist, I may need to explore another backup method...but that's another
topic for another day.

Thanks,

Gabegabe101 (gabe101@.gmail.com) writes:

Quote:

Originally Posted by

I've searched enough to know that this ability probably doesn't exist
in our current setup, thus the reason I'm coming to the community now.
When doing a database backup in Enterprise Manager (SQL Server 2000
sp4), is there a priority level setting so that I can put less load on
the CPU? Our small office uses one server for everything and when I do
a 12:00 pm full backup of our live DB, the server slows to a crawl for
about 4 or 5 minutes. I'd like to see the backup take longer but put
less load on the server so we can to use it. If this setting doesn't
exist, I may need to explore another backup method...but that's another
topic for another day.

No, there is no such setting. I don't really know when 12.00 pm is, but
I guess it's at noon, since it causes problems.

It may be a better to idea to schedule the full backup for off-hours,
and only take log backups during the day.

Then again, a full backup slowing the server to a crawl, does not seem
normal to me. Could be that your hardware is a bit thin. Do you take
the backup to local disk or a network drive?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog wrote:

Quote:

Originally Posted by

>
No, there is no such setting. I don't really know when 12.00 pm is, but
I guess it's at noon, since it causes problems.

Thank you and yes, you are correct...it's noon.

Quote:

Originally Posted by

It may be a better to idea to schedule the full backup for off-hours,
and only take log backups during the day.

We currently do a full back up every night and no log back-ups. We're
on 'Simple' recovery model and plan to just restore to the most recent
full backup during a crisis. We can afford this "luxury" because we're
a small office with few transactions during the work day. This is why
I chose to do one more live backup of our company database at the noon
hour...cutting potential losses by 50% (the small losses that they
would be).

Quote:

Originally Posted by

Then again, a full backup slowing the server to a crawl, does not seem
normal to me. Could be that your hardware is a bit thin. Do you take
the backup to local disk or a network drive?

We have a good server but it's our only server (Dual Xeon 3.0, 4GB RAM
and 4, 10K RPM RAID-5 drives). Originally, it was just SQL Server and
Symantec AV...now it's also Exchange. The backup is being stored to
the Data partition on that same server's RAID drives.

Thanks Erland.|||gabe101 (gabe101@.gmail.com) writes:

Quote:

Originally Posted by

We currently do a full back up every night and no log back-ups. We're
on 'Simple' recovery model and plan to just restore to the most recent
full backup during a crisis. We can afford this "luxury" because we're
a small office with few transactions during the work day. This is why
I chose to do one more live backup of our company database at the noon
hour...cutting potential losses by 50% (the small losses that they
would be).

One alternative you may want to investigate is to do a differntial
backup. That may tax your server less.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"gabe101" <gabe101@.gmail.comwrote in message
news:1166551052.960302.151960@.80g2000cwy.googlegro ups.com...

Quote:

Originally Posted by

Erland Sommarskog wrote:
>
We have a good server but it's our only server (Dual Xeon 3.0, 4GB RAM
and 4, 10K RPM RAID-5 drives). Originally, it was just SQL Server and
Symantec AV...now it's also Exchange. The backup is being stored to
the Data partition on that same server's RAID drives.

Argh, you're really stressing that machine.

Try backing up over the wire to a UNC share on another machine. You are
almost definitely stressing the disk I/O system on this box.

A backup on the same machine won't do you much good if say the RAID
controller fails.

Also, you may still want to look at transaction log backups, they'll
generally be much smaller and you can do more often w/o impacting
performance nearly as much.

Quote:

Originally Posted by

>
Thanks Erland.
>

|||Greg D. Moore (Strider) wrote:

Quote:

Originally Posted by

Argh, you're really stressing that machine.

Yeah, I agree. Unfortunately, it's not in the budget for us to expand
at this time. I will say this though, to me (and my mere two years
experience) it seems to be running very stable. Since we only run with
10 users, I've set SQL Server's memory allocation to 600MB and set
SBSMONITORING to 100MB, Exchange is being kind and staying between 575
- 600 MB and Symantec is occupying another 400 - 500. All together
I've still got about 800 - 1,000 MB of free RAM. My server report
shows an average of 18% busy disk time and both CPU's (shown as 4)
averaging about 8-10% use. Again, I only assume this is good because
I'm new to IT. I just got my degree two years ago. Would you say
our server is doing 'Good', 'Satisfactory' or 'Poor'?

Quote:

Originally Posted by

Try backing up over the wire to a UNC share on another machine. You are
almost definitely stressing the disk I/O system on this box.
>
A backup on the same machine won't do you much good if say the RAID
controller fails.

I sheepishly have to admit my inexperience again and say that this
makes perfect sense. We are currently saving the noon backup on the
same system and I'll save the BAK file to another box starting now.
Thanks.

Quote:

Originally Posted by

Also, you may still want to look at transaction log backups, they'll
generally be much smaller and you can do more often w/o impacting
performance nearly as much.

My only concern here comes back to my inexperience with transaction
logs. Doing a restore through the Enterprise Manager is incredibly
simple with a full .BAK file. I've not done a transaction log restore
and have been told it's much more complex. I agree with you and Erland
here, but given our current situation, a restore from a full backup
file and then manually entering the day's transactions may ultimately
be just as simple to accomplish.

Thank you both for your comments and if you have anything more to say,
I'm still listening (and learning).|||Yes, this is a response to my own response...

;-)

gabe101 wrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

Try backing up over the wire to a UNC share on another machine. You are
almost definitely stressing the disk I/O system on this box.

A backup on the same machine won't do you much good if say the RAID
controller fails.

>
I sheepishly have to admit my inexperience again and say that this
makes perfect sense. We are currently saving the noon backup on the
same system and I'll save the BAK file to another box starting now.
Thanks.

I suddenly have a reason for concern...

I transferred our existing 'Noon' backup file accross the network to
the share on another box. It took 8 minutes and 17 seconds and the
backup write process only takes between 3 and 4 minutes. Is this going
to cause a problem when the server is doing the backup and transferring
at the same time? Is it actually going to write to temporary location
on the server before making the transfer anyway? If so, that would
defeat the purpose for transferring to the other box. Again, any input
is appreciated.|||gabe101 wrote:

Quote:

Originally Posted by

Yes, this is a response to my own response...
>
;-)
>
gabe101 wrote:
>

Quote:

Originally Posted by

Quote:

Originally Posted by

Try backing up over the wire to a UNC share on another machine. You are
almost definitely stressing the disk I/O system on this box.
>
A backup on the same machine won't do you much good if say the RAID
controller fails.

I sheepishly have to admit my inexperience again and say that this
makes perfect sense. We are currently saving the noon backup on the
same system and I'll save the BAK file to another box starting now.
Thanks.

>
I suddenly have a reason for concern...
>
I transferred our existing 'Noon' backup file accross the network to
the share on another box. It took 8 minutes and 17 seconds and the
backup write process only takes between 3 and 4 minutes. Is this going
to cause a problem when the server is doing the backup and transferring
at the same time? Is it actually going to write to temporary location
on the server before making the transfer anyway? If so, that would
defeat the purpose for transferring to the other box. Again, any input
is appreciated.

Depends on your network of course but it doesn't surprise me that
sending a large file over a network would take longer than the local
backup, especially during the day. Have you considered getting a tape
drive to do your backups - they're not all that expensive, certainly a
lot cheaper than losing your data would be :)

Also, as someone else mentioned, consider doing a differential backup
during the day rather than the full backup. It's just one more step if
you need to restore, and it should be much smaller and faster than your
full backup.|||gabe101 (gabe101@.gmail.com) writes:

Quote:

Originally Posted by

My only concern here comes back to my inexperience with transaction
logs. Doing a restore through the Enterprise Manager is incredibly
simple with a full .BAK file. I've not done a transaction log restore
and have been told it's much more complex. I agree with you and Erland
here, but given our current situation, a restore from a full backup
file and then manually entering the day's transactions may ultimately
be just as simple to accomplish.

Note that what I suggested was *not* a transaction log dump, but a
differential backup. I will have to admit that I've never taken a
differential backup, nor restored from one, but I can't imagine
that it's that tricky. You would need to play with it on a test
box, so that you are confident with the process when the disaster
strikes.

A differential backup only includs the changed pages since the last
full backup, so it would be a lot smaller and thus tax the system
less.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog wrote:

Quote:

Originally Posted by

gabe101 (gabe101@.gmail.com) writes:

Quote:

Originally Posted by

My only concern here comes back to my inexperience with transaction
logs. Doing a restore through the Enterprise Manager is incredibly
simple with a full .BAK file. I've not done a transaction log restore
and have been told it's much more complex. I agree with you and Erland
here, but given our current situation, a restore from a full backup
file and then manually entering the day's transactions may ultimately
be just as simple to accomplish.

>
Note that what I suggested was *not* a transaction log dump, but a
differential backup. I will have to admit that I've never taken a
differential backup, nor restored from one, but I can't imagine
that it's that tricky.

Not tricky at all. Restore the full backup, then restore the diff.

Backup name with DateTime

I know how to backup my DB but i cant find a way to give a name with the current date time.

backup database SUSDB to disk = 'e:\backup\test_%date%.bak' with init;

Any idea how i can put the date to my file name?

thank you

You could use dynamic sq;:

declare

@.sql varchar(300)

set

@.sql='backup database SUSDB to disk = ''e:\backup\test_'+convert(varchar,getdate(),101)+'.bak'''

exec

(@.sql)

|||

You have two options modify the code in the first link for your needs or use the last two links to create one just for you. Hope this helps.

http://support.microsoft.com/kb/241397

Create custom code from these two.

http://forums.asp.net/thread/1049663.aspx

http://msdn2.microsoft.com/en-gb/library/aa259582(SQL.80).aspx

|||

Sorry i forgot to mention that i use my code inside SQL 2005 jobs,

so the backup is taken everynight

|||

So in your job step you should generate file name like was shown in one of previous steps, you can also if you wolud like to keep information how many backup you did in file name, modify backup step automatically on success to conatain current backup number. But for only modifing date, code from first answer should be helpful.

Good luck

BACKUP LOG cannot be performed because there is no current database backup.

Hi,

I have a MS SQL Server 2005 Enterprise Edition 9.0.2153 which manages my BizTalk Server 2006 Databases. The BizTalk server installer automatically creates a job named Backup BizTalk Server (BizTalkMgmtDb) which should back up the databases and transaction protocolls.

The SQL Server Agent runs under domain-administrator account which has full access to my backup directory D:\backups.

The job executes the following steps:

Step 1: BackupFull:

Command:

exec [dbo].[sp_BackupAllFull_Schedule] 'd' /* Frequency */, 'BTS' /* Name */, 'D:\Backups' /* location of backup files */

Step 2: MarkAndBackupLog

Command:

exec [dbo].[sp_MarkAll] 'BTS' /* Log mark name */, 'D:\Backups' /* location of backup files */

My challenges are:

1. The job doesn't back up my databases(D:\Backups is empty after executing the job)

2. I get every time the following error in error protocol of the job:

BACKUP LOG cannot be performed because there is no current database backup. [SQLSTATE 42000] (Fehler 4214) BACKUP LOG is terminating abnormally. [SQLSTATE 42000] (Fehler 3013).

I have already switched the Databases from full to simple recovery mode and vica versa, it didn't help. Also, the above stored procedures doesn't include neither the TRUNCATE nor the LOG parameters for the logfiles so I wasn't able to solve this issue by adjusting these flags.

Any help would be appreciated.

Thanks in advance,

Greg

I think you've just got a problem with the startup sequence.

Start by putting the database in full recovery mode.

Then, trigger a full backup. I suspect that your job may not do a full on the current day, causing your problem. Log backups won't work until there is a full to bas them on. You can do a manual full backup using Management Studio, or temporarily change the schedule on your BackupFull job to perform a full backup always, and then set it back after it's run once. After that, log backups should run fine.

|||

Hi Kevin,

first of all thanks for your reply. I really appreciate it!

I've adjusted my BizTalk 2006 Database Settings and all Databases are now in full recovery mode.

BizTalkDTADb - Full

BizTalkEDIDb - Full

BizTalkMgmtDb - Full

BizTalkMsgBoxDb - Full

BizTalkRuleEngineDb - Full

SSODB - Full

I am now able - with the above mentioned job - to reguraly back up the BizTalk Server Databases except for BizTalkEDIDb.

I executed manually a full Backup on the EDI database - as you suggested - and after that I let the backupjob run. Still no backups on this database.

In the job activity monitor there is no more error regarding BACKUP LOG. That's the good news for today. A step in the right direction. There are however no traces of transactional backups in my backup directory despite setting the right parameter for the path of the transactional log backup(see my first post).

There is one more error during the execution of the job named "DTA Purge and Archive (BizTalkDTADb)":

The @.nvcFolder parameter cannot be null. If you want to only purge the database, use the dtasp_PurgeTrackingDatabase stored procedure [SQLSTATE 42000] (Fehler 50000).

My three questions therefore:

1. Why can't I backup the EDI Database?

2. Why does the backup job not create *.trn files in my backup directory?

3. How can I get rid of the "DTA Purge and Archive" job error?

Thanks in advance,

Greg

|||

Hi,

my answers to my question:

1. I was not able to back up the EDI database because its name didn't exist in the adm_OtherBackupDatabases table of the BizTalkMgmtDb database. The question is why did the SQL Server back up other databases which were not present in this table either?

2. The job created transaction backups however with the extension *.bak and not with *.trn.

3. I have let the @.nvcFolder variable on its default NULL value which was false. After I set the step parameters of the DTA Purge and Archive (BizTalkDTADb) job like follows I got rid of the error:

exec dtasp_BackupAndPurgeTrackingDatabase
0, --@.nLiveHours tinyint, --Any completed instance older than the live hours +live days
1, --@.nLiveDays tinyint = 0, --will be deleted along with all associated data
30, --@.nHardDeleteDays tinyint = 0, --all data older than this will be deleted.
'D:\Backups', --@.nvcFolder nvarchar(1024) =N'D:\Backups' , --folder for backup files
null, --@.nvcValidatingServer sysname = null,
0 --@.fForceBackup int = 0 --