SQLTeam.com | Weblogs | Forums

SQL Express 2005 Backup failing - don't know why?


#1

Hi, I am using a .bat file to initiate a .sql file which backs up our database. It is in the Task Scheduler to run every single day, however I've just noticed that it seems to skip days out randomly and I can't find out why. The Task Scheduler seems to run every day, but there isn't a new backup file every day.

The database polls a different system every half hour and updates the database if there are new items in the other system. I was thinking that this updating was getting in the way, but the evidence doesn't seem to back it up (the backup runs at 8pm and on one of the failed days there was a download at 7:59pm so I thought that makes sense. But then another failed day there was no downloads an hour either side of 8pm)

Does anyone have any suggestions please? Here is my backup code..

Blockquote
DECLARE @Odyssey NVARCHAR(4000)
SET @MyDatabase = 'C:\Program Files (x86)\MyDatabase\Backup\MyDatabase'+REPLACE(REPLACE(CONVERT(varchar(100), GETDATE(), 100),':',''),' ','')+'.bak'
BACKUP DATABASE [MyDatabase] TO DISK = @MyDatabase WITH NOFORMAT, INIT, NAME = N'MyDatabase-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO


#2

Are you getting an error? If so post it. BTW NOREWIND and NOUNLOAD will be ignored unless you are backing up to tape.


#3

Hi Joseph, thanks for the reply. No error (the task seems to run hidden) so any error will also be hidden.

I think I may have cracked it though (last nights backup ran fine so fingers crossed). The downloads to the database happen every 20 mins during the day so 7:40, 8:00, 8:20 etc.. and the backup was setup run to 8:00.

My new thought was that at 8pm, even though there may have been no 'updates' to the DB, the mere fact of opening the DB in order to attempt an update (if needed) was enough to stop the backup. It seems 2 or 3 days a week, we were getting lucky and the backup was running before the DB was opening. I have now set the backup to be 8:10

I may be totally wrong and it fails tomorrow. We are fortunate in that we only need to update the DB every 20 mins but I wonder how do people backup DBs where it is getting updated every few seconds?

Thanks again for the reply.


#4

SQL Backup works when a DB is being updated.

As SQLExpress normally runs on Workstations or Laptops, I suspect the most likely cause of your missing backups is the machine being switched off.

Going forward, I would look at using Ola Hallengren's maintenance solution as it produces a log. (If there is no log then the backup did not start.)
Start by going to the 'How do I get started with the SQL Server Maintenance Solution on SQL Server Express?' section of:

https://ola.hallengren.com/frequently-asked-questions.html