Moving DB file

Question

Moving DB file

Nita Reddy

SSCarpal Tunnel

Points: 4182
More actions
January 12, 2022 at 5:38 pm

#3972464

Can I attach DB in another server on different drive??
For Example Server: AAA , Database Name: DB_1 the MDF and LDF file is located on C drive
Now I want this data file to copy on another Server: BBB on D drive and than attach the DB.
How Can I do this?? It didn't allow me to attach on D drive and it does allow me to attach from C drive.

Viewing 10 posts - 1 through 9 (of 9 total)

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

Ken McKelvey SSCoach Points: 18856 More actions · Answer 1

USE [master];
GO
CREATE DATABASE YourDB
ON 
   (FILENAME = N'D:\YourPath\DB_1.mdf')
   ,(FILENAME = N'D:\YourPath\DB_1.ldf')
FOR ATTACH;
GO

Nita Reddy SSCarpal Tunnel Points: 4182 More actions · Answer 2

Nita Reddy

SSCarpal Tunnel

Points: 4182

January 12, 2022 at 7:52 pm

#3972513

This is not working

Ant-Green SSC Guru Points: 113507 More actions · Answer 3

Please post error messages for what is not working.

More than likely you have not given permissions to the service account to the location needed.

Nita Reddy SSCarpal Tunnel Points: 4182 More actions · Answer 4

I am trying to copy MDF and LDF file from one server to another.

Initial On primary the DB was on C drive and on new server I was trying to attach on D drive

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 5

Nita Reddy wrote:

I am trying to copy MDF and LDF file from one server to another.
Initial On primary the DB was on C drive and on new server I was trying to attach on D drive

You never posted the requested error messages. You DO realize that you need to change the words "YourPath" and the names of the files and the name of the DB to attach in the correct example that Ken McKelvey posted, yes?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Johan Bijnens SSC Guru Points: 135376 More actions · Answer 6

also keep in mind, the SQLServer service account needs to be granted usage of the given paths !

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Nita Reddy SSCarpal Tunnel Points: 4182 More actions · Answer 7

This is what the error I got

USE [master]

GO

CREATE DATABASE [DBTest] ON

( FILENAME = N'D:\SQL_Data\DBTest.mdf' ),

( FILENAME = N'D:\SQL_Log\DBTest.ldf' )

FOR ATTACH

GO

ERROR Message: (Though I have full access to d drive and I can create any other DB on D drive)

Msg 5120, Level 16, State 101, Line 3

Unable to open the physical file "D:\SQL_Data\DBTest.mdf". Operating system error 5: "5(Access is denied.)".

Msg 1802, Level 16, State 7, Line 3

CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

ratbak Ten Centuries Points: 1382 More actions · Answer 8

As others have already mentioned, it is the SQL Server service account that needs permission to that folder.

Permissions aren't always inherited from the parent -- sometimes they get changed from the parent folder. Just because permissions exist on D: doesn't mean they are granted on D:\SQL_Data... or the individual files in D:\SQL_Data.

I'm assuming you have already copied the files to that folder.

When you right click on the D:\SQL_Data folder & view properties - Security, is the account under which SQL Server is executing (probably MSSQLSERVER unless you changed it on install or it's a named instance) present, and does it have Full Control? If not, you will need to add it. For MSSQLSERVER, you will have to type in NT SERVICE\MSSQLSERVER for a default instance, or NT SERVICE\MSSQL$InstanceName (using real instance name).
Likewise for the data & log files -- permissions may not be inherited from the folder, especially if copied from elsewhere. Does SQL Server account have Full Control on DBTest.mdf & DBTest.ldf?

Steve Jones - SSC Editor SSC Guru Points: 736479 More actions · Answer 9

To clarify the issue, you, or your account, is not attaching the database on the instance. You are requesting the SQL Server Database Engine do the attach. The database service, which is running under a service account, is what needs permissions.

The post above checks for the default service accounts, but it's possible some other account is being used. You would check this under the Services applet in Windows.