Restaurar una Base de Datos en SQL Server o como cambiar el propietario de los objetos de la base de datos

Índice de contenido

• Introducción.
• Entorno.
• Restaurar la base de datos.
• Habilitar el propietario de los objetos.
• Conclusiones.

Introducción

Prácticamente en todas las aplicaciones tenemos un sistema gestor de base de datos (SGBD) en el que se apoya nuestro desarrollo. Estas bases de datos nos las encontramos en los distintos
entornos, generalmente producción, integración y desarrollo, siendo necesario un sistema de copias de seguridad. Estas copias de seguridad no sólo nos sirven para
evitar una posible pérdida de los datos, también nos sirven para poder replicar estos entornos, en lo que a la base de datos se refiere sin necesidad de tener volver a crear todas las tablas
y demás objetos de la base de datos; y muy importante, sin tener que volver a cargar los datos que contienen.

En este tutorial vamos a ver como podemos replicar una base de datos de SQL Server en un servidor distinto al original sin que exista conexión entre ellos. Esto no tiene mucha complicación
si utilizamos el asistente de SQL Server para la creación y restauración de copias de seguridad, pero como veremos a continuación hay que tener en cuenta los usuarios propietarios de los objetos
de la base de datos (tablas, vistas, etc.). Principalmente cuando el usuario propietario es el que utiliza nuestra aplicación para conectarse a la base de datos. Por esto la segunda parte del
nombre de este tutorial.

Entorno

El tutorial está escrito usando el siguiente entorno:

• Hardware: Pentium 4 a 3.06 GHz, 1024 MB
  RAM.

• Sistema Operativo: Windows XP Profesional. Service Pack 3

• SQL Server 2000

Restaurar la base de datos

Como hemos dicho en la introducción la creación de la copia de seguridad de la base de datos es bastante sencilla si utilizamos el asistente de copias de seguridad que nos proprociona el «SQL Server Enterprise Manager»,
basta con pulsar con el botón derecho sobre la base de datos que queremos hacer la copia y seleccionar la opción «Todas las tareas –> Copia de seguridad de la base de datos» y seleccionar el fichero donde vamos a crear nuestro backup.

El caso que nos ocupa es poder restaurar una copia de seguridad ya existente en nuestro servidor de base de datos. Al igual que la creación de copias de seguridad, el «SQL Enterprise Manager» nos ofrece un asistente para restaurar
copias de seguridad, pero en este caso hay que tener en cuenta un par de detalles; por lo que este proceso lo vamos a explicar más detalladamente.

Antes de nada, lo que vamos a hacer es crearnos una nueva base de datos en nuestro servidor. Esta base de datos es la que utilizaremos como destino de la restauración de la base de datos original. Con «SQL Enterprise Manager»,
crearemos la nueva base de datos, en el apartado de «Bases de datos», botón derecho «Nueva base de datos». Le damos un nombre a la nueva base de datos (ej. restoreDB) y si no sabemos el lenguaje de la base de datos original no os preocupéis,
el proceso de restauración lo cambiará por el apropiado, fijaros que yo he elegido el leguaje «Japanese_CI_AI»

Ahora vamos a restaurar la base de datos, partiendo de una copia de seguridad completa que tenemos en el fichero «myDB.bak». Pulsamos con el botón derecho sobre nuestra base de datos
«restoreDB» y seleccionamos la opción «Todas las tareas –> Restaurar base de datos»; en el asistente que nos sale debemos seleccionar la opción «Desde dispositivos» y escoger nuestro fichero «myDB.bak».

Hasta aquí todo es normal y bastante sencillo; ahora debemos de cambiar a la pestaña «Opciones» y ya tenemos que tener en cuenta un par de detalles. Como la base de datos de destino ya existe, tenemos que marcar la opción «Forzar restauración sobre la base de datos existente»
(·ojo! esto machaca por completo la base de datos destino). Además de forzar la restauración, hay que tener en cuenta la ruta de los archivos a restaurar, en primer lugar comprobamos que en nuestro servidor existe la ruta de directorios, y en segundo lugar, nos aseguramos que
no vamos se van a machar ficheros que pertenezcan a otra base de datos.

Finalmente pulsamos en el botón aceptar para restaurar nuestra base de datos. Finalizado el proceso de restauración, si vemos las propiedades de nuestra base de datos «restoreDB», podemos ver como nos ha cambiado el lenguaje al lenguaje de la base de datos
original, en este caso «Modern_Spanish_CI_AI».

Habilitar el propietario de los objetos

Una vez hemos restaurado la base de datos podemos pensar que ya tenemos todo hecho, sin embargo si abrimos nuestra base de datos vemos que hay dos tablas «Table1» y «Table2», y un procedimiento almacenado «myProcedure» que pertenecen al usuario de base de datos «myOwner».
El primer impulso que tenemos es crear un nuevo inicio de sesión (usuario de SQLServer) con nombre «myOwner» y asociarlo a la base de datos «restoreDB», pero el sistema no nos deja al tener ya un usuario interno dentro de nuestra base de datos con ese mismo nombre.
Por lo que primero tendríamos que eliminarlo, y ésto no podemos hacerlo al ser propietario de varios objetos de la base de datos.

Es aquí donde tenemos que seguir una serie de pasos para poder tener la base de datos completamente restaurada y con un inicio de sesión (usuario de SQLServer) con acceso a la base de datos.

Los pasos a seguir son:

