SQLServerCentral / Discuss content posted by Jorge Novo / Article Discussions / Article Discussions by Author / Move Physical Files / Latest Posts

RE: Move Physical Files

opc.three — Mon, 09 Apr 2012 12:11:28 GMT

[quote][b]Perry Whittle (4/9/2012)[/b][hr][quote][b]opc.three (4/9/2012)[/b][hr]If I have two data files in my database, both on D:\, and I want to move one to E:\ and one to F:\, if I run an alter to move one to E:\ but I forget to move the other one to F:\ the database will happily come online with one data file on D:\ and one on E:\.)[/quote]if you left the old file in place yes it would. If you moved the file instead of copying it would fail to come online.[/quote]I never said otherwise.[quote]Look it's your opinion and you're entitled to it[/quote]I feel like I should thank you for allowing me my opinion :hehe:[quote]at the end of the day it's unnecessary to go single user first just take the data straight offline.[/quote]Agreed (note: this is the second time I am agreeing with you on this point).

RE: Move Physical Files

Perry Whittle — Mon, 09 Apr 2012 12:07:51 GMT

[quote][b]opc.three (4/9/2012)[/b][hr]If I have two data files in my database, both on D:\, and I want to move one to E:\ and one to F:\, if I run an alter to move one to E:\ but I forget to move the other one to F:\ the database will happily come online with one data file on D:\ and one on E:\.)[/quote]if you left the old file in place yes it would. If you moved the file instead of copying it would fail to come online.Look it's your opinion and you're entitled to it, at the end of the day it's unnecessary to go single user first just take the data straight offline.

RE: Move Physical Files

opc.three — Mon, 09 Apr 2012 12:02:44 GMT

[quote][b]Perry Whittle (4/9/2012)[/b][hr][quote][b]opc.three (4/9/2012)[/b][hr]however what if you had multiple files to move and missed one?[/quote]The database would not come online!The only time this would differ in any way is with file group restores but that's a whole different scenario and not even relevant to this post\script.[/quote]Sure it would. If I have two data files in my database, both on D:\, and I want to move one to E:\ and one to F:\, if I run an alter to move one to E:\ but I forget to move the other one to F:\ the database will happily come online with one data file on D:\ and one on E:\.A verification step would help me see if the desired outcome were achieved...sometimes I find it useful to write the verification checks at a completely separate time from when I write the code to make such changes...sometimes even before writing the code to make the changes ;-)

RE: Move Physical Files

Perry Whittle — Mon, 09 Apr 2012 11:57:49 GMT

[quote][b]opc.three (4/9/2012)[/b][hr]however what if you had multiple files to move and missed one?[/quote]The database would not come online!The only time this would differ in any way is with file group restores but that's a whole different scenario and not even relevant to this post\script.I always issue my move commands before offlining the database that's the preferred\tested method for me.

RE: Move Physical Files

opc.three — Mon, 09 Apr 2012 11:48:13 GMT

[quote][b]Perry Whittle (4/9/2012)[/b][hr]If you'd bothered to read the code in the post it reads as follows[code="SQL"]alter database DatabaseName set single_user with rollback immediatego-- Set databae Offlinealter database DatabaseName set Offlinego--- Cut and paste the files from the original location to the new location-- Be sure to write the name and new path exactly alter database DatabaseName Modify file (Name = logicalName,Filename = 'DriveLetter:ewPath\DataFileName.mdf/ndf or ldf')--Create one per every filego---Set database backonlinealter database DatabaseName set Onlinego--Set Database back to multi_useralter database DatabaseName set multi_user[/code]In this context setting the database single user is unnecessary since it uses rollback immediate anyway. I don't need to verify a file move, if I got it wrong the database will not start, then i'll go check sys.master_files, which incidentally resides in master so whats the point of keeping the database single user. What if you're not the user who gets the first connection?Maybe you'd like to point out exactly what it is you verify? If sys.master_files has an incorrect path, the database will not start![/quote]I did read the code. Did you read my post? I noted that you could skip setting it to single_user before taking it offline, as you had shown it, and that setting the database to single_user could be done while the database was offline. Granted, verifying sys.master_files may not uncover anything interesting, however what if you had multiple files to move and missed one? I am not sure why you are arguing against a verification step. My comment regarding running any proprietary scripts to exercise functionality and data before turning it over to users still stands as well. It's standard practice, and one I would not forego, even for the described change where the perceived risk is low.

