CODEWITHSUNDEEP
sql-server
Hossein Mobasher
Hossein Mobasher

Reputation: 4472

Change FileGroup of File

While working on SQL Server, I faced the problem with changing the FileGroup of File.

Suppose that, we create a database using below sql code:

CREATE DATABASE Example
ON PRIMARY
(NAME = example_dat,
 FILENAME = 'D:\exmaple_dat.mdf',
 SIZE = 10,
 MAXSIZE = 50,
 FILEGROWTH = 15%), 
 (NAME = example_second_dat,
  FILENAME = 'E:\example_second_dat.ndf',
  SIZE = 10,
  MAXSIZE = 50,
  FILEGROWTH = 15%)
LOG ON
(NAME = exmaple_log,
 FILENAME = 'D:\example.log',
 SIZE = 5,
 MAXSIZE = 25,
 FILEGROWTH = 5MB)
GO

Now, i want to add file group DataFileGroup and add exmaple_dat and example_second_dat to it.

Please give me a solution.

Thanks in advance :)

Upvotes: 2

Views: 9890

Answers (2)

Lucio Menci
Lucio Menci

Reputation: 143

With your example both files are in the same filegroup. There is a solution. You have to shift all content of the file you want change in another file of the same filegroup with DBCC SHRINKFIL(example_second_dat, EMPTYFILE) command, then remove the file, backup both DB and Log (or, eventually set the db recovery_model to simple), create the file with the right filegroup.

Upvotes: 0

Kevin Suchlicki
Kevin Suchlicki

Reputation: 3145

You can't move a file to another filegroup directly, but the below link offers some alternatives.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=146182

Upvotes: 3

Related Questions