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 ««12

.mdf question Expand / Collapse
Author
Message
Posted Wednesday, June 24, 2009 8:46 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, October 29, 2009 6:31 AM
Points: 39, 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
Post #741090
Posted Wednesday, June 24, 2009 5:35 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 3, 2014 1:43 AM
Points: 1, Visits: 214
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
Post #741449
Posted Sunday, May 19, 2013 7:25 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, June 23, 2014 6:49 AM
Points: 62, Visits: 326
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


Post #1454326
Posted Sunday, May 19, 2013 8:45 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 5:18 PM
Points: 42,437, Visits: 35,492
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 2008, MVP
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

Post #1454338
Posted Friday, May 24, 2013 1:39 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 11:35 PM
Points: 168, Visits: 733
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
Post #1456317
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse