restore backup - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

restore backup

Author

Topic

SHARI2008
Starting Member

4 Posts

Posted - 2011-01-06 : 02:00:56

i have windows 2003 server , sql 2008 server installed. i tried to restore the data from a backup file. i tried to restore it with the following query:

RESTORE DATABASE [mm.Restored]
FROM DISK = '\\BACKUPS\DB\mm_db_200601010000.BAK'
WITH NORECOVERY,
MOVE 'mm.Primary' TO 'E:\MSSQL\Data\mm\mm.mdf',
MOVE 'mm.Aggregate' TO 'E:\MSSQL\Data\mm\mm.Aggregate.ndf',
MOVE 'mm.CDRQueue' TO 'E:\MSSQL\Data\mm\mm.CDRQueue.ndf',
MOVE 'mm.CDRRated' TO 'E:\MSSQL\Data\mm\mm.CDRRated.ndf',
MOVE 'mm.CDRRatedCharge' TO 'E:\MSSQL\Data\mm\mm.CDRRatedCharge.ndf',
MOVE 'mm.CDRRatedSuppCharge' TO 'E:\MSSQL\Data\mm\mm.CDRRatedSuppCharge.ndf',
MOVE 'mm.CDRSuspense' TO 'E:\MSSQL\Data\mm\mm.CDRSuspense.ndf',
MOVE 'mm.CDRUnbillable' TO 'E:\MSSQL\Data\mm\mm.CDRUnbillable.ndf',
MOVE 'mm.CDRWork' TO 'E:\MSSQL\Data\mm\mm.CDRWork.ndf',
MOVE 'mm.CDRWorkCharge' TO 'E:\MSSQL\Data\mm\mm.CDRWorkCharge.ndf',
MOVE 'mm.General' TO 'E:\MSSQL\Data\mm\mm.General.ndf',
MOVE 'mm.Reference' TO 'E:\MSSQL\Data\mm\mm.Reference.ndf',
MOVE 'mm.Log' TO 'E:\MSSQL\Data\mm\mm.ldf'

i then received the following error:

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

kindly HELP

Kristen
Test

22859 Posts

Posted - 2011-01-06 : 02:38:08

"Use RESTORE FILELISTONLY to list the logical file names"

That's the bit you need to do

Step-by-step guide here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=54300#214941

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-01-06 : 04:21:32

Here is a script to generate the restore command automatically (2008-version at the bottom):

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=101995

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

SHARI2008
Starting Member

4 Posts

Posted - 2011-01-15 : 06:55:52

Thank you that was very helpful... i have another question.
I have restore reporting server from abackup that is 2 weeks old and it
was successful. However, now when I try to run any of the reports that are
displayed, I get the following error:
An internal error occurred on the report server. See the error log for more details.
For more information about this error navigate to the report server on the local server machine, or enable remote errors

kindly advice

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-01-15 : 11:39:04

"See the error log for more details."

Did you? What does the report server error log say?

--
Gail Shaw
SQL Server MVP

SHARI2008
Starting Member

4 Posts

Posted - 2011-01-16 : 00:32:53

the error log gives the following message:

Login failed for user 'x'. Reason: Failed to open the explicitly specified database. [CLIENT: ]

Please note that client IP given is the IP of a different server, that it takes information from.

Thanks in advance

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-01-16 : 05:11:37

What's that login's default database? Can you find out what database it's trying to connect to?

Either you have orphaned users after the restore, or the user doesn't exist in the restored database or the database name has changed.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources