How to create backup using Mdf and ldf files to another location

Question

Post reply

How to create backup using Mdf and ldf files to another location

poornima.s_pdi

SSCrazy

Points: 2720
More actions
May 25, 2017 at 9:00 pm

#321743

Hi,
I took backup of mdf and ldf file in another location.
But when i try to attach the files using the following code,
CREATE DATABASE MYDB ON (FILENAME='E:\MYDB.MDF'), (FILENAME='E:\MYDB.LDF') FOR ATTACH;
I got the following error,
Msg 5120, Level 16, State 101, Line 1 Unable to open the physical file "E:\MYDB.MDF". Operating system error 5: "5(Access is denied.)".
How to attach the file to the DB? Am I doing anything wrong while backup?
I just copy 2 files from the original location (C:\) and try to paste in to (E:\) after detaching the Database.
It shows " You 'll need to rovide administrator permission to copy this file". I just gave continue and pasted the file in (E:\).
Please help me to attach the backup file to the database
Regards,
Poornima

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

NorthernSoul SSCertifiable Points: 6869 More actions · Answer 1

It just looks like a permissions issue. Does the account you are using have access to E:\MYDB?

Thanks

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

Poornima

Yes, the SQL Server service account needs to have permission to access the files. I think the problem may be to do with your attempt to create a database in the root of a drive. I seem to remember trying to create a database with files in the root a while ago, and only succeeding when I moved the files to a subfolder. The real question, though, is why are you doing this with attach and not with backup and restore? How did you get those database files - did you detach the database offline first, or did you stop the SQL Server service?

John

poornima.s_pdi SSCrazy Points: 2720 More actions · Answer 3

Hi,

Thanks for your reply.
Yes I am taking Database offfline and detaching from DB then I copy MDF,LDF files and paste into another location.For eg) From C - drive to E-drive.
While pasting it shows "You 'll need to provide administrator permission to copy this file".
I created my Database using Sqlserver Authentication using 'sa'.
But still the problem exists.

Regards,
Poornima

Ed Wagner SSC Guru Points: 287024 More actions · Answer 4

You don't have to take the database offline to create a copy of it. Take a full backup of the database. Then, restore from the backup to a different database name. All permissions, along with everything else, will remain intact.

If you restore the copy to a different instance, the users associated with SQL logins (as opposed to AD logins) will have a SID mismatch and need to be fixed with sp_change_users_login.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

poornima.s_pdi - Sunday, May 28, 2017 8:13 PM
Yes I am taking Database offfline and detaching from DB then I copy MDF,LDF files and paste into another location.For eg) From C - drive to E-drive.

Why?

I created my Database using Sqlserver Authentication using 'sa'.
But still the problem exists.

Because the account under which SQL Server is running does not have permission to access files in that location.
Don't put database files in the root folders of drives, and if you put the files in a new location, you'll have to give the account under which SQL Server is running permission to that location.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Brandie Tarvin SSC Guru Points: 172994 More actions · Answer 6

poornima.s_pdi - Thursday, May 25, 2017 9:00 PM
Hi,
I took backup of mdf and ldf file in another location.
But when i try to attach the files using the following code,
CREATE DATABASE MYDB ON (FILENAME='E:\MYDB.MDF'), (FILENAME='E:\MYDB.LDF') FOR ATTACH;
I got the following error,
Msg 5120, Level 16, State 101, Line 1 Unable to open the physical file "E:\MYDB.MDF". Operating system error 5: "5(Access is denied.)".

FYI: You should never back up .MDF and .LDF files like that. That's really really bad practice because if you forget to turn off your service, then you'll never have a good backup. Plus you leave open the possibility of forgetting to turn the service back on or causing other issues like accidentally overwriting the original files while they're "unprotected" with the service account off.

EDIT: removed first line when I realized this point had already been raised and answered.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

poornima.s_pdi SSCrazy Points: 2720 More actions · Answer 7

Thank you very much for your replies.
I try doing the whole backup instead of MDF and LDF.
Thanks a lot.

Regards,
Poornima