¿Cómo crear (CREATE DATABASE), modificar (ALTER DATABASE) y eliminar (DROP DATABASE) una base de datos en SQL Server?

Este post esta estrechamente relacionado con el curso de base de datos que cree hace un tiempo, mejor dicho, es la continuación, así que te invito a revisarlo, te menciono esto, ya que, desde ese curso nos centramos en bases de datos relacionales y por ende, el manejador de base de datos que utilizaremos es SQL Server y SQL Server Management Studio, te dejo los siguientes link para que repases las clases anteriores:

• Modelo relacional y las bases de datos relacionales.
• Manejadores de base de datos: instalación de SQL Server y SQL Server Management Studio 2019.

Lenguaje Transact-SQL (T-SQL)

⚠️ NOTA: Antes de ir a los comandos, cabe aclarar que SQL Server utiliza el lenguaje Transact-SQL (T-SQL) el cual es una variante mejorada del lenguaje SQL, por lo tanto, cada comando lo intentaré hacer lo más apegado a SQL, pero, si hay que profundizar en cada comando, lo haré tomando en cuenta la sintaxis que proporciona Transact-SQL.

Archivos principales de una base de datos en SQL Server

Bien, dicho lo anterior comenzaremos definiendo tres conceptos que debemos comprender al momento de crear aun base de datos, por ejemplo:

Primary data files: archivo de datos primario

• El archivo de datos principal o primario, es el archivo de partida de la base de datos, el archivo primario apunta a otros archivos dentro de la base de datos.

Cada base de datos tiene un archivo de datos primario y la extensión para este tipo de archivos es .mdf.

Secondary data files: archivo de datos secundario

• El archivo de datos secundario es otra forma de almacenar datos, digamos que son iguales al archivo principal, pero con datos distintos, todo esto depende de la organización que requiera tu aplicación y el rendimiento que deseas obtener.

En una base de datos pueden o no existir archivos de datos secundarios, y de existir se les reconoce con la extensión .ndf.

Transaction log files: archivo de registro de transacciones

• El archivo de registro de transacciones, almacena toda la información de las transacciones hechas sobre la base de datos.

En un gran número de  aplicaciones verás que solo tienen un archivo principal y el de registro, ya que, quizá el volumen de datos o la cantidad de transacciones no son muy grandes, no obstante, conocer que cuentas con estas opciones es muy importante.

Filegroups: grupos de archivos

El concepto Filegroups o grupo de archivos es muy útil y también se utiliza bastante en la practica, ya que, nos permiten distribuir las tablas que tienen un gran volumen de datos en diferentes ubicaciones, que por lo regular, son diferentes discos fisicos.

Llevar acabo esta tarea nos permite separar los índices de las tablas, podemos definir un conjunto de archivos y crear un tipo de paralelismo en distintas unidades de almacenamiento, etc.

• Retomaremos los Filegroups mas adelante cuando hablemos del particionado de tablas.

Ahora que ya conoces estos conceptos, vayamos a la practica, recuerda ver el vídeo para comprender mejor cada ejemplo y si aun no lo he subido, suscríbete a mi canal de YouTube y al boletín para que te enteres cuando lo suba.

• SUSCRIBIRME AL CANAL DE EWebik

CREATE DATABASE: ¿Cómo crear una base de datos en SQL Server?

El primer comando que analizaremos es CREATE DATABASE, es muy importante que comprendas muy bien este comando, ya que, cuenta con muchas opciones, no obstante, aquí solo veremos tres casos de uso, pero no te preocupes, te dejare el link de la documentación oficial para que puedas revisarla por ti mismo.

¿Qué es CREATE DATABASE y para que sirve?

El comando CREATE DATABASE permite crear una nueva base de datos, además, permite definir, el archivo primario, archivos secundarios y los archivos de transacción.

Dado que el objetivo del post es introducirte en el mundo de SQL, veremos los tres casos más comunes y más fáciles de comprender.

