MS SQL Server - SQL Server backup issue

Author

SQL Server backup issue

Jimmy

2005-04-13, 7:23 am

i using BrightStor ARCserve Backup software to backup the SQL data file and
transaction log file (*.mdf and *.ldf)..

recently i received the error message as following

Unable to open file. (FILE=C:\Program Files\Microsoft SQL
Server\MSSQL\Data\te
mplog.ldf, EC=The process cannot access the file because
it is being used by )

W3404 04/11/2005 09:01:57 PM 409 1 Unable to open
file. (FILE=C:\Program Files\Microsoft SQL Server\MSSQL\Data\te
mpdb.mdf,
EC=The process cannot access the file because it is being used by )

W3404 04/11/2005 09:01:57 PM 409 1 Unable to open
file. (FILE=C:\Program Files\Microsoft SQL Server\MSSQL\Data\pu
bs_log.ldf,
EC=The process cannot access the file because it is being used by )

can someone guide me, how i going to do..

thanks a lot

Jimmy Teo

Mike Epprecht (SQL MVP)

2005-04-13, 7:23 am

Hi

Do not backup .ldf, .mdf, .ndf files directly.
Dump the database and then backup the dump. Backup of the data files
themselves is not psossible while SQL server is running (as it is in use, as
you found out), and data consistency can not be assured.

Lookup BACKUP in SQL Server BOL.

Regards
Mike

"Jimmy" wrote:

> i using BrightStor ARCserve Backup software to backup the SQL data file and
> transaction log file (*.mdf and *.ldf)..
>
> recently i received the error message as following
>
> Unable to open file. (FILE=C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\te
mplog.ldf, EC=The process cannot access the file because
> it is being used by )
>
> W3404 04/11/2005 09:01:57 PM 409 1 Unable to open
> file. (FILE=C:\Program Files\Microsoft SQL Server\MSSQL\Data\te
mpdb.mdf,
> EC=The process cannot access the file because it is being used by )
>
> W3404 04/11/2005 09:01:57 PM 409 1 Unable to open
> file. (FILE=C:\Program Files\Microsoft SQL Server\MSSQL\Data\pu
bs_log.ldf,
> EC=The process cannot access the file because it is being used by )
>
> can someone guide me, how i going to do..
>
> thanks a lot
>
> Jimmy Teo

Jimmy

2005-04-13, 7:23 am

Hi Mike
Thanks for information

you recommend do not back up .ldf,.mdf, .ndf directly, and backup the data
using SQLserver backup utility itself?

Before that, it always successfully back up the data directly using
BrightStor ARCserve Backup software, and SQL server was running , and the log
file (from BrightStor ARCserve) show as following;

Information 04/10/2005 09:02:05 PM BAOF: Allowed
access to open file C:\Program Files\Microsoft SQL
Server\MSSQL\Data\pu
bs_log.ldf
Information 04/10/2005 09:02:05 PM BAOF: Allowed
access to open file C:\Program Files\Microsoft SQL Server\MSSQL\Data\pu
bs.mdf

Information 04/10/2005 09:02:05 PM BAOF: Allowed
access to open file C:\Program Files\Microsoft SQL
Server\MSSQL\Data\no
rthwnd.mdf
Information 04/10/2005 09:02:05 PM BAOF: Allowed
access to open file C:\Program Files\Microsoft SQL
Server\MSSQL\Data\no
rthwnd.ldf
Information 04/10/2005 09:02:05 PM BAOF: Allowed
access to open file C:\Program Files\Microsoft SQL
Server\MSSQL\Data\ms
dblog.ldf
Information 04/10/2005 09:02:05 PM BAOF: Allowed
access to open file C:\Program Files\Microsoft SQL
Server\MSSQL\Data\ms
dbdata.mdf
Information 04/10/2005 09:02:05 PM BAOF: Allowed
access to open file C:\Program Files\Microsoft SQL
Server\MSSQL\Data\mo
dellog.ldf
Information 04/10/2005 09:02:05 PM BAOF: Allowed
access to open file C:\Program Files\Microsoft SQL Server\MSSQL\Data\mo
del.mdf
Information 04/10/2005 09:02:05 PM BAOF: Allowed
access to open file C:\Program Files\Microsoft SQL
Server\MSSQL\Data\ma
stlog.ldf

Only this few day unable to back up directly...

Need your help

Thanks & Regards
Jimmy

"Mike Epprecht (SQL MVP)" wrote:
[color=darkred]
> Hi
>
> Do not backup .ldf, .mdf, .ndf files directly.
> Dump the database and then backup the dump. Backup of the data files
> themselves is not psossible while SQL server is running (as it is in use, as
> you found out), and data consistency can not be assured.
>
> Lookup BACKUP in SQL Server BOL.
>
> Regards
> Mike
>
> "Jimmy" wrote:
>

