Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Moving Database Files in SQL Server


Moving Database Files in SQL Server

Author
Message
abacrotto
abacrotto
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 61
Hello, all!!! I liked the post. A lot. And I have a question regarding filestream. How do I move a filestream datafile ? Can I move only one datafile instead of all of them al once ?

Thanks in advance from Argentina.

Ariel.
Perry Whittle
Perry Whittle
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8772 Visits: 16543
you need to move the folder contents under the folder path that contains the file stream data. Check books online for more info on the alter database command

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

"Ya can't make an omelette without breaking just a few eggs" ;-)
karthiks.840
karthiks.840
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 93
Hi,
How would you handle moving a big database(say 200GB) to another drive with minimal time on a production server??

--
Regards,
Karthik
Perry Whittle
Perry Whittle
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8772 Visits: 16543
Whatever happens you will need to release the sql server handles on the disk file(s), what capacity do you have to copy 200gb of database files across the drives? You need to factor this time into the equation.

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

"Ya can't make an omelette without breaking just a few eggs" ;-)
karthiks.840
karthiks.840
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 93
Just a thought... not sure if this would work..
but is there any software that can create another copy of these db files irrespective of the handles present on it.. so that once it copies (say upto 95%), we can use the process you mentioned in the post, and copying the left over 5% shouldn't take much time.

Can we do something like this?

Am just curious if this can be done.
[http://lockhunter.com/ : This completely unlocks and deletes the files]

Is there anything which would copy the file even when there is a lock on it?

--
Regards,
Karthik
john.knight-557608
john.knight-557608
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 71
Nice article Pezza.

I went through a process just recently of renaming and moving a DB and used the old method of detach/attach, next time round I'll give this way a go.

Good work on another fine article.

Johnny
kevaburg
kevaburg
Mr or Mrs. 500
Mr or Mrs. 500 (555 reputation)Mr or Mrs. 500 (555 reputation)Mr or Mrs. 500 (555 reputation)Mr or Mrs. 500 (555 reputation)Mr or Mrs. 500 (555 reputation)Mr or Mrs. 500 (555 reputation)Mr or Mrs. 500 (555 reputation)Mr or Mrs. 500 (555 reputation)

Group: General Forum Members
Points: 555 Visits: 912
john.knight-557608 (5/17/2013)
Nice article Pezza.

I went through a process just recently of renaming and moving a DB and used the old method of detach/attach, next time round I'll give this way a go.

Good work on another fine article.

Johnny


Be careful when you use this method. With a "simple" detach/attach you lose the Transaction log. Better to do a backup and restore.....
kevaburg
kevaburg
Mr or Mrs. 500
Mr or Mrs. 500 (555 reputation)Mr or Mrs. 500 (555 reputation)Mr or Mrs. 500 (555 reputation)Mr or Mrs. 500 (555 reputation)Mr or Mrs. 500 (555 reputation)Mr or Mrs. 500 (555 reputation)Mr or Mrs. 500 (555 reputation)Mr or Mrs. 500 (555 reputation)

Group: General Forum Members
Points: 555 Visits: 912
karthiks.840 (2/19/2013)
Hi,
How would you handle moving a big database(say 200GB) to another drive with minimal time on a production server??

--
Regards,
Karthik


An option, should the SQL Server Versions be the same, is to mirror the database across to the new Server and once it is running as it should be, copy the users, Jobs, maintenance plans etc; across to the mirror from the principal. Once prepared, perform a switchover to the new machine and the move is complete with the very Minimum of downtime, measured in seconds. Users will be disconnected for the period of the switchover but that is easily planned for.

Of course, in order to do this you Need to be using the Enterprise Version of the Software.....
Perry Whittle
Perry Whittle
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8772 Visits: 16543
kevaburg (5/17/2013)
Better to do a backup and restore.....

Hmm, no, as my article shows the best and supported way to move your data files is to use the alter database command. Doing this, the database is not detached from the server at all.

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

"Ya can't make an omelette without breaking just a few eggs" ;-)
Perry Whittle
Perry Whittle
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8772 Visits: 16543
kevaburg (5/17/2013)
Of course, in order to do this you Need to be using the Enterprise Version of the Software.....

On a slightly different plain to my article as this is moving a database across instances. The synchronous mirroring in standard edition will achieve the same results.

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

"Ya can't make an omelette without breaking just a few eggs" ;-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search