Moving files

  • Comments posted to this topic are about the item Moving files

    M&M

  • Nice question, thanks.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Good one, thanks!

    H.

  • Did more people actually tick "True"??? 🙂

  • This was removed by the editor as SPAM

  • Nice question. Learned a few things while researching the answer.

  • great question!!!!

    WOW!!! 59% of incorrect answer!!! for a conceptual question!!!!


    [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!
  • Thanks for the question. Had to think about just what "data file" entailed to get it correct.

    [font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
    Connect to me on LinkedIn

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

    USE [master]

    GO

    /****** Object: Database [Test] Script Date: 08/25/2011 07:09:25 ******/

    IF EXISTS (SELECT name FROM sys.databases WHERE name = N'Test')

    DROP DATABASE [Test]

    GO

    USE [master]

    GO

    /****** Object: Database [Test] Script Date: 08/25/2011 07:09:25 ******/

    CREATE DATABASE [Test] ON PRIMARY

    ( NAME = N'Test', FILENAME = N'C:\SQL Server User Databases\Test.mdf'),

    FILEGROUP [SECONDARY]

    ( NAME = N'Test2', FILENAME = N'C:\SQL Server User Databases\Test2.ndf'),

    ( NAME = N'Test3', FILENAME = N'C:\SQL Server User Databases\Test3.ndf'),

    FILEGROUP [THIRDGROUP]

    ( NAME = N'Test4', FILENAME = N'C:\SQL Server User Databases\Test4.ndf')

    LOG ON

    ( NAME = N'Test_log', FILENAME = N'C:\SQL Server User Databases\Test_log.ldf')

    GO

    ALTER DATABASE [Test] SET COMPATIBILITY_LEVEL = 100

    GO

    USE Test

    GO

    IF EXISTS

    (

    SELECT1

    FROMINFORMATION_SCHEMA.TABLEST

    WHERET.TABLE_NAME='TABLE_1'

    ANDT.TABLE_SCHEMA='dbo'

    )

    BEGIN

    DROP TABLETABLE_1

    END

    GO

    CREATE TABLE dbo.Table_1

    (

    C1 int NOT NULL IDENTITY (1, 1),

    C2 varchar(50) NOT NULL

    ) ON [THIRDGROUP]

    GO

    ALTER TABLE dbo.Table_1 ADD CONSTRAINT

    PK_Table_1 PRIMARY KEY CLUSTERED

    (

    C1

    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    GO

    USE TEST

    GO

    SELECT*

    FROMTABLE_1

    GO

    USE [master]

    GO

    ALTER DATABASE [Test] REMOVE FILE [Test4]

    GO

    GO

    ALTER DATABASE [Test] ADD FILE ( NAME = N'Test4', FILENAME = N'C:\SQL Server User Databases\Test4.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB )

    TO FILEGROUP [SECONDARY]

    USE TEST

    GO

    SELECT*

    FROMTABLE_1

    GO

    USE master

    GO

    ALTER DATABASE [Test] REMOVE FILE [Test4]

    GO

    GO

    ALTER DATABASE [Test] ADD FILE ( NAME = N'Test4', FILENAME = N'C:\SQL Server User Databases\Test4.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB )

    TO FILEGROUP [THIRDGROUP]

    USE TEST

    GO

    SELECT*

    FROMTABLE_1

    GO

  • I though to myself if this was a tricky question when I read it... I'm glad it wasn't.

    @data_god: I guess what the script is doing is reconfiguring your filegroups because it has to remove the file and then add it to another filegroup. Maybe someone more experienced could explain that behaviour better to me... 🙂

    Thanks for the question.

    Best regards,

    Best regards,

    Andre Guerreiro Neto

    Database Analyst
    http://www.softplan.com.br
    MCITPx1/MCTSx2/MCSE/MCSA

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

  • 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

  • Yep, that makes sense.

    Thanks

  • Thanks for the question.

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

    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

Viewing 15 posts - 1 through 15 (of 27 total)

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