Home / server / Questions / 388477
Accepted
Peter Hahndorf
Asked: 2012-05-13 04:00:41 +0800 CST

Fresh SQL Server DB restore works most times, but fails sometimes

  • 772

I'm working on an automated setup of a Windows Server 2008 R2 Core with IIS and SQL Express

I'm starting with a fresh (just installed) OS in a VM (virtualBox in this test case)

My scripts then create users, install dot.NET 3.51 and 4, IIS and SQL-Server 2008 R2 Express. They also set up web sites and services, all is fine.

At some point I need to restore six SQL-Server databases, .bak files with just one full backup in them.

I'm using something like this:

RESTORE DATABASE FooBar FROM  DISK = N'E:\temp\db\FooBar.bak' WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 10

By now to tune my scripts I've done the whole setup procedure at least 40 times, most of the time the restore works fine, but sometimes one of more restore operations fail, even though the SQL-Server setup and the backup files are always the same.

There is one database which has FullText catalogs and that one fails more often than the others.

When I remotely connect to the server in Management Studio and run the same SQL, it usually still fails, however when I do the restore manually in SSMS it usually works.

What baffles me is that even though the sql-environment is always the same, the restores sometimes work and sometimes they don't.

Here's the actual error message:

100 percent processed.
Processed 11128 pages for database 'FooBar', file 'FooBar' on file 1.
Processed 3 pages for database 'FooBar', file 'FooBar_log' on file 1.
Msg 9004, Level 21, State 1, Server LAB45, Line 1
An error occurred while processing the log for database 'FooBar'.  If possible,
restore from backup. If a backup is not available, 
it might be necessary to rebuild the log.
Msg 3013, Level 16, State 1, Server LAB45, Line 1
RESTORE DATABASE is terminating abnormally.

Any ideas what the problem could be?

sql-server backup-restoration

2 Answers

  1. The only reason that you'd get an error like that is if the data in the backup is corrupt, or if the transaction log file is being corrupted while it's being written to the disk.

    FYI: SQL Server 2008 R2 isn't supported on Windows 2008 R2 core. SQL Server 2012 is the first edition which is supported on Windows 2008 R2 core.

    • 4
  2. Best Answer

    I haven't figured out why this happens, but I now have a workaround in place. I just run my restore script twice. The second time it always works!!!

    I added some T-SQL code to the restore statements to only restore it if it failed before or has not been restored at all. This way, only the databases where restoring failed, are restored again.

    Weird...

    • 2