Backing up SQL 2000

[Gabriel2010]

Hello mates!

New to the admin aspect of SQL 2000, wondering if there
are any advantages/disadvantages of backing the databases
using the NT backup utility found in Windows 2000 server.

Is it better to use third-party software?

Is there anything that should be excluded similar to,like,
the M drive on Exchange 2000?

Thanks !

Carlos

Glad to be here!

 

[JayKusch]

As a DBA, my method of choice is to use SQL Servers native backup, through the use of a job, to spool the backup to disc and then a third party utility to backup the disc backup to tape.

This way you have the tape for off-site storage, Disaster Recovery, and a disc backup to perform quick restores. Restore from tape take much longer. Of course you have to have the room for the disc backups for this method to work.

Where I am, we have a server dedicated for use as a backup server and repository for the backup files themselves.

Thanks

J. Kusch

 

[SQLBill]

Like Jay, I use SQL Server to do it's own backup to disk and then I use a third-party tool to copy that file to tape.

Why mess with a good thing? SQL Server knows how to do it's own backups and does them correctly. When Microsoft changes SQL Server, it will still be able to back itself up.

Third-party tools may not work properly. Even if they worked when you first started using them, they may not work after either SQL Server or the 3rd-party tool has been upgraded.

Also, why pay extra money for an agent to do the backup when SQL Server does it?

-SQLBill

 

[ecobb]

Personally, I prefer to use SQL Server's built in backup tools, instead of the NT backup utility. I figure that SQL Server backups are specifically designed to handle databases, so that's what I let them do.

I then place a copy of the backup files on another server that Backup Exec makes a tape copy of each night.

Hope This Helps!

Ecobb

"Alright Brain, you don't like me, and I don't like you. But lets just do this, and I can get back to killing you with beer." - Homer Simpson

 

[Gabriel2010]

Hello!

Thanks for your information.

I have setup a nightly backup maintenance plan within SQL that backsup databases to file. Then run the NT backup to backup those files.

Now, although it seems the back-t0-file is working since DBs show up in respective configured location, I get an error that doesnt seem to go away, event ID 208.

I think it was something about master db changed from simple to full recovery model. It was changed back to simple and msdb set to full:

"...Status: Failed - Invoked on: date - message: The Job failed. the was invoked by Schedule 4 (Schedule 1). The last step to run was step 1 (Step 1)."

That should be a new thread.

Thanks again guys!

Carlos





Glad to be here!

 

[SQLBill]

What kind of backup are you trying to do for the Master and MSDB?

A transaction log backup will fail for both of those. You can NEVER set Master to FULL recovery mode (which allows Transaction Log backups), it's always set for SIMPLE (which only allows FULL backups).

MSDB is set for SIMPLE, but I believe you can set it for FULL recovery mode.

-SQLBill

 

[nigelrivett]

Try not yo use maintenance plans.
Much better to create the backups yourself (see backup datatbase in bol) then you will know what is going on.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[Gabriel2010]

mmmmmmmmmm

So the Full Recovery option will backup transaction logs if the "Back up the transaction log of the database as part of the maintenance plan" is checked? I think it is clear.

Also, what are some essential things to keep in mind, to know about SQL 2000 in general, like the real-meat to know. Not asking for long tips just certain things that an SQL DB admin should just know or do (besides backups).

I would certainly appreciate any links to learning about the SQL admin aspect of it. Eventually it is good to learn about sql commands.

Disaster recovery links, information,white papers would be great.

Thanks for your help again.

Carlos



Glad to be here!