DR strategies

We have a large database for production and need to come up with a solution
for Disaster Recovery.
Performing tasks such as a full backup and transaction backups, then
shipping over across country is okay during initial setup. But long term it
will not be possible to ship a full backup across. Transactional backups
shipping and restoring to a read-only instance would be okay; but, occasiona
l
full backups of the production system would be needed for development teams.
So this would cause a problem for the DR system.
What are the different options either via a Microsoft solution or a 3rd part
y?Tom,
For MS solutions you might want to have a look at
http://support.microsoft.com/defaul...b;en-us;822400.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Tom" <Tom@.discussions.microsoft.com> wrote in message
news:9D54441D-E203-4544-8F37-6EDAFE7DCC79@.microsoft.com...
> We have a large database for production and need to come up with a
solution
> for Disaster Recovery.
> Performing tasks such as a full backup and transaction backups, then
> shipping over across country is okay during initial setup. But long term
it
> will not be possible to ship a full backup across. Transactional backups
> shipping and restoring to a read-only instance would be okay; but,
occasional
> full backups of the production system would be needed for development
teams.
> So this would cause a problem for the DR system.
> What are the different options either via a Microsoft solution or a 3rd
party?sql

DR strategies

We have a large database for production and need to come up with a solution
for Disaster Recovery.
Performing tasks such as a full backup and transaction backups, then
shipping over across country is okay during initial setup. But long term it
will not be possible to ship a full backup across. Transactional backups
shipping and restoring to a read-only instance would be okay; but, occasional
full backups of the production system would be needed for development teams.
So this would cause a problem for the DR system.
What are the different options either via a Microsoft solution or a 3rd party?Tom,
For MS solutions you might want to have a look at
http://support.microsoft.com/default.aspx?scid=kb;en-us;822400.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Tom" <Tom@.discussions.microsoft.com> wrote in message
news:9D54441D-E203-4544-8F37-6EDAFE7DCC79@.microsoft.com...
> We have a large database for production and need to come up with a
solution
> for Disaster Recovery.
> Performing tasks such as a full backup and transaction backups, then
> shipping over across country is okay during initial setup. But long term
it
> will not be possible to ship a full backup across. Transactional backups
> shipping and restoring to a read-only instance would be okay; but,
occasional
> full backups of the production system would be needed for development
teams.
> So this would cause a problem for the DR system.
> What are the different options either via a Microsoft solution or a 3rd
party?

DR strategies

We have a large database for production and need to come up with a solution
for Disaster Recovery.
Performing tasks such as a full backup and transaction backups, then
shipping over across country is okay during initial setup. But long term it
will not be possible to ship a full backup across. Transactional backups
shipping and restoring to a read-only instance would be okay; but, occasional
full backups of the production system would be needed for development teams.
So this would cause a problem for the DR system.
What are the different options either via a Microsoft solution or a 3rd party?
Tom,
For MS solutions you might want to have a look at
http://support.microsoft.com/default...;en-us;822400.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Tom" <Tom@.discussions.microsoft.com> wrote in message
news:9D54441D-E203-4544-8F37-6EDAFE7DCC79@.microsoft.com...
> We have a large database for production and need to come up with a
solution
> for Disaster Recovery.
> Performing tasks such as a full backup and transaction backups, then
> shipping over across country is okay during initial setup. But long term
it
> will not be possible to ship a full backup across. Transactional backups
> shipping and restoring to a read-only instance would be okay; but,
occasional
> full backups of the production system would be needed for development
teams.
> So this would cause a problem for the DR system.
> What are the different options either via a Microsoft solution or a 3rd
party?

Download database to local and use

I have SQL database hosted by my ISP. Every now and again we log on and create new tables using user XXX1. After getting a backup of the database, I have restored it on my local machine. When running the application on local, I get an error because there is a new user in database called XXX1.

I would like to change the user from XXX1 to dbo on my local machine for all tables, stored procedures and views. How do I do this easily?

Thanks in advance!

Dave

Hi,

http://groups.google.de/group/microsoft.public.sqlserver.programming/browse_frm/thread/f1625d70fb765701

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Downgrading MSSQL 2005 DB to MSSQL 2000 DB

