how to force backup?

Question

how to force backup?

bigdude

SSC Veteran

Points: 268
More actions
November 17, 2004 at 10:35 am

#91529

hi
i have an automated maintenance plan for a sql 2000 server
for some databases the backup doesn't succeed because other users are using the database
presumable because it can't be put into single user mode
this backup is critical
how can i force the backup?
thanks in advance
cheers

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

NeilA-162437 SSC Enthusiast Points: 188 More actions · Answer 1

If the users are not supposed to be in the database when the backups are to be performed you can kill their spids quite easily.

There are lots of example scripts to show how to do this safely.

hth

Neil.

Ray Mond SSCertifiable Points: 7159 More actions · Answer 2

SQL Server does not have to be in single user mode to perform backups. However, there are certain functions that might block a backup, such as CHECKALLOC, bulk copy & SELECT INTO.

SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.

SQLBill SSC Guru Points: 51440 More actions · Answer 3

This is why you shouldn't rely on Maintenance plans. Script your own backup jobs.

-SQLBill

bigdude SSC Veteran Points: 268 More actions · Answer 4

hello!

thanks for all the replies

as you can probably guess, i'm new to sql server

i'll work on my own plan with scripts

thanks!

sswords SSCrazy Eights Points: 8207 More actions · Answer 5

When you created the Maintenance plan, if you had the 'Attempt to repair any minor problems' box checked on the Integrity Checks tab AND you have set the option to 'Perform these checks before backing up the Database or Transaction Log', the job will always fail if there is more than one user in the database. This is because running the integrity checks with the repair option requires that the database to be in single user mode. Uncheck this box and monitor the integrity checks anb backup jobs for errors. The backups should now run normally. Hope this helps.

My hovercraft is full of eels.

bigdude SSC Veteran Points: 268 More actions · Answer 6

thanks!!

i did have both boxes checked...

i've unchecked 'these checks before backing up the Database or Transaction Log' and i'll see if it works better

thanks so much

cheers!