May 24, 2012 at 8:44 am
I am relatively new to both SQL and Netbackup. I am having an issue with the backup of one of my SQL servers. This server is running Windows 2003, SQL 2005, and Netbackup 7.0.1. The problem is that I am backing up the Server, meaning all drives. It appears that the backup of file on one of the network drives is not be completed. There are several files that give the error "The process cannot access the fie because it is being used by another process". The files in questions are sql database files. The error logs are below. I am actually backing up the SQL database using Netbackup SQL scripts directly to tape, so I am wondering if I shpuld be concerned. Any assistance would be appreciated.
5/23/2012 9:26:27 PM - Info bpbrm(pid=6728) from client Abcd1234: TRV - object not found for file system backup: I:
5/23/2012 9:26:58 PM - Warning bpbrm(pid=6728) from client Abcd1234: WRN - can't open file: E:\Microsoft SQL Server\MSSQL.4\MSSQL\Data\DES_20111209.ldf (WIN32 32: The process cannot access the file because it is being used by another process. )
5/23/2012 9:26:58 PM - Warning bpbrm(pid=6728) from client Abcd1234: WRN - can't open file: E:\Microsoft SQL Server\MSSQL.4\MSSQL\Data\DES_20111209.mdf (WIN32 32: The process cannot access the file because it is being used by another process. )
5/23/2012 9:26:58 PM - Warning bpbrm(pid=6728) from client Abcd1234: WRN - can't open file: E:\Microsoft SQL Server\MSSQL.4\MSSQL\Data\master.mdf (WIN32 32: The process cannot access the file because it is being used by another process. )
5/23/2012 9:26:58 PM - Warning bpbrm(pid=6728) from client Abcd1234: WRN - can't open file: E:\Microsoft SQL Server\MSSQL.4\MSSQL\Data\mastlog.ldf (WIN32 32: The process cannot access the file because it is being used by another process. )
5/23/2012 9:26:58 PM - Warning bpbrm(pid=6728) from client Abcd1234: WRN - can't open file: E:\Microsoft SQL Server\MSSQL.4\MSSQL\Data\model.mdf (WIN32 32: The process cannot access the file because it is being used by another process. )
5/23/2012 9:26:58 PM - Warning bpbrm(pid=6728) from client Abcd1234: WRN - can't open file: E:\Microsoft SQL Server\MSSQL.4\MSSQL\Data\modellog.ldf (WIN32 32: The process cannot access the file because it is being used by another process. )
5/23/2012 9:26:58 PM - Warning bpbrm(pid=6728) from client Abcd1234: WRN - can't open file: E:\Microsoft SQL Server\MSSQL.4\MSSQL\Data\msdbdata.mdf (WIN32 32: The process cannot access the file because it is being used by another process. )
5/23/2012 9:26:58 PM - Warning bpbrm(pid=6728) from client Abcd1234: WRN - can't open file: E:\Microsoft SQL Server\MSSQL.4\MSSQL\Data\msdblog.ldf (WIN32 32: The process cannot access the file because it is being used by another process. )
5/23/2012 9:26:59 PM - Warning bpbrm(pid=6728) from client Abcd1234: WRN - can't open file: E:\Microsoft SQL Server\MSSQL.4\MSSQL\Data\tempdb.mdf (WIN32 32: The process cannot access the file because it is being used by another process. )
5/23/2012 9:26:59 PM - Warning bpbrm(pid=6728) from client Abcd1234: WRN - can't open file: E:\Microsoft SQL Server\MSSQL.4\MSSQL\Data\templog.ldf (WIN32 32: The process cannot access the file because it is being used by another process. )
5/23/2012 9:26:59 PM - Info bpbrm(pid=6728) from client Abcd1234: TRV - last message being suppressed after 10 occurrences
5/24/2012 5:45:11 AM - Info bpbrm(pid=6728) from client Abcd1234: TRV - object not found for file system backup: X:
5/24/2012 5:45:24 AM - end writing; write time: 08:24:42
the requested operation was partially successful(1)
May 24, 2012 at 10:00 am
Doing O/S type backups of database files while SQL Server is running will not work. If you want to use Netbackup to back up your SQL Servers, you need to use the SQL Server agent provided by Netbackup. That agent uses the native SQL Server backup utilities underneath so that backups are transactionally consistent and can be restored correctly.
The SQL Server manages transactional activity using its own memory, data files (.mdf), and log files (.ldf). Just copying the O/S files, even if you could do it, would not give you a backup that could be restored successfully. You might want to look at Books Online to get an overview of SQL Server backup issues.
There are some high-end SAN systems that can do SQL Server backups, or so I've been told, but that doesn't sound like the case here.
May 24, 2012 at 10:18 am
Thanks for your information. Just for my clarification, should that sql database "not" be is use, the O/S backup would include the .mdf, and .ldf, files? I am asking because there are other mdf/ldf files in that were backed up last month.
May 24, 2012 at 10:30 am
If the SQL Server is running, those files will probably be useless in a restore. You can back up SQL Server files when the server is not running, but it's always better to use the native SQL Server backup utilities to back up SQL Server data and the same goes for restores - use the native utilities.
You can set up a "maintenance plan" in the SQL Server itself that can perform backups of databases to flat files on disk and those flat files can be picked up by the native O/S backups and then can be used later to restore SQL Server database. That's how a lot of small shops do it. There are wizards in the SQL Server Management Studio management utility that can help you set those maintenance plans up and schedule them appropriately.
Backups are a pretty complex subject and have a tremendous impact on an organization's abiity to recover critical data in a timely manner. If you could tell us a little bit about your system and how the data is used, the folks here can offer better advise. For instance:
How critical is this data to the business? In the event of a failure, how much data (if any) can they afford to lose or have to re-enter? How long can the data be unavailable? Is any of the data subject to HIPPA or SOX reporting? How much data do you have?
That's my experience, others should feel free to jump in if their milage differs.
May 25, 2012 at 2:21 am
When a database is attached to SQL, the sqlserver.exe locks the files so that it has exclusive access to the files, the only way to backup databases via NetBackup without the SQL Agent plugin in their MDF/NDF/LDF format would be to stop SQL or detach the db's.
Personally I would use the native tools (BACKUP DATABASE, maintenance plans), or something like LiteSpeed from Quest, or Red-Gates equivilant etc to backup your databases to a BAK or TRN file which NetBackup can just backup as part of its file copy tasks.
If you want a good backup solution, take a look at my signature for Ola's scripts, they work a charm.
But as David has already said, backups can be a complex beast. One thing I would say is restore your NetBackup to a seperate server and see if it restores it as it should and nothing is missing and data is consistant. Your in the old saying of "backups are worthless, but restores are priceless"
Also ask the business what your RTO and RPO is so that you can plan the best backup strategy to support your business.
May 25, 2012 at 4:55 am
This is a classic issue for backups and one that leads to severe problems when you try to recover these backups. I detail this issue, along with a few others, in this article[/url]. Shortest possible answer, don't use backup software directly against the MDF/LDF of the database unless you know, because you've tested recover with it, that your backup software is transactionally aware. Otherwise, use SQL Server's BACKUP DATABASE command to backup the database, then you backup the backup.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy