Mover base de datos SQL Server

Mover base de datos SQL
9 junio 2015
Josep Ma Solanes 13

Se puede dar el caso que necesitamos mover la ubicación de los archivos de las bases de datos Microsoft SQL Server (versiones 2012, 2014). Ya sea por falta de espacio en disco, para cambiar el tipo de alojamiento, para requerimientos de rendimiento, sistemas de copias de seguridad, etc… Pero, no sólo hay que mover las bases de datos de usuario, sino que, seguramente, también hay que tocar las bases de datos de sistema, es decir, la master, model, msdb y tempdb.

Para hacerlo, se parte de una instalación de Microsoft SQL Server en que las bases de datos, ya sean datos de usuario o de sistema, con sus archivos de datos y transacciones, se encuentran en la misma carpeta de la instancia, ubicadas en un único disco duro. La necesidad es separar estos archivos, poniendo los archivos de datos en un disco duro dedicado y los archivos de transacciones en otro. El resto de datos de la instancia se mantienen en la misma carpeta.

Para comprenderlo mejor, seguiré un ejemplo donde se mueven las bases de datos de su ubicación original (E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA).

moure-base-de-dades-sql-002

A la unidad K:\SQL Server Data, para los archivos de datos, y la unidad L:\SQL Server Logs, para los archivos de transacciones.

moure-base-de-dades-sql-001

Mover bases de datos

Para hacer las operaciones con la base de datos necesitaremos del administrador de SQL Server, vaya, el Microsoft SQL Server Management Studio. Desde el menú de aplicaciones hacer clic en SQL Server Management Studio.

moure-base-de-dades-sql-003

Indicar la instancia SQL Server donde conectar para gestionar con permisos de administrador. Es decir, en la pregunta que se hace indicamos como tipo de servidor: Motor de base de datos, como nombre del servidor se puede seleccionar el propio nombre del equipo, o localhost si es el servidor local. Como autenticación se indica la integrada de Windows. Hacer clic en Conectar para continuar.

moure-base-de-dades-sql-004

 

Se cargan los objetos del servidor Microsoft SQL Server en el panel de explorador de objetos. Hacer clic en el Símbolo de más (+) en la carpeta de base de datos para visualizar su contenido. En la fotografía se observa una base de datos de usuario (servicedesk) y las 4 de sistema (master, model, msdb, tempdb).

moure-base-de-dades-sql-005

 

Cambiar las ubicaciones por defecto de los archivos de bases de datos

Para evitar que las nuevas bases de datos que se creen lo hagan en la carpeta por defecto de la instancia, se tiene que cambiar la ruta por defecto donde se crean los nuevos archivos de bases de datos. Botón derecho sobre el nombre del servidor y hacer clic en Propiedades.

moure-base-de-dades-sql-006

 

Se visualiza una nueva ventana con las propiedades del servidor. En el lado izquierdo, hacer clic en Configuración de base de datos.

moure-base-de-dades-sql-007

En el lado derecho, en la parte inferior, se encuentran las opciones de ubicación de las bases de datos (archivos, transacciones y copias de seguridad). Hacer clic en el botón con los tres puntos (…) Para cambiar la ubicación por defecto de cualquiera de ellas.

moure-base-de-dades-sql-008

Seleccionar la nueva carpeta y hacer clic en el botón Aceptar.

moure-base-de-dades-sql-009

Repetir el proceso para el registro de transacciones. Cuando esté correcto, hacer clic en el botón Aceptar. Recordar que esto hace que las nuevas bases de datos se ubiquen en estas carpetas, pero no mueve las bases de datos ya creadas.

moure-base-de-dades-sql-010

Para aplicar los cambios se debe reiniciar el servicio. Botón derecho sobre el nombre del servidor. Hacer clic en Reiniciar.

moure-base-de-dades-sql-011

Se pide confirmación ya que se perderá el acceso a las bases de datos, hacer clic en el botón Si.

moure-base-de-dades-sql-012

Comprobar que se han aplicado correctamente los cambios después del reinicio volviendo a acceder a las propiedades del servidor SQL Server (Botón derecho sobre el nombre del servidor, hacer clic en la opción Propiedades y acceder al apartado configuración de base de datos).

moure-base-de-dades-sql-013

Para acabar de validar el cambio se puede crear una nueva base de datos. Botón derecho sobre la carpeta base de datos y hacer clic en Nueva base de datos.

moure-base-de-dades-sql-014

Indicar el nombre de la base de datos (BD_Prova). En el listado de archivos de base de datos se puede ver que los caminos donde se ubican los archivos son los nuevos que se han definido. Hacer clic en el botón Aceptar.

moure-base-de-dades-sql-015

Mover la ubicación física de las bases de datos existentes

Desde el administrador de SQL Server (SQL Server Management Studio), botón derecho sobre la base de datos que se quiere mover, hacer clic en Propiedades.

Hacer clic en la opción de archivos.

moure-base-de-dades-sql-017

Se observa que el camino donde están los archivos es el común de la instancia (unidad E en este caso). Hacer clic en Aceptar para cerrar las propiedades.

moure-base-de-dades-sql-018

