7

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?

Improve this question
5
  • Silly question most likely, but have you set the database to single user mode before doing the modification?
    – Hannah Vernon ♦
    Aug 1, 2014 at 22:04
  • Another silly question: Are all remnants of TDE gone? All?
    – Sean Gallardy
    Aug 1, 2014 at 22:05
  • 1
    @MaxVernon I should have mentioned that - yes I have and that doesn't make any difference unfortunately. This is a very strange issue. From what I've seen it sounds like this setting really is not more than a simple flag in the database (plz correct me if I'm wrong) and the fact that we're getting this error is something that MS hasn't seen before it sounds like.
    – Adam Plocher
    Aug 1, 2014 at 22:05
  • @SeanGallardy I'm really not sure. I was the one who removed TDE but it was so many years ago and I honestly have no experience with it. It's possible that I didn't remove everything... I wish I could remember what I did... the DB has been working fine in PROD for years now, this is the first strange issue we've seen.
    – Adam Plocher
    Aug 1, 2014 at 22:06
  • @AdamPlocher I have outlined the steps HERE with scripts for BCP OUT and BCP IN as well. That will be a lot faster. Check it out to see if it helps in your situation.
    – Kin Shah
    Aug 6, 2014 at 13:43

3 Answers 3

Reset to default
8

Use SSMS to generate scripts for the entire database DDL.

enter image description here

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.

Improve this answer
6
  • 1
    Thank you Max, I'm going to give this a shot in the off-hours this evening. I think this will work, but I'll let you know for sure in a few hours :)
    – Adam Plocher
    Aug 1, 2014 at 22:08
  • Make sure you script whatever server-level objects such as logins that you require if you are moving the new database to new server.
    – Hannah Vernon ♦
    Aug 1, 2014 at 22:22
  • 1
    I would script the table structure, import the data, and then synch the database structure -- foreign keys, indexs, procs and so forth.
    – jmoreno
    Aug 2, 2014 at 5:14
  • 1
    Hi Max, apologies for not getting back to you. I have been fighting with several invalid objects (procs with references to invalid fields, etc) and had a few issues getting bcp.exe to do what I wanted. I'm very close to having something that hopefully works... I just want to confirm it works but will mark as answer once I do (hopefully today)
    – Adam Plocher
    Aug 3, 2014 at 22:53
  • 1
    Thanks again Max. I'm not 100% done - and it did take a little finagling but you answered my main question: should I script the schema then import the data? I ended up using the SQL Import Wizard for most data related items, though but that was mainly due to my lack of understanding with bcp.exe. It was so much data, too many trials and errors just took too long, but I know it would have worked if I kept at it so it is definitely a valid answer for future folks who find this. I scripted schema, dropped constraints, imported with import wiz, recreated constraints with a SQL Compare diff. Thx!
    – Adam Plocher
    Aug 6, 2014 at 19:20
5

To make the scripting task alot easier, you can also use 3rd party tools. MSVisualStudio has both a Schema and Data comparison toolset. I personnally prefer RedGate's SQLCompare tool.

I also use theses kind of tools all the time to ease DEV->QA->PROD deployments. Once the schema is in place, you do a simple "Data import Wizard" to copy the data over, as needed.

The plus side of using theses tools is that they are more intelligent when building the scripts, putting items in the proper creation order, warning you beforehand of possible errors, scripting only items you need created/updated, and including server items you may forget when only using the DBscripting tool, like logins or linked servers or security.

Improve this answer
1
  • Thanks Philippe. I did end up using combinations of SQL Compare / Data Compare (those are two must-have tools for me), but the Data Compare tool really isn't practical for large amounts of data. The fastest process I found was to drop constraints and use the data Import Wizard with IDENTITY INSERT enabled (in the Wiz options). Used SQL Compare to regen constraints after. It was a million times faster and didn't fill up my C: drive (where %TEMP% is located). Our C: partition isn't massive like our data/log partitions.
    – Adam Plocher
    Aug 6, 2014 at 19:23
1

There is another way to rebuild an entire MS SQL database.

Step 1: Use script to copy data of all tables from Old to New database.

Declare @OldDBName Varchar(200)
Declare @NewDBName Varchar(200)

Select @OldDBName='OldDB',@NewDBName='NewDB'

    Select 'Select * into '+@NewDBName+'.'+sc.name+'.'+tbl.Name+ ' From '+@OldDBName+'.'+sc.name+'.'+tbl.Name
    from sys.tables tbl inner join sys.schemas sc on sc.schema_id=tbl.schema_id
     where type='U'

Step 2: Apply all type of Constraints on tables of New database.
1. Primary Keys
2. Foreign keys
3. Default Constraints
4. Computed Columns
5. Identities
6. Indexes

Note: You can find those constraint synchronization scripts on Connectsql.com (http://www.connectsql.com/2013/11/sql-server-database-sychronization_29.html?utm_source=BP_recent)

Step3: Generate script for objects through SSMS respectively and execute on New database.
1. Generate Functions script through SSMSS and execute on New database.
2. Generate Views script through SSMSS and execute on New database.
3. Generate Stored Procedures script through SSMSS and execute on New database.
4. Generate Triggers script through SSMSS and execute on New database.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.