Restore being annoying

Hi,

I am trying to restore a database and call it a different name, but it comes up with the following error:

Executed as user: sa. Invalid object name 'msdb.dbo.restorestatus'. [SQLSTATE 42S02] (Error 208) Logical file '_datafile' is not part of database 'TMG2'. Use RESTORE FILELISTONLY to list the logical file names. [SQLSTATE 42000] (Error 3234) RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.

Please help this is doing my head in.

The database I am trying to restore from is called TMG and I would like to call the new Database TMG2Howdy

I suspect you have a filename problem - go to the options tab and make sure the filenames for the mdf & ldf files for TMG2 are unique to the server. Alter them if need be just before starting the restore.

The logical file names dont have to be unique.

Cheers,

SG.|||Hi,

TMG2 is not created as a database yet, I want the restore to create the new database.|||Hi again,

This is my code:

RESTORE FILELISTONLY
FROM Dumps_TMG_PM

RESTORE DATABASE TMG2 from Dumps_TMG_PM with RECOVERY,

MOVE 'TMG_dat.mdf' TO 'D:\Data\TMG2_data.mdf',
MOVE 'TMG_log.ldf' TO 'E:\TMG2_log.ldf'|||What does...

RESTORE FILIELISTONLY FROM DISK = '<pathname><dbname>.bak'

Give you?

Check out BOL for more examples?|||Hi,

It gives me this:

Executed as user: sa. Invalid object name 'msdb.dbo.restorestatus'. [SQLSTATE 42S02] (Error 208) Logical file 'tmg_dat.mdf' is not part of database 'TMG2'. Use RESTORE FILELISTONLY to list the logical file names. [SQLSTATE 42000] (Error 3234) RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.

Same error basically.|||That's from the restore...did you highlight an execute the command I gave you only?

If you just execute, it'll do the whole window...you are in QA, correct?

What did you type exactly?|||Brett .. what exactly does this table do ... 'msdb.dbo.restorestatus'... any idea. Is it created on the run time coz my msdb also does not have this object (table or sp or whatever)|||TMG_dat I:\Data\tmg_data.mdf D PRIMARY 6815744000 35184372080640
TMG_log k:\TMG_log.LDF L NULL 1867776000 7340032000

IN QA this is what it gave me.|||How about:

RESTORE DATABASE TMG2
FROM DISK = '<database dump path>\TMG.dmp'
WITH MOVE 'TMG_Data' TO 'D:\Data\TMG.MDF'
, MOVE 'TMG_Log' TO 'E:\TMG_Log.LDF'
, REPLACE

And I have no idea about that system table...

and what version of sql are we talking about?|||Originally posted by SQLSlammer
TMG_dat I:\Data\tmg_data.mdf D PRIMARY 6815744000 35184372080640
TMG_log k:\TMG_log.LDF L NULL 1867776000 7340032000

IN QA this is what it gave me.

try this command :

restore database TMG2 from disk = '<pathname><dbname>.bak'
with move 'TMG_dat' to ''<pathname>tmg2_data.mdf',
move 'TMG_log' to ''<pathname>tmg2_log.ldf'|||Originally posted by SQLSlammer
TMG_dat I:\Data\tmg_data.mdf D PRIMARY 6815744000 35184372080640
TMG_log k:\TMG_log.LDF L NULL 1867776000 7340032000

IN QA this is what it gave me.

try this command :

restore database TMG2 from disk = '<pathname><dbname>.bak'
with move 'TMG_dat' to ''<pathname>tmg2_data.mdf',
move 'TMG_log' to ''<pathname>tmg2_log.ldf'|||Sorry chaps still gives me the same error, by the way this database does not exist yet.

There is already a database called TMG on this server, but it is in warm standby mode as it is being log shipped.|||It doesn't have to exist...

my bad btw

RESTORE DATABASE TMG2
FROM DISK = '<database dump path>\TMG.dmp'
WITH MOVE 'TMG_Data' TO 'I:\Data\TMG.MDF'
, MOVE 'TMG_Log' TO '\K:\TMG_Log.LDF'
, REPLACE

Cut and paste that...it should work...|||Hi in QA it came up with this:

Server: Msg 3234, Level 16, State 2, Line 1
Logical file 'TMG_Data' is not part of database 'TMG2'. Use RESTORE FILELISTONLY to list the logical file names.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Same thing........this is doing my head in!

Thanks for all your help so far chaps|||Originally posted by Brett Kaiser
It doesn't have to exist...

my bad btw

RESTORE DATABASE TMG2
FROM DISK = '<database dump path>\TMG.dmp'
WITH MOVE 'TMG_Data' TO 'I:\Data\TMG.MDF'
, MOVE 'TMG_Log' TO '\K:\TMG_Log.LDF'
, REPLACE

Cut and paste that...it should work...

Your Bad again .. Brett !!!

RESTORE DATABASE TMG2
FROM DISK = '<database dump path>\TMG.dmp'
WITH MOVE 'TMG_Dat' TO 'I:\Data\TMG.MDF'
, MOVE 'TMG_Log' TO '\K:\TMG_Log.LDF'
, REPLACE|||Good catch...thanks...

But let me ask you...do ever set it up as dat?

dat is da funnyest ting I even sawed...|||Originally posted by Brett Kaiser
dat is da funnyest ting I even sawed...

it is dat same case here :)|||Guys,

dat was the problem! lol

Thanks very much for your help,

Take it easy!|||Guys,

dat was the problem! lol

Thanks very much for your help,

Take it easy!|||Run this first:
--Query_1:

restore headeronly from Logical_device_name

Get the position fo the backup set you want to restore.

Next as stated in the responses above, use:

--Query_2:

restore filelistonly from Logical_device_name

Get the database filenames.

RESTORE DATABASE TMG2
FROM DISK = '<database dump path>\TMG.bak'
,WITH file = 'position from Query_1', RECOVERY, REPLACE,
MOVE 'TMG_Data' TO 'I:\Data\TMG.MDF'
,MOVE 'TMG_Log' TO 'K:\TMG_Log.LDF'
,Move 'TMG_File_n' to 'Drive:...\SomeName.ndf or ldf as the case may be'

Items after the keyword 'MOVE' are obtained from Query_2.

Make sure, you have sufficient space on your discs.|||Can you specify a different location to restore to?

For instance:

MOVE 'TMG_Data' TO 'J:\Data\TMG.MDF'
,MOVE 'TMG_Log' TO 'R:\TMG_Log.LDF'
,Move 'TMG_File_n' to 'Drive:...\SomeName.ndf or ldf as the case may be'

where J and R are on a diffrent server than where it was backed up from?

The problem I am having is I get his

[Microsoft][ODBC SQL Server Driver]Unspecified error occurred on SQL Server. Connection may have been terminated by the server.
Server: Msg 3158, Level 16, State 1, Line 1
Could not create one or more files. Consider using the WITH MOVE option to identify valid locations.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Connection Broken