Alter Database – modificando archivos de base de datos

Modificación de una base de datos – archivos

  • Los archivos de las bases de datos en SQL Server puede ubicarse en diferentes discos y carpetas, además de agruparlos en Grupos de archivos. En este post aprenderemos a agregar, modificar y eliminar estos archivos.
  • Los archivos de la base de datos son de tres tipos, el primario, los secundarios y los archivos de transacciones, en los dos primeros se almacenarán los datos guardados en la base de datos, se recomienda que sean mas de uno en cada grupo cuando se proyecta un crecimiento acelerado de la base de datos.
  • Los archivos de transacciones no se ubican en grupos de archivos.
  • Ningún archivo puede tener un tamaño inicial menor al que se ha especificado en la base de datos model.
  • Es recomendable que el nombre que usará SQL Server sea igual al que se visualiza en el sistema operativo, estos se especifican en los parámetros Name para el nombre de SQL Server y Filename para el que se visualiza en el Sistema Operativo.

Para modificar la base de datos y trabajar con archivos se puede:

  • Agregar: Archivo Secundario

Alter database NombreBaseDatos add file (…) to filegroup GRUPO

  • Agregar: Archivo de Transacciones

Alter database NombreBaseDatos add log file (…)

  • Modificar:

Alter Database NombreBaseDatos modify file (Name…. nuevas opciones)

  • Eliminar:

Alter DataBase NombreBaseDatos remove file NombreArchivo

El archivo a borrar debe estar vacío, es decir no tener tablas, además no ser el único archivo de un grupo por defecto.

  • Reducir el tamaño de un archivo

DBCC Shrinkfile(NombreArchivo, Tamaño)

El nuevo tamaño no puede ser menor al de model.

Ejercicios

1. Creamos primero la base de datos

xp_create_subdir ‘C:\BD’
go
xp_create_subdir ‘D:\Data\Respaldo’
go
Create Database SistemasE
on Primary
(Name=’S01′,Filename=’C:\BD\S01.mdf’, Size = 10MB, Maxsize= 200GB,
Filegrowth= 5MB),
(Name=’S02′,Filename=’D:\Data\S02.ndf’),
filegroup VENTAS
(Name=’S03′,Filename=’D:\Data\Respaldo\S03.ndf’)
LOG ON
(Name=’L01′,Filename=’C:\BD\L01.ldf’)
go

2. Agregar grupos PERSONAL y FINANZAS

Alter database SistemasE add filegroup PERSONAL
Alter database SistemasE add filegroup FINANZAS
go

3. Agregar dos archivos al grupo Finanzas

Alter database SistemasE
add file (Name=’Datos01′, Filename=’C:\BD\Datos01.ndf’),
(Name=’Datos02′, Filename=’C:\BD\Datos02.ndf’)
to filegroup Finanzas
go

5. Para listar los Grupos y archivos

select * from sys.filegroups
select * from sys.database_files
go

El script que muestra mejor la lista es como sigue:

select A.name, a.physical_name,G.name
from sys.database_files As A
join sys.filegroups As G on A.data_space_id = G.data_space_id
go

6. Cambiar el nombre del archivo Datos01 a Finanzas01

Alter database SistemasE
modify file (Name=’Datos01′, newname = ‘Finanzas01’)
go

7. Asignar el tamaño de 30MB al archivo Finanzas01

Alter database SistemasE
modify file (Name=’Finanzas01′, Size = 30MB)
go

8. Ver los archivos del grupo Finanzas

sp_helpfilegroup FINANZAS
go

9. Reducir el archivo Finanzas01 a 10MB

DBCC ShrinkFile (Finanzas01, 10)
go

Note el nuevo tamaño de 10MB

10. Agregar el archivo de registro de transacciones Pasos.ldf

if not exists (select * from sys.database_files where name = ‘Pasos’)
Begin
alter database SistemasE add log file (name=’Pasos’, Filename=’C:\bd\Pasos.ldf’)
End
go