Restore of Backup problem

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."butatista" <gmcintyre@.countrywidemobility.co.uk> wrote in message
news:1105093628.003615.238050@.z14g2000cwz.googlegr oups.com...
> 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.

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|||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|||"butatista" <gmcintyre@.countrywidemobility.co.uk> wrote in message
news:1105357006.178235.268550@.c13g2000cwb.googlegr oups.com...
> 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

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_06cc3a97f512_" 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

restore MSDB or transfer jobs?

I restored MSDB onto a new box and my jobs won't run now:
Unable to connect to SQL Server '(local)'. The step failed.
Am I better off to use the Transfer Jobs Task in DTS or is there a simple
answer to this? If so, what about moving the DTS Packages?
--
SQL2K SP3
TIA, ChrisRI would scrip them out an apply on new server. But you might be able
to fix them on the new server
declare @.NewServerName varchar(50)
select @.NewServerName = cast(serverproperty ('ServerName') as
nvarchar(50))
update msdb..sysjobs
set originating_server = @.NewServerName
GO
checkpoint
GO|||The query converts the @.NewServerName to nothing:
Server: Msg 515, Level 16, State 2, Line 3
Cannot insert the value NULL into column 'originating_server', table
'msdb.dbo.sysjobs'; column does not allow nulls. UPDATE fails.
The statement has been terminated.
declare @.NewServerName varchar(50)
select @.NewServerName = cast(serverproperty ('MyBox') as nvarchar(50))
print @.NewServerName
However, I did do an old fashioned:
Update SysJobs
set originating_server = 'MyBox'
but still I have the same problem.
If I did the script method, what would I do with my DTS Packages? Just save
to the new box? Would I be missing anything then from NOT restoring MSDB?
What do most of you do in this scenario?
"bert" <bertcord@.gmail.com> wrote in message
news:1106868585.875164.102430@.z14g2000cwz.googlegroups.com...
> I would scrip them out an apply on new server. But you might be able
> to fix them on the new server
> declare @.NewServerName varchar(50)
> select @.NewServerName = cast(serverproperty ('ServerName') as
> nvarchar(50))
> update msdb..sysjobs
> set originating_server = @.NewServerName
> GO
> checkpoint
> GO
>|||select serverproperty ('ServerName') returns null?
Sorry cant help you with the DTS jobs... I stay away from DTS.
did you stop and restart SQL
Bert|||Have a look here: http://www.sqldts.com/default.aspx?204
--
Andrew J. Kelly SQL MVP
"ChrisR" <bla@.noemail.com> wrote in message
news:ub2Z2MMBFHA.2392@.TK2MSFTNGP14.phx.gbl...
>I restored MSDB onto a new box and my jobs won't run now:
> Unable to connect to SQL Server '(local)'. The step failed.
> Am I better off to use the Transfer Jobs Task in DTS or is there a simple
> answer to this? If so, what about moving the DTS Packages?
> --
> SQL2K SP3
> TIA, ChrisR
>|||Thanks Andrew.
The "Save as" option suits me just fine. But assuming I do that in place of
Restoring MSDB, what about the jobs? Both the jobs to run these DTS Packages
and those that don't. I know the other options are to script them or to use
DTS to transfer them. Is anyone aware of the pros/ cons for either one? If I
do either of these methods, will I be missing something vital from NOT
Restoring MSDB? I don't think so based on past experience, but its been a
while.
TIA, ChrisR
"ChrisR" <bla@.noemail.com> wrote in message
news:ub2Z2MMBFHA.2392@.TK2MSFTNGP14.phx.gbl...
> I restored MSDB onto a new box and my jobs won't run now:
> Unable to connect to SQL Server '(local)'. The step failed.
> Am I better off to use the Transfer Jobs Task in DTS or is there a simple
> answer to this? If so, what about moving the DTS Packages?
> --
> SQL2K SP3
> TIA, ChrisR
>|||Not an option. Thanks Bert.
"bert" <bertcord@.gmail.com> wrote in message
news:1106878056.175663.124700@.z14g2000cwz.googlegroups.com...
> select serverproperty ('ServerName') returns null?
> Sorry cant help you with the DTS jobs... I stay away from DTS.
> did you stop and restart SQL
> Bert
>|||I prefer to script the jobs myself but I don't know of any thing that says
that is better or worse than moving MSDB per say. Even the KB that talks
about moving DB's suggests moving jobs via scripting.
http://www.support.microsoft.com/?id=314546
Andrew J. Kelly SQL MVP
"ChrisR" <ChrisR@.noEmail.com> wrote in message
news:uQAGA5PBFHA.3524@.TK2MSFTNGP15.phx.gbl...
> Thanks Andrew.
> The "Save as" option suits me just fine. But assuming I do that in place
> of
> Restoring MSDB, what about the jobs? Both the jobs to run these DTS
> Packages
> and those that don't. I know the other options are to script them or to
> use
> DTS to transfer them. Is anyone aware of the pros/ cons for either one? If
> I
> do either of these methods, will I be missing something vital from NOT
> Restoring MSDB? I don't think so based on past experience, but its been a
> while.
> TIA, ChrisR
>
> "ChrisR" <bla@.noemail.com> wrote in message
> news:ub2Z2MMBFHA.2392@.TK2MSFTNGP14.phx.gbl...
>> I restored MSDB onto a new box and my jobs won't run now:
>> Unable to connect to SQL Server '(local)'. The step failed.
>> Am I better off to use the Transfer Jobs Task in DTS or is there a simple
>> answer to this? If so, what about moving the DTS Packages?
>> --
>> SQL2K SP3
>> TIA, ChrisR
>>
>|||Thanks again Andrew. I think Im going to change my strategy on this whole
thing. It seems like to "Save as" for the DTS Packages and Script the Jobs/
Operators/ and Alerts I could get everything that I would from Restoring
MSDB minus a few headaches.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:#AVGIwUBFHA.3924@.TK2MSFTNGP10.phx.gbl...
> I prefer to script the jobs myself but I don't know of any thing that says
> that is better or worse than moving MSDB per say. Even the KB that talks
> about moving DB's suggests moving jobs via scripting.
> http://www.support.microsoft.com/?id=314546
>
> --
> Andrew J. Kelly SQL MVP
>
> "ChrisR" <ChrisR@.noEmail.com> wrote in message
> news:uQAGA5PBFHA.3524@.TK2MSFTNGP15.phx.gbl...
> > Thanks Andrew.
> >
> > The "Save as" option suits me just fine. But assuming I do that in place
> > of
> > Restoring MSDB, what about the jobs? Both the jobs to run these DTS
> > Packages
> > and those that don't. I know the other options are to script them or to
> > use
> > DTS to transfer them. Is anyone aware of the pros/ cons for either one?
If
> > I
> > do either of these methods, will I be missing something vital from NOT
> > Restoring MSDB? I don't think so based on past experience, but its been
a
> > while.
> >
> > TIA, ChrisR
> >
> >
> >
> > "ChrisR" <bla@.noemail.com> wrote in message
> > news:ub2Z2MMBFHA.2392@.TK2MSFTNGP14.phx.gbl...
> >> I restored MSDB onto a new box and my jobs won't run now:
> >>
> >> Unable to connect to SQL Server '(local)'. The step failed.
> >>
> >> Am I better off to use the Transfer Jobs Task in DTS or is there a
simple
> >> answer to this? If so, what about moving the DTS Packages?
> >>
> >> --
> >> SQL2K SP3
> >>
> >> TIA, ChrisR
> >>
> >>
> >
> >
>