Hello all,
Does anybody have any experience in trying to backup a DB from MSSQL
2005 and then restoring it on a machine with MSSQL 2000? Can it be
done? Is it completely impossible?
While at it - Is it possible to do the reverse without problems? Is
MSSQL 2005 100% back-compatible with MSSQL 2000, so I can just backup a
whole DB on MSSQL 2000, restore it on MSSQL 2005 and have it run as
smoothly?
Thanks in advance.You can go from 2000 to 2005 and the db will be upgraded to the 2005 format.
But once it is in the 2005 format you can not go backwards. The upgrade is
seamless in and of itself but there may be incompatibilities with your
existing code / app that you might have to deal with. You can use the
upgrade advisor to see first.
http://www.microsoft.com/downloads/details.aspx?familyid=63FCE120-5E87-4AF1-B080-CA461AEAFAA2&displaylang=en
--
Andrew J. Kelly SQL MVP
<Baudolino@.gmail.com> wrote in message
news:1128954487.426069.160440@.f14g2000cwb.googlegroups.com...
> Hello all,
> Does anybody have any experience in trying to backup a DB from MSSQL
> 2005 and then restoring it on a machine with MSSQL 2000? Can it be
> done? Is it completely impossible?
> While at it - Is it possible to do the reverse without problems? Is
> MSSQL 2005 100% back-compatible with MSSQL 2000, so I can just backup a
> whole DB on MSSQL 2000, restore it on MSSQL 2005 and have it run as
> smoothly?
> Thanks in advance.
>

Downgrading MSSQL 2005 DB to MSSQL 2000 DB

Hello all,
Does anybody have any experience in trying to backup a DB from MSSQL
2005 and then restoring it on a machine with MSSQL 2000? Can it be
done? Is it completely impossible?
While at it - Is it possible to do the reverse without problems? Is
MSSQL 2005 100% back-compatible with MSSQL 2000, so I can just backup a
whole DB on MSSQL 2000, restore it on MSSQL 2005 and have it run as
smoothly?
Thanks in advance.
You can go from 2000 to 2005 and the db will be upgraded to the 2005 format.
But once it is in the 2005 format you can not go backwards. The upgrade is
seamless in and of itself but there may be incompatibilities with your
existing code / app that you might have to deal with. You can use the
upgrade advisor to see first.
http://www.microsoft.com/downloads/d...displaylang=en
Andrew J. Kelly SQL MVP
<Baudolino@.gmail.com> wrote in message
news:1128954487.426069.160440@.f14g2000cwb.googlegr oups.com...
> Hello all,
> Does anybody have any experience in trying to backup a DB from MSSQL
> 2005 and then restoring it on a machine with MSSQL 2000? Can it be
> done? Is it completely impossible?
> While at it - Is it possible to do the reverse without problems? Is
> MSSQL 2005 100% back-compatible with MSSQL 2000, so I can just backup a
> whole DB on MSSQL 2000, restore it on MSSQL 2005 and have it run as
> smoothly?
> Thanks in advance.
>

Downgrading MSSQL 2005 DB to MSSQL 2000 DB

Hello all,
Does anybody have any experience in trying to backup a DB from MSSQL
2005 and then restoring it on a machine with MSSQL 2000? Can it be
done? Is it completely impossible?
While at it - Is it possible to do the reverse without problems? Is
MSSQL 2005 100% back-compatible with MSSQL 2000, so I can just backup a
whole DB on MSSQL 2000, restore it on MSSQL 2005 and have it run as
smoothly?
Thanks in advance.You can go from 2000 to 2005 and the db will be upgraded to the 2005 format.
But once it is in the 2005 format you can not go backwards. The upgrade is
seamless in and of itself but there may be incompatibilities with your
existing code / app that you might have to deal with. You can use the
upgrade advisor to see first.
http://www.microsoft.com/downloads/...&displaylang=en
--
Andrew J. Kelly SQL MVP
<Baudolino@.gmail.com> wrote in message
news:1128954487.426069.160440@.f14g2000cwb.googlegroups.com...
> Hello all,
> Does anybody have any experience in trying to backup a DB from MSSQL
> 2005 and then restoring it on a machine with MSSQL 2000? Can it be
> done? Is it completely impossible?
> While at it - Is it possible to do the reverse without problems? Is
> MSSQL 2005 100% back-compatible with MSSQL 2000, so I can just backup a
> whole DB on MSSQL 2000, restore it on MSSQL 2005 and have it run as
> smoothly?
> Thanks in advance.
>

downgrading from 2005 to sql server express

Hi

