MS SQL Server - Strange Error when restoring database

Author

Strange Error when restoring database

Charles

2006-10-24, 6:30 pm

Okay...I'm stumped. I've installed SqlServer 2005 SP1 on my laptop and am
trying to restore a customer's database from a SQL Server 2000 full database
backup. I've tried through Management Studio GUI and also through a
script...every time I get the following error:

Processed 267584 pages for database 'SOMEBODY', file 'SOMEBODY_Data' on file
1.
Msg 3283, Level 16, State 1, Line 1
The file "SOMEBODY_Data" failed to initialize correctly. Examine the error
logs for more details.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Can anyone help? I'm pulling my hair out over this one. I've tried three
different SQL Server 2000 backups and none work. I've confirmed that they
are valid backups with 2000.

Regards,
Charles

Tibor Karaszi

2006-10-24, 6:30 pm

> I've confirmed that they are valid backups with 2000.

How? RESTORE VERIFYONLY doesn't give you much assurence that the integrity of the data is OK. 2005
has checksum options, which makes VERIFYONLY useful.

Possibly you have a corruption in the original database. Try DBCC CHECKDB(dbname) WITH NO_INFOMSGS
and see if you get any errors.

Also, check out the MOVE and REPLACE options of the RESTORE command. And, investigate the file
layout of the backup using RESTORE HEADERONLY and RESTORE FILELISTONLY. I don't think this is the
issue (judging from the error message), but you never know.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/

"Charles" <charles.no.spam.earl@bsainc.ca> wrote in message
news:448FEB02-27E3-4F51-938A- 99523D1BF82D@microso
ft.com...
> Okay...I'm stumped. I've installed SqlServer 2005 SP1 on my laptop and am trying to restore a
> customer's database from a SQL Server 2000 full database backup. I've tried through Management
> Studio GUI and also through a script...every time I get the following error:
>
> Processed 267584 pages for database 'SOMEBODY', file 'SOMEBODY_Data' on file 1.
> Msg 3283, Level 16, State 1, Line 1
> The file "SOMEBODY_Data" failed to initialize correctly. Examine the error logs for more details.
> Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
>
> Can anyone help? I'm pulling my hair out over this one. I've tried three different SQL Server 2000
> backups and none work. I've confirmed that they are valid backups with 2000.
>
> Regards,
> Charles

Charles

2006-10-24, 6:30 pm

Hi Tibor,

I've actually physically restored the databases on the SQL Server 2000
instances they were backed up from. The restore was fine into a new
database. I've checked the file layout with RESTORE FILELISTONLY and there's
one data (MDF) file and one log (LDF) file. I'm using the MOVE option of the
RESTORE command; the RESTORE option fails if I don't use MOVE because I'm
going from one server to my laptop and I'm changing the file location of the
data files. I'm very comfortable with backup/restores on SQL Server 2000; I
do it all the time as part of my job...I'm just not sure why I'm having such
a huge problem with 2005...I know that you're able to restore SQL Server
2000 backups into 2005 databases.

Charles
"Tibor Karaszi" <tibor_please.no. email_karaszi@hotmai
l.nomail.com> wrote in
message news:ucwb7Pl3GHA.3516@TK2MSFTNGP06.phx.gbl...
>
> How? RESTORE VERIFYONLY doesn't give you much assurence that the integrity
> of the data is OK. 2005 has checksum options, which makes VERIFYONLY
> useful.
>
>
> Possibly you have a corruption in the original database. Try DBCC
> CHECKDB(dbname) WITH NO_INFOMSGS and see if you get any errors.
>
> Also, check out the MOVE and REPLACE options of the RESTORE command. And,
> investigate the file layout of the backup using RESTORE HEADERONLY and
> RESTORE FILELISTONLY. I don't think this is the issue (judging from the
> error message), but you never know.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www. solidqualitylearning
.com/
>
>
> "Charles" <charles.no.spam.earl@bsainc.ca> wrote in message
> news:448FEB02-27E3-4F51-938A- 99523D1BF82D@microso
ft.com...
>

