asked
restore of the database is not working correctly
UMS.ndf' is claimed by 'UMSNET_Relational'(4) and 'UMSNET_Entity'(3). The WITH MOVE clause can be used to relocate one or more files. Msg 3176, Level 16, State 1, Line 1 File 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\UMS.ndf' is claimed by 'UMSNET_Reference'(5) and 'UMSNET_Entity'(3). The WITH MOVE clause can be used to relocate one or more files. Msg 3176, Level 16, State 1, Line 1 File 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\UMS.ndf' is claimed by 'UMSNET_Note'(6) and 'UMSNET_Entity'(3). The WITH MOVE clause can be used to relocate one or more files. Msg 3176, Level 16, State 1, Line 1 File 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\UMS.ndf' is claimed by 'UMSNET_Transaction'(7) and 'UMSNET_Entity'(3). The WITH MOVE clause can be used to relocate one or more files. Msg 3176, Level 16, State 1, Line 1 File 'C:\Program Files\Microsoft SQL UMS.ndf' is claimed by 'UMSNET_System'(8) and 'UMSNET_Entity'(3). The WITH MOVE clause can be used to relocate one or more files. Msg 3176, Level 16, State 1, Line 1 File 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\UMS.ndf' is claimed by 'UMSNET_Audit'(9) and 'UMSNET_Entity'(3). The WITH MOVE clause can be used to relocate one or more files. Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally.
ASK SQL Server Central runs by you voting. For all helpful answers below, please indicate this by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate this by clicking on the check mark next that answer.
It appears you are trying to restore over a live database. Is this what you are attempting to do on purpose? If you need to overwrite an existing database you can chose WITH REPLACE or you can chose to set the existing database offline first and disconnect any users with ALTER DATABASE DB_NAME SET OFFLINE WITH ROLLBACK IMMEDIATE. With that being said, if you are going to over write an existing database and it is production, please ensure you have backed up the tail end of the transaction log first. Now if you are trying to restore a copy of the database with a new name, simply change the name in the WITH MOVE statements to give each file a unique name or change the path to another folder to isolate each file name.
I am not doing this on
purpose.My coworker thinks that c drive might be out of space. He thinks trying to move the files to other disk and then try restoring again.
Ok, then you are trying to restore over a live database. You will need to first shut down the application server to stop any new data modifications. You can also use backup log db_name to disk = 'drive:\path\filename.bak' WITH NO_RECOVERY. This will take a tail log backup and put the database in a recovering state to ensure no more changes are being made. Then restore the database using WITH REPLACE and specify the new path for the data and log files if you are most comfortable with backup and restore. I also encourage you though, if this is just for moving the files off of the C:\ to consider a detach/attach process. Microsoft documents that process [here](
https://support.microsoft.com/en-us/kb/224071)
As @Tim mentions, it looks like you are trying to overwrite an existing database, if that's not your intention then I'd ensure you understand what your scripts are doing first and foremost - understanding what a script does first is key to not killing your environment :) I'd be inclined to move your db's anyway, having database data and log files on the c drive isn't ideal, they should be on their own volume really. A few reasons for this but not filling up your c drive is one of them. Check out [this post][1], there are some examples of how to move your database and some scripts to make it all easier too: [moving-user-database][1] Hope that helps resolve your issue, it should give you the options you need. [1]:
http://devondba.blogspot.co.uk/2013/11/moving-user-database.html
17 People are following this question.
