We need to backup only the database schema (tables, stored procs, views and functions) and not the data. Then we need to restore this as a new database name. Lastly, we would like to have this automated.
The reason for this is to move production data into our test database. We have an automated process that restores the production database into test, but we want to keep the tables, stored procs, views, and functions from test. We use SQL Compare from redgate to move those back into test, by backing up the test database and restoring it as a new test database. But the databases are very large, and restoring a backup of the test database to a new database is time and space consuming. So we would like to just restore the schema of the test database to a new database and do the SQL compare. Is there a way to automate this?
Thanks for any help, John