0

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?

Peter Hahndorf
  • 13,763
  • 3
  • 37
  • 58

2 Answers2

4

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.

mrdenny
  • 27,074
  • 4
  • 40
  • 68
  • As the backup files always work at the second attempt and most often at the first, they can not be corrupt. I experienced the exact same behavior on a full Windows Server 2008 R2, so this has nothing to do with Server Core. – Peter Hahndorf May 15 '12 at 06:08
  • 1
    I wouldn't expect it to be a problem with server core. I would expect that you've got a hard drive starting to fail, or a RAID controller or HBA with a problem. There shouldn't be any errors when restoring a database. If there is, there's a bigger problem that needs to be addressed. – mrdenny May 16 '12 at 20:13
  • +1 - I'm with Denny. Check out http://support.microsoft.com/kb/2015753 on the error. Severity 1 means a VLF header is corrupted. That means you have corruption issues. If it's not deterministic, and you get corruption errors, you most likely have an issue in your IO stack. – Anon246 May 16 '12 at 20:26
  • Keep in mind that the hard disks are virtual and are created at the beginning of each setup procedure. Using Virtual Box the database with fulltext has the problem 95% of the time. However when running on Hyper-V a few times the problem did not occur. – Peter Hahndorf May 17 '12 at 11:33
2

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...

Peter Hahndorf
  • 13,763
  • 3
  • 37
  • 58
  • Thanks, you saved me - I got the same error when restoring a backup to another SQL server which did not have this database at all. At first run I got this error, and then I just restored again and it worked just fine, all my data and tables are back. – JustAMartin Dec 11 '12 at 12:20