If you ever want to transfer a large DB to a new one with more than one file, here is the way I am going to use (tested and approved)

1. Create a file which is as large as the data in your primary file (call it "buffer")

2. Empty the primary file (DBCC SHRINKFILE (<FILENAME>, EMPTYFILE))

3. Restart SQL Server Engine

4. Shrink the primary file to the Data size divided by the number of files you're gonna create (DBCC SHRINKFILE (<FILENAME>, NEWSIZE))

5. Create all the new files with the size of data divided by the number of files

6. Restrict their growth in order to fill the primary file in the next operation

7. Empty the buffer file (DBCC SHRINKFILE (BUFFER, EMPTYFILE))

8. Delete the buffer file (ALTER DATABASE REMOVE FILE (NAME=BUFFER))

9. Set final size of data files and unrestrict their growth according to the final configuration needed

No comments:

Post a Comment