Database restore problem

A customer's Windows 2000 server would not reboot. They
ran a repair, but the drive letters on the server moved
ahead two spots. The drive letter D is now drive F.
I attempted a restore of a database through Enterprise
Manager, but I get the following error message :
"Device activation error: the physical file name d:\sql
db\otg.mdf: may be incorrect. File 'otg data' cannot be
restored to d:\sql db\otg.mdf'. Use with move to identify
a valid location for the file. Device activation error.
The physical file name 'D:\sql db\otglog.ldf may be
incorrect. File 'otglog' cannot be restored to 'd:\sql
db\otglog.ldf.' Use with move to identify a valid location
for the file. Restore db is terminating abnormally."
The original database files were located on C. A backup of
the database was saved to D.
What can I do to restore this database? Can anyone suggest
a query that restores the database to its previous state?
Thanks for your help.Hello Craig !
If you restore in EM you should change the path where you want to place the
new datafiles from the backup.
Please note, that the directories will NOT be created by EM, they must be
created before restoring the database.
HTH, Jens Süßmeyer.|||Try this:
RESTORE DATABASE otg
FROM DISK= 'c:\wherever the .bak file is'
WITH RECOVERY,replace,
MOVE 'otg_data' TO 'd:\sql_db\otg.mdf',
MOVE 'otgLog' TO 'd:\sql_db\otglog.ldf.'
Make sure you have enough permissions, with OTG imaging database you can use
the sysop user ;)
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Craig Kornacki" <ckornack@.localnet.com> wrote in message
news:034701c36f5b$2e13c320$a301280a@.phx.gbl...
> A customer's Windows 2000 server would not reboot. They
> ran a repair, but the drive letters on the server moved
> ahead two spots. The drive letter D is now drive F.
> I attempted a restore of a database through Enterprise
> Manager, but I get the following error message :
> "Device activation error: the physical file name d:\sql
> db\otg.mdf: may be incorrect. File 'otg data' cannot be
> restored to d:\sql db\otg.mdf'. Use with move to identify
> a valid location for the file. Device activation error.
> The physical file name 'D:\sql db\otglog.ldf may be
> incorrect. File 'otglog' cannot be restored to 'd:\sql
> db\otglog.ldf.' Use with move to identify a valid location
> for the file. Restore db is terminating abnormally."
> The original database files were located on C. A backup of
> the database was saved to D.
> What can I do to restore this database? Can anyone suggest
> a query that restores the database to its previous state?
> Thanks for your help.|||I know it's late, but what I would do is change the Drive letter to the
original drive letter using windows disk manager... This will likely prevent
other mapping kinds of errors from other applications as well.
"Craig Kornacki" <ckornack@.localnet.com> wrote in message
news:034701c36f5b$2e13c320$a301280a@.phx.gbl...
> A customer's Windows 2000 server would not reboot. They
> ran a repair, but the drive letters on the server moved
> ahead two spots. The drive letter D is now drive F.
> I attempted a restore of a database through Enterprise
> Manager, but I get the following error message :
> "Device activation error: the physical file name d:\sql
> db\otg.mdf: may be incorrect. File 'otg data' cannot be
> restored to d:\sql db\otg.mdf'. Use with move to identify
> a valid location for the file. Device activation error.
> The physical file name 'D:\sql db\otglog.ldf may be
> incorrect. File 'otglog' cannot be restored to 'd:\sql
> db\otglog.ldf.' Use with move to identify a valid location
> for the file. Restore db is terminating abnormally."
> The original database files were located on C. A backup of
> the database was saved to D.
> What can I do to restore this database? Can anyone suggest
> a query that restores the database to its previous state?
> Thanks for your help.|||Will this query work even if there is no D drive on the
server?
>--Original Message--
>Try this:
>RESTORE DATABASE otg
>FROM DISK= 'c:\wherever the .bak file is'
> WITH RECOVERY,replace,
> MOVE 'otg_data' TO 'd:\sql_db\otg.mdf',
> MOVE 'otgLog' TO 'd:\sql_db\otglog.ldf.'
>Make sure you have enough permissions, with OTG imaging
database you can use
>the sysop user ;)
>HTH
>--
>Ray Higdon MCSE, MCDBA, CCNA
>--
>"Craig Kornacki" <ckornack@.localnet.com> wrote in message
>news:034701c36f5b$2e13c320$a301280a@.phx.gbl...
>> A customer's Windows 2000 server would not reboot.
They
>> ran a repair, but the drive letters on the server moved
>> ahead two spots. The drive letter D is now drive F.
>> I attempted a restore of a database through Enterprise
>> Manager, but I get the following error message :
>> "Device activation error: the physical file name
d:\sql
>> db\otg.mdf: may be incorrect. File 'otg data' cannot
be
>> restored to d:\sql db\otg.mdf'. Use with move to
identify
>> a valid location for the file. Device activation
error.
>> The physical file name 'D:\sql db\otglog.ldf may be
>> incorrect. File 'otglog' cannot be restored to 'd:\sql
>> db\otglog.ldf.' Use with move to identify a valid
location
>> for the file. Restore db is terminating abnormally."
>> The original database files were located on C. A
backup of
>> the database was saved to D.
>> What can I do to restore this database? Can anyone
suggest
>> a query that restores the database to its previous
state?
>> Thanks for your help.
>
>.
>

database repair without data loss?

Scenario: Database maintenance plan failed in "check data and index linkage" activity. Ran DBCC CHECKDB WITH PHYSICAL_ONLY option which revealed a few "page id" problems. It appears all errors on related to one table. The CHECKDB stated specifically: "repair_allow_data_loss is the minimum repair level for the errors found"
My question is: Is there any way to repair database/table without data loss?Yes, restore from your last backup and apply all the log backups since the
backup was taken (stopping at the point the corruption appears if necessary)
It is not *guaranteed* that repair will have to delete data to repair the
database but it is highly likely (if REPAIR_ALLOW_DATA_LOSS is needed).
Repair should always be your last resort. You should also determine the root
cause of the corruption (i.e. examine NT event logs, SQL Server error log,
run hardware diagnostics etc) as a hardware fault will most likely cause the
same or similar corruption in future if not corrected.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Alan T" <infopro@.3wlogic.net> wrote in message
news:FD16E2B3-DEF4-486C-8A80-DFABB549720B@.microsoft.com...
> Scenario: Database maintenance plan failed in "check data and index
linkage" activity. Ran DBCC CHECKDB WITH PHYSICAL_ONLY option which
revealed a few "page id" problems. It appears all errors on related to one
table. The CHECKDB stated specifically: "repair_allow_data_loss is the
minimum repair level for the errors found".
> My question is: Is there any way to repair database/table without data
loss?|||Thanks, Paul. With the help of someone with a great deal more experience I was able to recover virtually all data.
The corruption was limited to one table, so after some minor unsuccessful attempts at repair we ran DBCC CHECKTABLE WITH REPAIR_ALLOW_DATA_LOSS. We then restored a "good" backup into a temporary database and from that database pulled records from the problem table that were missing in the production table after the REPAIR_ALLOW_DATA_LOSS. It appears we were able to recover all but about 11 records. It's wasn't a "perfect" recovery but I'm happy and grateful for the help.
Best wishes.