SQL server 2000 - scheduled database backup will not run

[purefreshair]

Hello,
I have an instance where a background service runs on a SQL 2000 server. It has Windows 2003 server standard edition as the OS. The service is part of an app that polls data from another source and stores that data on the SQL database at selected times through out the day.
I have a database integrity check and backup scheduled to run on Saturday mornings. The backup fails to process since the polling service is still running in the background and therefore, still sees an active connection to the database.
Is there any way that I can write and schedule some kind of code/trigger etc. that would stop the service at a scheduled time prior to the backup and then start it at a time after the backup completes?

 

[mrdenny]

If you are using the BACKUP DATABASE command you can backup the database while users are connected to the database. There isn't any reason to stop the service to backup the database.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[purefreshair]

Hello Mrdenny,

My scheduled integrity check and backup did not run because the error reports state that the "Database needs to be in single user mode".
According to Microsoft's site, having the "attempt to repair any minor problems" checked can cause this. I did have it checked so I have de-selected it. However their site states that this issue relates to SQL server 7.0, so if that's the case, why do I get this under SQL server 2000?

 

[SQLBill]

As MrDenny asked, are you using SQL Server's BACKUP DATABASE commands. It sounds like you aren't...I would guess that you might be using some software that is trying to backup the actual files (.mdf and .ldf). Those must be 'offline' to be properly backed up (as files). Using the BACKUP DATABASE command will backup the database even if it is in use. Also, which database are you trying to back up? It is a user database and not a system database, correct?

-SQLBill

Posting advice: FAQ481-4875

 

[purefreshair]

SQLBill,
I am merely setting up a maintenance schedule using the feature/wizard inside Enterprise Manager as part of SQL Server 2000. Yes; this is a user database, not a system database.

 

[mrdenny]

For SQL to repair an issue the database needs to be in single user mode. If you are getting those problems you'll need to run DBCC CHECKDB against the database and get the issue resolved before the problem becomes larger.

In the maintaince plan do you have the option for "Perform these tests before backup up the database or transaction log" checked on the Itegrity tab? If so that backup won't happen until the Integrity is successful.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[purefreshair]

So, to place a user database into single user mode, is it just a matter then of conecting to the database using query analyzer and issuing the "alter database db-name set SINGLE_USER" command?

 

[mrdenny]

Yep. You will need to kill any spids which are using the database before you can issue the ALTER DATABASE Command.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com