Buscar

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:


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.

Mover base de datos sql server a otro disco

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.

Mover base de datos sql server a otro disco

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:

Mover base de datos sql server a otro disco

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

Mover base de datos sql server a otro disco

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

Mover base de datos sql server a otro disco

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".

Mover base de datos sql server a otro disco

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.

17 comentarios:

  1. Estimados, buenas tardes.
    Muy buena explicación.
    La única duda que tengo es si, al separar la BD y luego Adjuntar la BD ¿Se pierden los permisos de las cuentas asociadas a esta base de dato?
    Saludos

    ResponderEliminar
    Respuestas
    1. No, no se pierden los permisos.

      Piensa que solo estamos cambiando la ubicación física de los ficheros.

      Un saludo,

      Xavi.

      Eliminar
  2. Esta perfecto tu manual, pero que sucede si existe algun proceso corriendo en esa BD, como podrias parar ese flujo de datos para mover los files sin afectar transacciones?

    ResponderEliminar
    Respuestas
    1. Hola Filiberto,

      Siempre que he realizado este proceso, lo he hecho situando las bases de datos offline.

      Un saludo,

      Xavi.

      Eliminar
  3. Hola, esto aplica para las Bases de Datos de Sistema? Master, Model y msdb?

    ResponderEliminar
    Respuestas
    1. Buena pregunta.

      Para mover las BBDD de sistema, puedes realizar el siguiente procedimiento:

      1) Usar ALTER DATABASE tal y como indica el post indicando la ruta del MDF y LDF.

      2) Detener la instancia de SQLServer

      3) Mover los archivos a la nueva ubicación.

      4) Reiniciar la instancia de SQLServer o bien el servidor.

      5) Verificar resultado.

      Un saludo,

      Xavi.

      Eliminar
  4. Hola estimados. Hay algun problema en mover solo los archivos demi base de datos a otra particion, dejando en la particion origen las DB del sistema? Gracias!!

    ResponderEliminar
    Respuestas
    1. Hola Oscar,

      No hay problema, puedes dejar las bases de datos de sistema en su ubicación original.

      Un saludo,

      Xavi.

      Eliminar
  5. Hola, tengo una duda relacionada con la seguridad de acceso a la nueva carpeta de datos ¿qué permisos debe tener la nueva carpeta en la que en adelante se van a almacenar los ficheros de BD mdf,ldf y ndf?
    Lo digo por que supongo que, no todas las cuentas del PC-servidor deben tener acceso a esta carpeta de datos. Supongo que el servicio SQL que trabaje con esta tarjeta debe tener alguna "cuenta" de acceso a esta carpeta . ¿Cual es esta cuenta?

    ResponderEliminar
    Respuestas
    1. Hola,

      Para saber la cuenta con la que está funcionando SQL Server, bastará con que ejecutes lo siguiente:

      SELECT servicename, service_account FROM sys.dm_server_services

      Si la instalación de SQLServer es la una instalación por defecto, te aparecerá:

      SQL Server (MSSQLSERVER) LocalSystem
      Agente SQL Server (MSSQLSERVER) LocalSystem

      Como puedes ver, la primera columna se refiere al nombre del servicio mientras que la segunda corresponde al nombre la la cuenta.

      Si la cuenta es LocalSystem, con los permisos NTFS por defecto de una unidad, es suficiente.

      Un saludo,

      Xavi.

      Eliminar
  6. Muchas gracias, me funciono. obviamente lo hice por Gui.

    ResponderEliminar
    Respuestas
    1. Hola Albert,

      Gracias a ti por tu comentario.

      Me alegro que te haya funcionado.

      Un saludo,

      Xavi.

      Eliminar
  7. Buenas...
    Estoy necesitando instalar un servidor nuevo desde CERO.
    Tengo los backup de las bases de datos en un disco externo.
    La idea es poner dos discos.
    El principal será "C" y lo que se pretende es que solo contenga el sistema operativo y SqlServer pero las bases de datos tenerlas en "D" (el otro disco).
    Es Seguro?
    No tendríamos fallas?
    Sería bueno instalar todo el SQLServer primero y luego "rutear" las bases de datos? o... Sería mejor instalar el SQLServer y a la par ya definir la ruta donde estará la base de datos?

    ResponderEliminar
    Respuestas
    1. Hola Ariel,

      Sobre las preguntas que planteas:

      1) Siempre es mejor situar las BBDD en un volumen donde no esté instalado el sistema operativo. Los volúmenes, tanto del sistema operativo como el volumen de la BBDD deberían disponer de discos con tolerancia a fallos, etc..

      2) Puedes definir la ubicación de una base de datos cuando la creas o bien puedes utilizar el procedimiento descrito en este post.

      Un saludo,

      Xavi.



      Eliminar
  8. Hola Xavi.
    Muy bien explicado, gracias.
    Se podrian mover las DDBB a un almacenamiento externo como una cabina de discos o NetApp??
    Jose

    ResponderEliminar
    Respuestas
    1. Hola Jose,

      Sí, puedes situar la BBDD en un storage esterno pero ten cuidado con el rendimiento, etc...

      Un saludo,

      Xavi.

      Eliminar