Backup fail on msllockdb & pwe files

We use Arcserve for our backup software. Everynight, we get errors that
msllockdb.mdf, msllockdb_ldf, pwe.mdf, & pwe_log.ldf cannot be accessed
because they are being used by another process. Does anyone know what is
causing this and how to correct it?
--
Thanks!You are obviously not using the SQL Server plugin for Arcserver. It is
trying to copy the files directly and that is always a no no for SQL Server.
You need to invoke the SQL Server backup command thru Arcserves SQL Server
specific extensions.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Saucer Man" <saucerman@.nospam.com> wrote in message
news:4742f3fc$0$27025$cc2e38e6@.news.uslec.net...
> We use Arcserve for our backup software. Everynight, we get errors that
> msllockdb.mdf, msllockdb_ldf, pwe.mdf, & pwe_log.ldf cannot be accessed
> because they are being used by another process. Does anyone know what is
> causing this and how to correct it?
> --
> Thanks!
>|||Thanks. I will look into this.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:O0dQXa4KIHA.1184@.TK2MSFTNGP04.phx.gbl...
> You are obviously not using the SQL Server plugin for Arcserver. It is
> trying to copy the files directly and that is always a no no for SQL
> Server. You need to invoke the SQL Server backup command thru Arcserves
> SQL Server specific extensions.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Saucer Man" <saucerman@.nospam.com> wrote in message
> news:4742f3fc$0$27025$cc2e38e6@.news.uslec.net...
>> We use Arcserve for our backup software. Everynight, we get errors that
>> msllockdb.mdf, msllockdb_ldf, pwe.mdf, & pwe_log.ldf cannot be accessed
>> because they are being used by another process. Does anyone know what is
>> causing this and how to correct it?
>> --
>> Thanks!
>

Backup db files

I am currently using Veritas Netbackup to backup the filesystem on my SQL
server.
Currently, a whole host of .mdf and .ldf files in the <\Program
Files\Microsoft SQL Server\MSSQL\Data> directory are skipped by the backup
software because they are in use by another process.
Is there any way to perform a flatfile backup of MSSQL database without
using any software agents? Or must the db be offlined each time in order to
free up those .mdf/.ldf files to be backed up?
Thanks!
"Merc76" <Merc76@.discussions.microsoft.com> wrote in message
news:B01DF591-88C1-4C33-854C-8E151E180018@.microsoft.com...
>I am currently using Veritas Netbackup to backup the filesystem on my SQL
> server.
> Currently, a whole host of .mdf and .ldf files in the <\Program
> Files\Microsoft SQL Server\MSSQL\Data> directory are skipped by the
> backup
> software because they are in use by another process.
> Is there any way to perform a flatfile backup of MSSQL database without
> using any software agents? Or must the db be offlined each time in order
> to
> free up those .mdf/.ldf files to be backed up?
> Thanks!
You need to use SQL Server's own native backups to backup the database while
its online. That's something you should definitely do anyway because you
have many more options to control just how the files get backed up and
restored. Read the backup topics in SQL Server Books Online.
Veritas has an agent module that allows you to manage native SQL Server
backups through their software.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||Hi
You cannot copy files while a database is in use, however you will be able
to issue BACKUP DATABASE command
"Merc76" <Merc76@.discussions.microsoft.com> wrote in message
news:B01DF591-88C1-4C33-854C-8E151E180018@.microsoft.com...
>I am currently using Veritas Netbackup to backup the filesystem on my SQL
> server.
> Currently, a whole host of .mdf and .ldf files in the <\Program
> Files\Microsoft SQL Server\MSSQL\Data> directory are skipped by the
> backup
> software because they are in use by another process.
> Is there any way to perform a flatfile backup of MSSQL database without
> using any software agents? Or must the db be offlined each time in order
> to
> free up those .mdf/.ldf files to be backed up?
> Thanks!

Backup db files

