SQL Clone
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
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 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
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19724 Visits: 17239
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 (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 103
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
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19724 Visits: 17239
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 (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 103
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
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1569 Visits: 988
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
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1569 Visits: 988
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
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19724 Visits: 17239
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
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19724 Visits: 17239
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