Sintaxis T-SQL: CREATE DATABASE

CREATE DATABASE database_name
[ CONTAINMENT = { NONE | PARTIAL } ]
[ ON
      [ PRIMARY ] <filespec> [ ,...n ]
      [ , <filegroup> [ ,...n ] ]
      [ LOG ON <filespec> [ ,...n ] ]
]
[ COLLATE collation_name ]
[ WITH <option> [,...n ] ]
[;]

<option> ::=
{
      FILESTREAM ( <filestream_option> [,...n ] )
    | DEFAULT_FULLTEXT_LANGUAGE = { lcid | language_name | language_alias }
    | DEFAULT_LANGUAGE = { lcid | language_name | language_alias }
    | NESTED_TRIGGERS = { OFF | ON }
    | TRANSFORM_NOISE_WORDS = { OFF | ON}
    | TWO_DIGIT_YEAR_CUTOFF = <two_digit_year_cutoff>
    | DB_CHAINING { OFF | ON }
    | TRUSTWORTHY { OFF | ON }
    | PERSISTENT_LOG_BUFFER=ON ( DIRECTORY_NAME='<Filepath to folder on DAX formatted volume>' )
}

<filestream_option> ::=
{
      NON_TRANSACTED_ACCESS = { OFF | READ_ONLY | FULL }
    | DIRECTORY_NAME = 'directory_name'
}

<filespec> ::=
{
(
    NAME = logical_file_name ,
    FILENAME = { 'os_file_name' | 'filestream_path' }
    [ , SIZE = size [ KB | MB | GB | TB ] ]
    [ , MAXSIZE = { max_size [ KB | MB | GB | TB ] | UNLIMITED } ]
    [ , FILEGROWTH = growth_increment [ KB | MB | GB | TB | % ] ]
)
}

<filegroup> ::=
{
FILEGROUP filegroup name [ [ CONTAINS FILESTREAM ] [ DEFAULT ] | CONTAINS MEMORY_OPTIMIZED_DATA ]
    <filespec> [ ,...n ]
}

Como puedes observar, la sintaxis T-SQL posee un gran número de posibilidades y opciones, esto es por que en SQL Server, podemos crear una base de datos e indicar: 

• Cual es nuestro archivo de datos principal.
• El archivo de transacciones.
• O crear múltiples archivos de datos secundarios.

Quizá suene confuso, pero haremos un ejemplo que te ayudará a comprender, pero antes, definamos algunos comandos de esta sintaxis:

• database_name: es el nombre de la nueva base de datos, no debe superar los 128 caracteres.
• ON: permite especificar un bloque que contendrá la definición de nuestros <filespec> y <filegroup>, archivos primarios y secundarios.
• PRIMARY:
  • Indica que el primer archivo especificado en la lista <filespec> asociada, es el archivo de datos primario.
  • Si no se especifica el PRIMARY el primer archivo listado se convierte en el archivo de datos primario.
  • Recuerda que una base de datos solo puede tener un archivo de datos primario.
• LOG ON:
  • Especifica un bloque <filespec> que contendrá la definición de los archivos de transacción.
  • Si no se especifica, se crea en automatico un archivo de transacción.
• <filegroup>: nos permite definir un bloque donde indicaremos los archivos filegroup que declararemos en nuestra base de datos.
  • FILEGROUP: espesifica el nombre lógico de nuestro grupo de archivos.
