Moving files

  • 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.


    [font="Times New Roman"]rfr.ferrari[/font]
    DBA - SQL Server 2008
    MCITP | MCTS

    remember is live or suffer twice!
    the period you fastest growing is the most difficult period of your life!
  • 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"


    [font="Times New Roman"]rfr.ferrari[/font]
    DBA - SQL Server 2008
    MCITP | MCTS

    remember is live or suffer twice!
    the period you fastest growing is the most difficult period of your life!
  • 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. 😎

  • 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

  • Thanks for the question!

  • Thanks for the question.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Nice question. thanks

  • I got this one wrong but I learned something...

  • good question - thanks

  • I got it wrong because I was reading 'Data files can be moved...' when I was actually thinking 'Data can be moved..'

    Welll, reading questions correctly is also part of the challenge 😛

  • Nice Question Moinu.... But I got it wrong 🙁

    ===========================================
    Better try and fail than not to try at all...

    Database Best Practices[/url]

    SQL Server Best Practices[/url]

  • Same here 😉

    Tom.Thomson (8/25/2011)


    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.

  • Simple and easy question.

Viewing 13 posts - 16 through 27 (of 27 total)

You must be logged in to reply to this topic. Login to reply