Tibor Karaszi

2006-10-24, 6:30 pm

Sorry for the basics, Charles. You never know...

OK, so the file layout isn't the problem then. I guessed so, judging from the error message, but I
just wanted to cover that possibility.

The error message is strange to me. It seems like RESTORE managed to put the pages into the database
file, but then couldn't initialize the database file for normal operations (the recovery phase of
the restore operation). Check permissions on the folder etc. for the SQL Server service account,
just in case.
You don't try to put the database in a compressed folder, do you? I don't think so, as I believe
that 2005 actually gives up a proper error message if you try. Perhaps it does for CREATE DATABASE
but not for RESTORE?

Can you restore this backup to a 2000 instance? If not, then the backup is flaky, possibly
corruption in the source database. If you can, you should be able to restore it to 2005.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/

"Charles" <charles.no.spam.earl@bsainc.ca> wrote in message
news:5369F2BE-FC57-4465-B7BD- 53118D4DE9A7@microso
ft.com...
> Hi Tibor,
>
> I've actually physically restored the databases on the SQL Server 2000 instances they were backed
> up from. The restore was fine into a new database. I've checked the file layout with RESTORE
> FILELISTONLY and there's one data (MDF) file and one log (LDF) file. I'm using the MOVE option of
> the RESTORE command; the RESTORE option fails if I don't use MOVE because I'm going from one
> server to my laptop and I'm changing the file location of the data files. I'm very comfortable
> with backup/restores on SQL Server 2000; I do it all the time as part of my job...I'm just not
> sure why I'm having such a huge problem with 2005...I know that you're able to restore SQL Server
> 2000 backups into 2005 databases.
>
> Charles
> "Tibor Karaszi" <tibor_please.no. email_karaszi@hotmai
l.nomail.com> wrote in message
> news:ucwb7Pl3GHA.3516@TK2MSFTNGP06.phx.gbl...
>

Charles

2006-10-24, 6:30 pm

Hi Tibor,

Don't worry about the basics, good to have them covered first! :) I
appreciate your help.

I'm wondering if maybe it's a permission issue. I'm using the system account
and I'm noticing that it doesn't have access to all the folders on my c:
drive. I created a folder called sqldata off the root of c: for simplicity's
sake. So the restore files are going to c:\sqldata. The folder isn't
compressed. I'm going to try to start the sqlserver service as an
administrator of the machine rather than the system account.

And you're right...it does seem like the restore finishes, but it can't
initialize the database for normal use. Once the error message goes away, I
can refresh the management studio screen and see the database. If I try to
connect or use the database it tells me that the database is in the middle
of a restore.

I can definitely restore these backups on a SQL Server 2000 install, so it
seems like the backups are okay. I'm going to try a fresh install of SQL
Server 2005 on a Win2000 PRO virtual machine to see if I have any luck. And
I'll double-check the permissions on my c:\sqldata folder as well. Thanks
again!

Charles
"Tibor Karaszi" <tibor_please.no. email_karaszi@hotmai
l.nomail.com> wrote in
message news:uc%23oobl3GHA.1040@TK2MSFTNGP06.phx.gbl...
> Sorry for the basics, Charles. You never know...
>
> OK, so the file layout isn't the problem then. I guessed so, judging from
> the error message, but I just wanted to cover that possibility.
>
> The error message is strange to me. It seems like RESTORE managed to put
> the pages into the database file, but then couldn't initialize the
> database file for normal operations (the recovery phase of the restore
> operation). Check permissions on the folder etc. for the SQL Server
> service account, just in case.
> You don't try to put the database in a compressed folder, do you? I don't
> think so, as I believe that 2005 actually gives up a proper error message
> if you try. Perhaps it does for CREATE DATABASE but not for RESTORE?
>
> Can you restore this backup to a 2000 instance? If not, then the backup is
> flaky, possibly corruption in the source database. If you can, you should
> be able to restore it to 2005.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www. solidqualitylearning
.com/
>
>
> "Charles" <charles.no.spam.earl@bsainc.ca> wrote in message
> news:5369F2BE-FC57-4465-B7BD- 53118D4DE9A7@microso
ft.com...
>

