February 18, 2013 at 5:06 am
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.
February 18, 2013 at 8:04 am
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" 😉
February 19, 2013 at 9:07 am
Hi,
How would you handle moving a big database(say 200GB) to another drive with minimal time on a production server??
--
Regards,
Karthik
February 19, 2013 at 11:15 am
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" 😉
February 20, 2013 at 12:16 am
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
May 17, 2013 at 8:58 am
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
May 17, 2013 at 10:50 pm
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.....
May 17, 2013 at 10:55 pm
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.....
May 17, 2013 at 11:09 pm
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" 😉
May 17, 2013 at 11:12 pm
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" 😉
May 17, 2013 at 11:23 pm
Perry Whittle (5/17/2013)
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.
I was actually referring to use the Backup/Restore method over the Attach/Detach method. As has been pointed out to me, simply using the latter method can result in an inconsistent database because the Transaction log is missing. This means that when the instance/database starts up and recovery is performed, uncommitted Transactions cannot be rolled back. This is where the database becomes inconsistent and Problems start to occur.
If the attach/detach method really must be used, then it should be as a last Resort or when the admin knows 100% that consistency can be guaranteed.
May 17, 2013 at 11:25 pm
Guess who didn't read the article BEFORE posting comments....?
Sorry Perry....
Viewing 12 posts - 31 through 41 (of 41 total)
You must be logged in to reply to this topic. Login to reply