Llegados a este punto, tenemos dos opciones:

  • Desmontar la base de datos, mover los archivos y cargarla de nuevo con las nuevas ubicaciones. La afectación sólo sería en esta base de datos y no implica la parada del servicio de SQL. No se permite cambiar las bases de datos de sistema (master, model, msdb, tempdb).
  • Cambiar todas las ubicaciones de las bases de datos, parar el servidor SQL Server, mover los archivos y volver a arrancar. Implica la parada del servicio de SQL. Permite cambiar todas las bases de datos.

Personalmente, opto por esta última al encontrarme en fase de mantenimiento, ya que se pretende cambiar las ubicaciones de todas las bases de datos y es una buena manera de hacerlo de golpe.

Desde el administrador de SQL Server, iniciar una nueva consulta haciendo clic en el botón Nueva consulta de la barra de tareas.

moure-base-de-dades-sql-019

Aunque en los comandos se haga la selección de la base de datos, también prefiero seleccionar en la ventana de consulta la base de datos correcta. En la barra de tareas, en el desplegable donde está el nombre de la base de datos, seleccionar la base de datos MASTER que es donde se ejecutarán las diferentes instrucciones.

moure-base-de-dades-sql-020

Comprobar la ubicación física de las diferentes bases de datos por comandos. Escribir las siguientes instrucciones en la ventana de consulta, cambiando el nombre de la base de datos BD_Prova por el que corresponda en cada caso:

Use master
Go
Select name,physical_name from sys.master_files
 Where database_id=DB_ID(N'BD_Prova');
Go

Hacer clic en el botón Ejecutar o pulsar la tecla F5. En la parte inferior del código se visualiza su resultado. Para la base de datos que se acaba de crear (BD_Prova) las ubicaciones ya son las correctas.

moure-base-de-dades-sql-021

El resultado es el mismo que hemos visualizado en sus propiedades. Podemos hacer lo mismo para el resto de bases de datos de sistema y usuario:

Use master
Go
Select name,physical_name from sys.master_files
 Where database_id=DB_ID(N'master');
Go
Select name,physical_name from sys.master_files
 Where database_id=DB_ID(N'model');
Go
Select name,physical_name from sys.master_files
 Where database_id=DB_ID(N'msdb');
Go
Select name,physical_name from sys.master_files
 Where database_id=DB_ID(N'tempdb');
Go

moure-base-de-dades-sql-023

Para no borrar el listado con los nombres de los archivos y su ubicación, que necesitaremos y al que volveremos después para comprobar, abrir una nueva consulta (botón Nueva consulta) y ejecutar el comando de modificación de la ubicación física del archivo dentro de la base de datos:

