August 24, 2011 at 9:14 pm
Comments posted to this topic are about the item Moving files
M&M
August 25, 2011 at 12:33 am
Nice question, thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 25, 2011 at 1:23 am
Good one, thanks!
H.
August 25, 2011 at 2:19 am
Did more people actually tick "True"??? 🙂
August 25, 2011 at 3:37 am
This was removed by the editor as SPAM
August 25, 2011 at 4:14 am
Nice question. Learned a few things while researching the answer.
August 25, 2011 at 5:14 am
great question!!!!
WOW!!! 59% of incorrect answer!!! for a conceptual question!!!!
August 25, 2011 at 5:34 am
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
August 25, 2011 at 5:37 am
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
August 25, 2011 at 5:50 am
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/
August 25, 2011 at 5:52 am
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
August 25, 2011 at 5:55 am
Yep, that makes sense.
Thanks
August 25, 2011 at 6:07 am
Thanks for the question.
August 25, 2011 at 6:17 am
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