Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


.mdf question


.mdf question

Author
Message
Nero-1119276
Nero-1119276
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 132
Hi,
I have added new file abc.ndf as per your guidlines but how can i move data to new filegroup.

Sorry but i am new to Sql Server
Zelis-424853
Zelis-424853
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 302
If you want to add a datafile you can do as joe writes or through SSMS.
Is there a specific reason why you want to move tables to a different datafile?
If you add a datafile (.ndf) as I understand it the data are spread across the datafiles evenly when you make a rebuild of all the indexes or when adding data, correct me if I´m wrong.
If you want to determine the fill level of the datafiles before/after a rebuild use the tips Here.

Regards
Stefan
dineshvishe
dineshvishe
Valued Member
Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)

Group: General Forum Members
Points: 68 Visits: 342
hi,
1)First Take backup of current database with to NewTestDbNew.bak
BACKUP DATABASE [oldTestDb] TO DISK = N'D:\NewTestDbNew.bak' WITH NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

2) Restore above NewTestDbNew.bak to your required name and required file path.


RESTORE DATABASE [NewTestDbNew] FROM DISK = N'D:\NewTestDbNew.bak'
WITH FILE = 1, MOVE N'BACKUPAEMMS_Data'
TO N'E:\database\NewTestDbNew.MDF',
MOVE N'BACKUPAEMMS_Log' TO N'E:\database\NewTestDbNew_1.LDF',
NOUNLOAD, STATS = 10
GO
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47254 Visits: 44382
Please note: 4 year old thread. Also backup/restore is not an option for moving some objects from one filegroup to another.


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Database admin(DBA)
Database admin(DBA)
SSC Veteran
SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)

Group: General Forum Members
Points: 218 Visits: 1309
First of all, you should have a solid reason for moving the table to new file-group most preferably on a different drive. Secondly, you should keep in mind that there could be downtime required. So you have to plan accordingly.

There are some solutions , but the best solution would depend upon your environment and data structures. If you have that table without any dependencies, without B/LOB data type etc. data, and you do not want to move such data, it will work great. Moreover, if you have ENTERPRISE edition, you could use index build ONLINE option to move the data without a downtime (some conditions do apply)

Keeping in mind the limitation of moving B/LOB columns etc., non-flexibility of the CREATE WITH DROP_EXISTING clustered index and table data could be huge, my preference is something like (Hope not missing something)

TAKE BACKUP OF THE DATABASE
MAKE SURE THE NEW FILE HAS SIZE AT LEAST EQUAL TO THE TABLE
GET ALL DEFINITIONS RELATED TO THE TABLE (WOULD BE USED FOR NEW TABLE)
CHANGE THE RECOVERY MODEL TO SIMPLE OR BULK_LOGGED
CREATE COPY (JUST MAIN STRUCTURE)OF THE TABLE ON THE NEW FILE GROUP
DROP FOREIGN KEYS IF ANY
DROP NON-CLUSTERED INDEXES / MANUAL STATISTICS (OFTEN OVERLOOKED)
CREATE CLUSTERED INDEXANF NOT ANY ON EXISTING TABLE (PREFERABLY IDENTITY COLUMN)
MOVE THE DATA IN BATCHES (SORTED IF POSSIBLE) TO KEEP THE LOG FILE IN CHECK IN CASE OF SPACE CONSTRAINTS
BACKUP LOG IF POSSIBLE
VALIDATE THE DATA IF POSSIBLE
CREATE CLUSTERED INDEX ON NEW TABLE (IF NOT POSSIBLE BEFORE)
CREATE NON-CLUSTERED INDEXES / MANUAL STATISTICS
VALIDATE REFERENTIAL INTEGRITY BY CREATING FOREIGN KEYS IF ANY

SQL server DBA
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search