I have a sql server 2005 database - i want to downgrade it to sql server express , i ahve tried to do a restore from a backup file from the 2005 database , I basically want to restore tables stored procs and data from the 2005 into sql server express , any ideas on the best direction for this ?

thanks

If both SQL Servers are in the same network you just register the Express with the full version and in the backup and restore wizard choose the restore from device option. If both are not in the same network then you take the .bak file and put it in the backup subfolder in Microsoft SQL Server and it is important you let Windows create the file path for you and follow the previous direction. Post again if you still have question. Hope this helps.

Downgrade a SQL 2K5 D.B. TO SQL 2K

Hi, I am working on a test installation of SQL 2K5 but I need to copy a D.B. from SQL 2K5 to a SQL 2K. I tried with backup but the restore in SQL 2K does not work, anyone can help me?

Many thanks,

Fabio.

Hi,

either import the data from SQL Server 2000 or script out the structure and the data (which would be some more work to do) Backups made in SQL Server 2005 are not readable in SQL Server 2000.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks Jens,

the D.B. is too big (about 200 tables) and I have no network access to make an import.

I hoped that was possible to restore it from sql 2k5 to sql 2k ...

I think I have to install a sql2k5 instance on my developement PC.

Fabio.

|||For the data transfer you must have to go import/export route and for the schema you can script the database so.

Downgarde from SQL 2000 to SQL 7

HI i need to downgrade one of my databases to SQL 7..but ti doesnt allow me to not a backup restore or a script import works.
please can any one help.or is there some tool that does this fot you
Hi,
DTS will be the easiest way to transfer the data from SQL 2000 to SQL 7.
For other objects, generate the SQL script and execute in SQL 7.
Thanks
Hari
MCDBA
"Emil" <emil@.mint.co.za> wrote in message
news:25998566-E751-4E34-9530-9F05532F0B53@.microsoft.com...
> HI i need to downgrade one of my databases to SQL 7..but ti doesnt allow
me to not a backup restore or a script import works.
> please can any one help.or is there some tool that does this fot you

Downgarde from SQL 2000 to SQL 7

HI i need to downgrade one of my databases to SQL 7..but ti doesnt allow me
to not a backup restore or a script import works.
please can any one help.or is there some tool that does this fot youHi,
DTS will be the easiest way to transfer the data from SQL 2000 to SQL 7.
For other objects, generate the SQL script and execute in SQL 7.
Thanks
Hari
MCDBA
"Emil" <emil@.mint.co.za> wrote in message
news:25998566-E751-4E34-9530-9F05532F0B53@.microsoft.com...
> HI i need to downgrade one of my databases to SQL 7..but ti doesnt allow
me to not a backup restore or a script import works.
> please can any one help.or is there some tool that does this fot you

Downgarde from SQL 2000 to SQL 7

HI i need to downgrade one of my databases to SQL 7..but ti doesnt allow me to not a backup restore or a script import works
please can any one help.or is there some tool that does this fot youHi,
DTS will be the easiest way to transfer the data from SQL 2000 to SQL 7.
For other objects, generate the SQL script and execute in SQL 7.
Thanks
Hari
MCDBA
"Emil" <emil@.mint.co.za> wrote in message
news:25998566-E751-4E34-9530-9F05532F0B53@.microsoft.com...
> HI i need to downgrade one of my databases to SQL 7..but ti doesnt allow
me to not a backup restore or a script import works.
> please can any one help.or is there some tool that does this fot yousql

Doubts about Transaction Log