MODIFY FILE (NAME = (Corresponde a la primera columna del listado anterior), FILENAME='(nueva ubicación del archivo)’);

De esta manera, para nuestro ejemplo, los comandos para las bases de datos model, msdb, tempdb y la del usuario servicedesk (no la incluye en el script al ser un ejemplo), exceptuando la MASTER que es diferente, serían:

Use master;
Go
ALTER DATABASE model
 MODIFY FILE (NAME = modeldev, FILENAME = 'K:\SQL Server Data\model.mdf');
 Go
ALTER DATABASE model
 MODIFY FILE (NAME = modellog, FILENAME = 'L:\SQL Server Logs\modellog.ldf');
 Go
ALTER DATABASE msdb
 MODIFY FILE (NAME = MSDBData, FILENAME = 'K:\SQL Server Data\MSDBData.mdf');
 Go
ALTER DATABASE msdb
 MODIFY FILE (NAME = MSDBLog, FILENAME = 'L:\SQL Server Logs\MSDBLog.ldf');
 Go
ALTER DATABASE tempdb
 MODIFY FILE (NAME = tempdev, FILENAME = 'K:\SQL Server Data\tempdb.mdf');
 Go
ALTER DATABASE tempdb
 MODIFY FILE (NAME = templog, FILENAME = 'L:\SQL Server Logs\templog.ldf');
 Go

moure-base-de-dades-sql-025

Iniciar el configurador de SQL Server. Hacer clic en el botón INICIO de Windows y en el listado de aplicaciones, hacer clic en Administrador de configuración de SQL Server.

moure-base-de-dades-sql-026

En el lado izquierdo, si no está ya seleccionado, hacer clic en Servicios de SQL Server. En el lado derecho, botón derecho sobre SQL Server (Nombre de la instancia) y hacer clic en la opción Parar para detener el servicio. Y sí, se puede hacer desde muchos lugares diferentes más (administrador de servicios, administrador de SQL Server), se ha hecho desde aquí porque se tienen que cambiar unos parámetros en esta ventana.

moure-base-de-dades-sql-027

moure-base-de-dades-sql-028

Con el servicio ya detenido, botón derecho sobre SQL Server (Nombre de la instancia) y hacer clic en Propiedades.

moure-base-de-dades-sql-029

Hacer clic en la pestaña Parámetros de inicio.

moure-base-de-dades-sql-030

De la lista de parámetros, nos interesa el -d y el -l, correspondientes a la ubicación del archivo de datos y transacciones de la base de datos Master.

moure-base-de-dades-sql-031

Seleccionar el parámetro -d… Su valor se carga en el cuadro de texto de la parte superior. Modificar la ruta del archivo y hacer clic en el botón Actualizar.

moure-base-de-dades-sql-032

Repetir el proceso para el parámetro -l… Cuando esté todo correcto, hacer clic en el botón Aceptar. Se advierte que para que los cambios tengan validez se tiene que reiniciar el servicio. No es problema porque ya está el servicio detenido.

moure-base-de-dades-sql-034

Antes de arrancar se deben mover los archivos manualmente en las nuevas ubicaciones. Recordar que el tipo de archivo SQL Server Database Primary Data (marcados en rojo) va a la unidad K y el archivo SQL Server Database Transaction Log (marcados en azul) a la unidad L.

moure-base-de-dades-sql-035

 

moure-base-de-dades-sql-036

Volviendo al administrador de configuración SQL Server, botón derecho sobre SQL Server (nombre instancia) y hacer clic en Iniciar. Si todo está correcto se debe arrancar el servicio de SQL Server y ya se puede cerrar esta aplicación.

moure-base-de-dades-sql-037

Volver al Administrador SQL Server (SQL Management Studio), actualizar la conexión con el servidor, botón derecho sobre el nombre del servidor y hacer clic en Actualizar.

moure-base-de-dades-sql-039

Abrir la pestaña con la consulta de las ubicaciones de los archivos de la base de datos para volverla a ejecutar (Tecla F5 o hacer clic en Ejecutar). Las ubicaciones de los archivos de las bases de datos ya están en las nuevas. Operación completada con éxito.

moure-base-de-dades-sql-040

 

¿Te ha gustado el artículo? Lo puedes compartir en las redes sociales. También puedes dejar tu opinión, comentario o sugerencia. ¡Gracias!

Similar Posts by The Author:

 

13 comentaris per a
“Mover base de datos SQL Server”

  1. Hola Josep , como siempre muchas gracias por tus aportes a la comunidad , una pequeña duda , si deseo mover una base de datos que se encuentra en sql server 2008 a sql server 2012 , con hacer un backup y restaurarla es suficiente .

    Un saludo.

     
    1. Hola Alvaro,
      Para migrar una base de datos de una versión anterior de SQL Server a una nueva, lo puedes hacer como bien dices recuperando la copia de seguridad de la base de datos.

      No obstante, yo prefiero poner en offline la base de datos original (así me aseguro que nadie modifica nada), copio los ficheros al nuevo servidor de SQL y hago un «attach» como una nueva base de datos.

      Eso sí, tanto con un método como en el otro, seguramente tengas que reasociar los logins de usario a los usuarios de la base de datos.

      Saludos,

       
  2. Hola Josep.

    Buen articulo, mil gracias.

    Te comento. Al querer Iniciar el servicio, me da un error de «timeout», revisando el log de errores (Visor de Eventos) me da como error:

    FCB::Open failed: Could not open file C:\SqlServer\miRuta\mastlog.ldf for file number 2. OS error: 5(Acceso denegado.).

    Le he quitado el modo de solo lectura a las carpetas, no se me ocurre que más hacer, Agradezco tu ayuda.

     
    1. Buena reflexión Lenin.

      Los permisos siempre son importantes. Tener en cuenta de utilizar los justos en cada caso. En la instalación de SQL Server, como bien comentas, el servicio del motor debe tener asociado un usuario (en caso de dominio para clústeres, o local si sólo vas a utilizar dicho usuario). Dicho usuario debe tener acceso completo en la ubicación de las bases de datos. Al igual que el usuario asociado a la cuenta del agente debe tener acceso de escritura donde se deba realizar las copias de seguridad de las mismas.

      Saludos.

       
  3. Muy bueno el arctículo, Josep

    Yo sólo tengo una duda. En el caso de querer mover las BBDD del sistema a otra ubicación, y la instancia está en cluster, ¿tengo que llevar a cabo alguna tarea más de las descritas por tí?

    Mugras gracias de antemano,
    Carmelino

     
  4. revise en diferentes foros del Internet como comprimir una base de datos de sql server 2008, lo que quiero hacer es enviar una base de datos de sql server a otra ciudad y cuando intento comprimir me sale error y no se comprime…..espero alguna…respuesta.

     
    1. Buenas Damian, para comprimir los ficheros de la base de datos antes debes parar el servicio de base de datos o poner la base de datos en modo offline, ya que sino el archivo está bloqueado.
      En el fichero comprimido debes añadir los ficheros con extensión mdf y ldf que componen la base de datos.

      Saludos,

       
  5. Buen día, cuando tienes una base de datos que tiene una replicacion de mezcla y no se puede mover los ficheros hay alguna otra forma de hacerlo.

    Se los agradecería mucho.

    Saludos.

     

Deixar un comentari

Recorda que no es contestaran preguntes personals, només d´interés comú que ens enriqueixin a tots.
La teva adreça de correu electrònic no serà publicada. Els camps obligatoris estan indicats.

Este sitio usa Akismet para reducir el spam. Aprende cómo se procesan los datos de tus comentarios.