Charles

2006-10-24, 6:30 pm

This is totally, absolutely bizzare! I just installed SQL 2005 on a windows
2000 PRO fresh install and the databases restored properly, no problem at
all.

So...I backup the database in 2005 and copy the file to my host machine that
has SQL 2005 installed as well (where I'm getting the error messages). I try
to restore a SQL 2005 restore to this machine and it gives me the same ERORR
MESSAGE!? So, now I'm wondering what might have caused this...and I'm
wondering if Visual Studio 2005 Professional has anything to do with this? I
installed VS 2005 Pro on my laptop AFTER installing SQL 2005 Express SP1. I
declined the install of the express edition with VS 2005, but I'm wondering
if SQL Server 2005 express stops working properly after installing VS 2005?
Can anyone confirm/deny this? It's the only thing I can think of that
explains why SQL Server 2005 works properly on a fresh install but not on
mine. Very weird.

I also re-confirmed security on c:\sqldata and everything looks fine.

Charles
"Tibor Karaszi" <tibor_please.no. email_karaszi@hotmai
l.nomail.com> wrote in
message news:uc%23oobl3GHA.1040@TK2MSFTNGP06.phx.gbl...
> Sorry for the basics, Charles. You never know...
>
> OK, so the file layout isn't the problem then. I guessed so, judging from
> the error message, but I just wanted to cover that possibility.
>
> The error message is strange to me. It seems like RESTORE managed to put
> the pages into the database file, but then couldn't initialize the
> database file for normal operations (the recovery phase of the restore
> operation). Check permissions on the folder etc. for the SQL Server
> service account, just in case.
> You don't try to put the database in a compressed folder, do you? I don't
> think so, as I believe that 2005 actually gives up a proper error message
> if you try. Perhaps it does for CREATE DATABASE but not for RESTORE?
>
> Can you restore this backup to a 2000 instance? If not, then the backup is
> flaky, possibly corruption in the source database. If you can, you should
> be able to restore it to 2005.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www. solidqualitylearning
.com/
>
>
> "Charles" <charles.no.spam.earl@bsainc.ca> wrote in message
> news:5369F2BE-FC57-4465-B7BD- 53118D4DE9A7@microso
ft.com...
>

Tracy McKibben

2006-10-24, 6:30 pm

Charles wrote:
> This is totally, absolutely bizzare! I just installed SQL 2005 on a
> windows 2000 PRO fresh install and the databases restored properly, no
> problem at all.
>
> So...I backup the database in 2005 and copy the file to my host machine
> that has SQL 2005 installed as well (where I'm getting the error
> messages). I try to restore a SQL 2005 restore to this machine and it
> gives me the same ERORR MESSAGE!? So, now I'm wondering what might have
> caused this...and I'm wondering if Visual Studio 2005 Professional has
> anything to do with this? I installed VS 2005 Pro on my laptop AFTER
> installing SQL 2005 Express SP1. I declined the install of the express
> edition with VS 2005, but I'm wondering if SQL Server 2005 express stops
> working properly after installing VS 2005? Can anyone confirm/deny this?
> It's the only thing I can think of that explains why SQL Server 2005
> works properly on a fresh install but not on mine. Very weird.
>
> I also re-confirmed security on c:\sqldata and everything looks fine.

Are you 100% CERTAIN that you are restoring to SQL 2005 Express and not
MSDE? MSDE is limited to a maximum database size of 2GB, and if my math
is correct, your database is just over that size (267584 pages * 8K).

--
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Charles

2006-10-24, 6:30 pm

Hi Tracy,

It's a good thought...but I don't even have MSDE installed. Visual Studio
2005 comes with SQL Server Express, not the MSDE. And I made sure to
de-select it as part of the install. I think I'm going to try installing
Visual Studio then SQL Server 2005 on a fresh machine and see if it works
that way...

Charles
"Tracy McKibben" <tracy@realsqlguy.com> wrote in message
news:4514191E.1010906@realsqlguy.com...
> Charles wrote:
>
> Are you 100% CERTAIN that you are restoring to SQL 2005 Express and not
> MSDE? MSDE is limited to a maximum database size of 2GB, and if my math
> is correct, your database is just over that size (267584 pages * 8K).
>
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

Tibor Karaszi

2006-10-24, 6:31 pm

> If I try to connect or use the database it tells me that the database is in the middle of a
> restore.

And you did the restore with the RECOVERY option? Assuming you did:

I doubt VS should affect this.

Perhaps there is something fishy with auto-close db option and some anti-virus software getting in
between to check the newly restored database file? But, I think auto-close is inherited with the
backup, so you would have the same issue on the other machines (assuming the same AV software). Does
this happen for every db you restore on the fishy machine? Can you do a test with two db's one that
had auto-close and one which didn't have autoclose?

Also, did you do you test using an account with Administrator? Btw, make sure you change service
account using SQL Server Configuration Manager, not the windows Services applet.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/

"Charles" <charles.no.spam.earl@bsainc.ca> wrote in message
news:CB9AD1EE-B3FE-4AA7-AB6C- AE3C31861FE4@microso
ft.com...
> Hi Tibor,
>
> Don't worry about the basics, good to have them covered first! :) I appreciate your help.
>
> I'm wondering if maybe it's a permission issue. I'm using the system account and I'm noticing that
> it doesn't have access to all the folders on my c: drive. I created a folder called sqldata off
> the root of c: for simplicity's sake. So the restore files are going to c:\sqldata. The folder
> isn't compressed. I'm going to try to start the sqlserver service as an administrator of the
> machine rather than the system account.
>
> And you're right...it does seem like the restore finishes, but it can't initialize the database
> for normal use. Once the error message goes away, I can refresh the management studio screen and
> see the database. If I try to connect or use the database it tells me that the database is in the
> middle of a restore.
>
> I can definitely restore these backups on a SQL Server 2000 install, so it seems like the backups
> are okay. I'm going to try a fresh install of SQL Server 2005 on a Win2000 PRO virtual machine to
> see if I have any luck. And I'll double-check the permissions on my c:\sqldata folder as well.
> Thanks again!
>
> Charles
> "Tibor Karaszi" <tibor_please.no. email_karaszi@hotmai
l.nomail.com> wrote in message
> news:uc%23oobl3GHA.1040@TK2MSFTNGP06.phx.gbl...
>

Tracy McKibben

2006-10-24, 6:31 pm

Tibor Karaszi wrote:
>
> And you did the restore with the RECOVERY option? Assuming you did:
>
> I doubt VS should affect this.
>
> Perhaps there is something fishy with auto-close db option and some
> anti-virus software getting in between to check the newly restored
> database file? But, I think auto-close is inherited with the backup, so
> you would have the same issue on the other machines (assuming the same
> AV software). Does this happen for every db you restore on the fishy
> machine? Can you do a test with two db's one that had auto-close and one
> which didn't have autoclose?
>
> Also, did you do you test using an account with Administrator? Btw, make
> sure you change service account using SQL Server Configuration Manager,
> not the windows Services applet.
>

Ahhh, I didn't think about A/V software, that would make a lot of sense.
The restore finishes creating the file, but before SQL can recover the
database, the A/V software has grabbed it.

--
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Tibor Karaszi

2006-10-24, 6:31 pm

> The restore finishes creating the file, but before SQL can recover the database, the A/V software
> has grabbed it.

Well, at least it is a theory. But with a bunch of disclaimers.

Why, for instance would the file be closed after page loading and before recovery? Sounds like
strange behavior by SQL Server, just asking for this to occur.

And why only this machine? (I assume a corporate-wide AV sw in place, so should be same on all
machines...)

And, auto-close should be inherited from the source db, and no-one in their right mind would run
autoclose in production, right? ;-)