Hello to Everybody!
I have a doubts about Transaction Log. What's happen when I execute the
follow command
BACKUP LOG [database] WITH NO_LOG ou BACKUP LOG [database] WITH TRUNCATE_ONLY
So, When should I use this command to avoid fill up the log?
Thanks a Lot
Juliano HortaDon't use it. Either have the db in simple recovery mode. Or do regular transaction log backups (the
log is emptied when you do a log backup).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Juliano H via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:541010E0E3B40@.SQLMonster.com...
> Hello to Everybody!
> I have a doubts about Transaction Log. What's happen when I execute the
> follow command
> BACKUP LOG [database] WITH NO_LOG ou BACKUP LOG [database] WITH TRUNCATE_ONLY
> So, When should I use this command to avoid fill up the log?
> Thanks a Lot
> Juliano Horta|||Only use this if you do not need to be recoverable via trans log, if so
maybe you should just use simple recovery mode
Below is from BOL
Note If backing up the log does not appear to truncate most of the log, an
old open transaction may exist in the log. Log space can be monitored with
DBCC SQLPERF (LOGSPACE). For more information, see Transaction Log Backups.
NO_LOG | TRUNCATE_ONLY
Removes the inactive part of the log without making a backup copy of it and
truncates the log. This option frees space. Specifying a backup device is
unnecessary because the log backup is not saved. NO_LOG and TRUNCATE_ONLY
are synonyms.
After backing up the log using either NO_LOG or TRUNCATE_ONLY, the changes
recorded in the log are not recoverable. For recovery purposes, immediately
execute BACKUP DATABASE.
"Juliano H via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:541010E0E3B40@.SQLMonster.com...
> Hello to Everybody!
> I have a doubts about Transaction Log. What's happen when I execute the
> follow command
> BACKUP LOG [database] WITH NO_LOG ou BACKUP LOG [database] WITH
> TRUNCATE_ONLY
> So, When should I use this command to avoid fill up the log?
> Thanks a Lot
> Juliano Horta|||Hi,
To add on to Tibor and David; Go ahead with SIMPLE recovery model if you do
not require a point in time recovery.
There is no difference between NO_LOG and TRUNCATE_ONLY. Both are same and
will truncate the inactive
portion of the log.
Thanks
Hari
SQL Server MVP
"David J. Cartwright" <davidcartwright@.hotmail.com> wrote in message
news:uYKNlZKtFHA.3080@.TK2MSFTNGP15.phx.gbl...
> Only use this if you do not need to be recoverable via trans log, if so
> maybe you should just use simple recovery mode
>
> Below is from BOL
>
> Note If backing up the log does not appear to truncate most of the log,
> an old open transaction may exist in the log. Log space can be monitored
> with DBCC SQLPERF (LOGSPACE). For more information, see Transaction Log
> Backups.
>
> NO_LOG | TRUNCATE_ONLY
> Removes the inactive part of the log without making a backup copy of it
> and truncates the log. This option frees space. Specifying a backup device
> is unnecessary because the log backup is not saved. NO_LOG and
> TRUNCATE_ONLY are synonyms.
> After backing up the log using either NO_LOG or TRUNCATE_ONLY, the changes
> recorded in the log are not recoverable. For recovery purposes,
> immediately execute BACKUP DATABASE.
>
> "Juliano H via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
> news:541010E0E3B40@.SQLMonster.com...
>> Hello to Everybody!
>> I have a doubts about Transaction Log. What's happen when I execute the
>> follow command
>> BACKUP LOG [database] WITH NO_LOG ou BACKUP LOG [database] WITH
>> TRUNCATE_ONLY
>> So, When should I use this command to avoid fill up the log?
>> Thanks a Lot
>> Juliano Horta
>

Doubt to be Clarified

hi

I have a backup up strategy as follows
Differential - every 4 hrs(4am,8am,12pm,...)
transaction - every 10 min

I am using SQL SERVER 2000.

At Some point of time my Differential and Transaction backup clashes
at(4am,8am,...)

when i Check my Entriprise Manager(Locks/process id)
I find Spid (blocked).

1)spid = 661

Properties Window
BACKUP DATABASE [imcl] TO [DiffBkp_IMCL] WITH NOINIT , NOUNLOAD ,
DIFFERENTIAL , NAME = N'IMCL Device Differential Backup', NOSKIP ,
STATS = 10, NOFORMAT

2)spid 708 blocked by 661
Properties Window
BACKUP LOG [imcl] TO DISK =
N'F:\DatabaseBackups\IMCL\Tran\NewTranImcl\imcl\im cl_tlog_200505261200.TRN'
WITH INIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT

What I want to know is - :
a)Should I ignore this Blocking as it is solved automatically.

b)Whether my backup plan is poor.

c)Will this effect my users who are connected to my Server.

Thank u in advanceThe blocking will be resolved, in that spid 708 is essentially 'on
hold' until 661 completes. Probably the easiest way to avoid the issue
is to set the log backups to run every ten minutes but starting at five
minutes past the hour instead of on the hour, which I guess is what you
currently have.

If the differential backup takes more than 5 minutes, then you'll still
get the blocking, though, so if this is the case, then you might want
to create multiple active schedules for your transaction log backups -
the first one from 00:00 to 03:50, the next from 04:20 to 07:50 (or
whatever interval allows the differential backup to complete without
blocking) etc.