I am currently using Veritas Netbackup to backup the filesystem on my SQL
server.
Currently, a whole host of .mdf and .ldf files in the <\Program
Files\Microsoft SQL Server\MSSQL\Data> directory are skipped by the backup
software because they are in use by another process.
Is there any way to perform a flatfile backup of MSSQL database without
using any software agents? Or must the db be offlined each time in order to
free up those .mdf/.ldf files to be backed up?
Thanks!"Merc76" <Merc76@.discussions.microsoft.com> wrote in message
news:B01DF591-88C1-4C33-854C-8E151E180018@.microsoft.com...
>I am currently using Veritas Netbackup to backup the filesystem on my SQL
> server.
> Currently, a whole host of .mdf and .ldf files in the <\Program
> Files\Microsoft SQL Server\MSSQL\Data> directory are skipped by the
> backup
> software because they are in use by another process.
> Is there any way to perform a flatfile backup of MSSQL database without
> using any software agents? Or must the db be offlined each time in order
> to
> free up those .mdf/.ldf files to be backed up?
> Thanks!
You need to use SQL Server's own native backups to backup the database while
its online. That's something you should definitely do anyway because you
have many more options to control just how the files get backed up and
restored. Read the backup topics in SQL Server Books Online.
Veritas has an agent module that allows you to manage native SQL Server
backups through their software.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Hi
You cannot copy files while a database is in use, however you will be able
to issue BACKUP DATABASE command
"Merc76" <Merc76@.discussions.microsoft.com> wrote in message
news:B01DF591-88C1-4C33-854C-8E151E180018@.microsoft.com...
>I am currently using Veritas Netbackup to backup the filesystem on my SQL
> server.
> Currently, a whole host of .mdf and .ldf files in the <\Program
> Files\Microsoft SQL Server\MSSQL\Data> directory are skipped by the
> backup
> software because they are in use by another process.
> Is there any way to perform a flatfile backup of MSSQL database without
> using any software agents? Or must the db be offlined each time in order
> to
> free up those .mdf/.ldf files to be backed up?
> Thanks!

Backup db files

I am currently using Veritas Netbackup to backup the filesystem on my SQL
server.
Currently, a whole host of .mdf and .ldf files in the <\Program
Files\Microsoft SQL Server\MSSQL\Data> directory are skipped by the backup
software because they are in use by another process.
Is there any way to perform a flatfile backup of MSSQL database without
using any software agents? Or must the db be offlined each time in order to
free up those .mdf/.ldf files to be backed up?
Thanks!"Merc76" <Merc76@.discussions.microsoft.com> wrote in message
news:B01DF591-88C1-4C33-854C-8E151E180018@.microsoft.com...
>I am currently using Veritas Netbackup to backup the filesystem on my SQL
> server.
> Currently, a whole host of .mdf and .ldf files in the <\Program
> Files\Microsoft SQL Server\MSSQL\Data> directory are skipped by the
> backup
> software because they are in use by another process.
> Is there any way to perform a flatfile backup of MSSQL database without
> using any software agents? Or must the db be offlined each time in order
> to
> free up those .mdf/.ldf files to be backed up?
> Thanks!
You need to use SQL Server's own native backups to backup the database while
its online. That's something you should definitely do anyway because you
have many more options to control just how the files get backed up and
restored. Read the backup topics in SQL Server Books Online.
Veritas has an agent module that allows you to manage native SQL Server
backups through their software.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Hi
You cannot copy files while a database is in use, however you will be able
to issue BACKUP DATABASE command
"Merc76" <Merc76@.discussions.microsoft.com> wrote in message
news:B01DF591-88C1-4C33-854C-8E151E180018@.microsoft.com...
>I am currently using Veritas Netbackup to backup the filesystem on my SQL
> server.
> Currently, a whole host of .mdf and .ldf files in the <\Program
> Files\Microsoft SQL Server\MSSQL\Data> directory are skipped by the
> backup
> software because they are in use by another process.
> Is there any way to perform a flatfile backup of MSSQL database without
> using any software agents? Or must the db be offlined each time in order
> to
> free up those .mdf/.ldf files to be backed up?
> Thanks!

Backup database creates smaller files....

