New answers tagged microsoft
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 ...
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 ...
Use SSMS to generate scripts for the entire database DDL. 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.
Top 50 recent answers are included