Click here to monitor SSC
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-Enthusiastic
SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)

Group: General Forum Members
Points: 155 Visits: 251
Comments posted to this topic are about the item Move Physical Files
Charles Kincaid
Charles Kincaid
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1119 Visits: 2383
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-Enthusiastic
SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)

Group: General Forum Members
Points: 155 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
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

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

---------------------------------------------------------------------
Janie.Carlisle
Janie.Carlisle
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 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-Enthusiastic
SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)

Group: General Forum Members
Points: 155 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 Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 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
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10854 Visits: 16801
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
SSCrazy Eights
SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)

Group: General Forum Members
Points: 9731 Visits: 14376
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
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10854 Visits: 16801
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