...but from a 3+GB MDF file I obtain a .BAK of only 56 MB!!!!!!!!
Is it possible or am I crazy?
Fabri
('N patri campa centu figghi e centu figghi nun s'a firunu a campari a
'n patri.)Are you doing a differential backup and overwriting the old file?
"Fabri" <no@.sp.am> wrote in message news:d4o6ul$2cp$1@.news.ngi.it...
> ...but from a 3+GB MDF file I obtain a .BAK of only 56 MB!!!!!!!!
> Is it possible or am I crazy?
> --
> Fabri
> ('N patri campa centu figghi e centu figghi nun s'a firunu a campari a 'n
> patri.)|||It is possible and SQL Server backup doesn't backup unused storage. But also
make sure you are doing
the right type of backup etc.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Fabri" <no@.sp.am> wrote in message news:d4o6ul$2cp$1@.news.ngi.it...
> ...but from a 3+GB MDF file I obtain a .BAK of only 56 MB!!!!!!!!
> Is it possible or am I crazy?
> --
> Fabri
> ('N patri campa centu figghi e centu figghi nun s'a firunu a campari a 'n patri.)[
/color]

Backup Contains .mdf .ldf and .ndf dont want .ndf

Hello,
I have a database(.mdf and .ldf) with a large table that is attached to
its own filegroup(.ndf). I have created a full database backup and used
Restore FilelistOnly From Disk = 'C:\MSSQL\Data\Backup\Contact.bak'
I see 3 files
contact.mdf
contact.ldf
cont.ndf
I restored just the .mdf and .ldf to another database and when I look at
the properties and goto file groups it shows that the filegroup is still
there too. How is the ndf a part of this database when I never restored
the .ndf
I wan to restore only .mdf and .ldf and leave the .ndf behind is this
possible and how. Thanks for all HELP!
*** Sent via Developersdex http://www.examnotes.net ***First, if you don't supply a "WITH MOVE" clause for the .ndf file, it will
use the original file path from where the backup was created. Second, if
you do a full database restore, you must restore all the files. You cannot
omit any files. You can delete the file/filegroup after the restore is
completed.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Sean John" <sj@.aol.com> wrote in message
news:eoNsFaTHGHA.2064@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I have a database(.mdf and .ldf) with a large table that is attached to
> its own filegroup(.ndf). I have created a full database backup and used
> Restore FilelistOnly From Disk = 'C:\MSSQL\Data\Backup\Contact.bak'
> I see 3 files
> contact.mdf
> contact.ldf
> cont.ndf
> I restored just the .mdf and .ldf to another database and when I look at
> the properties and goto file groups it shows that the filegroup is still
> there too. How is the ndf a part of this database when I never restored
> the .ndf
> I wan to restore only .mdf and .ldf and leave the .ndf behind is this
> possible and how. Thanks for all HELP!
>
>
> *** Sent via Developersdex http://www.examnotes.net ***|||When trying to delete file it says that
The file 'Contact'cannot be removed because it is not empty.
Is there a way to backup only .mdf or .ldf or restore just .mdf or .ldf
and no .ndf?
*** Sent via Developersdex http://www.examnotes.net ***|||Have a look in BOL under DBCC SHRINKFILE specifically the EMPTYFILE option
and then ALTER DATABASE specifically the REMOVE FILE option.
Andrew J. Kelly SQL MVP
"Sean John" <sj@.aol.com> wrote in message
news:O3$1kXUHGHA.1424@.TK2MSFTNGP12.phx.gbl...
> When trying to delete file it says that
> The file 'Contact'cannot be removed because it is not empty.
> Is there a way to backup only .mdf or .ldf or restore just .mdf or .ldf
> and no .ndf?
>
> *** Sent via Developersdex http://www.examnotes.net ***|||You have to delete all user objects from the file.filegroup. Then folow
Andrew's instructions.
Backups and restores always require a full database to work with. You can
do a filegroup backup, but it must be restored to a full database. The
short answer is no, you cannot transfer part of a database using backup and
restore. The tools simply do not work that way.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Sean John" <sj@.aol.com> wrote in message
news:O3$1kXUHGHA.1424@.TK2MSFTNGP12.phx.gbl...
> When trying to delete file it says that
> The file 'Contact'cannot be removed because it is not empty.
> Is there a way to backup only .mdf or .ldf or restore just .mdf or .ldf
> and no .ndf?
>
> *** Sent via Developersdex http://www.examnotes.net ***

backup and restoring

I used backup and restore to upgrade a database from sql 2000 to sql 2005. Is it necessary to create the mdf and ldf on the new server at the time of restore under options or should I copy them to the new server in the data folder? I am new and not quite sure what the log files hold.
Thanksif you have created a backup of a database, you don't need anything but the .bak file.

when you restore from a bak file, the data and log files will be created automatically by the server.|||Edit: You don't need to create them, the restore does.

How backup works:
When SQL Server backs up a database, it backs up the data file first. During the backup of the data file, no changes are written to the data file, only to the log. When the bacup of the data file is complete, the changes written to the log file is backed up. In other words, backup of a SQL Server restores to the point of time when the backup finished, not started. Furthermore, your log file will be used to keep track of transactions afer the backup have restored, so you will need the file.|||Ok. If I don't specify in options at the time of restore, I can't find where the the mdf and ldf files were created. And are these new mdf and ldf files or do they hold the same data as the database before they were created on the new server.
thanks|||this will tell you where they are:

exec sp_helpfile

Backup and restore of mdf database

Hi,

I've got a following problem: I have a VB 2005 application that uses SQL Server 2005 Express as DB engine. This application has it's own database stored in mdf file, e.g. invoices.mdf with corresponding log file.

I'd like to give users opportunity to backup and restore this DB file from this application (there won't be a system administrator, only end-users). Unfortunatelly after I connect to this database (using user instance of SQL Server), I'm unable to read or copy it, despite the fact, that I close all connections. And I'm sure that all connections are closed.