Charles, please keep us informed...

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/

"Tracy McKibben" <tracy@realsqlguy.com> wrote in message news:45143953.7060201@realsqlguy.com...
> Tibor Karaszi wrote:
>
> Ahhh, I didn't think about A/V software, that would make a lot of sense. The restore finishes
> creating the file, but before SQL can recover the database, the A/V software has grabbed it.
>
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

Charles

2006-10-24, 6:31 pm

Tibor you're a GENIUS! :D

It was the anti-virus software...I disabled the real-time checking and the
restore completed successfully! It's very odd though...I've got real-time
scanning only enabled for certain extensions... MDF and LDF are not in the
list but it seems to be checking these files anyway. Not good...at any rate,
this seems to have solved my problem as one of the databases that was
definitely giving me the error has now been restored.

My machine is stand-alone off the network...so not all machines would
necessarily have the same anti-virus software as mine. And auto-close was
indeed turned off on these databases as they are production databases. I
normally turn it on once I've got the databases restored on my laptop.

Thanks again for all your help!

Regards,
Charles
"Tibor Karaszi" <tibor_please.no. email_karaszi@hotmai
l.nomail.com> wrote in
message news:uWAvk5n3GHA.3476@TK2MSFTNGP04.phx.gbl...
>
> Well, at least it is a theory. But with a bunch of disclaimers.
>
> Why, for instance would the file be closed after page loading and before
> recovery? Sounds like strange behavior by SQL Server, just asking for this
> to occur.
>
> And why only this machine? (I assume a corporate-wide AV sw in place, so
> should be same on all machines...)
>
> And, auto-close should be inherited from the source db, and no-one in
> their right mind would run autoclose in production, right? ;-)
>
> Charles, please keep us informed...
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www. solidqualitylearning
.com/
>
>
> "Tracy McKibben" <tracy@realsqlguy.com> wrote in message
> news:45143953.7060201@realsqlguy.com...
>

