Changing Locations of DataFile

Question

Post reply

Changing Locations of DataFile

Viewing 10 posts - 61 through 69 (of 69 total)

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

OnlyOneRJ Hall of Fame Points: 3894 More actions · Answer 1

I did, Execute the Alter Datafile query

i got outut - the files are moved . next time when DB starts..

Fine enough..then i took the DB to OFfline state..

Now i am trying to copy the Old datafiles to the new location which i have set...

But error -

Access Denied, File may be currently in use :w00t:

i have admin rights on the drives & folders.. & nothing is open still its not allowing me to copy the files :w00t:

************************************
Every Dog has a Tail !!!!! :-D

Paul Clark-418949 SSC-Addicted Points: 428 More actions · Answer 2

runal_jagtap (11/29/2012)
I did, Execute the Alter Datafile query
i got outut - the files are moved . next time when DB starts..
Fine enough..then i took the DB to OFfline state..
Now i am trying to copy the Old datafiles to the new location which i have set...
But error -
Access Denied, File may be currently in use :w00t:
i have admin rights on the drives & folders.. & nothing is open still its not allowing me to copy the files :w00t:

Hiya,

Taking the Database Offline isn't good enough, this stops the database from being accessed but SQL Server is still accessing the file. You need to stop the SQL Server Service then move the files then start the SQL Server service again.

sanket kokane Hall of Fame Points: 3465 More actions · Answer 3

runal_jagtap (11/29/2012)
I did, Execute the Alter Datafile query
i got outut - the files are moved . next time when DB starts..
Fine enough..then i took the DB to OFfline state..
Now i am trying to copy the Old datafiles to the new location which i have set...
But error -
Access Denied, File may be currently in use :w00t:
i have admin rights on the drives & folders.. & nothing is open still its not allowing me to copy the files :w00t:

if your database is still accessible, then you have one more option to move your database files,

1)take the backup of the DB

2)Restore on the same server with different name and log file on different drive than data file with norecovery .

3)make sure that no user is connected to DB , and then take log backup and append it on newly created DB with recovery

4)Change old DB name , change the new DB name to original DB name .

I hope thats not much confusing,it will also minimize your downtime

-----------------------------------------------------------------------------
संकेत कोकणे

OnlyOneRJ Hall of Fame Points: 3894 More actions · Answer 4

Good Idea if i have one or two DB's..

But there are 170 + Db's :w00t:

************************************
Every Dog has a Tail !!!!! :-D

Perry Whittle SSC Guru Points: 234013 More actions · Answer 5

runal_jagtap (11/29/2012)
But error -
Access Denied, File may be currently in use :w00t:
i have admin rights on the drives & folders.. & nothing is open still its not allowing me to copy the files :w00t:

Verify the ACLs on the folder, being an admin on the server does not gurantee that you can access the files if the ACLs have been changed. Right click the folder and select properties, then click the security tab.

Paul Clark-418949 (11/29/2012)
Taking the Database Offline isn't good enough,

Taking the database offline will allow you to copy, move or even delete the database files, it's my guess there are ACLs stopping the user copying the file.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Paul Clark-418949 SSC-Addicted Points: 428 More actions · Answer 6

Runal,

See my post, above sanket's.The document I provided you does say you need to stop the SQL Server service before moving the physical files.

Taking the Database Offline only makes the Database inaccessible. SQL Server will still have a lock on the physical file.

So...

1.) Stop the SQL Server Service.

2.) Move the physical files.

3.) Start the SQL Server Service.

Regards

Paul

OnlyOneRJ Hall of Fame Points: 3894 More actions · Answer 7

thanks Perry, great paul 🙂 It worked

************************************
Every Dog has a Tail !!!!! :-D

Perry Whittle SSC Guru Points: 234013 More actions · Answer 8

Paul Clark-418949 (11/29/2012)
Taking the Database Offline only makes the Database inaccessible. SQL Server will still have a lock on the files

No it doesn't! This is an action I have performed more times than I care to remember, offlining the database will release any hold the engine had on the files, you may then copy move or even delete them!

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

sanket kokane Hall of Fame Points: 3465 More actions · Answer 9

runal_jagtap (11/29/2012)
Good Idea if i have one or two DB's..
But there are 170 + Db's :w00t:

is that your Production server ?

-----------------------------------------------------------------------------
संकेत कोकणे

OnlyOneRJ Hall of Fame Points: 3894 More actions · Answer 10

hi Perry,

No its the Development Server, i have done with moving of dsatafiles to other location only for One DB..In sometime i am going to do it for all

************************************
Every Dog has a Tail !!!!! :-D