question

sri1542 avatar image
sri1542 asked

SQL Server database split

Dear All, I have a database with 235 GB Data. Now currently database is shared by two countries transnational data. Now I got the requirement to separate the data of two countries as two individual databases. Would you please suggest the best way to do this. I have couple of questions here: 1) It has 300 tables and how can I generate all the constrains create script? 2)How can I delete the relationship of all tables? 3) after splitting the databases how can I make sure the integrity and How can I make sure to get the as is of same production database? Thank you in advance Srikanth
sqlserver 2008
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Grant Fritchey avatar image
Grant Fritchey answered
There's no magic way to get this done. You're going to have to write a very careful set of scripts that pull the data apart based on the relationships between the tables. I would very strongly suggest you set up a system to do testing on first and then begin this process. In answer to your specific questions: 1) You can use SQL Server Management Studio to generate scripts of your database. Right click on a database and select Tasks, Generate Scripts. From there you can pick and choose individual items, including constraints. You're going to want to script the entire database though, not just the constraints. 2) There's a DROP command for that. But, why would you want to. You need those constraints in place to ensure the data you're moving is correct. Don't drop them just because it makes the data move easier. It does make it easier, but it makes it radically less safe. 3) Use scripts to build the database and don't modify those scripts. What's on your production server should be what you script out. As long as you don't modify that script, you should have the same database at the end. You could look to a tool like SQL Compare from Red Gate (my employer) to help with ensuring this is accurate. This is just going to be a long slog. Good luck.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.