Mover base de datos sql server a otro disco

Una de las buenas prácticas de cualquier sistema de base de datos, por ejemplo SQL Server, es mantener la ubicación de los ficheros de base de datos (ficheros MDF y LDF) separados de la unidad donde tenemos instalado el sistema operativo, por defecto, la unidad C:

En bases de datos donde se efectúan muchos cambios en el contenido de las bases de datos, también se recomienda situar los logs de transacciones (ficheros LDF) de una unidad distinta a la ubicación de las bases de datos (ficheros MDF) y también distinta a los binarios del sistema operativo.

En el caso de SQL Server, por defecto, la ubicación de las bases de datos de sistema y bases de datos que el administrador vaya creando será la unidad donde se encuentran instalados los binarios de SQL Server, de forma predeterminada, la unidad C:

Dependiendo de la versión de SQL Server, la ruta cambia el directorio que indica la versión, por ejemplo:

SQL Server 2008 R2 (Versión 10.5):

"C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA"

SQL Server 2008 (Versión 10):

"C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA"

Para saber la versión de SQL Server instalada, podemos revisar el siguiente enlace:

MSSQL: Saber la versión de SQL Server instalada (SYSADMIT.com)

En este post veremos como mover una base de datos de SQL Server a otra ruta y también cambiar la ubicación predeterminada al crear nuevas bases de datos.

Veremos como efectuar ambas tareas administrativas vía GUI (SQL Server Management Studio) o vía T-SQL (Transact-SQL).

1) Mover base de datos SQL Server a otro disco

A modo de ejemplo, queremos mover la base de datos de SQL Server: SYSADMIT-DB ubicada en la ruta: C:\test a la ruta: D:\MSSQLData

El nombre lógico y ruta origen de ficheros MDF y LDF es la siguiente:

SYSADMIT-DB       C:\test\SYSADMIT-DB.mdf

SYSADMIT-DB_log   C:\test\SYSADMIT-DB_log.ldf

El nombre lógico y ruta destino de ficheros MDF y LDF es la siguiente:

SYSADMIT-DB     D:\MSSQLData\SYSADMIT-DB_Data.mdf

SYSADMIT-DB_log  D:\MSSQLData\SYSADMIT-DB_Log.ldf

Mover base de datos vía GUI:

Desde "SQL Server Management Studio", conectamos a nuestro SQL Server.

En primer lugar, anotamos el nombre lógico de la base de datos y las rutas actuales donde se ubican los ficheros MDF y LDF.

Para ello, nos situamos sobre la base de datos, botón de derecho, propiedades.

Nos situamos sobre el apartado "Archivos".

Allí podremos ver el nombre lógico la ruta y nombres de archivo para el fichero MDF y LDF de la base de datos.

Una vez tenemos anotada la información anterior, podemos proceder a situar la base de datos sin conexión.

Nos situamos sobre la base de datos, botón derecho, "Separar".

No confundir "Separar" con la opción "Poner sin conexión". Si ponemos sin conexión la base de datos, no podremos modificar su ubicación.

A continuación, desde CMD o vía explorador de ficheros, podemos mover los ficheros de ubicación.

Para ello, podemos abrir una ventana de CMD y ejecutar:

md "D:\MSSQLData"
Move "C:\test\SYSADMIT-DB.mdf" "D:\MSSQLData\SYSADMIT-DB_Data.mdf"
Move "C:\test\SYSADMIT-DB_log.ldf" "D:\MSSQLData\SYSADMIT-DB_log.ldf"

Vista ejecución:

Una vez tenemos los ficheros en la nueva ubicación, nos situamos sobre "Bases de datos", botón derecho, "Adjuntar".

y a continuación, seleccionamos la nueva ubicación y corregimos las rutas del fichero MDF y LDF:

Finalmente ya tendremos la base de datos movida de ubicación.

Mover base de datos vía T-SQL:

A continuación, podemos ver el código T-SQL para realizar el procedimiento de mover la base de datos de SQL Server a otro disco, indicando una ruta para el fichero MDF y otra para el fichero LDF.

En el código T-SQL encontraremos comentarios que explican cada linea de ejecución.

El código T-SQL ha sido verificado sobre SQL Server 2008 R2.

Tengamos en cuenta que hay un paso en el que debemos mover los ficheros de lugar de forma manual, en el código he situado tres lineas de comandos de CMD que deberemos ejecutar desde una ventana de CMD fuera de SQL Server.

También podemos realizar el movimiento de ficheros y renombrado de los mismos utilizando otros métodos como el explorador de Windows.

-- Mostramos las rutas actuales de la base de datos.

USE master SELECT name, physical_name FROM sys.master_files

-- Tomamos nota de los datos de la BD que queremos mover, por ejemplo:
--
-- SYSADMIT-DB         C:\test\SYSADMIT-DB.mdf
-- SYSADMIT-DB_log     C:\test\SYSADMIT-DB_log.ldf
--
-- Siendo la primera columna el nombre lógico de la base de datos y
-- la segunda columna la ruta física
--

GO

-- Situamos la base de datos a offline, haciendo el rollback inmediato de 
-- todas las transacciones abiertas.

ALTER DATABASE "SYSADMIT-DB" SET offline WITH ROLLBACK IMMEDIATE;

GO

-- Procedemos a mover los ficheros, por ejemplo desde CMD con el comando move.
-- Abrimos una ventana de CMD y ejecutamos:
--

md "D:\MSSQLData"
Move "C:\test\SYSADMIT-DB.mdf" "D:\MSSQLData\SYSADMIT-DB_Data.mdf"
Move "C:\test\SYSADMIT-DB_log.ldf" "D:\MSSQLData\SYSADMIT-DB_log.ldf"


-- Modificamos la ruta del MDF de la base de datos.
-- El nombre lógico de la base de datos está obtenido después de la ejecución 
-- del primer comando.

ALTER DATABASE "SYSADMIT-DB" MODIFY FILE ( NAME = "SYSADMIT-DB", FILENAME = "D:\MSSQLData\SYSADMIT-DB_Data.mdf")

GO

-- Modificamos la ruta del LDF de la base de datos.
-- El nombre de la base de datos está obtenido después de la ejecución del primer comando.

ALTER DATABASE "SYSADMIT-DB" MODIFY FILE ( NAME = "SYSADMIT-DB_Log", FILENAME = "D:\MSSQLData\SYSADMIT-DB_Log.ldf")

GO

-- Situamos la base de datos online.

ALTER DATABASE "SYSADMIT-DB" SET online

GO

-- Mostramos las rutas actuales de las base de datos

USE master SELECT name, physical_name FROM sys.master_files

2) Cambiar la ubicación predeterminada de las bases de datos:

Este cambio en la configuración solo afectará a las nuevas bases de datos que creemos.

En el siguiente ejemplo, cambiaremos la ubicación predeterminada de las bases de datos a: D:\MSSQLData

Cambio ruta predeterminada BD vía GUI:

Por ejemplo, desde SQL Server 2008 R2:

Conectamos con "SQL Server Management Studio" a nuestro SQL Server y a continuación, botón derecho sobre el servidor, propiedades.

A continuación veremos la opción "Configuración de base de datos", junto a "Ubicaciones predeterminadas de la base de datos".

Cambio ruta predeterminada BD vía T-SQL:

USE [master]

GO

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', REG_SZ, N'D:\MSSQLData'

GO

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', REG_SZ, N'D:\MSSQLData'

GO

De hecho, si examinamos el código T-SQL, vemos que la configuración se guarda en claves en el registros de Windows, así que también podemos hacer el cambio en la configuración utilizando regedit.