• <filespec>: es un listado de los archivos de datos primarios, secundarios o transacción, y cuenta con las siguientes propiedades:
  • NAME: contiene nuestro logical_file_name o lo que es lo mismo, el nombre lógico del archivo.
  • FILENAME: especifica la ruta física de nuestro archivo.
  • SIZE:
    • Especifica el tamaño inicial de nuestro archivo.
    • Puede ser expresado en KB, MB, GB o TB, si no se especifica un sufijo, tomara MB por defecto.
    • Si este valor no es especificado, establece un valor de 8 MB por defecto.
  • MAXSIZE:
    • Especifica el tamaño máximo al que puede crecer nuestro archivo.
    • Puede ser expresado en KB, MB, GB o TB, si no se especifica un sufijo, tomara MB por defecto.
    • Si esta propiedad no se especifica, el archivo crecerá según la capacidad de nuestro disco duro.
  • FILEGROWTH:
    • Especifica el intervalo de crecimiento de nuestro archivo, cada vez que este se llene.
    • Puede ser expresado en KB, MB, GB, TB o porcentaje (%), si no se especifica un sufijo, tomara MB por defecto.
    • Si esta propiedad no es especificada, el valor por defecto se expresa en la siguiente tabla.

📌 NOTA: no he descrito todas las propiedades de esta sintaxis, solo he descrito lo que utilizaremos en esta clase, no obstante, existen muchas variables y casos de uso que se deben tomar en cuenta, así que te dejo la página oficial de Microsoft donde puedes acceder a la documentación completa: Fuente

• Excelente, es momento de practicar, veamos en acción cada sintaxis.

Ejemplo 1: ¿Cómo crear una base de datos en SQL Server?

En este primer ejemplo veremos la forma más sencilla de crear una base de datos, solo debemos pasar el nombre de la nueva base de datos a nuestro comando CREATE DATABASE, por ejemplo:

CREATE DATABASE ewebikdb;

• Como puedes ver la instrucción es muy simple y hemos creado una base de datos en SQL Server de nombre “ewebikdb”.
• Si intentáramos volver a correr el comando nos marcaria un error: “Database 'ewebikdb' already exists. Choose a different database name.”
• Lo que indica que ya existe una base de datos con el mismo nombre y, por lo tanto, no puede crear otra base de datos con esas características.

Ejemplo 2: ¿Cómo crear una base de datos en SQL Server especificando el archivos de datos y de transacción?

Ahora hagamos un ejemplo utilizando más propiedades que Transact-SQL nos proporciona, esto solamente si deseas especificar el nombre y ruta de tu archivo de datos y transacciones en el disco duro.

CREATE DATABASE ewebikdb
ON
( NAME = ewebikdb_dat,
    FILENAME = 'C:\db\sql\ewebikdb_dat.mdf',
    SIZE = 10,
    MAXSIZE = 50,
    FILEGROWTH = 5 )
LOG ON
( NAME = ewebikdb_log,
    FILENAME = 'C:\db\sql\ewebikdb_log.ldf',
    SIZE = 5MB,
    MAXSIZE = 25MB,
    FILEGROWTH = 5MB );

Con el código anterior hemos creado una base de datos de nombre: ewebikdb. Y un bloque de archivos de datos (<filespec>):

• Solo tiene un archivo con NAME (nombre) ewebikdb_dat , y dado que no se especifico PRIMARY, se convierte en nuestro archivo de datos primario.
• Establecemos una ruta personalizada donde queremos que este almacenado nuestro archivo:
  • C:\db\sql\ewebikdb_dat.mdf
  • Recuerda que nuestro archivo primario debe llevar la extensión .mdf
• Establecemos:
  • Un SIZE (tamaño del archivo) en 10.
  • El MAXSIZE (máximo tamaño del archivo) en 50.
  • Y su FILEGROWTH (intervalo de crecimiento del archivo) en 5.
  • Y como no se expresan los sufijos, el sistema toma por defecto MB.

También establecimos un bloque de archivos de transacción (<filespec>):

• Solo tiene un archivo de transacción ewebikdb_log
• En la ruta C:\db\sql\ewebikdb_log.ldf, con extensión .ldf.
• Para el SIZE, MAXSIZE y FILEGROWTH si establecemos un sufijo MB.

Ejemplo 3: ¿Cómo crear una base de datos en SQL Server especificando múltiples archivos de datos y de transacción?

En este ejemplo crearemos múltiples archivos de datos e indicaremos que el primer archivo es el principal a través de PRIMARY, los archivos secundarios los reconoceremos por que le agregaremos la extensión .ndf.

CREATE DATABASE ewebikdb
ON
PRIMARY
    (NAME = ewebikdb_dat,
    FILENAME = 'C:\db\sql\ewebikdb_dat.mdf',
    SIZE = 100MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = ewebikdb_1_dat,
    FILENAME = 'C:\db\sql\ewebikdb_1_dat.ndf',
    SIZE = 100MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
    ( NAME = ewebikdb_2_dat,
    FILENAME = 'C:\db\sql\ewebikdb_2_dat.ndf',
    SIZE = 100MB,
    MAXSIZE = 200,
    FILEGROWTH = 20)
LOG ON
  (NAME = ewebikdb_log,
    FILENAME = 'C:\db\sql\ewebikdb_log.ldf',
    SIZE = 100MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
  (NAME = ewebikdb_1_log,
    FILENAME = 'C:\db\sql\ewebikdb_1_log.ldf',
    SIZE = 100MB,
    MAXSIZE = 200,
    FILEGROWTH = 20) ;

• El código anterior es similar a los ejemplos anteriores, así que funciona prácticamente igual, en este punto cada comando ya lo conoces y se que el código lo entiendes a la perfección.

Ejemplo 4: ¿Cómo crear una base de datos en SQL Server especificando los Filegroups?

En el siguiente ejemplo crearemos una base de datos en SQL Server que contiene nuestros archivos primarios, secundarios y estableceremos dos grupo de archivos.

CREATE DATABASE ewebikdb
ON PRIMARY
(NAME = ewebikdb_dat,
    FILENAME = 'C:\db\sql\ewebikdb_dat.mdf',
    SIZE = 100MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
( NAME = ewebikdb_1_dat,
    FILENAME = 'C:\db\sql\ewebikdb_1_dat.ndf',
    SIZE = 100MB,
    MAXSIZE = 200,
    FILEGROWTH = 20),
FILEGROUP ewebikdb_semestre1
( NAME = ewebikdb_FG1_1_dat,
    FILENAME = 'C:\db\sql\semestre1\ewebikdb_FG1_1_dat.ndf',
    SIZE = 10,
    MAXSIZE = 50,
    FILEGROWTH = 5 ),
( NAME = ewebikdb_FG1_2_dat,
    FILENAME = 'C:\db\sql\semestre1\ewebikdb_FG1_2_dat.ndf',
    SIZE = 10,
    MAXSIZE = 50,
    FILEGROWTH = 5 ),
( NAME = ewebikdb_FG1_3_dat,
    FILENAME = 'C:\db\sql\semestre1\ewebikdb_FG1_3_dat.ndf',
    SIZE = 10,
    MAXSIZE = 50,
    FILEGROWTH = 5 ),
FILEGROUP ewebikdb_semestre2
( NAME = ewebikdb_FG2_1_dat,
    FILENAME = 'C:\db\sql\semestre2\ewebikdb_FG2_1_dat.ndf',
    SIZE = 10,
    MAXSIZE = 50,
    FILEGROWTH = 5 ),
( NAME = ewebikdb_FG2_2_dat,
    FILENAME = 'C:\db\sql\semestre2\ewebikdb_FG2_2_dat.ndf',
    SIZE = 10,
    MAXSIZE = 50,
    FILEGROWTH = 5 ),
( NAME = ewebikdb_FG2_3_dat,
    FILENAME = 'C:\db\sql\semestre2\ewebikdb_FG2_3_dat.ndf',
    SIZE = 10,
    MAXSIZE = 50,
    FILEGROWTH = 5 )
LOG ON
(NAME = ewebikdb_log,
    FILENAME = 'C:\db\sql\ewebikdb_log.ldf',
    SIZE = 100MB,
    MAXSIZE = 200,
    FILEGROWTH = 20);

Lo que podemos destacar del código anterior que que tenemos 2 FILEGROUP:

• El primero de nombre ewebikdb_semestre1 y el segundo de nombre ewebikdb_semestre2.
• Cada FILEGROUP tiene como objetivo almacenar la información por semestre.
• Al mismo tiempo, cada FILEGROUP cuenta con tres archivos secundarios, por lo tanto, cada uno almacenará la información de dos meses.
• En cuanto al bloque donde definimos el archivo primario y el de transacción, es prácticamente lo mismo que en los ejemplos anteriores, así que ya sabes como funcionan.

En este ejemplo cada FILEGROUP fue almacenado en una carpeta diferente, pero, esto es con la intensión de que imagines que en la practica, no son carpetas, sino, discos duros diferentes y cada FILEGROUP esta almacenado en cada uno de ellos.

Esto es muy útil, ya que podremos acceder a la información con un mejor rendimiento, recuerda que hay un tiempo de lectura y escritura en los discos duros y al separa nuestros archivos el rendimiento se incrementa.

Transact-SQL ALTER DATABASE: ¿Cómo modificar una base de datos?

⚠️ NOTA: NO modifiques una base de datos sin conocer, los posibles riesgos de ejecutar un ALTER DATABASE, este proceso podría dañar tu base de datos y causar perdida de información, este post es educativo y no representa una recomendación.

Bien, ya hemos aprendido algunas de las formas en que podemos crear una base de datos, no son todas, pero algunas formas son avanzadas y escapan al objetivo de este curso básico.

De igual forma, las opciones que Transact-SQL nos ofrece para modificar la definición de una base de datos son amplias y, dado que estamos en etapa de aprendizaje, veremos dos ejemplos sumamente sencillos.

• De todos modos te dejo la página oficial si desea profundizar aun más: fuente.

¿Qué es y para que sirve ALTER DATABASE?

Con ALTER DATABASE podemos modificar una base de datos, desde el nombre, collation; hasta la definición de los archivos primarios, secundario y grupos de archivos.

Veamos primero que indica su sintaxis.

Sintaxis general Transact-SQL ALTER DATABASE

-- SQL Server Syntax
ALTER DATABASE { database_name | CURRENT }
{
    MODIFY NAME = new_database_name
  | COLLATE collation_name
  | <file_and_filegroup_options>
  | SET <option_spec> [ ,...n ] [ WITH <termination> ]
}
[;]

<file_and_filegroup_options>::=
  <add_or_modify_files>::=
  <filespec>::=
  <add_or_modify_filegroups>::=
  <filegroup_updatability_option>::=

<option_spec>::=
{
  | <auto_option>
  | <change_tracking_option>
  | <cursor_option>
  | <database_mirroring_option>
  | <date_correlation_optimization_option>
  | <db_encryption_option>
  | <db_state_option>
  | <db_update_option>
  | <db_user_access_option><delayed_durability_option>
  | <external_access_option>
  | <FILESTREAM_options>
  | <HADR_options>
  | <parameterization_option>
  | <query_store_options>
  | <recovery_option>
  | <service_broker_option>
  | <snapshot_option>
  | <sql_option>
  | <termination>
  | <temporal_history_retention>
  | <data_retention_policy>
  | <compatibility_level>
      { 150 | 140 | 130 | 120 | 110 | 100 | 90 }
}

La sintaxis Transact-SQL es muy amplia y permite llevar acabo varios tipos de operaciones, por ejemplo:

• Podemos modificar archivos y grupos de archivos.
• Modificar propiedades de la base de datos.
• Cambiar o modificar los niveles de compatibilidad.
• Modificar la configuración de ámbito, relacionado con la optimización y comportamiento relacionado con la ejecución de consultas.
• Etc.

Veamos un par de ejemplos sencillos.

Ejemplo 1: ¿Cómo modificar el nombre de una base de datos con ALTER DATABASE?

Esto es muy sencillo y solo necesitamos un par de líneas de código como mínimo.

ALTER DATABASE ewebikdb
Modify Name = ewebikdb1;

Como puedes ver, cambiar el nombre de la base de datos es muy simple, y aunque sea simple, no debe tomarse a la ligera.

Ten cuidado al ejecutar este comando y, por favor, no lo hagas en una ambiente productivo sin saber los posibles riesgos, ya que, al ejecutar este comando, podrías ver la base de datos “In Recovery”.

• In Recovery: puede suceder cuando se inicia un proceso de modificación, restauración, etc., de la base de datos y, no se completo o no ha terminado, no obstante, la base de datos queda inaccesible hasta que salga de este modo.

Ejemplo 2: ¿Cómo modificar el collation de una base de datos con ALTER DATABASE?

Otro ejemplo simple que podemos modificar en una base de datos es el collation o intercalación de datos.

• Collation: es el conjunto de reglas que le indican al motor de la base de datos, como debe comparar y ordenar los caracteres dentro de la base de datos.

USE ewebikdb1;

SELECT DATABASEPROPERTYEX('ewebikdb1','collation');
-- Modern_Spanish_CI_AS

ALTER DATABASE ewebikdb1
COLLATE SQL_Latin1_General_CP1_CI_AS;

select DATABASEPROPERTYEX('ewebikdb1','collation');
-- SQL_Latin1_General_CP1_CI_AS

En el código anterior hacemos el cambio de collation:

• USE ewebikdb1: indicamos que queremos usar la base de datos ewebikdb1, esto hace que todas nuestras consultas posteriores sean sobre ewebikdb1.
• SELECT DATABASEPROPERTYEX: aun no llegamos a select, pero, esta consulta nos permite recuperar el collation actual de la base de datos.
  • Para este caso al ejecutar nos mostro: Modern_Spanish_CI_AS
• Después simplemente ejecutamos ALTER DATABASE e indicamos en COLLATE, que queremos establecer ahora SQL_Latin1_General_CP1_CI_AS como collation.
• Volvemos a ejecutar SELECT DATABASEPROPERTYEX y vemos que el nuevo collation es:
  • SQL_Latin1_General_CP1_CI_AS

Y listo, ya hemos modificado nuestra base de datos.

Transact-SQL DROP DATABASE: ¿Cómo eliminar una base de datos en SQL Server?

⚠️ NOTA: DROP DATABASE es un comando con el cual debes tener cuidado, ya que, si no tienes precaución, podrías eliminar una base de datos y afectar gravemente tu aplicación, recuerda que este post es educativo y no debes considerarlo como recomendación.

¿Qué es y como funciona DROP DATABASE?

Con DROP DATABASE podemos eliminar una o varias bases de datos existentes.

No cuenta con muchas opciones, aunque, en la documentación se nos indican varias recomendaciones y algunos casos de uso, te dejo el link, para que la revises por ti mismo: fuente.

Sintaxis general Transact-SQL DROP DATABASE

DROP DATABASE [ IF EXISTS ] { database_name | database_snapshot_name } [ ,...n ] [;]

De esta sintaxis podemos destacar:

• IF EXISTS: el cual indica la acción de “si existe”, es decir, si existe has esto.
• También podemos observar que a este comando le podemos indicar que elimine una o varias bases de datos al mismo tiempo.

Ejemplo: ¿Cómo eliminar (DROP DATABASE) una base de datos en SQL Server?

Esto será muy sencillo:

• Solo debes poner el comando seguido del nombre de la base de datos.
• O el comando seguido del nombre de las bases de datos a eliminar separadas por comas.

-- Eliminar una unica base de datos
DROP DATABASE ewebikdb;

-- Eliminar más de una base de datos
DROP DATABASE ewebikdb, ewebikdb1;

Y listo ya has eliminad tu base de datos, solo ten cuidado y siempre procura tener respaldo de tus bases de datos.