Your backup plan is fine in principle - see "Reducing Recovery Time" in
Books Online (provided that you're also making full backups at some
point, of course).

Simon|||Thank you Simon for advice

Simon Hayes wrote:
> The blocking will be resolved, in that spid 708 is essentially 'on
> hold' until 661 completes. Probably the easiest way to avoid the issue
> is to set the log backups to run every ten minutes but starting at five
> minutes past the hour instead of on the hour, which I guess is what you
> currently have.
> If the differential backup takes more than 5 minutes, then you'll still
> get the blocking, though, so if this is the case, then you might want
> to create multiple active schedules for your transaction log backups -
> the first one from 00:00 to 03:50, the next from 04:20 to 07:50 (or
> whatever interval allows the differential backup to complete without
> blocking) etc.
> Your backup plan is fine in principle - see "Reducing Recovery Time" in
> Books Online (provided that you're also making full backups at some
> point, of course).
> Simon

Dont backup if Database hasnt changed

I do weekly full backups of my SQL databases via a scheduled T-SQL job.
I noticed that I have some static databases that dont normally change,
so I dont want to back it up if it has not changed, but when it does,
then I want a backup.
Is there something in the master table, as example, that I can check
prior to running the backup that will indicate any changes?
An example is the Northwind database. I could exclude it from the
backup, but then I would not back it up if it where to change. Again
this is an example, I would not need to modify Northwind.
Thanks in advance for any ideas; they usually give me ideas to problems
yet to come...
Rob CamardaI don't think there's a generic way to tell if a database has changed
or not (assuming that you mean when data in a user table has changed).
You could run a trace which looks for INSERT/UPDATE/DELETE statements,
log the trace in a table, and then check the contents of the table in a
custom backup job, but that doesn't seem very practical.

If your concern is to reduce disk space used for backups, you could use
differential backups for your 'static' databases, and only do the full
backup once a month, or whatever interval is appropriate. But if your
concern is to simplify administration, and given how cheap disks are
relative to a DBA's time, I would consider just adding another disk and
continuing with the same backup job for all databases.

If this isn't helpful, you might want to give some more details about
your environment, especially about how big the databases are, how often
you expect updates, and what you're trying to achieve (eg. save disk
space).

Simon|||I use SQLsafe to backup and compress my data, which in turn is saved to
my backup server. My tape is HP's Surestore 6/6000 library. I have
5540GB tapes in the library.
Monday through Saturday I perform a differential backup and a full
backup on Sunday. My plan is to keep a 1 month worth of data on the
shared disk (890GB currently, upgrading to 10 300GB raid5 next year).
Once the full backup is a month old, I move it to the library and
delete the files from the disk once the backup is successful. I wont
backup the diffs to tape and will delete the prior weeks diff backups
once the full backup is complete.
This is my current plan, it will change as I get useful input and see
how it works in practice.
My full backup of some tables are not currently large; the largest
5.3GB after compression. However, I tend to look forward, and forsee
more demand on the backup server, so why backup data that hasnt
changed? The library will be backing up 3 Linux servers, 16 windows
servers, 5 SQL and 3 Sun machines with more to come. So, I wish to
maximize the storage on the Library by keeping unnecessary data off the
library.
Again, there may not be a practical solution to my question, but often
I find I learn something unrelated that may help me with a future
problem.|||Hi

If you don't back up the database(s) then the last good backup may fall off
the tape cycle. If you chose to do this then restoring the database(s) will
you having to search more tapes for the relevant backups.

