February 20, 2007 at 12:39 pm
Hi all,
We have a Sql2000 DB in production on the D: drive. I am trying to put it on a server in our test lab on the C: drive. I did a full backup of production without errors to create the .BAK file.
My problem now is that in the Entp Manager, the DB is Silver in color and says "Loading". All other DBs are good.
This is my first time dealing with MSSQL so forgive me if this is lengthy. I'm not sure what is relavent and what ain't. Here are the steps I have done to attemp to restore the DB.
1. In the Ent Manager, I attempted to restore. It ran for a few minutes Then I got this error:
2007-02-20 08:14:35.15 spid51 The backup data in 'C:\REMEDY-DB\ARSystem_db_200702022300.BAK' is incorrectly formatted. Backups cannot be appended, but existing backup sets may still be usable.
2. I thought this error was because of the difference in hard drive locations, D vrs C: so I went into the Query Manager and ran this command:
RESTORE DATABASE ARSystem FROM DISK='C:\REMEDY-DB\ARSystem_db_200702022300.BAK'
WITH MOVE 'ARSystem' to 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\ARSystem.mdf',
MOVE 'ARSystem_log' to 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\ARSystem_log.LDF', REPLACE
3. It ran for quite awhile, then I got:
ERROR
Server: Msg 3270, Level 16, State 1, Line 1 An internal consistency error occurred. Contact Tech Support
4. I ran DBCC CHECKDB
CHECKDB found 0 allocation errors and 0 consistency errors in database 'master'.
And that's where I'm at. Oh, I checked the size of the files and everything looks good. I did reboot though after running the restore command. Probably, way too soon. From what I've found on Google, I think the transaction log needs to do something after the restore, but I really don't know.
Any help is greatly appreciated!!
Regards,
Zeek
February 20, 2007 at 1:03 pm
Hard to tell exactly why you are having the errors but I can tell you backups and restores are for the most part very dependable in Mssql
If you are still stuck if possible do the following .This are pretty basic and you should know but sometimes another eye over your shoulder makes all the difference
On destination Create a base database to overwrite during the restore ..
On Source
1)Do a full backup to an empty folder because a backup will append if there is a file on that location with the same name
2)Verify the backup is finished without errors.
3)Move the backup to new Server
4)In Enterprise Manager select the location of the backup file.
Under options choose the new location for your mdf and ldf files and restore
Let me know what happens
February 21, 2007 at 12:17 am
Looks like your backup process was interupted so that it gave you incorrectly formatted error...
Just create a new backup WITH INIT option and use the new backup to restore...
Backup database dbname to disk = 'backup filename with path' with INTI
MohammedU
Microsoft SQL Server MVP
February 21, 2007 at 7:59 am
Thanks very much Veteran and MohammedU.
Why do you think the backup was no good? I didn't receive any errors on it.
My problem is with the restore.
Zeek
February 21, 2007 at 11:23 pm
When you try to append backup to the existing file it failed with "incorrectly formatted" error.
You are trying to restore the db using the same file and getting the error...
I believe backup is causing the restore error...
It is better to take a full backup and run the restore...to rule out the restore issue...
http://support.microsoft.com/kb/290787
MohammedU
Microsoft SQL Server MVP
February 22, 2007 at 11:06 am
Thanks again MohammedU.
I did do a full backup with no errors.
I got the error "..is incorrectly formatted. Backups cannot be appended, but existing backup sets may still be usable."" while attempting to RESTORE on the new server.
Zeek
February 23, 2007 at 12:07 am
Can you please post your backup and restore commands as is... just change the db name...
MohammedU
Microsoft SQL Server MVP
February 23, 2007 at 3:55 pm
The backup was thru the Gui. We used full backup and took all the defaults.
Restore is :
RESTORE DATABASE ARSystem FROM DISK='C:\REMEDY-DB\ARSystem_db_200702022300.BAK'
WITH MOVE 'ARSystem' to 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\ARSystem.mdf',
MOVE 'ARSystem_log' to 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\ARSystem_log.LDF', REPLACE
Thanks Mohammed!!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply