sql database structure backup

Question

sql database structure backup

Navigation

#1 by (1 votes)

1

Hi, I have several servers that I use in my development environment, but I would like to know if colleagues know of any tools or procedures in sql itself that I can only back up the databases framework.

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

That way I would also take up less space to retain the backups I make.

Thank you in advance.

sql-server

asked by anonymous 09.02.2015 / 12:55

1 answer

How to get the url from the parent page of an iframe without being in the same domain? Plugin to sort images in an array

score 1 · Accepted Answer

@JeanBraz, if you use SQL Server Management Studio, you can generate a Script with the structure of the Database.

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 the 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 Sql Server Management Studio DLLs:

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 Server0\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();
        }
    }
}

In addition, you only need to adapt the program to save the script to a file.