Tibor Karaszi

2005-04-13, 7:23 am

Almost all SQL Server administrators backup SQL Server databases through SQL Server, not through
backing up the files directly. What is someone modifies data in the database between backup of two
files that constitutes a database. Have you tested restore for this?

In case you insist to backup open files, you need to check in your backup software. This is not in
SQL Server's control. There's probably some setting there to allow you to backup open files. But
again, not recommended.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/

"Jimmy" <Jimmy@discussions.microsoft.com> wrote in message
news:01451093-677F-42FD-8912- 9EB0B7882311@microso
ft.com...[color=darkred]
> Hi Mike
> Thanks for information
>
> you recommend do not back up .ldf,.mdf, .ndf directly, and backup the data
> using SQLserver backup utility itself?
>
> Before that, it always successfully back up the data directly using
> BrightStor ARCserve Backup software, and SQL server was running , and the log
> file (from BrightStor ARCserve) show as following;
>
> Information 04/10/2005 09:02:05 PM BAOF: Allowed
> access to open file C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\pu
bs_log.ldf
> Information 04/10/2005 09:02:05 PM BAOF: Allowed
> access to open file C:\Program Files\Microsoft SQL Server\MSSQL\Data\pu
bs.mdf
>
> Information 04/10/2005 09:02:05 PM BAOF: Allowed
> access to open file C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\no
rthwnd.mdf
> Information 04/10/2005 09:02:05 PM BAOF: Allowed
> access to open file C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\no
rthwnd.ldf
> Information 04/10/2005 09:02:05 PM BAOF: Allowed
> access to open file C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\ms
dblog.ldf
> Information 04/10/2005 09:02:05 PM BAOF: Allowed
> access to open file C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\ms
dbdata.mdf
> Information 04/10/2005 09:02:05 PM BAOF: Allowed
> access to open file C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\mo
dellog.ldf
> Information 04/10/2005 09:02:05 PM BAOF: Allowed
> access to open file C:\Program Files\Microsoft SQL Server\MSSQL\Data\mo
del.mdf
> Information 04/10/2005 09:02:05 PM BAOF: Allowed
> access to open file C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\ma
stlog.ldf
>
>
> Only this few day unable to back up directly...
>
>
> Need your help
>
>
> Thanks & Regards
> Jimmy
>
>
> "Mike Epprecht (SQL MVP)" wrote:
>

Dan Guzman

2005-04-13, 7:23 am

You should backup SQL databases directly only if the third part backup
software has a SQL Server agent specifically designed for this purpose.
Many backup utilities allow you to backup open files directly but this must
never be done for SQL Server files. These backups are at best unreliable
and most likely useless. You may very well end up with a corrupt database
after a restore.

Without such a backup agent, your best bet is to backup databases to disk
using SQL BACKUP commands and then archive the backup files to tape using
your backup software.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Jimmy" <Jimmy@discussions.microsoft.com> wrote in message
news:01451093-677F-42FD-8912- 9EB0B7882311@microso
ft.com...[color=darkred]
> Hi Mike
> Thanks for information
>
> you recommend do not back up .ldf,.mdf, .ndf directly, and backup the data
> using SQLserver backup utility itself?
>
> Before that, it always successfully back up the data directly using
> BrightStor ARCserve Backup software, and SQL server was running , and the
> log
> file (from BrightStor ARCserve) show as following;
>
> Information 04/10/2005 09:02:05 PM BAOF: Allowed
> access to open file C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\pu
bs_log.ldf
> Information 04/10/2005 09:02:05 PM BAOF: Allowed
> access to open file C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\pu
bs.mdf
>
> Information 04/10/2005 09:02:05 PM BAOF: Allowed
> access to open file C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\no
rthwnd.mdf
> Information 04/10/2005 09:02:05 PM BAOF: Allowed
> access to open file C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\no
rthwnd.ldf
> Information 04/10/2005 09:02:05 PM BAOF: Allowed
> access to open file C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\ms
dblog.ldf
> Information 04/10/2005 09:02:05 PM BAOF: Allowed
> access to open file C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\ms
dbdata.mdf
> Information 04/10/2005 09:02:05 PM BAOF: Allowed
> access to open file C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\mo
dellog.ldf
> Information 04/10/2005 09:02:05 PM BAOF: Allowed
> access to open file C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\mo
del.mdf
> Information 04/10/2005 09:02:05 PM BAOF: Allowed
> access to open file C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\ma
stlog.ldf
>
>
> Only this few day unable to back up directly...
>
>
> Need your help
>
>
> Thanks & Regards
> Jimmy
>
>
> "Mike Epprecht (SQL MVP)" wrote:
>

Sponsored Links