Moving files

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Mohammed Moinudheen... » Moving files

28 posts, Page 1 of 3

1 2 3 »»»

Moving files

Rate Topic

Display Mode

Topic Options

Author

Message

mohammed moinudheen

Posted Wednesday, August 24, 2011 9:14 PM

SSCrazy

Group: General Forum Members
Last Login: Today @ 10:29 PM
Points: 2,178, Visits: 3,599

Comments posted to this topic are about the item Moving files

Mohammed Moinudheen

Post #1165091

Koen Verbeeck

Posted Thursday, August 25, 2011 12:33 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Today @ 2:54 AM
Points: 9,409, Visits: 6,494

Nice question, thanks.

How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence

Post #1165131

Bex

Posted Thursday, August 25, 2011 1:23 AM

Ten Centuries

Group: General Forum Members
Last Login: Friday, June 14, 2013 4:16 AM
Points: 1,121, Visits: 1,410

Good one, thanks!

H.

Post #1165142

Kwex

Posted Thursday, August 25, 2011 2:19 AM

SSC Veteran

Group: General Forum Members
Last Login: Friday, October 12, 2012 7:03 AM
Points: 249, Visits: 224

Did more people actually tick "True"??? Smile

Post #1165163

Stewart "Arturius" Campbell

Posted Thursday, August 25, 2011 3:37 AM

Hall of Fame

Group: General Forum Members
Last Login: Thursday, June 13, 2013 9:46 AM
Points: 3,157, Visits: 4,341

Good question, thanks

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”

Post #1165189

Michael Lysons

Posted Thursday, August 25, 2011 4:14 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 6:34 AM
Points: 1,127, Visits: 936

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

Post #1165199

rfr.ferrari

Posted Thursday, August 25, 2011 5:14 AM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, June 06, 2013 4:06 PM
Points: 1,219, Visits: 13,509

great question!!!!

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

rfr.ferrari
DBA - SQL Server 2008
MCITP | MCTS

remember is live or suffer twice!

Post #1165225

Thomas Abraham

Posted Thursday, August 25, 2011 5:28 AM

Ten Centuries

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 5:11 AM
Points: 1,168, Visits: 1,470

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

Please don't go. The drones need you. They look up to you.

Post #1165236

Data_God

Posted Thursday, August 25, 2011 5:33 AM

UDP Broadcaster

Group: General Forum Members
Last Login: Today @ 4:35 AM
Points: 1,457, Visits: 422

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
(
SELECT 1
FROM INFORMATION_SCHEMA.TABLES T
WHERE T.TABLE_NAME = 'TABLE_1'
AND T.TABLE_SCHEMA = 'dbo'
)
BEGIN
DROP TABLE TABLE_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 *
FROM TABLE_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 *
FROM TABLE_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 *
FROM TABLE_1
GO

Post #1165241

codebyo

Posted Thursday, August 25, 2011 5:37 AM

SSC Eights!

Group: General Forum Members
Last Login: Tuesday, June 04, 2013 8:50 AM
Points: 862, Visits: 1,440

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

Post #1165243

« Prev Topic | Next Topic »

28 posts, Page 1 of 3

1 2 3 »»»

Permissions