.trn File not backing up to an external drive

Question

.trn File not backing up to an external drive

Erika Goodman

SSC Enthusiast

Points: 136
More actions
February 9, 2006 at 2:29 pm

#113295

So, i have set up the database maintanience plan...i have one set up for and external backup and another plan set up for just a normal internal backup i.e. within the server itself...When I run the External Plan the integrity check fails and the transaction log backup fails.
I have checked the backup destination 2ce i have also done a wizard and for some reason NOTHING...i get no .trn files backing up and with the integrity check i'm not really sure what to look for. My logs give me no clue as to where my problem is. Any hint i would GREATLY appreciate.

Thanks
Erika

Viewing 9 posts - 1 through 8 (of 8 total)

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

sswords SSCrazy Eights Points: 8207 More actions · Answer 1

This sounds like an authentication problem.

By external, I'm assuming you mean another server? In your back up plan, you'll need to use the full UNC path to the backup server and folder like "MYDOMAIN\myserver\d\backup" or whatever. You will also need to make sure that the account that the SQL Service runs under has sufficient rights in the domain to do this.

My hovercraft is full of eels.

Eric Heilman Old Hand Points: 385 More actions · Answer 2

I had a similar problem where my backup jobs would fail when I was trying to post my backup files to my file server that backs up to tape. I found on several forums that this is a known issue on SQL Server 2000.

I got around the problem by backing them up locally on the SQL Server then I wrote a VB.NET app to move the files to my file server. I also have my VB.NET app take care of deleting the old files (anything older than 2 days is deleted from the SQL backups).

I have 3 SQL Servers running like this and have not had a problem since I made this switch (the VB.NET app moves the data from all 3 servers to the file server). The VB.NET app runs in the background by a scheduled task on the server every 3 hours to capture all the full backups and transaction backups.

Erika Goodman SSC Enthusiast Points: 136 More actions · Answer 3

well it's just an external hard drive?? would that make a difference? I'm gonna go and check this out...thank you...for the help i appreciate it

sswords SSCrazy Eights Points: 8207 More actions · Answer 4

Okay...ignore my post then. Can you browse to the external drive from Explorer on the server? As long as the logical drive letter can be seen from the OS, SQL Server should see it as well. The external drive should also be fromatted as NTFS.

My hovercraft is full of eels.

Erika Goodman SSC Enthusiast Points: 136 More actions · Answer 5

everything is running fine...but i just can't seem to get those 2 plans working...

SQLBill SSC Guru Points: 51440 More actions · Answer 6

PLEASE tell me you aren't backing up one database to two different places.

A transaction log backup applies ONLY to the previous FULL backup, same with a differential backup.

Two backup drives A (internal) & B (external)

Full backup to A

Full Backup to B

Transaction log backup to A (this tlog actually belongs to the full backup on B)

Transaction log backup to B (this is the second tlog for Full Backup on B)

Transaction Log backup to A (this belongs to the full backup on B).

etc......

Now you need to do a RESTORE and oooppps, backups on B are gone. You now only have a FULL BACKUP (the first one) and that is all.

Restore and all that is available is the B backups? Ooopss. You can restore the second Full Backup and nothing else. The first tlog that needs restored is on the A backups, which don't exist.

The proper way to do this is, do all your backups to A. Then COPY those backup files to another location (B).

-SQLBill

Erika Goodman SSC Enthusiast Points: 136 More actions · Answer 7

Thank you So much that made COMPLETE sense...and it also worked...

but as well as this i'm also trying to see the difference with bulk logged and full recovery models...you guys are AWESOME thanks SO much for the help

Erika Goodman SSC Enthusiast Points: 136 More actions · Answer 8

So i have changed my recovery model to a full back up, i have adjusted where all my backups are going...and still my transaction log is erroring out. any ideas? where i should check? anything?

thanks so much