SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Mohammed Moinudheen / Moving files / Latest Posts

RE: Moving files

CoolCodeShare — Sun, 28 Aug 2011 00:30:44 GMT

Simple and easy question.

RE: Moving files

Wildcat — Fri, 26 Aug 2011 11:12:42 GMT

Same here ;-)[quote][b]Tom.Thomson (8/25/2011)[/b][hr]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.[/quote]

RE: Moving files

BuntyBoy — Thu, 25 Aug 2011 15:59:39 GMT

Nice Question Moinu.... But I got it wrong :-(

RE: Moving files

sdelachapelle — Thu, 25 Aug 2011 13:01:53 GMT

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 :-P

RE: Moving files

OzYbOi d(-_-)b — Thu, 25 Aug 2011 10:34:37 GMT

good question - thanks

RE: Moving files

Revenant — Thu, 25 Aug 2011 10:13:28 GMT

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

RE: Moving files

KWymore — Thu, 25 Aug 2011 09:55:22 GMT

Nice question. thanks

RE: Moving files

SQLRNNR — Thu, 25 Aug 2011 09:52:48 GMT

Thanks for the question.

RE: Moving files

skrilla99 — Thu, 25 Aug 2011 08:32:40 GMT

Thanks for the question!

RE: Moving files

L' Eomot Inversé — Thu, 25 Aug 2011 08:11:07 GMT

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.

RE: Moving files

SanDroid — Thu, 25 Aug 2011 07:26:04 GMT

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. :cool:

RE: Moving files

rfr.ferrari — Thu, 25 Aug 2011 06:35:37 GMT

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"

RE: Moving files

rfr.ferrari — Thu, 25 Aug 2011 06:23:04 GMT

[quote][b]Data_God (8/25/2011)[/b][hr]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?....[/quote]It's easy!!! you must include data in table and to try remove data file... and verify if this script works.

RE: Moving files

Kiara — Thu, 25 Aug 2011 06:17:55 GMT

[quote][b]Kwex (8/25/2011)[/b][hr]Did more people actually tick "True"??? :-)[/quote]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... ;-)

RE: Moving files

Mattrick — Thu, 25 Aug 2011 06:07:36 GMT

Thanks for the question.

RE: Moving files

Data_God — Thu, 25 Aug 2011 05:55:29 GMT

Yep, that makes sense.Thanks

RE: Moving files

John Mitchell-245523 — Thu, 25 Aug 2011 05:52:08 GMT

[quote][b]Data_God (8/25/2011)[/b][hr]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?[/quote]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

RE: Moving files

Britt Cluff — Thu, 25 Aug 2011 05:50:33 GMT

Glad I just answered and got it correct. I almost convinced myself I was missing something and checked true. Thanks for the question.

RE: Moving files

codebyo — Thu, 25 Aug 2011 05:37:51 GMT

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,

RE: Moving files

Data_God — Thu, 25 Aug 2011 05:33:59 GMT

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]GOUSE [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')GOALTER DATABASE [Test] SET COMPATIBILITY_LEVEL = 100GOUSE TestGOIF EXISTS ( SELECT 1 FROM INFORMATION_SCHEMA.TABLES T WHERE T.TABLE_NAME = 'TABLE_1' AND T.TABLE_SCHEMA = 'dbo' )BEGIN DROP TABLE TABLE_1ENDGOCREATE TABLE dbo.Table_1 ( C1 int NOT NULL IDENTITY (1, 1), C2 varchar(50) NOT NULL ) ON [THIRDGROUP]GOALTER 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]GOUSE TESTGOSELECT *FROM TABLE_1GOUSE [master]GOALTER DATABASE [Test] REMOVE FILE [Test4]GOGOALTER DATABASE [Test] ADD FILE ( NAME = N'Test4', FILENAME = N'C:\SQL Server User Databases\Test4.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [SECONDARY]USE TESTGOSELECT *FROM TABLE_1GOUSE masterGOALTER DATABASE [Test] REMOVE FILE [Test4]GOGOALTER DATABASE [Test] ADD FILE ( NAME = N'Test4', FILENAME = N'C:\SQL Server User Databases\Test4.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [THIRDGROUP] USE TESTGOSELECT *FROM TABLE_1GO

RE: Moving files

Thomas Abraham — Thu, 25 Aug 2011 05:28:15 GMT

Thanks for the question. Had to think about just what "data file" entailed to get it correct.

RE: Moving files

rfr.ferrari — Thu, 25 Aug 2011 05:14:48 GMT

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

RE: Moving files

Michael Lysons — Thu, 25 Aug 2011 04:14:47 GMT

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

RE: Moving files

Stewart "Arturius" Campbell — Thu, 25 Aug 2011 03:37:11 GMT

Good question, thanks

RE: Moving files

Kwex — Thu, 25 Aug 2011 02:19:58 GMT

Did more people actually tick "True"??? :-)

RE: Moving files

Bex — Thu, 25 Aug 2011 01:23:32 GMT

Good one, thanks!H.

RE: Moving files

Koen Verbeeck — Thu, 25 Aug 2011 00:33:53 GMT

Nice question, thanks.

Moving files

mohammed moinudheen — Wed, 24 Aug 2011 21:14:26 GMT

Comments posted to this topic are about the item [B]Moving files [/B]