Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Moving files Expand / Collapse
Author
Message
Posted Wednesday, August 24, 2011 9:14 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, April 14, 2014 5:49 PM
Points: 2,263, Visits: 3,758
Comments posted to this topic are about the item Moving files

Mohammed Moinudheen
Post #1165091
Posted Thursday, August 25, 2011 12:33 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:41 PM
Points: 12,201, Visits: 9,156
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
Posted Thursday, August 25, 2011 1:23 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, April 14, 2014 2:13 AM
Points: 1,160, Visits: 1,470
Good one, thanks!

H.
Post #1165142
Posted Thursday, August 25, 2011 2:19 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, September 03, 2013 1:55 AM
Points: 298, Visits: 236
Did more people actually tick "True"???
Post #1165163
Posted Thursday, August 25, 2011 3:37 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 5:25 AM
Points: 3,683, Visits: 4,817
Good question, thanks

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #1165189
Posted Thursday, August 25, 2011 4:14 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, April 11, 2014 1:38 AM
Points: 1,144, Visits: 1,050
Nice question. Learned a few things while researching the answer.
Post #1165199
Posted Thursday, August 25, 2011 5:14 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 2:25 PM
Points: 1,253, Visits: 13,546
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
Posted Thursday, August 25, 2011 5:28 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 6:09 AM
Points: 1,622, Visits: 2,032
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.
Connect to me on LinkedIn
Post #1165236
Posted Thursday, August 25, 2011 5:33 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, April 03, 2014 5:32 AM
Points: 1,735, Visits: 488
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
Posted Thursday, August 25, 2011 5:37 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 10:36 AM
Points: 896, Visits: 1,479
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 »

Add to briefcase 123»»»

Permissions Expand / Collapse