RE: Move Physical Files

Perry Whittle — Mon, 09 Apr 2012 11:41:00 GMT

If you'd bothered to read the code in the post it reads as follows[code="SQL"]alter database DatabaseName set single_user with rollback immediatego-- Set databae Offlinealter database DatabaseName set Offlinego--- Cut and paste the files from the original location to the new location-- Be sure to write the name and new path exactly alter database DatabaseName Modify file (Name = logicalName,Filename = 'DriveLetter:ewPath\DataFileName.mdf/ndf or ldf')--Create one per every filego---Set database backonlinealter database DatabaseName set Onlinego--Set Database back to multi_useralter database DatabaseName set multi_user[/code]In this context setting the database single user is unnecessary since it uses rollback immediate anyway. I don't need to verify a file move, if I got it wrong the database will not start, then i'll go check sys.master_files, which incidentally resides in master so whats the point of keeping the database single user. What if you're not the user who gets the first connection?Maybe you'd like to point out exactly what it is you verify? If sys.master_files has an incorrect path, the database will not start!

RE: Move Physical Files

opc.three — Mon, 09 Apr 2012 11:29:22 GMT

[quote][b]Perry Whittle (4/9/2012)[/b][hr][quote][b]opc.three (4/9/2012)[/b][hr]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[/quote]What verification checks?The database will either come online or fail if the path\filename is not correct ;-)[/quote]Basically anything the admin wants to verify before allowing users back into the database. A check to make sure the file paths look good in sys.master_files is a good one that comes to mind for this scenario. It's also a good idea to have a script to exercise basic functions and data in a database before turning it over to users. I have one for each of my databases that support user applications. Do you not have verification scripts for your databases? Or do you just assume that if a database is online that everything must be in working order?

RE: Move Physical Files

Perry Whittle — Mon, 09 Apr 2012 11:04:45 GMT

[quote][b]opc.three (4/9/2012)[/b][hr]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[/quote]What verification checks?The database will either come online or fail if the path\filename is not correct ;-)

RE: Move Physical Files

opc.three — Mon, 09 Apr 2012 09:38:37 GMT

[quote][b]Perry Whittle (3/30/2012)[/b][hr]You shouldn't need to set the database single user first, just issue the following[code="sql"]alter database set offline with rollback immediate[/Code][/quote]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.

RE: Move Physical Files

Perry Whittle — Fri, 30 Mar 2012 01:10:31 GMT

You shouldn't need to set the database single user first, just issue the following[code="sql"]alter database set offline with rollback immediate[/Code]

RE: Move Physical Files

Janie.Carlisle — Tue, 30 Sep 2008 15:46:22 GMT

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:

RE: Move Physical Files

Jorge Novo-@ETLDEVDBA — Tue, 30 Sep 2008 14:45:27 GMT

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.

RE: Move Physical Files

Janie.Carlisle — Tue, 30 Sep 2008 08:56:29 GMT

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.

RE: Move Physical Files

george sibbald — Sun, 01 Jun 2008 05:46:55 GMT

......and if doing this type of process good to do a backup just before just in case..............:)

RE: Move Physical Files

Jorge Novo-@ETLDEVDBA — Sat, 31 May 2008 22:04:17 GMT

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

RE: Move Physical Files

Charles Kincaid — Fri, 30 May 2008 07:59:21 GMT

Good work. This puts it all together. Much better than the detach / move files / attach process that I've been using.

Move Physical Files

Jorge Novo-@ETLDEVDBA — Tue, 22 Apr 2008 12:50:32 GMT

Comments posted to this topic are about the item [B]Move Physical Files [/B]