SQL - Como hacer un Downgrade de SQL 2005 a SQL 2000 o de SQL 2008 a SQL 2005.

Como hacer un Downgrade de SQL 2005 a SQL 2000 o de SQL 2008 a SQL 2005.

Hay muchas ocasiones en las cuales nos enfrentamos a una situacion que requiere upgradear una base de datos o bien una instancia completa de SQL.

El proceso de upgrade para una sola base de datos es bastante directo ya que solo requiere un backup del server original y un restore en el nuevo. Y por supuesto el scripteo y copia de cualquier login u objeto de servidor relacionado a esa BD.

Sin embargo esta clase de situacion involucra una serie de pruebas exhaustivas de la aplicacion que usa dicha base, y muchas veces una bateria completa de pruebas no es posible ya sea por limitaciones de tiempo o debido a alguna clase de proceso que no es regular (como ser un cierre mensual o algun proceso que corre una vez cada tanto), y muchas veces no es tan sencillo probar todo debido a la complejidad de los procesos y la informacion que requieren para hacerlo.

Teniendo en cuenta esto, la mayoria de los DBAs sabemos que la probabilidad de falla de este tipo de procesos de upgrade es muy baja, sin embargo los procedimientos existentes para la mayoria de los procesos de control de cambios requieren un plan de rollback de todo en el peor escenario.

En este caso el peor escenario seria que tenemos una base upgradeada (por ej. a SQL 2005) y los testeos iniciales fueron exitosos y todo sigue su marcha, pero en algun punto del mes la aplicacion falla debido a una incompatibilidad con la nueva version de la base e imaginemos que es aun peor, y que nos vemos obligados a hacer un rollback a la vieja version debido a una directiva de las altas gerencias de la empresa.

Como ya deben saber no se puede hacer un restore de una base de 2005 en un server de SQL 2000, asi que la unica opcion viable es realizar un export de los datos de la base 2005 e importarlos en la base 2000 en el viejo server.

Para realizar esto necesitamos seguir los siguientes pasos:

- Deshabilitar todas las constraints en la vieja base.
- Deshabilitar todos los triggers en la vieja base.
- Realizar un BCP out de todas las tablas de la nueva BD.
- Poner offline la base con version mas nueva.
- Truncar todas las tablas en la base con version vieja.
- Realizar un BCP in en las tablas de la vieja DB con la nueva data.
- Habilitar las constraints nuevamente.
- Habilitar los triggers nuevamente.

Para hacer esto se deben usar los siguientes scritps:

/**********************************************************************************************/
/* ENABLE/DISABLE FOREIGN KEYS */
/**********************************************************************************************/
DECLARE
@ExStr nVARCHAR(4000),
@tabla VARCHAR(100),
@fkname VARCHAR(100)

DECLARE @ESTADO VARCHAR (20)
SET @ESTADO = 'ENABLE' --change to DISABLE or ENABLE depending what you need to do

DECLARE fke_cur INSENSITIVE CURSOR FOR
SELECT object_name(parent_obj), name
FROM sysobjects s
where xtype in ('F')
order by object_name(parent_obj)

OPEN fke_cur
FETCH NEXT FROM fke_cur INTO @tabla,@fkname

WHILE (@@FETCH_STATUS=0)
BEGIN
IF (@ESTADO='ENABLE')
SET @ExStr= 'ALTER TABLE '+@tabla+' CHECK CONSTRAINT '+@fkname
ELSE
IF (@ESTADO='DISABLE')
SET @ExStr= 'ALTER TABLE '+@tabla+' NOCHECK CONSTRAINT '+@fkname
PRINT @ExStr;
EXECUTE sp_executesql @ExStr
FETCH NEXT FROM fke_cur INTO @tabla,@fkname
END

CLOSE fke_cur
DEALLOCATE fke_cur

