April 22, 2008 at 12:50 pm
Comments posted to this topic are about the item Move Physical Files
May 30, 2008 at 7:59 am
Good work. This puts it all together. Much better than the detach / move files / attach process that I've been using.
ATBCharles Kincaid
May 31, 2008 at 10:04 pm
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
June 1, 2008 at 5:46 am
......and if doing this type of process good to do a backup just before just in case..............:)
---------------------------------------------------------------------
September 30, 2008 at 8:56 am
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.
September 30, 2008 at 2:45 pm
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.
September 30, 2008 at 3:46 pm
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. 😎
March 30, 2012 at 1:10 am
You shouldn't need to set the database single user first, just issue the following
alter database set offline with rollback immediate
[/Code]
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 9, 2012 at 9:38 am
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
[/Code]
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
April 9, 2012 at 11:04 am
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" 😉
April 9, 2012 at 11:29 am
Perry Whittle (4/9/2012)
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 movedWhat verification checks?
The database will either come online or fail if the path\filename is not correct 😉
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?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 9, 2012 at 11:41 am
If you'd bothered to read the code in the post it reads as follows
alter database DatabaseName set single_user with rollback immediate
go
-- Set databae Offline
alter database DatabaseName set Offline
go
--- 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 file
go
---Set database backonline
alter database DatabaseName set Online
go
--Set Database back to multi_user
alter database DatabaseName set multi_user
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!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 9, 2012 at 11:48 am
Perry Whittle (4/9/2012)
If you'd bothered to read the code in the post it reads as follows
alter database DatabaseName set single_user with rollback immediate
go
-- Set databae Offline
alter database DatabaseName set Offline
go
--- 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 file
go
---Set database backonline
alter database DatabaseName set Online
go
--Set Database back to multi_user
alter database DatabaseName set multi_user
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!
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.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 9, 2012 at 11:57 am
opc.three (4/9/2012)
however what if you had multiple files to move and missed one?
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.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 9, 2012 at 12:02 pm
Perry Whittle (4/9/2012)
opc.three (4/9/2012)
however what if you had multiple files to move and missed one?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.
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 😉
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply