SQL 2k and backup agents

[MrRetro]

Hello all
Can someone give me some clarity on backups with a sql agent please. We are running Arcserv which I know nothing about I'm afraid, and we have a sql box holding a few db's.
When I looked at the sql setup I noticed that there was no maintenance plans setup for any of the db's.
This seemed wrong to me but when I asked the guy that configured the sql box, he said that Arcserv with the agent will back up the db's and the transaction logs so there is no need for a sql maintenance plan. Is this accurate ?
A search of the hard drive returned no .trn files so I have no idea how the backup software is backing them up. I have some db experience with sql 7 but 2k is new to me (though there is lots that looks the same as 7).

All feedback is appreciated, thanks in advance.

 

[SQLBill]

Take a look for .bak files. That's what my SQL Server 2000 backup files use for the extension.

Unless you are REALLY stuck to using third-party software and agents, I suggest doing SQL Server BACKUP commands to backup the databases to hard drive and then using the third-party software to copy those backup files to tape.

-SQLBill

 

[MrRetro]

Thx Bill, not a bak file to be found that relates to any of the DB's. It is my impression that one wont get made unless a maintenance plan is in place. Am I losing my mind ? or is that accurate ? Anyone know ?

 

[SQLBill]

How are the backups being done? You don't need a maintenance plan unless you are letting SQL Server do the backups and even then there may not be a maintenance plan.

For SQL Server backups, you can either use a mainentance plan, which is like using a Wizard to create the backups or you can use the BACKUP DATABASE commands yourself.

Or you can use third party tools (usually with a SQL Agent) to do backups. If you are using this method, the backup file might not use a 'normal' SQL Server backup extension.

Preferred method is to use the BACKUP DATABASE method to make a backup file on disk and then use a third party backup tool to copy that backup file to tape.

So, how ARE the backups being done?

-SQLBill

 

[MDXer]

Where are the backups being made to? if the agent writes the backup to tape or other media you won't find a file by searching the drives on the server because they are on the tape.

As for using 3rd party software I agree with Bill. Besides being a waste of money they can also impact the performance of the DB while a backup is running.

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[MrRetro]

Thanks for jumping in guys.
The whole sql box is being backed up every night with Arcserv (just doing all the drives). I will look a little deeper at what Arcserv is doing with the sql stuff.
Restoring a backup resulted on no BAK files or TRN logs so I am a little miffed about how we would recover from a crash halfway through the day.
Hope I am not confusing you guys too much with my questions.
Bill the way you describe the backup method is the way I have always done it in the past, maintenance plan spits files to a certain directory, make that directory part of your daily backups, your good to go.
Bill you say some setups dont use a maintenance plan, isnt the integrity checks a maintenance plan does important ?
Thanks for enlightening me guys, its been a few years since I have had the pleasure of messing with sql and db's...fixing email is starting to look pretty good again

 

[SQLBill]

The SQL Maintenance plan is just a 'wizard'. You tell it what you want to do and it figures out how to do it (the coding).

You may hear a lot of "using the maintenance plan is for beginners, companies without a DBA, or lazy DBAs". I don't agree with that....yes it's easier to use than doing the coding yourself, but that doesn't make a DBA lazy.

I personally do all the coding myself. I run the BACKUP DATABASE, BACKUP LOG, etc. commands myself. I've created JOBs that do this on the schedule that I set (including reindexing/defragging).

Am I doing it the best way....depends on how you look at it. I have more work to do and more scripts (jobs) to keep up. However, I know what my jobs are doing. If a job needs 'tweaked' I can do that. Maintenace Plans aren't as flexible (in my opinion).

The above is also one of the arguments about using Enterprise Manager - it lets you do a lot of things (creating tables, dropping tables, etc.) but it does it 'behind the scenes' and you really never see what commands it's running. But while I do all my own scripts, etc., there are times I use Enterprise Manager since it can usually do the job faster.

-SQLBill

 

[SQLBill]

BTW- there is a command RESTORE VERIFYONLY that verifies the backup. See the BOL for more information about that command.

-SQLBill

BOL = Books OnLine = Microsoft SQL Server's Help
Installed as part of the Client Tools
Found at Start>Programs>Microsoft SQL Server>Books OnLine