SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Move Physical Files


Move Physical Files

Author
Message
Jorge Novo-@ETLDEVDBA
Jorge Novo-@ETLDEVDBA
SSC Veteran
SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)

Group: General Forum Members
Points: 209 Visits: 251
Comments posted to this topic are about the item Move Physical Files
Charles Kincaid
Charles Kincaid
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2133 Visits: 2384
Good work. This puts it all together. Much better than the detach / move files / attach process that I've been using.

ATBCharles Kincaid
Jorge Novo-@ETLDEVDBA
Jorge Novo-@ETLDEVDBA
SSC Veteran
SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)

Group: General Forum Members
Points: 209 Visits: 251
Thanks, this process can be used to many others uses, a cp files can be however, not many dba's like to have cmd other than sqlcmd runing on theirs boxes
george sibbald
george sibbald
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13044 Visits: 13695
......and if doing this type of process good to do a backup just before just in case..............Smile

---------------------------------------------------------------------
Janie.Carlisle
Janie.Carlisle
SSC Journeyman
SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)

Group: General Forum Members
Points: 81 Visits: 86
Just to be sure this is the best way to go about achieving what I need to accomplish; here's the scenario.

Today, I need to free up space on existing drives to make room for a ton of data that will be added this evening. It seems to me the best way to do this is to move a filegroup from Drive D [which has the least amount of space and will grow the most] to Drive F [the new empty drive]. Will your procedure work for this particular situation? Is there a better way [easier way] to do this? I am always amazed that you can't redirect to a new physical drive just as you do when you create a new filegroup but of course, that would be too easy. Thanks for input.
Jorge Novo-@ETLDEVDBA
Jorge Novo-@ETLDEVDBA
SSC Veteran
SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)

Group: General Forum Members
Points: 209 Visits: 251
Yes, it will work, however, before trying this is always good to create a backup of your database; also, it doesn't hurt to set the database to single_user and run the script within the same session.
Janie.Carlisle
Janie.Carlisle
SSC Journeyman
SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)

Group: General Forum Members
Points: 81 Visits: 86
Dude, you rock! The last time I did that it was an incredibly painful process; detach database; move everything over; redirect and redo indexes; double check permissions; reattach database. This was way more efficient and from the looks of things, everything worked without intervention - no recreating logins and permissions, no redirecting in code. Awesome! I am a happy person. Cool
Perry Whittle
Perry Whittle
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26679 Visits: 17338
You shouldn't need to set the database single user first, just issue the following


alter database set offline with rollback immediate



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

"Ya can't make an omelette without breaking just a few eggs" ;-)
Orlando Colamatteo
Orlando Colamatteo
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18999 Visits: 14398
Perry Whittle (3/30/2012)
You shouldn't need to set the database single user first, just issue the following


alter database set offline with rollback immediate


That would accomplish setting it offline and kicking out all users in one step, but having the database in single-user mode when it is brought online again is useful as it gives us a chance to run some verification checks on the database after the data files have been moved, and before others begin to use it. We can change the database to single-user mode while the database is offline though.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Perry Whittle
Perry Whittle
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26679 Visits: 17338
opc.three (4/9/2012)
but having the database in single-user mode when it is brought online again is useful as it gives us a chance to run some verification checks on the database after the data files have been moved

What verification checks?
The database will either come online or fail if the path\filename is not correct ;-)

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

"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