I see one of three options here:
1) you can have a templated script to create databases that explicitly includes the recovery model.
2) you can set the model database to simple and not have to worry about this.
3) you can hope everybody remembers, which seems like what you are doing. (not recommended)
I would personally go with number two. That's what the model database is there for.
You have three different types of problems from the looks of it.
Cannot insert explicit value for identity column in table 'MyTableName' when
IDENTITY_INSERT is set to OFF.
You have identity columns. For a table that has an identity column you have three choices. First when you do your insert exclude the identity column. Second remove the identity property from the column in the destination table. Third use identity_insert. To use identity_insert you first turn it on for the table.
SET IDENTITY_INSERT MyTableName ON
Then you do your insert. Note: you have to list all of the columns in the insert.
INSERT INTO MyTableName (fieldlist) VALUES (valuelist)
Not
INSERT INTO MyTableName VALUES (valuelist)
And last but not least make sure you turn identity_insert back off again. Identity_insert can only be turned on for one table at a time for a given connection.
SET IDENTITY_INSERT MyTableName OFF
Here is a good walkthrough for insert into identity columns.
Invalid column name 'MyColumnName'.
You said you changed the structure of 'newDatabase'. Well my guess is you removed 'MyColumnName'. Either exclude this from the insert or add the column back. You either need to make sure the structure in newDatabase matches oldDatabase or account for the differences in your inserts.
Violation of PRIMARY KEY constraint 'PK_MyTableName'. Cannot insert duplicate
key in object 'dbo.MyTableName'.
Last but not least you are inserting data into newDatabase were the data already exists. Or at least the primary key values already exist in newDatabase.MyTableName. My suggestion here is if there is any chance that you are doing updates as well as inserts change your INSERT statements to MERGE statements. Otherwise add a WHERE NOT EXISTS or something similar to the SELECT statement in your INSERT statement to exclude any rows where the primary key already exists in newDatabase.MyTableName.
Best Answer
More Google searching did not help, but after fiddling around with SSMS I managed to figure it out. Thought I'd answer my own question for anyone else who might stumble across this.
To script all of the changes at once, simply right-click a blank area of the diagram and click 'Select All' (or press Ctrl+A). Then right-click one of the selected tables and choose 'Generate Change Script...' at the bottom.