Moving File Groups and Tables in SQL Server
-
The db I am testing is locked ATM doing some data-loads so I can't get various potential methods of doing this so I figured I would ask. I have a database with each table having its own file group and each file group has one file. Is there a simple method (other than Import Data and Bulk insert type operations) to detach the file groups (ok, maybe not detach they will be read-only) and copy the table, file group, and file into another database on a completely different server. My thought would be to just copy the physical files and then through some magical T-SQL have the files appear as the new table. I believe in My-SQL copying db files is all that is required but SQL Server is a special lady. Any thoughts? I am only dealing with about 60 million records an hour so not that much data.
Need custom software developed? I do custom programming based primarily on MS tools with an emphasis on C# development and consulting. "And they, since they Were not the one dead, turned to their affairs" -- Robert Frost "All users always want Excel" --Ennis Lynch
-
The db I am testing is locked ATM doing some data-loads so I can't get various potential methods of doing this so I figured I would ask. I have a database with each table having its own file group and each file group has one file. Is there a simple method (other than Import Data and Bulk insert type operations) to detach the file groups (ok, maybe not detach they will be read-only) and copy the table, file group, and file into another database on a completely different server. My thought would be to just copy the physical files and then through some magical T-SQL have the files appear as the new table. I believe in My-SQL copying db files is all that is required but SQL Server is a special lady. Any thoughts? I am only dealing with about 60 million records an hour so not that much data.
Need custom software developed? I do custom programming based primarily on MS tools with an emphasis on C# development and consulting. "And they, since they Were not the one dead, turned to their affairs" -- Robert Frost "All users always want Excel" --Ennis Lynch
Ennis Ray Lynch, Jr. wrote:
Is there a simple method (other than Import Data and Bulk insert type operations) to detach the file groups (ok, maybe not detach they will be read-only) and copy the table, file group, and file into another database on a completely different server.
Yes. Detach, xcopy, attach.
sp_attach_db [ @dbname= ] 'dbname'
, [ @filename1= ] 'filename_n' [ ,...16 ]Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]
-
Ennis Ray Lynch, Jr. wrote:
Is there a simple method (other than Import Data and Bulk insert type operations) to detach the file groups (ok, maybe not detach they will be read-only) and copy the table, file group, and file into another database on a completely different server.
Yes. Detach, xcopy, attach.
sp_attach_db [ @dbname= ] 'dbname'
, [ @filename1= ] 'filename_n' [ ,...16 ]Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]
Thanks for your reply, unfortunately, your method requires that the other database have the tables and files and file groups already created and requires the DB to be taken offline in order to add the new files and file groups which contain the table data. What I am looking for is some black magic to take files from a separate database and bring then into a different database without bringing it offline. Assuming both databases have the exact same configuration, this is technically possible (otherwise you couldn't add new files and file groups while SQL Server is running) but it is likely a dark-art. Bringing the database offline is not a valid solution for me needs.
Need custom software developed? I do custom programming based primarily on MS tools with an emphasis on C# development and consulting. "And they, since they Were not the one dead, turned to their affairs" -- Robert Frost "All users always want Excel" --Ennis Lynch
-
Thanks for your reply, unfortunately, your method requires that the other database have the tables and files and file groups already created and requires the DB to be taken offline in order to add the new files and file groups which contain the table data. What I am looking for is some black magic to take files from a separate database and bring then into a different database without bringing it offline. Assuming both databases have the exact same configuration, this is technically possible (otherwise you couldn't add new files and file groups while SQL Server is running) but it is likely a dark-art. Bringing the database offline is not a valid solution for me needs.
Need custom software developed? I do custom programming based primarily on MS tools with an emphasis on C# development and consulting. "And they, since they Were not the one dead, turned to their affairs" -- Robert Frost "All users always want Excel" --Ennis Lynch
Ennis Ray Lynch, Jr. wrote:
your method requires that the other database have the tables and files and file groups already created and requires the DB to be taken offline in order to add the new files and file groups which contain the table data.
Not necessarily so; you can attach it under a different name to the same server. Next, you
SELECT INTOthe tables from Database1.Dbo.TableName to Database2.Dbo.TableName :)Ennis Ray Lynch, Jr. wrote:
What I am looking for is some black magic
Aight, sacrifice a chicken and a donkey at the next full moon. More seriously; if you have access to a DBA, ask him/her how they'd implement horizontal partitioning. You could do something similar on a more simpeler level; you'd have to change your select-query to dynamically check for new databases, and to union it along. Could be done by querying
sys.databasesand building the select/union from that.Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]