patternsqlMinor
Rebuilding an entire Microsoft SQL Server Database
Viewed 0 times
entiresqlmicrosoftdatabaseserverrebuilding
Problem
Is there a suggested way to rebuild an entire SQL Server database from another database in SQL Server 2012 SP2?
Let me explain... we have a database that we are trying to enable partial containment on and then add it to our AlwaysOn cluster. We are getting nothing but deadlock errors while trying to set the containment to partial. The database has gone from SQL Server 2008 to 2008R2 to 2012 (no SP, SP1, SP2) and had TDE for a while.
Microsoft support has been looking at it for a couple of days, but it's not looking very promising and I think we're now leaning towards some form of corruption. Our application and everything else seems to be able to still access it just fine, it's just setting this one little flag isn't working. Very strange issue.
The only reference I found online that shows the same exact issue is from a Technet forum post in Portuguese (translated link), but it was never resolved.
At this point, I can't wait any longer and I just want to rebuild this data in a new database. I suppose I could script the entire thing out, but this DB is around 20GB already, so that would be one nasty script.
Does anyone have a suggestion on how to recreate a new database based off of another - without the standard Backup / Restore? Is scripting it out the right way to go?
Let me explain... we have a database that we are trying to enable partial containment on and then add it to our AlwaysOn cluster. We are getting nothing but deadlock errors while trying to set the containment to partial. The database has gone from SQL Server 2008 to 2008R2 to 2012 (no SP, SP1, SP2) and had TDE for a while.
Microsoft support has been looking at it for a couple of days, but it's not looking very promising and I think we're now leaning towards some form of corruption. Our application and everything else seems to be able to still access it just fine, it's just setting this one little flag isn't working. Very strange issue.
The only reference I found online that shows the same exact issue is from a Technet forum post in Portuguese (translated link), but it was never resolved.
At this point, I can't wait any longer and I just want to rebuild this data in a new database. I suppose I could script the entire thing out, but this DB is around 20GB already, so that would be one nasty script.
Does anyone have a suggestion on how to recreate a new database based off of another - without the standard Backup / Restore? Is scripting it out the right way to go?
Solution
Use SSMS to generate scripts for the entire database DDL.
Use the script to create a new, empty database.
Use
MAKE SURE YOU BACKUP THE ORIGINAL DATABASE before
Use the script to create a new, empty database.
Use
BCP to export the data from all tables. Use BCP to import that data into the new database.MAKE SURE YOU BACKUP THE ORIGINAL DATABASE before
DROPing it (if you in fact intend to drop it at all). MAKE SURE YOU TEST RESTORE THE BACKUP.Context
StackExchange Database Administrators Q#72974, answer score: 8
Revisions (0)
No revisions yet.