CREATE DATABASE ends up cloning an old database

Question

I have run into something quite odd, i haven't had this issue before, or maybe i'm missing something here.

In SQL Server Management Studio 2012 i am trying to run a Query to create a new database but it ends up cloning an old database, the code i'm using is:

CREATE DATABASE SEC_SSG_INOUT;
GO

USE SEC_SSG_INOUT;
GO

The database gets created with the correct name but it contains the same tables and stored procedures as an old database called SEC_SSG, but with empty data.

What is going on here?

Setaceous

There are many methods to clone a DB. Just 2 examples: 1) Backup - restore is the easiest. The server where you restore must be the same version or newer. 2) Script DB including data (probably in two separate .sql). This method allows to downgrade server used. – Ofay

@AlexKudryashev - They aren't asking how to clone a DB. They are asking why CREATE DATABASE unexpectedly appears to do so. – Justice

Sorry. Error reading throu ;( – Ofay

Answer 1

You must have added these objects to your model database by mistake at some point.

This gets used as the template for all new databases. Any objects created in there will be present in new databases CREATE-ed.

Justice

This doesn't explain why create statement isn't failing over "database already exists" – Tripalmitin

@YosiDahari - The database doesn't already exist. The old one mentioned in the question is called SEC_SSG. They are creating one called SEC_SSG_INOUT – Justice

Yep.. I can see that now. – Tripalmitin

I see i have checked Databases>System Databases>model>Programability>Stored Procedures, there they are :D but i can't see the tables, however i can query them, is it safe to just DROP them and then try my CREATE query? – Setaceous

@Setaceous - Try refreshing the object explorer tree view and see if the tables appear. – Justice

can't see them, in fact the Tables folder doesn't even appear, i will try dropping the tables and report back – Setaceous

@Setaceous - It might be because it is a system database. I know some versions of SSMS don't show all folders under there. Just make sure when you drop them you are definitely in the context of USE model and using one or two part names. – Justice

Awesome worked perfectly, i dropped all the tables and stored procedures from the model database and my CREATE query now works fine, Thank You very much! (i wonder how those tables got in there ...) – Setaceous