asked
Backup timing out
I execute the following command from within SSMSS 2005 Express and within a few seconds get the dreaded "timeout expired or server not responding" message, and stats showing '34 percent processed'. I haven't altered any settings (as far as I know) so what's the problem ? BACKUP DATABASE [ITDatabase] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\test1.bak' WITH FORMAT, INIT, MEDIANAME = N'test', NAME = N'ITDatabase-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 1 GO
@Fatherjack has some excellent questions... One thing to try is to right click in the query window and select "Query Option". Change "Execution time-out" to 0 (if it is not already)
just questions I'm afraid - How big is the database? How big are the backups usually? Is there space on the hard drive? Have you tried another location for the backup? Do other databases backup successfully? Is the server very busy doing other stuff?
If you're going straight to disk, I wouldn't bother with NOREWIND and NOUNLOAD. These are tape specific commands and just clutter the landscape of your backup. I agree with Jonlee. It sounds like you might be setting a timeout on your connection. Don't do that. I'd check the settings for your Management Studio environment. Look at Query Execution to validate that the timeout is set to 0 and that the lock timeout in Sql Server, Advanced is set to -1. Check the logs to see if there are other errors being reported as part of this process.
Ah, thank you, Jonlee ! For some strange reason the query execution time-out was set to 5 seconds. Changed that to 0 and all was fine. Thanks to you other guys for your suggestions too. If ever you're in the Half Moon in Wilstone ( just outside Tring, UK ) I'll buy you a beer - just ask for Neil ...
If that works for you, you can select Jonlee's answer as the accepted one.
@sensetech, click the "thumbs-up" beside Jonlee's answer so that he gets the credit for the correct solution and other people finding this question will quickly get to the solution. Thanks for the drink offer, not sure how many people you can accommodate ... ! You never know!