Tibor Karaszi

2006-10-24, 6:31 pm

Glad you got it working, Charles. What AV sw are you using? Just curious... Probably worth a support
call to that sw vendor.

> And auto-close was indeed turned off on these databases as they are production databases. I
> normally turn it on once I've got the databases restored on my laptop.

Hmm, I don't know what type of work you do on your laptop, but check out
http://www.karaszi.com/SQLServer/info_dont_shrink.asp.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/

"Charles" <charles.no.spam.earl@bsainc.ca> wrote in message
news:2302F533-BA64-4B6E-B244- 614745B3C1EE@microso
ft.com...
> Tibor you're a GENIUS! :D
>
> It was the anti-virus software...I disabled the real-time checking and the restore completed
> successfully! It's very odd though...I've got real-time scanning only enabled for certain
> extensions... MDF and LDF are not in the list but it seems to be checking these files anyway. Not
> good...at any rate, this seems to have solved my problem as one of the databases that was
> definitely giving me the error has now been restored.
>
> My machine is stand-alone off the network...so not all machines would necessarily have the same
> anti-virus software as mine. And auto-close was indeed turned off on these databases as they are
> production databases. I normally turn it on once I've got the databases restored on my laptop.
>
> Thanks again for all your help!
>
> Regards,
> Charles
> "Tibor Karaszi" <tibor_please.no. email_karaszi@hotmai
l.nomail.com> wrote in message
> news:uWAvk5n3GHA.3476@TK2MSFTNGP04.phx.gbl...
>

Sponsored Links