/**********************************************************************************************/
/* ENABLE/DISABLE TRIGGERS */
/**********************************************************************************************/
DECLARE @ESTADO VARCHAR (20)
SET @ESTADO = 'ENABLE' -- change for DISABLE or ENABLE depending what you need to do

DECLARE cur_trigger INSENSITIVE CURSOR FOR
SELECT name
FROM sysobjects
WHERE xtype='U';

DECLARE @n_trig VARCHAR(50), @ExStr nVARCHAR(4000), @tabla VARCHAR(100);

OPEN cur_trigger;
FETCH NEXT FROM cur_trigger INTO @tabla;

WHILE (@@FETCH_STATUS=0)
BEGIN
IF (@ESTADO='ENABLE')
SET @ExStr='ALTER TABLE '+@tabla+' ENABLE TRIGGER ALL;'
ELSE
IF(@ESTADO='DISABLE')
SET @ExStr='ALTER TABLE '+@tabla+' DISABLE TRIGGER ALL;';
PRINT @ExStr;
EXECUTE sp_executesql @ExStr;
FETCH NEXT FROM cur_trigger INTO @tabla;
END;

CLOSE cur_trigger;
DEALLOCATE cur_trigger;

/* ************************************************************************
BORRA DATA DE TODAS LAS TABLAS
Nota: No use Truncate porque para usar truncate las FK
tienen que ser dropeadas no es suficiente con deshabilitarlas
************************************************************************* */
DECLARE cur_delete INSENSITIVE CURSOR FOR
SELECT name FROM sysobjects WHERE xtype='U';

DECLARE @n_trig VARCHAR(50),
@ExStr nVARCHAR(4000),
@tabla VARCHAR(100);

OPEN cur_delete;
FETCH NEXT FROM cur_delete INTO @tabla;

WHILE (@@FETCH_STATUS=0)
BEGIN
SET @ExStr='DELETE FROM '+@tabla+';';
print @ExStr
EXECUTE sp_executesql @ExStr;
FETCH NEXT FROM cur_delete INTO @tabla;
END;

CLOSE cur_delete;
DEALLOCATE cur_delete;

/* ************************************************************** */


Espero que este simple proceso los ayude a documentar apropiadamente sus cambios sobre SQL y que los ayude a completar exitosamente un downgrade en el caso que tengan que enfrentarse al mismo.

Nota: Recomiendo que tengan a mano at menos por los primeros 2 meses el ultimo backup realizado sobre la base en la vieja version (por ej. el ultimo backup de la base en SQL 2000), asi tienen el esqueleto de la BD para poder trabajar en caso de tener que hacer un downgrade.


Saludos!
MaX

SQL - How to Downgrade a SQL2005 DB to SQL 2000 or SQL 2008 to SQL2005

There are many times when you have to face a situation that requires to upgrade a database or an entire SQL Instance.

The process for upgrade a single db it's very straightforward since this only requires a backup on the original version and a restore on the new one. And of course script and copy any login or server object related to that DB.

However this kind of situation involves a very comprehensive test of the application that use that database and many times a full battery of test cannot be completed due to time limitations or because some kind of operations are not quite regular (monthly account process or some kind of process that runs every once in a while), and many times it's not so easy to fully test everything due to the complexity of that processes and the input information needed.

Knowing this in most of the cases as a DBA we know that failure probability for this kind of upgrade process is very low, however the procedures in place for most of the change management process requires a backout plan for the worst case scenario.

In this case the worst case would be that we have the DB upgraded for example to SQL 2005, the initial test are good and everything moves on, but in some point of the month the App fails do to some kind of incompatibility with the new version of the DB and imagine even worst that we have to rollback to the older version due to a directive from the high management.

As you may know we cannot restore a SQL 2005 DB into a SQL 2000 server, so the only viable option it's to perform an export and later an import of the data in the old server.

To accomplish that you need to do these steps:

- Disable all the constraints in the old DB
- Disable all triggers in the old DB
- Perform a BCP out of all the tables on the new DB.
- Put the new DB offline.
- Truncate all the data from the tables on the old DB.
- Perform a BCP in of the tables on the old DB.
- Enable all the constraints in the old DB.
- Enable all the triggers in the old DB.

