SQL Services and backups

[bowfonz]

Hello,
I'm trying to do flat file backup of a SQL database. I'm using the NETSVC command to stop the MSSQLSERVER service before the backup and again after the backup to start the service which is working great. But I still get messages like this:
"Unable to open file. (FILE=\\SERVERNAME\D$\MSSQL7\Data\filename.mdf, EC=SHARING VIOLATION)"

There are 14 such files that produce this error in the log. All mdf and ldf files.

My question is, are there more services that I need to stop? Is what I'm trying to do even possible?

Any help will be appreciated.
TIA
-Tom

 

[JayKusch]

Why are you stopping the service in the first place? Most backup apps do not require the stopping of services though having not heard of NETSVC, I may be off base on this one.

Thanks

J. Kusch

 

[bowfonz]

To backup normally, a SQL agent needs to be installed on the SQL server. There is not one in this case. So I was attempting to stop the SQL service and just backup the files like normal data.

NETSVC is just a utility to stop services on a remote machine. It comes in the Resource Kit.

 

[JCDugas]

Try

net stop MSSQLServer

 

[bowfonz]

I think the point is being missed here.
I'm using NETSVC because I'm stopping the service from another server on the network and that is working fine. I say again, I am not having a problem stopping and starting services.

The point is that even after stopping the MSSQLSERVER service, I get sharing violation errors in the backup log as stated in my earlier post. I was wondering if anyone knew why I would still be getting those errors (i.e. other services that also need to be stopped prior to backup).

 

[JayKusch]

Have you verified that the service is indeed being stopped by your remote call? Run just the stop command and see if you can copy one of the smaller MDF/LDFs on the target server to a temp dir on that same server.

Thanks

J. Kusch

 

[SQLBill]

You don't say how you are doing the backup and that could be the problem. Some backup programs need the SQL Services running and will start them. For example, Backup Exec - when you use the Agent for SQL Server, it will start the services if they are stopped.

Rule of thumb- if you use BACKUP DATABASE commands, the service needs to be running.
if you are 'copying' the datafiles (.mdf/.ldf), then the services can be stopped.

-SQLBill

Posting advice: FAQ481-4875

 

[bowfonz]

if you are 'copying' the datafiles (.mdf/.ldf), then the services can be stopped.

Now we're getting somewhere, this is what I am doing. You said "services", so there's more than one? What are the services that need to be stopped?

 

[SQLBill]

Disclaimer: I use SQL Server 2000.

There is the MSSQLSERVER service and the SQLSERVERAGENT service. Each of those services is duplicated for each named instance (ie. MSSQLSERVER/myinstance).

But if you are just copying the actual files (.mdf and .ldf) there may be an easier way. You can detach the database (sp_detach_db), copy the files, and then attach the database (sp_attach_db). In this case the services don't need to be stopped.

-SQLBill