• Creamos un usuario temporal para asociarle todos los objetos del usuario «myOwner».
• Eliminamos el usuario «myOwner» de la base de datos «restoreDB».
• Creamos un nuevo inicio de sesión «myOwner» asociado a nuestra base de datos.
• Volvemos a asignar los objetos al usuario «myOwner».
• Eliminamos el usuario temporal.

Creación de un usuario temporal

Con el «SQL Enterprise Manager» creamos un nuevo inicio de sesión «tempOwner» asociado a la base de datos «restoreDB» y como propietario de la base de datos para poder ejecutar los scripts necesarios.

Ahora tenemos que cambiar el propietario de los objetos que pertenecen al usuario «myOwner» y ponerles como propietario el usuario «tempOwner». Para ésto, SQLServer nos ofrece el procedimiento «sp_changeobjectowner»
pero tendríamos que ir buscando todos los objetos que pertenecen al usuario «myOwner» e ir ejecutándolo uno a uno. Para que esta tarea no sea tan costosa vamos a crear nosotros un procedimiento almacenado que nos cambie
el propietario de todos los objetos que pertenecen al usuario «myOwner». Abrimos el «Analizador de consultas» y nos conectamos a la base de datos «restoreDB» con el usuario «tempOwner» y ejecutamos el siguiente script.

CREATE PROCEDURE [dbo].[chObjOwner]( @usrName varchar(20), @newUsrName varchar(50))
as
-- @usrName propietario actual
-- @newUsrName nuevo propietario

set nocount on
declare @uid int                   -- UID del usuario
declare @newUid int                -- UID del nuevo usuario
declare @objName varchar(50)       -- Objetos que pertenecen al usuario
declare @currObjName varchar(50)   -- Objetos que pertenecen al usuario "owner"."object" 
declare @sqlStr nvarchar(256)       -- Comando SQL para cambiar el propietario de los objetos con 'sp_changeobjectowner'
set @uid = user_id(@usrName)

declare chObjOwnerCur cursor static
for
select name from sysobjects
where uid = @uid
AND xtype in ( 'P', 'U', 'V')

open chObjOwnerCur

fetch next from chObjOwnerCur into @objName

while @@fetch_status = 0
begin
  set @currObjName = @newUsrName + '.' + @objName
  set @sqlStr = 'sp_changeobjectowner ''' + @usrName + '.' + @objName + ''',''' + @newUsrName + ''''
  print @sqlStr
    execute sp_executesql @sqlStr
  fetch next from chObjOwnerCur into @objName
end


close chObjOwnerCur
deallocate chObjOwnerCur
set nocount off
return 0
    

Ahora ya sólo tenemos que ejecutar el procedimiento que acabamos de crear para cambiar los objetos de «myOwner» a propiedad de «tempOwner». Para hacer esto ejecutamos:

  EXECUTE chObjOwner 'myOwner','tempOwner'
   

Al ejecutar este script nos salen las siguientes advertencias:

sp_changeobjectowner 'tempOwner.myProcedure','myOwner'
Advertencia: al cambiar cualquier parte del nombre de un objeto pueden dejar de ser válidas secuencias de comandos y procedimientos almacenados.
sp_changeobjectowner 'tempOwner.Table1','myOwner'
Advertencia: al cambiar cualquier parte del nombre de un objeto pueden dejar de ser válidas secuencias de comandos y procedimientos almacenados.
sp_changeobjectowner 'tempOwner.Table2','myOwner'
Advertencia: al cambiar cualquier parte del nombre de un objeto pueden dejar de ser válidas secuencias de comandos y procedimientos almacenados.   
   

Como después vamos a volver a asignar estos mismos objetos otra vez al usuario «myOwner» no nos deben preocupar. Estas advertencias nos indican que si por ejemplo tenemos un
procedimiento almacenado que haga referencia a «myOwner».»Table1″ ahora no funcionaría ya que no encontraría el objeto al haberlo cambiado de propietario.

Eliminamos el usuario «myOwner» de la base de datos «restoreDB»

Como ahora el usuario de base de datos «myOwner» no es propietario de ningún objeto de base de datos podemos eliminarlo sin que nos de ningún error. Para eliminarlo volvemos
al «SQL Enterprise Manager» y en los usuarios de la base de datos «restoreDB» pulsamos con el botón derecho sobre el usuario «myOwner» y seleccionamos «Eliminar».

Creamos un nuevo inicio de sesión «myOwner» asociado a nuestra base de datos

Ahora ya podemos crear un nuevo inicio de sesión con el nombre «myOwner» y asociarlo a la base de datos «restoreDB».

Volvemos a asignar los objetos al usuario «myOwner»

Ahora tenemos que volver a asociar los objetos al usuario «myOwner» ejecutando:

  EXECUTE chObjOwner 'tempOwner','myOwner'
   

Nos vuelven a salir las mismas alerta que tuvimos antes, pero como ahora estamos dejando la base de datos como estaba, todo volverá a funcionar sin necesidad de hacer cambios.

Eliminamos el usuario temporal

Por último, sólo queda eliminar el inicio de sesion del usuario «tempOwner» desde el «SQL Enterprise Manager» (acordaros que para poder eliminar el usuario es necesario que no tengamos ninguna conexión abierta con dicho usuario).

Conclusiones

Como véis SQLServer nos ofrece unos asistentes que nos facilitan las tareas de backup y restauración de la base de datos, pero aún así hay que tener en cuenta un par de cosas
para poder tener la copia de nuestra base de datos operativa; y siempre es más fácil si nos apoyamos en procedimientos que nos automaticen las labores manuales.

Un saludo.
Borja Lázaro