Database files become accessible for reading after I close the whole application.

I've tried to detach this database using sp_detach_db prcedure, but it can't be done with database I'm connected to.

I will be grateful for any help.

Tomasz

hi Tomasz,

obviously at least 1 connection is still open referencing the database you are dealing with..

try changing the database context to the master system database (that can be done in .Net via the workingConnection.ChangeDatabase("master") statement...)

a workaround to "orphaned" connections could even be an alter database statement to close all pending connections, like

USE master;

GO

ALTER DATABASE dbName

SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

GO-- and the counterpart is

ALTER DATABASE dbName

SET MULTI_USER WITH ROLLBACK IMMEDIATE;

but I'd prefer to find and close all pending connections...

regards

|||

We just had a couple threads about this topic in the forum over the last week. I've summarized how to use SMO to handle Backup and Restore of User Instance database in my blog. Backup should work with connection open but restore is not so forgiving.

Mike

|||

Hi,

thanks for this advice, I will try today to implement backup/restore approach according to the blog.

I'm 100% sure that I close all connections to the DB. I've made a very simple application which just connects to the database and immediatly closes this one an single connection. After that mdf file remains locked until I close the application. It seems, that there is some "stealth" connection, which I can't close directly. You can replicate this behaviour by creating a new application which on start does:

Dim lCon As New SqlClient.SqlConnection(sConnStr)

lCon.Open()

lCon.Close()

lCon.Dispose() 'even that won't help

And then try to copy mdf file to other location while this application is still running.

greetings

Tomasz

|||

Hi,

I've got the same problem as Tomasz. I read all that was written and I was wondering if there isn't a command capable of closing all connection to the database file (.mdf). I just need to upload it to a remote location not restore it.

Thanks in advance

backup 300G DB error

I have a problem with backing up 300G DB. It is SQL Server 2000, the
MDF(50MBs) is in C drive, one NDF(33,000MBs) and one LDF (21G) are in F
drive. The index files are in F drive too. The backup mode is "Full".
When I tried to back it up in Enterprise Manager( right click on
database -> All tasks ->backup database), after 10 minutes of running, I
got the following error:
---
Microsoft SQL-DMO (ODBC SQL State: 42000)
Error 601: Could not continue scan with NOLOCK due to data movement.
---
Then I hit 'OK' and this one pops up
---
Microsoft SQL-DMO (ODBC SQL State:HY000)
Attempt to fetch logical page (1:5439) in database 'msdb' belongs to object
'sysdtspackages', not to object 'backupmediafamily'
---
Can anyone help me out?
Thanks a million.Hi
Look like you have corruption in MSDB. Run DBCC CHECKDB on the database.
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jluo" <jluost1@.yahoo.com> wrote in message
news:41C9D1B9.8060802@.yahoo.com...
> I have a problem with backing up 300G DB. It is SQL Server 2000, the
> MDF(50MBs) is in C drive, one NDF(33,000MBs) and one LDF (21G) are in F
> drive. The index files are in F drive too. The backup mode is "Full".
> When I tried to back it up in Enterprise Manager( right click on
> database -> All tasks ->backup database), after 10 minutes of running, I
> got the following error:
> ---
> Microsoft SQL-DMO (ODBC SQL State: 42000)
> Error 601: Could not continue scan with NOLOCK due to data movement.
> ---
> Then I hit 'OK' and this one pops up
> ---
> Microsoft SQL-DMO (ODBC SQL State:HY000)
> Attempt to fetch logical page (1:5439) in database 'msdb' belongs to
object
> 'sysdtspackages', not to object 'backupmediafamily'
> ---
> Can anyone help me out?
> Thanks a million.
>