To do this here are the scripts:

/**********************************************************************************************/
/* ENABLE/DISABLE FOREIGN KEYS */
/**********************************************************************************************/
DECLARE
@ExStr nVARCHAR(4000),
@tabla VARCHAR(100),
@fkname VARCHAR(100)

DECLARE @ESTADO VARCHAR (20)
SET @ESTADO = 'ENABLE' --change to DISABLE or ENABLE depending what you need to do

DECLARE fke_cur INSENSITIVE CURSOR FOR
SELECT object_name(parent_obj), name
FROM sysobjects s
where xtype in ('F')
order by object_name(parent_obj)

OPEN fke_cur
FETCH NEXT FROM fke_cur INTO @tabla,@fkname

WHILE (@@FETCH_STATUS=0)
BEGIN
IF (@ESTADO='ENABLE')
SET @ExStr= 'ALTER TABLE '+@tabla+' CHECK CONSTRAINT '+@fkname
ELSE
IF (@ESTADO='DISABLE')
SET @ExStr= 'ALTER TABLE '+@tabla+' NOCHECK CONSTRAINT '+@fkname
PRINT @ExStr;
EXECUTE sp_executesql @ExStr
FETCH NEXT FROM fke_cur INTO @tabla,@fkname
END

CLOSE fke_cur
DEALLOCATE fke_cur

/**********************************************************************************************/
/* ENABLE/DISABLE TRIGGERS */
/**********************************************************************************************/
DECLARE @ESTADO VARCHAR (20)
SET @ESTADO = 'ENABLE' -- change for DISABLE or ENABLE depending what you need to do

DECLARE cur_trigger INSENSITIVE CURSOR FOR
SELECT name
FROM sysobjects
WHERE xtype='U';

DECLARE @n_trig VARCHAR(50), @ExStr nVARCHAR(4000), @tabla VARCHAR(100);

OPEN cur_trigger;
FETCH NEXT FROM cur_trigger INTO @tabla;

WHILE (@@FETCH_STATUS=0)
BEGIN
IF (@ESTADO='ENABLE')
SET @ExStr='ALTER TABLE '+@tabla+' ENABLE TRIGGER ALL;'
ELSE
IF(@ESTADO='DISABLE')
SET @ExStr='ALTER TABLE '+@tabla+' DISABLE TRIGGER ALL;';
PRINT @ExStr;
EXECUTE sp_executesql @ExStr;
FETCH NEXT FROM cur_trigger INTO @tabla;
END;

CLOSE cur_trigger;
DEALLOCATE cur_trigger;

/* ************************************************************************
DELETE DATA FROM ALL TABLES
Note: I didn't use truncate because to truncate the FK must be dropped
it's not enough with the disable.
************************************************************************* */
DECLARE cur_delete INSENSITIVE CURSOR FOR
SELECT name FROM sysobjects WHERE xtype='U';

DECLARE @n_trig VARCHAR(50),
@ExStr nVARCHAR(4000),
@tabla VARCHAR(100);

OPEN cur_delete;
FETCH NEXT FROM cur_delete INTO @tabla;

WHILE (@@FETCH_STATUS=0)
BEGIN
SET @ExStr='DELETE FROM '+@tabla+';';
print @ExStr
EXECUTE sp_executesql @ExStr;
FETCH NEXT FROM cur_delete INTO @tabla;
END;

CLOSE cur_delete;
DEALLOCATE cur_delete;

/* ************************************************************** */

I hope this simple process help you to properly document your changes to the SQL and if you face a downgrade scenario help you to complete it successfully.

Note: I recommend that you have at hand for at least a couple of months the last backup of the old server version of the DB (last backup of the DB on SQL 2000 for example), so you have the complete skeleton of the DB to work with in case of a downgrade.

Regards!
MaX