If the backups don't fit onto a single tape then you may wish to use an
autochanger (if you aren't already!). It might be possible that you could
use a server that can stage the backups on disc before putting to tape at a
different time. You may also want to consider separating database backups
from other types of backups to speed up the time needed to recover.

You may want to remove the sample databases like northwind and pubs from
your live systems, they are re-creatable from the scripts which are
downloadable if necessary.

If databases are not updated or not updated in an ad-hoc way, then you may
wish to make them read-only and use a different backup cycle for them.

John

"rcamarda" <rcamarda@.cablespeed.com> wrote in message
news:1126799621.306011.34090@.o13g2000cwo.googlegro ups.com...
>I use SQLsafe to backup and compress my data, which in turn is saved to
> my backup server. My tape is HP's Surestore 6/6000 library. I have
> 5540GB tapes in the library.
> Monday through Saturday I perform a differential backup and a full
> backup on Sunday. My plan is to keep a 1 month worth of data on the
> shared disk (890GB currently, upgrading to 10 300GB raid5 next year).
> Once the full backup is a month old, I move it to the library and
> delete the files from the disk once the backup is successful. I wont
> backup the diffs to tape and will delete the prior weeks diff backups
> once the full backup is complete.
> This is my current plan, it will change as I get useful input and see
> how it works in practice.
> My full backup of some tables are not currently large; the largest
> 5.3GB after compression. However, I tend to look forward, and forsee
> more demand on the backup server, so why backup data that hasnt
> changed? The library will be backing up 3 Linux servers, 16 windows
> servers, 5 SQL and 3 Sun machines with more to come. So, I wish to
> maximize the storage on the Library by keeping unnecessary data off the
> library.
> Again, there may not be a practical solution to my question, but often
> I find I learn something unrelated that may help me with a future
> problem.

Doing a Transaction Log Backup.

I am going to back up my databases using a Database Maintenence Plans (from
Enterprise Manager). I see there is a Complete Backup and a Transaction Log
Backup.
If I do a Complete Backup, should I do a Transaction Log Backup as well ?
What would you do a Transaction Log Backup ?
Thanks,
CraigHere is an excerpt from SQL Server Books OnLine:
Transaction Log Backups
The transaction log is a serial record of all the transactions that have
been performed against the database since the transaction log was last backe
d
up. With transaction log backups, you can recover the database to a specific
point in time (for example, prior to entering unwanted data), or to the poin
t
of failure.
When restoring a transaction log backup, Microsoft? SQL Server? rolls
forward all changes recorded in the transaction log. When SQL Server reaches
the end of the transaction log, it has re-created the exact state of the
database at the time the backup operation started. If the database is
recovered, SQL Server then rolls back all transactions that were incomplete
when the backup operation started.
Transaction log backups generally use fewer resources than database backups.
As a result, you can create them more frequently than database backups.
Frequent backups decrease your risk of losing data.
Note Sometimes a transaction log backup is larger than a database backup.
For example, a database has a high transaction rate causing the transaction
log to grow quickly. In this situation, create transaction log backups more
frequently.
Transaction log backups are used only with the Full and Bulk-Logged Recovery
models. For more information, see Using Recovery Models.
Using Transaction Log Backups with Database Backups
Restoring a database using both database and transaction log backups works
only if you have an unbroken sequence of transaction log backups after the
last database or differential database backup. If a log backup is missing or
damaged, you must create a database or differential database backup and star
t
backing up the transaction logs again. Retain the previous transaction logs
backups if you want to restore the database to a point in time within those
backups.
The only time database or differential database backups must be synchronized
with transaction log backups is when starting a sequence of transaction log
backups. Every sequence of transaction log backups must be started by a
database or differential database backup.
Usually, the only time that a new sequence of backups is started is when the
database is backed up for the first time or a change in recovery model from
Simple to Full or Bulk-Logged has occurred. For more information, see
Switching Recovery Models.
Let me know if it helps,
Edgardo Valdez
MCSD, MCDBA, MCSE
"Craig HB" wrote:

> I am going to back up my databases using a Database Maintenence Plans (fro
m
> Enterprise Manager). I see there is a Complete Backup and a Transaction L
og
> Backup.
> If I do a Complete Backup, should I do a Transaction Log Backup as well ?
> What would you do a Transaction Log Backup ?
> Thanks,
> Craig|||Thanks, Edgardo
That was helpful. It seems from that excerpt that I should do transaction
backups between database backups.
Just one more question on that:
When you restore a transaction log, do you restore it in the same way as
restoring a database ? i.e. Enterprise Manager / All Tasks / Retore / Select
the file and click OK.
Thanks,
Craig|||Sorry for not replying before.
I usually do Transaction Log Backups in between full backups, with a
frequency that varies from 10 min to 4 hours, depending of the type of
database (production, development, etc), disk space available, etc.
You can also refer to this article that can help you
(http://www.microsoft.com/technet/pr...st.mspx
)
, specially where it says "Performing Transaction Log Backups through
Enterprise Manager"
I hope it helps you.
"Craig HB" wrote:

> Thanks, Edgardo
> That was helpful. It seems from that excerpt that I should do transaction
> backups between database backups.
> Just one more question on that:
> When you restore a transaction log, do you restore it in the same way as
> restoring a database ? i.e. Enterprise Manager / All Tasks / Retore / Sele
ct
> the file and click OK.
> Thanks,
> Craig

Does this log backup overwrite?

The following command is run every 5 minutes:
BACKUP LOG [Skinstore] TO [Skinstore-Log-Diff] WITH INIT , NOUNLOAD , NAME = N''Skinstore Log Differential'', NOSKIP , STATS = 10, NOFORMAT , NO_TRUNCATE
I think it is overwriting the TRN file every time.Yes -- the INIT option makes it overwrite.
--
Adam Machanic
SQL Server MVP - http://sqlblog.com
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"Jay" <nospam@.nospam.org> wrote in message
news:%23bXzE9l6HHA.3264@.TK2MSFTNGP02.phx.gbl...
> The following command is run every 5 minutes:
> BACKUP LOG [Skinstore] TO [Skinstore-Log-Diff] WITH INIT , NOUNLOAD , NAME
> = N''Skinstore Log Differential'', NOSKIP , STATS = 10, NOFORMAT ,
> NO_TRUNCATE
> I think it is overwriting the TRN file every time.
>|||Psssst! Wrong answer Hans!
I was really hoping to be told I'm a idiot and didn't RTFM right.
Thank you sir.
"Adam Machanic" <amachanic@.IHATESPAMgmail.com> wrote in message
news:BEBE0224-CE84-4872-A44C-A77F784A57EC@.microsoft.com...
> Yes -- the INIT option makes it overwrite.
> --
> Adam Machanic
> SQL Server MVP - http://sqlblog.com
> Author, "Expert SQL Server 2005 Development"
> http://www.apress.com/book/bookDisplay.html?bID=10220
>
> "Jay" <nospam@.nospam.org> wrote in message
> news:%23bXzE9l6HHA.3264@.TK2MSFTNGP02.phx.gbl...
>> The following command is run every 5 minutes:
>> BACKUP LOG [Skinstore] TO [Skinstore-Log-Diff] WITH INIT , NOUNLOAD ,
>> NAME = N''Skinstore Log Differential'', NOSKIP , STATS = 10, NOFORMAT ,
>> NO_TRUNCATE
>> I think it is overwriting the TRN file every time.
>|||You should really think about generating backups with a unique filename each
time. Appending to a single file can get pretty ugly since you can't delete
individual backups in the file. It's all or nothing.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Jay" <nospam@.nospam.org> wrote in message
news:%23hG$XGm6HHA.3900@.TK2MSFTNGP02.phx.gbl...
> Psssst! Wrong answer Hans!
> I was really hoping to be told I'm a idiot and didn't RTFM right.
> Thank you sir.
> "Adam Machanic" <amachanic@.IHATESPAMgmail.com> wrote in message
> news:BEBE0224-CE84-4872-A44C-A77F784A57EC@.microsoft.com...
>> Yes -- the INIT option makes it overwrite.
>> --
>> Adam Machanic
>> SQL Server MVP - http://sqlblog.com
>> Author, "Expert SQL Server 2005 Development"
>> http://www.apress.com/book/bookDisplay.html?bID=10220
>>
>> "Jay" <nospam@.nospam.org> wrote in message
>> news:%23bXzE9l6HHA.3264@.TK2MSFTNGP02.phx.gbl...
>> The following command is run every 5 minutes:
>> BACKUP LOG [Skinstore] TO [Skinstore-Log-Diff] WITH INIT , NOUNLOAD ,
>> NAME = N''Skinstore Log Differential'', NOSKIP , STATS = 10, NOFORMAT ,
>> NO_TRUNCATE
>> I think it is overwriting the TRN file every time.
>>
>|||Actually, I've been here a month now and had initially only verified that
backups were being done. Why so little? Because, no one would ever overwrite
their log backup without having backed it up, or archived it first ... would
they?
I have since spoken to the guy that set it up (my boss) and he said he
thought that he was making a complete copy of the .ldf changes for the day
each time he wrote to the .trn. I literally had to stop myself when I heard
my tone in replying to him. Not a good idea to speak to your boss like
you're talking to an idiot.
Anyway, when drive space is available, I will be changing to my own backup
program that was based on RealSQLGuy's backup program. The only problem with
the program is that it assumes the job that puts it to tape will also be
removing old files. A situation that is not the case here.
Thanks,
Jay
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23tB8RCn6HHA.5012@.TK2MSFTNGP02.phx.gbl...
> You should really think about generating backups with a unique filename
> each time. Appending to a single file can get pretty ugly since you can't
> delete individual backups in the file. It's all or nothing.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Jay" <nospam@.nospam.org> wrote in message
> news:%23hG$XGm6HHA.3900@.TK2MSFTNGP02.phx.gbl...
>> Psssst! Wrong answer Hans!
>> I was really hoping to be told I'm a idiot and didn't RTFM right.
>> Thank you sir.
>> "Adam Machanic" <amachanic@.IHATESPAMgmail.com> wrote in message
>> news:BEBE0224-CE84-4872-A44C-A77F784A57EC@.microsoft.com...
>> Yes -- the INIT option makes it overwrite.
>> --
>> Adam Machanic
>> SQL Server MVP - http://sqlblog.com
>> Author, "Expert SQL Server 2005 Development"
>> http://www.apress.com/book/bookDisplay.html?bID=10220
>>
>> "Jay" <nospam@.nospam.org> wrote in message
>> news:%23bXzE9l6HHA.3264@.TK2MSFTNGP02.phx.gbl...
>> The following command is run every 5 minutes:
>> BACKUP LOG [Skinstore] TO [Skinstore-Log-Diff] WITH INIT , NOUNLOAD ,
>> NAME = N''Skinstore Log Differential'', NOSKIP , STATS = 10, NOFORMAT ,
>> NO_TRUNCATE
>> I think it is overwriting the TRN file every time.
>>
>>
>|||One possible way is to base the backups on weekday, day in month or similar. Take the easy route,
day of week. You have a Monday file, a Tuesday file etc. The first backup of the day, you do INIT,
then the rest of the backups of the day you do NONIT. You now have a tail of 6 backup files
(excluding the current day). This will cut down number of physical files (nice of you have lots of
databases and frequent log backups). However, some SQL persons aren't that familiar with several
backups on same file, so they don't understand the WITH FILE = option of the RESTORE command...
Pretty much a matter of taste. And, remember that the code you write need to be understood and
maintained by somebody... ;-).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jay" <nospam@.nospam.org> wrote in message news:ui%23tvhn6HHA.5984@.TK2MSFTNGP04.phx.gbl...
> Actually, I've been here a month now and had initially only verified that backups were being done.
> Why so little? Because, no one would ever overwrite their log backup without having backed it up,
> or archived it first ... would they?
> I have since spoken to the guy that set it up (my boss) and he said he thought that he was making
> a complete copy of the .ldf changes for the day each time he wrote to the .trn. I literally had to
> stop myself when I heard my tone in replying to him. Not a good idea to speak to your boss like
> you're talking to an idiot.
> Anyway, when drive space is available, I will be changing to my own backup program that was based
> on RealSQLGuy's backup program. The only problem with the program is that it assumes the job that
> puts it to tape will also be removing old files. A situation that is not the case here.
> Thanks,
> Jay
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23tB8RCn6HHA.5012@.TK2MSFTNGP02.phx.gbl...
>> You should really think about generating backups with a unique filename each time. Appending to
>> a single file can get pretty ugly since you can't delete individual backups in the file. It's all
>> or nothing.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Jay" <nospam@.nospam.org> wrote in message news:%23hG$XGm6HHA.3900@.TK2MSFTNGP02.phx.gbl...
>> Psssst! Wrong answer Hans!
>> I was really hoping to be told I'm a idiot and didn't RTFM right.
>> Thank you sir.
>> "Adam Machanic" <amachanic@.IHATESPAMgmail.com> wrote in message
>> news:BEBE0224-CE84-4872-A44C-A77F784A57EC@.microsoft.com...
>> Yes -- the INIT option makes it overwrite.
>> --
>> Adam Machanic
>> SQL Server MVP - http://sqlblog.com
>> Author, "Expert SQL Server 2005 Development"
>> http://www.apress.com/book/bookDisplay.html?bID=10220
>>
>> "Jay" <nospam@.nospam.org> wrote in message news:%23bXzE9l6HHA.3264@.TK2MSFTNGP02.phx.gbl...
>> The following command is run every 5 minutes:
>> BACKUP LOG [Skinstore] TO [Skinstore-Log-Diff] WITH INIT , NOUNLOAD , NAME = N''Skinstore Log
>> Differential'', NOSKIP , STATS = 10, NOFORMAT , NO_TRUNCATE
>> I think it is overwriting the TRN file every time.
>>
>>
>