How can i generate "CREATE DATABASE" script from existing sql server for all databases.

Question

How can i generate "CREATE DATABASE" script from existing sql server for all databases.

AP 0

Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2023-09-25T20:52:39.5233333+00:00

You have tagged this with Azure SQL Database and SQL Server. Please clarify what environment you want this in: Azure SQL Database or SQL Server on-prem.

And what do you want the script in? T-SQL? SMO maybe easier in this case, at least for on-prem.

If you want it in T-SQL, what level of ambition do you have? Do you need all umpteen database settings, or just the basics?
AP 0 Reputation points

2023-09-25T20:55:38.8233333+00:00

Sorry first time using this so was not sure. This is for on prem sql server.

T-SQL

SMO is kind of manually select hightlight in object explorer and do create script. But i am kind of looking for dynamic sql which generates "CREATE database" script for all exisiting databases. any help, appreciate.
SSingh-MSFT 16,371 Reputation points Moderator

2023-09-27T08:12:28.11+00:00

Hi
AP •,

Following up to see if the below suggestion was helpful. If this answers your query, do click Accept Answer and Mark Helpful for the same. And, if you have any further query do let us know.

Thanks

3 answers

Your answer

Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2023-09-25T20:52:39.5233333+00:00

You have tagged this with Azure SQL Database and SQL Server. Please clarify what environment you want this in: Azure SQL Database or SQL Server on-prem.

And what do you want the script in? T-SQL? SMO maybe easier in this case, at least for on-prem.

If you want it in T-SQL, what level of ambition do you have? Do you need all umpteen database settings, or just the basics?
AP 0 Reputation points

2023-09-25T20:55:38.8233333+00:00

Sorry first time using this so was not sure. This is for on prem sql server.

T-SQL

SMO is kind of manually select hightlight in object explorer and do create script. But i am kind of looking for dynamic sql which generates "CREATE database" script for all exisiting databases. any help, appreciate.
SSingh-MSFT 16,371 Reputation points Moderator

2023-09-27T08:12:28.11+00:00

Hi
AP •,

Following up to see if the below suggestion was helpful. If this answers your query, do click Accept Answer and Mark Helpful for the same. And, if you have any further query do let us know.

Thanks

Answer 1

Pankaj Dhillon 5

you can use PowerShell for this

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null 

$smos = new-object ('Microsoft.SqlServer.Management.Smo.Server') "sqlinstance"  

$dbs=$smos.Databases 

foreach($db in $dbs)
{
    $dbname="filepathhere\"+$db.name+".sql" 
    $db.Script() | Out-File $dbname
}

Answer 2

Erland Sommarskog 121.4K MVP Volunteer Moderator

I still think this may be better with SMO, since they already know of the tons of options there are.

Check out https://dbatools.io/ to see what they have available.

Answer 3

Hi @AP

How about this c# code:

String Connectionstring = CCMMUtility.CreateConnectionString(false, txt_DbDataSource.Text, "master", "sa", "happytimes", 1000);

        SqlConnection con = new SqlConnection();
        con.ConnectionString = Connectionstring;
        bool resultdbexistencx = CCMMUtility.CheckDatabaseExists(con, txt_DbName.Text);
        if (!resultdbexistencx)
        {
            // if not exists create it check the user name for sub-admin avialibe or not.

            if (txt_DbName.Text.Trim() == string.Empty) return;

            string strDbCreate;
            strDbCreate = "CREATE DATABASE " + txt_DbName.Text + " ON PRIMARY " +
            "(NAME = " + txt_DbName.Text + "_Data, " +
            "FILENAME = 'D:\\" + txt_DbName.Text + "Data.mdf', " +
            "SIZE = 4MB, MAXSIZE = 10GB, FILEGROWTH = 100%) " +
            "LOG ON (NAME = " + txt_DbName.Text + "_Log, " +
            "FILENAME = 'D:\\" + txt_DbName.Text + ".ldf', " +
            "SIZE = 4MB, " +
            "MAXSIZE = 10GB, " +
            "FILEGROWTH = 100%)";
            SqlConnection sqlconn = new SqlConnection(Connectionstring);
            SqlCommand cmd = new SqlCommand(strDbCreate, sqlconn);
            try
            {
                sqlconn.Open();
                sqlconn.ChangeDatabase("master");
                cmd.ExecuteNonQuery();
            }
           catch (Exception ex)
            {
                Int32 dbRollbackResult = RollBackTheWholetransaction(txt_DbName.Text.Trim(), Convert.ToInt32(HospitalResult));
                if (dbRollbackResult == 1)
                {
                    Response.Write(ex.Message);
                    lblMessage.DisplayMessage(StatusMessages.ErrorMessage, "There is some problem while generating the database or database name doesn't avialible.");
                }
             }

Please refer to Ram Singh's answer for more details: Generate database creation scripts.

Best regards,

Cosmog Hong

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Share via

How can i generate "CREATE DATABASE" script from existing sql server for all databases.

3 answers

Your answer