February 13, 2008 at 9:28 am
I am running Complete Backups and Transactional Log Backups in Microsoft SQL Enterprise Manager Version 8.0 on ServerA. These backups occur at 4:00 AM. My logs show that these backups are successful.
On ServerB, I am running BrightStor ARCserve backups of (among other things) ServerA every night at midnight. Every morning when I check my backups on ServerB, I read
"Unable to open file. (FILE=\\ServerA\SQL\db_log.ldf, EC=SHARING VIOLATION)"
"Unable to open file. (FILE=\\ServerA\SQL\db_Data.mdf, EC=SHARING VIOLATION)" - and many more lines like this, referring to every database file on our SQL Server.
I "inherited" this setup, and I have been given the job of cleaning it all up.
My first thought is: if I (with BrightStor backups on ServerB) am backing up all of my .BAK files as well as Transactional logs on the SQL Server, should I need to be backing up my actual .MDF and .LDF files on the SQL Server? In order to accomplish that, do I need to be shutting down my Sql Server until that full backup is accomplished?
Any help greatly appreciated.
February 13, 2008 at 9:42 am
It seems from what you have said the 3rd party product is not backing up the SQL database but trying to backup the actual files in the file system. which is causing the file share error.
Previously i have taken full database backup and transaction log backups to disk and then backing up these files to tape. This does not then interfere with your database files.
To retore from the tape backup you need to copy the backup file you need to disk and then restore from disk.
Hope this Helps
Gethyn Elliswww.gethynellis.com
February 13, 2008 at 11:06 am
I agree. That is how I accomplish my backup of the backup procedures. Of course the next step is to ensure you have a copy of the backup off site. I zip and transfer those backup files to our offsite location.
Marvin Dillard
Senior Consultant
Claraview Inc
February 15, 2008 at 8:22 am
Thank everyone for the replies. I was *hoping* someone would recognise an issue with our SQL Server 2000 - and tell me why these SQL database files were not getting backed up by the third party software.
So I will try the approach you have suggested: I will manually backup the database files to disk elsewhere, then to tape (possibly compressing them at the same time). I guess (at that time) I could tell the 3rd party backup software to ignore these database files - just backup the copies of these files.
Again, thanks.
February 17, 2008 at 11:42 pm
I'm unfamiliar with the 3rd party backup software you mentioned. If it is not specifically intended for backing up a data server, and is only meant for backing up a file server, it won't work and the errors you get make sense. By their nature, the .mdf and .ldf files are not accessible (except by some 3rd party backup software packages) unless you shut down the SQL Server service which of course in most cases is unacceptable. If you are doing SQL database and log backups, then that is all you need; you never need to touch the .mdf and .ldf files. As mentioned above, just copy those files to a safe place.
- Paul
http://paulpaivasql.blogspot.com/
February 17, 2008 at 11:59 pm
You wouldnt be able to backup the mdf and ldf files using the mentioned third party software unless you stop the sql server service..
IF you still need to take a live backup of sql server database, rather than backing up the backup files(*.BAK) and log backups..check with the third party vendor....brightstor has an SQL server agent, you would need to probably purchase and isntall that for taking backup of the live database....some backup software also provide open file backup agents..which backsup open files, but this would not in anyway backup the mdf and ldf files..
February 19, 2008 at 3:37 am
If you do use a backup agent do make your backups, make sure you test the restore process!
Any plans for making backups need to start with your recovery SLAs. You need to know what is expected of you if a) the application writes meaningless data, b) the server becomes unusable, c) your site becomes unusable. Most problems relate to item a) !
When you know what the expected recovery times are, and how close you need to get to the point of failure, you can plan your backup strategy. This may show you need full backups daily and transaction log backups taken every 30 minutes, or may show you can run in Simple mode with a weekly backup, or one of 101 other combinations.
This should be documented and signed off by management. If the recovery SLA required 30-minute log backups this has a cost implication, and the sign-off needs to cover the costs required.
You can then look at if using a backup agent to copy the database files direct is sensible, or if it is better to take SQL Server backups and get the backup agent to backup the backups. Having done all this, test your restore process. Restore testing should not just cover checking you can do a restore... you should also (maybe 4 times a year) request a backup from offsite storage and confirm you can get the restore completed within your SLA time.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
February 19, 2008 at 8:30 am
Great responses everyone. I really appreciate it. All of our BAK and TRN files are being backed up to tape, and I am now having the scheduled 3rd party backup software ignore the mdf and ldf files.
Also, I am going to be doing a series of test restore operations to see if there will be any issues there.
February 19, 2008 at 8:39 am
Paul.Blain (2/15/2008)
Thank everyone for the replies. I was *hoping* someone would recognise an issue with our SQL Server 2000 - and tell me why these SQL database files were not getting backed up by the third party software.So I will try the approach you have suggested: I will manually backup the database files to disk elsewhere, then to tape (possibly compressing them at the same time). I guess (at that time) I could tell the 3rd party backup software to ignore these database files - just backup the copies of these files.
Again, thanks.
The issue would be that even if you managed to get past the locking issue - the data and log files being backed up externally wouldn't be in any useable format (meaning - the internal content wouldn't be consistent, and therefore couldn't be used). Unless you stopped all activity of any kind while the backup is going on.
BrightStore surely has a specific SQL Agent to do the backups if you want it to run the data and log backups. That being said - i've always had issues with third-party agents performing the backups, so I've found that the "back up to disk, and have 3rd party back up the backups" is the best method.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 19, 2008 at 11:55 am
What Matt said. At my last job, (with an outsourcer), I had to know about Legato, Tivoli, ARCserve & Backup Exec agents. And restoring from them can be a right pain.
One of my first tasks at my current job was removing third party agents and having SQL Server do the SQL backups. Worked out well because one of the sysadmins was looking to upgrade BrightStor but couldn't due to an issue around the SQL Agent. No agent, no issue.
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply