Restore of Backup problem

butatista

Please help

SQL Server 2000 Standard

I am trying to restore databases on my server and am unable to get them
to restore using the database option.

For example, the location of my backup file is:

D:\MSSQL\BACKUP\MY_DB\MY_DB_ddmmyy.bak

But when checking the properties of the restore on the general tab, the
restore shows the correct time and date of the backup we want, but
under RESTORE FROM it states
MY_DB_00__43c6cdc2_06a2_4431_b304_06cc3a97f512_

and not the correct file path. So hence the restore fails with error
"unable to find backup device Device offline".

The only way I can restore is to use the "from device" option then
manually browse to the backup file.

Whilst this is a work around, I want to be able to do point in time
restores which will only work on a database restore.
Any help with this would be appreciated.

Jul 23 '05 #1

Simon Hayes

"butatista" <gmcintyre@countrywidemobility.co.uk> wrote in message
news:1105093628.003615.238050@z14g2000cwz.googlegr oups.com...[color=blue]
> Please help
>
> SQL Server 2000 Standard
>
> I am trying to restore databases on my server and am unable to get them
> to restore using the database option.
>
> For example, the location of my backup file is:
>
> D:\MSSQL\BACKUP\MY_DB\MY_DB_ddmmyy.bak
>
> But when checking the properties of the restore on the general tab, the
> restore shows the correct time and date of the backup we want, but
> under RESTORE FROM it states
> MY_DB_00__43c6cdc2_06a2_4431_b304_06cc3a97f512_
>
> and not the correct file path. So hence the restore fails with error
> "unable to find backup device Device offline".
>
> The only way I can restore is to use the "from device" option then
> manually browse to the backup file.
>
> Whilst this is a work around, I want to be able to do point in time
> restores which will only work on a database restore.
> Any help with this would be appreciated.
>[/color]

Are you using a third-party backup tool, perhaps? That might explain the
unusual device name you see, and if so then you should probably look into
doing restores using that tool. If you're looking for some sort of
programmatic solution, then you would need to look at the RESTORE command in
Books Online - it accepts variables for the source file name(s) and the time
to restore to, so you should be able to write a stored procedure or script
to do what you want.

Simon

Jul 23 '05 #2

butatista

No, I am not using a third party backup tool. I want to do this using
SQL Server and would prefer to use the GUI not a stored procedure or
script.

Thanks

Jul 23 '05 #3

Simon Hayes

"butatista" <gmcintyre@countrywidemobility.co.uk> wrote in message
news:1105357006.178235.268550@c13g2000cwb.googlegr oups.com...[color=blue]
> No, I am not using a third party backup tool. I want to do this using
> SQL Server and would prefer to use the GUI not a stored procedure or
> script.
>
> Thanks
>[/color]

I just tested (SQL2000 Enterprise SP3a) backing up a database to a device
(full backup then a log backups), and doing a point in time restore - it
worked fine, and it displayed the correct file name in the dialogues. If
you're not using a third party tool, then I have no idea where the name
"MY_DB_00__43c6cdc2_06a2_4431_b304_06cc3a97f51 2_" might come from - have you
applied the latest servicepack on the client as well as the server? There
are a number of bugs in the MS KB related to restore operations in EM.

Apart from that, the best idea in the long run is to start using RESTORE for
your recovery operations - it will give you more control and flexibility,
and there are some things which can only be done from TSQL (eg. restore to a
marked transaction). If (when) you need to do batch or scheduled operations,
you will need to do it with RESTORE anyway.

Simon

Jul 23 '05 #4