Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Moving files Expand / Collapse
Author
Message
Posted Thursday, August 25, 2011 5:50 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, October 24, 2012 8:17 PM
Points: 1,588, Visits: 247
Glad I just answered and got it correct. I almost convinced myself I was missing something and checked true. Thanks for the question.

http://brittcluff.blogspot.com/
Post #1165252
Posted Thursday, August 25, 2011 5:52 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:58 AM
Points: 5,077, Visits: 8,918
Data_God (8/25/2011)
I got this one wrong but only after I tried using the script below on my SQL Server 2008 R2 environment.

Could someone explain why the script below allows me to move a file between Filegroups?

The script removes the file from one file group and then adds it to another. I understand that the file is physically removed from the file system and then a new file is created on the file system when the script adds a file to the other filegroup but why do the select statements selecting data from Table_1 continue to work?


TABLE_1 was assigned to the filegroup, not to any specific file within that filegroup. That's why the table was still available when you removed a file from the filegroup. What you did, effectively, was not to move the file, but to remove it and then create a new one with exactly the same name, size and file name on a different filegroup.

Hope that makes sense!

John
Post #1165255
Posted Thursday, August 25, 2011 5:55 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, April 03, 2014 5:32 AM
Points: 1,735, Visits: 488
Yep, that makes sense.
Thanks
Post #1165257
Posted Thursday, August 25, 2011 6:07 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, April 04, 2014 8:53 AM
Points: 581, Visits: 721
Thanks for the question.
Post #1165264
Posted Thursday, August 25, 2011 6:17 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, May 01, 2013 6:14 AM
Points: 592, Visits: 1,423
Kwex (8/25/2011)
Did more people actually tick "True"???


Well, if many of them were like me, and read the question before coffee, and allowed one's brain to answer the question they *thought* was being asked rather than the one that was *actually* being asked, then yes...

Good morning, brain. The question said data files. It meant data files. Not some other thing that you substituted for data files in a fit of failing to think...


-Ki
Post #1165269
Posted Thursday, August 25, 2011 6:23 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 2:25 PM
Points: 1,253, Visits: 13,546
Data_God (8/25/2011)
I got this one wrong but only after I tried using the script below on my SQL Server 2008 R2 environment.

Could someone explain why the script below allows me to move a file between Filegroups?

The script removes the file from one file group and then adds it to another. I understand that the file is physically removed from the file system and then a new file is created on the file system when the script adds a file to the other filegroup but why do the select statements selecting data from Table_1 continue to work?
....


It's easy!!! you must include data in table and to try remove data file... and verify if this script works.



rfr.ferrari
DBA - SQL Server 2008
MCITP | MCTS

remember is live or suffer twice!
Post #1165274
Posted Thursday, August 25, 2011 6:35 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 2:25 PM
Points: 1,253, Visits: 13,546
Data_God,

the structure of table is allocated on primary filegroup or mdf file!!!! when you do references 'on filegroup .....' on table, you are saying: "the DATA/Records will allocated in filegroup"



rfr.ferrari
DBA - SQL Server 2008
MCITP | MCTS

remember is live or suffer twice!
Post #1165287
Posted Thursday, August 25, 2011 7:26 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 31, 2013 8:01 AM
Points: 1,232, Visits: 1,046
Great question. Read it way to fast. Was thinking about the objects and not the physical file and clicked yes like 60% so far. WOW!
Bet we will see this one again.
Post #1165335
Posted Thursday, August 25, 2011 8:11 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 10:06 PM
Points: 8,283, Visits: 8,733
Good question.

I thought about this one for quite a bit. Obviously I can move indices between filegroups, unless they support unmovable constraints (which can be fixed using DROP EXISTING) but I can't think of any way to move a file except to remove it and then add it back elsewhere - and I'm not at all sure that that will preserve the content (maybe it deletes the file and creates a new one? - one can't specify a filegroup when modifying a file , only when adding it); so the answer was probably "no" - so I got my point, not because I knew the answer but because to me it seemed to be the most probably correct answer.


Tom
Post #1165374
Posted Thursday, August 25, 2011 8:32 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, April 04, 2014 6:01 PM
Points: 1,117, Visits: 1,217
Thanks for the question!


Post #1165397
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse