The WITH MOVE clause can be used to relocate one o

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)

The WITH MOVE clause can be used to relocate one o

Author

Topic

pramod21
Yak Posting Veteran

64 Posts

Posted - 2011-05-19 : 01:38:26

I have issues while restoring database from .bak file.

This .bak file contains .ndf file which serves dependency of two objects. While resotring .ndf file i'm getting following erroe

The WITH MOVE clause can be used to relocate one or more files.
Msg 3013, Level 16, State 1, Line 14

here is the code:

RESTORE FILELISTONLY
FROM DISK = 'E:\sp_script\fullbackup.bak'

ALTER DATABASE rule_database
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE

ALTER DATABASE Rule_database SET MULTI_USER

restore database rule_database
FROM DISK = 'E:\sp_script\fullbackup.bak'

With Replace,
Move 'Rule_database' To 'E:\sp_script\MAY 2011\Rule_database.mdf',
Move 'Tree'To 'E:\sp_script\MAY 2011\Rule_database.ndf',
Move 'Result'To 'E:\sp_script\MAY 2011\Rule_database.ndf',
Move 'Rule_database_log' To 'E:\sp_script\MAY 2011\Rule_database.ldf'

Kindly help me that asap.

Thanks in advance folks!!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-05-19 : 03:00:49

One or all of the files are in use while trying to restore the database.
try to add


USE MASTER
GO

And also try to do the

ALTER DATABASE Rule_database SET MULTI_USER

at the end and not before the restore command.

No, you're never too old to Yak'n'Roll if you're too young to die.

pramod21
Yak Posting Veteran

64 Posts

Posted - 2011-05-19 : 03:19:22

Okay..
One thing I wish to mention that Rule_database already exist in the Server.

@webfred.. thnx for the reply... but i'm still getting error :

Msg 3176, Level 16, State 1, Line 9
File 'E:\sp_script\MAY 2011\Rule_database.ndf' is claimed by 'Result'(5) and 'tree'(3). The WITH MOVE clause can be used to relocate one or more files.
Msg 3013, Level 16, State 1, Line 9
RESTORE DATABASE is terminating abnormally.

Kindly help with that.. Thnx

pramod21
Yak Posting Veteran

64 Posts

Posted - 2011-05-19 : 03:24:11

I tried followig code after deleting the database.. still no success:

RESTORE FILELISTONLY
FROM DISK = 'E:\sp_script\fullbackup.bak'

restore database rule_database
file = 'Rule_database',
file = 'Tree',
file = 'Result'
FROM DISK = 'E:\sp_script\fullbackup.bak' with replace,file = 1,
Move 'Rule_database' To 'E:\sp_script\MAY 2011\Rule_database.mdf',
Move 'Tree'To 'E:\sp_script\MAY 2011\Rule_database.ndf',
Move 'Result'To 'E:\sp_script\MAY 2011\Rule_database.ndf',
Move 'Rule_database_log' To 'E:\sp_script\MAY 2011\Rule_database.ldf'

,NOUNLOAD, STATS = 10

ALTER DATABASE Rule_database SET MULTI_USER

Error:

Msg 3176, Level 16, State 1, Line 9
File 'E:\sp_script\MAY 2011\Rule_database.ndf' is claimed by 'Result'(5) and 'tree'(3). The WITH MOVE clause can be used to relocate one or more files.
Msg 3013, Level 16, State 1, Line 9
RESTORE DATABASE is terminating abnormally.
Msg 5011, Level 14, State 5, Line 24
User does not have permission to alter database 'Rule_database', the database does not exist, or the database is not in a state that allows access checks.
Msg 5069, Level 16, State 1, Line 24
ALTER DATABASE statement failed.

I understnad the issues with alter error as database was not created...

Kindly help...

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-05-19 : 03:31:23

You're giving the same file name for two files.
Move 'Tree'To 'E:\sp_script\MAY 2011\Rule_database.ndf',
Move 'Result'To 'E:\sp_script\MAY 2011\Rule_database.ndf',

--
Gail Shaw
SQL Server MVP

pramod21
Yak Posting Veteran

64 Posts

Posted - 2011-05-19 : 05:17:01

after changing the file name, it is working............
thanks buddy........thanks a lot........

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-05-19 : 05:20:33

OMG - I should have seen that before...

@pramod21: Your first post wasn't showing the complete error message...

No, you're never too old to Yak'n'Roll if you're too young to die.

ngenoh
Starting Member

1 Post

Posted - 2012-02-10 : 10:16:38