Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
DB Engine
SQL ServerMSDESQL Server CE
Services
Analysis (Data Mining)Analysis (OLAP)DTSIntegration ServicesNotification ServicesReporting Services
Programming
CLRConnectivitySQLXML
Other Technologies
ClusteringEnglish QueryFull-Text SearchReplicationService Broker
General
Data WarehousingPerformanceSecuritySetupSQL Server ToolsOther SQL Server Topics
DirectoryUser Groups
Related Topics
MS AccessOther DB ProductsMS Server Products.NET DevelopmentVB DevelopmentJava DevelopmentMore Topics ...

SQL Server Forum / DB Engine / SQL Server / March 2008

Tip: Looking for answers? Try searching our database.

newbie question regarding sql 2000 backup

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
om - 20 Mar 2008 00:43 GMT
Hi,

I am new to MS sql server (2000) and have couple question regarding the
backup.

We are running sql server 2000 and the enterprise manager is configured
to perform a full database backup daily. My question is do I have to
backup the transaction log as well? I tried to add a backup entry
(within the enterprise manager) for the transaction log by selecting the
transaction log and adding another file device, however, it appears that
  the full database backup option will be de-selected (after selecting
transaction log). Is this normal? Does that mean the full database will
still be backup?

Thanks
Tibor Karaszi - 20 Mar 2008 07:49 GMT
> We are running sql server 2000 and the enterprise manager is configured to perform a full database
> backup daily. My question is do I have to backup the transaction log as well?

We cannot answer this for you. You need to determine this based on SLA etc. I suggest you read the
chapters about backup and restore in Books Online. Be prepared to spend at least a day to understand
these topics. Based on that you decide with whoever own your system what types of backup you
require. IT all boils down to how much data loss you can tolerate.

Pressing the "schedule" button in the EM backup dialog creates a new SQL Server Agent how. Check out
your jobs, if you selected this several times you probably have lots of Agent Jobs that do backup.
Signature

Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

> Hi,
>
[quoted text clipped - 8 lines]
>
> Thanks
Tibor Karaszi - 20 Mar 2008 07:50 GMT
... also you might want to check out the maintenance plans. These has options to keep x generations
of backups and clean up old backup files etc.

Signature

Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

> Hi,
>
[quoted text clipped - 8 lines]
>
> Thanks
bass_player - 20 Mar 2008 10:08 GMT
Backup and recovery procedures should be defined based on your RPO/RTO. You
can have a combination of FULL, Differential, Filegroup and transaction log
backups in SQL Server 2000.  But then, again, these will only make sense if
you define your RPO/RTO

> Hi,
>
[quoted text clipped - 11 lines]
>
> Thanks
Tom Cooper - 20 Mar 2008 16:57 GMT
Depending on how critical your database(s) are to your business, you should
consider engaging a consultant to help you set up your backup and disaster
recovery plans.  I realize this can be an expense you haven't planned for,
but if you are new to all this, it can be, IMO, well worth it.  If you don't
have experience in this area, it is easy to have an inadquate
backup/disaster recovery plan.  And the problem with that is you often don't
know what you are doing is inadquate until you need to do a restoration, and
then it's too late.

It's hard for a newsgroup to give you answers on this since it depends so
much on what you are doing with your database(s) and how important they are
to your company.  What you need to be doing may well depend on information
that is confidential to your business and that you can't be posting on a
public newsgroup.  On the other hand, a consultant who knows how to do this
may well be able to do this fairly inexpensively and quickly.

Tom

> Hi,
>
[quoted text clipped - 11 lines]
>
> Thanks
OM - 20 Mar 2008 20:26 GMT
Thanks for all the feedback.

> Depending on how critical your database(s) are to your business, you should
> consider engaging a consultant to help you set up your backup and disaster
[quoted text clipped - 29 lines]
>>
>> Thanks
Greg D. Moore (Strider) - 23 Mar 2008 17:10 GMT
> Hi,
>
[quoted text clipped - 4 lines]
> perform a full database backup daily. My question is do I have to backup
> the transaction log as well?

To add to the comments, you should either back up the transactin log OR set
the database recovery model to SIMPLE.

Otherwise, your transaction log will grow w/o bounds and you'll be back here
in 6 months asking how to shrink it or worse, back here saying, "well it
grew to big, so I found a random web page that suggested deleting it and
letting SQL Server create a new one and now my database is hosed."

HOWEVER, before making the decision between backups or SIMPLE mode, PLEASE
make sure to read all the other comments posted.  WHY you might decide one
over the other is a critical decision; as is how often you back up the
transaction log.  Some places do it once a day, others 4 or more times an
hour.

> I tried to add a backup entry (within the enterprise manager) for the
> transaction log by selecting the transaction log and adding another file
[quoted text clipped - 3 lines]
>
> Thanks

Signature

Greg Moore
SQL Server DBA Consulting           Remote and Onsite available!
Email: sql  (at)  greenms.com          http://www.greenms.com/sqlserver.html

 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.