August 10, 2004 at 1:38 pm
Hi all,
I've just been told that backup and restore is going to become the responsibility of our network / systems administrators team (not to be confused with SQL Server DBA / SA).
Now, as the de facto DBA / SQL SA here, I've been handling the backups and restores for quite some time. However, if someone wants to take some load off my back, I'm not going to complain -- unless it puts the organization at greater risk, or risks us not being able to deliver to our users the service they need (access to DBs and data).
So, I'm wondering if anyone out there has some really compelling reasons why it would be / not be a good thing to unload this responsibility to the network admins.
One of the things I'd like specifically addressed is their plan to use 3rd party software, ArcServe, which I believe can automate the shutdown and re-start of the server while the backups are being done -- directly to tape.
I'm not convinced this is a great idea, currently we use SQL Server to perform the backups on a schedule, then backup the dumps to tape.
TIA,
Paul
August 10, 2004 at 2:09 pm
I personally don't like 3rd party software backing up my SQL Server databases. I've encountered problems in the past. What I do is a SQL Server backup to disk, then use 3rd party software to backup (copy) the backup files to tape.
Who should do that? I do it all, but if I had to change it - DBA backup SQL Server to disk, SysAdmin backup (copy) the backup files and other OS files to tape. Then for the restore, I would use my disk backup first. If that was corrupted, I would ask the SysAdmin for the file from tape.
Some 3rd party backup software can backup SQL Server without shutting it down. If your software can't do that - don't use it. Why make your database unusable for even a short time when SQL Server can do a backup without shutting down?
-SQLBill
August 10, 2004 at 2:55 pm
SQLBill,
I too would like to set up a back up routine where the DB's are backed up to .dat files and then use Veritas to back it up to tape.
Do you have a script that you can direct me to that automatically backs up the databases to file?
Thx,
DM
August 11, 2004 at 6:23 am
DM,
This will back up all DBs except pubs, Northwind, and tempdb, to the path you specify. Files will be named according to this format: _Dump.bak.
IF OBJECT_ID('dbo.utl_backup_DBs') IS NOT NULL
DROP PROCEDURE dbo.utl_backup_DBs
GO
CREATE PROCEDURE dbo.utl_backup_DBs
@cBakFilePathArgVARCHAR(512)
AS
BEGIN
SET NOCOUNT ON
DECLARE @cDBNameVarSYSNAME
DECLARE @nMyCursorVarCURSOR
DECLARE @cCommandVarNVARCHAR(4000)
SET @nMyCursorVar = CURSOR FOR
SELECT Name
FROMMaster.dbo.sysdatabases
WHEREName NOT IN ('pubs', 'Northwind', 'tempdb')
FETCH NEXT FROM @nMyCursorVar INTO @cDBNameVar
WHILE @@FETCH_STATUS -1
BEGIN
IF @@FETCH_STATUS -2
BEGIN
SELECT @cCommandVar = 'BACKUP DATABASE ' + RTRIM(@cDBNameVar) + ' TO DISK=''' + @cBakFilePathArg + RTRIM(@cDBNameVar) + '_Dump.bak'''
--SELECT @cCommandVar
EXECUTE sp_executesql @cCommandVar
END
FETCH NEXT FROM @nMyCursorVar INTO @cDBNameVar
END
CLOSE @nMyCursorVar
DEALLOCATE @nMyCursorVar
END
GO
--EXEC dbo.utl_backup_DBs
August 11, 2004 at 7:32 am
DM,
I just created dump devices (sp_addumpdevice) and then created a job to run the backup command.
EXEC SP_ADDUMPDEVICE 'disk', 'masterfull', '<path>\masterfull.bak'
BACKUP DATABASE Master TO MasterFull
Refer to the BOL for more information on sp_addumpdevice and BACKUP DATABASE commands.
-SQLBill
August 11, 2004 at 10:06 am
In our environment it is a split responsibility. As the DBA I am responsible for, and manage, all backups of the SQL Server instances, and all restores from backups. The systems/networkpeople are responsible for managing the resulting files (tape and off-site storage).
Backups are scheduled as SQL Server agent jobs, with a "failover" process on a separate server in the domain that kicks in if the backups are not where they are supposed to be, when they are supposed to be.
August 11, 2004 at 10:56 am
Several years ago, we were using Arcserve to backup a 60GB SQL database on one of our servers, not my idea and I protested it at the time. It so happened that server was the one we would restore for a disaster recovery test. To make a long story slightly less long, we were unable to restore from the Arcserve backup. Thankfully, it was a test and we didn't NEED that backup. My case for SQL native backups was made, and we have never used 3rd party software to backup the databases since.
Steve
August 11, 2004 at 12:04 pm
Do a native SQL Server backup to disk, have tha admins backup that file/directory. DON'T use any 3rd party SW that stopes SQL Server to do a backup, if a problem should occur during the re-start or backup you won't know until next day >>> too late. SQL backup solutions should be determined by the DBA, not sysadmins/managers.
Use the maintenance plan wizard to create a maintenance plan to backup the database and tranaction logs (this allows you to control the scheduling of the backups and how long to retain the files).
If your database is very large you might consider using SQL LiteSpeed; I have achived a compression over SQL native of 4:1, LiteSpeed also allows you to backup to UNC directories if you don't have room on your server.
develop/test/maintain a disaster recovery plan >>> use "log Shipping" look it up in SQL Books On Line.
Remember::: You are the DBA, if the database gets hit, so do you.
Good luck.
August 11, 2004 at 12:16 pm
Hey gang,
I appreciate each of your input. I suspected I was right all along, but... I cut'n'paste below (in quotes) the original notice I received from my management. The background: I schedule the DB backups to disk, and ArcServe backs those up to tape.
"The backup to tape is a copy of a backup and not a backup. This is 2 steps away from a restore and is not the correct procedure for database backups to tape.
We should be using the backup restore software that is used for all other backups.
I understand that product to be ArcServe. This product can be used for SQL Server 2000 backups. It may be required to take the database off line prior to the backup and put it back on line after the backup but that shouldn't be a problem since the backups are run during 'off' hours. ArcServe provides a before and after facility to issue these commands.
The current backup restore procedure will be too labour intensive and error prone if we are trying to restore to a empty server offsite. Also, the control and handling of a recovery procedure is an operational process and as such belongs in the hands of 'system' operations not a DBA."
Anyone else have anything to add? The more I can show management, the better.
PS: SQL Draggon, I am currently in the process of preparing a proposal to begin using log shipping here to keep a backup server up-to-date.
Thanks all,
Paul
August 11, 2004 at 2:42 pm
I used Veritas Backup Exec with the SQL Server Agent. I was faithfully backing up my databases every day. Unfortunately, we do not have a test system and I was unable to test a restore of the data. (Didn't have enough room on the production machine either).
A few months pass. The server crashed and needed to be rebuilt. When we began to recover (restore) from the tape, we got an error message. Turns out the Veritas method was not compatible to our set up unless we had a specific Veritas Hotfix applied. Which we were not told about when we purchased it, nor was it found on their website. We had to send our tapes to Veritas for recovery. It took 1 1/2 months to get the tape recovered and returned.
Do I trust third-party software to do my backups? NO!!!!
Point to be made.....MS can change SQL Server and the 'native' backup method will ALWAYS work. However, 3rd party software may not keep up with the change and fail when it's most needed.
If you can't get them to agree and change their mind, you need to get it in writing (which you already really have) that the responsiblity for backing up and recovering the database is not yours.
-SQLBill
August 11, 2004 at 3:00 pm
Once I was told that Veritas Backup use sql server backup engine ("native" backup method) to do database backup on SQL Server, so I was pretty comfortable with veritas backup. I did several restore test from database backup performed by Veritas Backup and no problem was found.
Could pls tell me more about specific Veritas Hotfix or refer to somewhere I can get more information about it?
August 12, 2004 at 3:56 am
I reckon SQL Server's backup & restore capabilities are excellent and easy to use (have you ever backed up an Oracle database??) so don't reinvent the wheel. Back your dbs up to disk then get the other team to copy the backup files via ArcServe. Use differential backups if your dbs are too big for daily full backups. Don't pass resposibility over either - it's a few seconds work to check whether a backup has been successful and you can script checks if you have dozens of databases on several servers (although even this would only take 10 or 15 minutes using MMC/EM).
PS You are using the BACKUP DATABASE command aren't you rather than shutting down the SQL Server and copying the data and log device files to disk (it has been known...)????
August 12, 2004 at 6:33 am
Yes, using BACKUP DATABASE. And what ArcServe proposes to do is stop the server and/or detach the DB, backup the DB and log files, I guess, and re-start/re-attach.
Seems like madness to me....
August 12, 2004 at 8:05 am
Vincent,
As I said in my post, it was a very specific issue.
1. You had to be running SQL Server 2000 Enterprise Edition
2. It had to be on Windows Advanced Server 2000
3. It had to be clustered
4. Backup Exec had to be on the same server as SQL Server 2000
5. Backup Exec had to be 8.6
If that's your scenerio then you needed to apply hotfix 4.
-SQLBill
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply