backup of the sql database structure

Question

Good morning, I have several servers that I use in my development environment, but I would like to know if colleagues know any tool or procedure in the sql itself that I can only backup the Database structure.

That is, if there is a disaster in this environment, I do not need to return the data because as it is a development environment, only the structure is enough.

That way I would also take far less space to retain the backups I do.

Thank you very much.

Answer 1

@Jeanbraz, if you use SQL Server Management Studio, you can generate a Script with the Database Structure.

DataBase > Right Click > Tarefas > Gerar Scripts > ...Todo o Banco de Dados... > Avançado > Tipos de dados dos quais gerar script > Esquema somente > OK

Now you need to choose where to save the script, the rest is just good old Next > Next > Finish.

EDIT

Below is an example of SMO: In Visual Studio, include in your solution a Console Application Then import the following Dlls from Sql Server Management Studio:

• Microsoft.SqlServer.Connectioninfo.dll
• Microsoft.SqlServer.Smo.dll
• Microsoft.SqlServer.Management.Sdk.Sfc.dll
• Microsoft.SqlServer.Sqlenum.dll

If you have Sql Management Studio 2012 on your machine, you can find these Dlls at the following physical address:

C:\Program Files\Microsoft SQL Server\110\SDK\Assemblies

Finally, replace your Program.Cs with the following:

using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Sdk.Sfc;
using System.Data.SqlClient;

namespace MyProject
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.Write(Program.CreateScript());
        }

        private static string CreateScript()
        {
            var sqlConnection = new SqlConnection("Server ConnectionString");
            var serverConnection = new ServerConnection(sqlConnection);
            var server = new Server(serverConnection);
            var database = server.Databases["DataBase Name"];

            var scripter = new Scripter(server);
            scripter.Options.ScriptDrops = false;
            scripter.Options.WithDependencies = true;
            scripter.Options.Indexes = true;
            scripter.Options.DriAllConstraints = true;
            scripter.Options.Triggers = true;
            scripter.Options.FullTextIndexes = true;
            scripter.Options.NoCollation = false;
            scripter.Options.Bindings = true;
            scripter.Options.IncludeIfNotExists = false;
            scripter.Options.ScriptBatchTerminator = true;
            scripter.Options.ExtendedProperties = true;
            scripter.PrefetchObjects = true;

            var listaUrn = new List<Urn>();
            foreach (Table table in database.Tables)
                if (!table.IsSystemObject)
                    listaUrn.Add(table.Urn);

            foreach (View view in database.Views)
                if (view.IsSystemObject == false)
                    listaUrn.Add(view.Urn);

            foreach (StoredProcedure storedProcedure in database.StoredProcedures)
                if (storedProcedure.IsSystemObject == false)
                    listaUrn.Add(storedProcedure.Urn);

            var stringBuilder = new StringBuilder();
            var stringCollection = scripter.Script(listaUrn.ToArray());
            foreach (var script in stringCollection)
            {
                stringBuilder.AppendLine(script);
                stringBuilder.AppendLine("GO");
            }

            return stringBuilder.ToString();
        }
    }
}

All you need to do is adapt the program to save the script to a file.