New to Backups

Question

New to Backups

stewart noble

SSCrazy

Points: 2009
More actions
June 1, 2006 at 9:24 am

#168390

Hi all
I have been entrusted with setting up a workable backup strategy for a SQL Server 2000 implementation. I have been studying the various scripts on here, which have given me many ideas, and I have come up with a script to generate either Full or differential Database backups and a Transaction Log backup as appropriate.
In testing out my script, I have come across a few issues, one of which is the following error message, which i am not sure what to do about:
Server: Msg 3201, Level 16, State 1, Line 1
Cannot open backup device 'c:\backups\eXpress_200606011613.BAK'. Device error or device off-line. See the SQL Server error log for more details.
Server: Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.
My script is generating the following command to be executed:
BACKUP LOG eXpress TO DISK = 'c:\backups\eXpress_200606011613.BAK' WITH NAME = 'eXpress(Logfile Backup) ', DESCRIPTION = 'TRANSACTION LOG BACKUP at:Jun 1 2006 4:13PM.',MEDIANAME = 'eXpress_Log20060601',DESCRIPTION = 'TRANSACTION LOG BACKUP at:Jun 1 2006 4:13PM.', STATS = 10

I have a feeling that I need to add a dump device but I am not really sure.
Any help or thoughts would be much appreciated.

Thanks

S

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Norene Malaney SSCommitted Points: 1896 More actions · Answer 1

Since you are using the "DISK" parameter in the backup code you should not need to create a "backup device" (FYI - created through SQL EM -> DATABASENAME -> Management -> Backup). However, does a folder named "backups" exist on the C drive?

Have a good day,

Norene Malaney

stewart noble SSCrazy Points: 2009 More actions · Answer 2

Thanks Norene,

I assume that the folder "c:\Backups" needs to exist on the computer that SQL Server is running on?

When this goes into production I will be using a network drive which will be specified at runtime (as an input parameter to the procedure). This will be in UNC form

Can you tell me who will need what permissions to write to that location?

Thanks again

S

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

Stewart

Yes, the folder needs to exist, and the account you use to start SQL Server needs read and write permissions on it.

I do not recommend backing up to a network location. If there are any problems with the network at the time the backup is being attempted, the backup may fail. Much better (if disk space allows) to back up locally and then copy to the network location.

John

stewart noble SSCrazy Points: 2009 More actions · Answer 4

thanks very much.

That was very helpful.

cheers

S