<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss content posted by Perry Whittle
»
Moving Database Files in SQL Server
42 posts, Page 4 of 5
««
«
1
2
3
4
5
»»
Moving Database Files in SQL Server
Rate Topic
Display Mode
Topic Options
Author
Message
abacrotto
abacrotto
Posted Monday, February 18, 2013 5:06 AM
Forum Newbie
Group: General Forum Members
Last Login: Saturday, March 23, 2013 7:08 AM
Points: 5,
Visits: 44
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.
Post #1421140
Perry Whittle
Perry Whittle
Posted Monday, February 18, 2013 8:04 AM
SSCertifiable
Group: General Forum Members
Last Login: Today @ 8:44 AM
Points: 5,204,
Visits: 11,165
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"
Post #1421236
karthiks.840
karthiks.840
Posted Tuesday, February 19, 2013 9:07 AM
Grasshopper
Group: General Forum Members
Last Login: Wednesday, May 22, 2013 8:29 AM
Points: 10,
Visits: 70
Hi,
How would you handle moving a big database(say 200GB) to another drive with minimal time on a production server??
--
Regards,
Karthik
Post #1421707
Perry Whittle
Perry Whittle
Posted Tuesday, February 19, 2013 11:15 AM
SSCertifiable
Group: General Forum Members
Last Login: Today @ 8:44 AM
Points: 5,204,
Visits: 11,165
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"
Post #1421781
karthiks.840
karthiks.840
Posted Wednesday, February 20, 2013 12:16 AM
Grasshopper
Group: General Forum Members
Last Login: Wednesday, May 22, 2013 8:29 AM
Points: 10,
Visits: 70
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
Post #1421941
john.knight-557608
john.knight-557608
Posted Friday, May 17, 2013 8:58 AM
Forum Newbie
Group: General Forum Members
Last Login: Monday, May 20, 2013 2:17 AM
Points: 8,
Visits: 64
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
Post #1454049
kevaburg
kevaburg
Posted Friday, May 17, 2013 10:50 PM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Yesterday @ 5:56 AM
Points: 159,
Visits: 193
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.....
Post #1454223
kevaburg
kevaburg
Posted Friday, May 17, 2013 10:55 PM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Yesterday @ 5:56 AM
Points: 159,
Visits: 193
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.....
Post #1454224
Perry Whittle
Perry Whittle
Posted Friday, May 17, 2013 11:09 PM
SSCertifiable
Group: General Forum Members
Last Login: Today @ 8:44 AM
Points: 5,204,
Visits: 11,165
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"
Post #1454229
Perry Whittle
Perry Whittle
Posted Friday, May 17, 2013 11:12 PM
SSCertifiable
Group: General Forum Members
Last Login: Today @ 8:44 AM
Points: 5,204,
Visits: 11,165
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"
Post #1454230
« Prev Topic
|
Next Topic »
42 posts, Page 4 of 5
««
«
1
2
3
4
5
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
