A continuación dejo un par de ejemplos de como funciona el backup simple de sqlserver 2008 y como hacer un restore. En el primer ejemplo hacemos un drop de la base de datos que en un entorno real puede significar la perdida de un datafile o disco etc etc. El segundo ejemplo es algo más rebuscado y lo que se hace es restaurar la copia de la base de datos para recuperar una tabla y extraer sus datos. En ambos ejemplos se trabaja con bases de datos de ejemplo descargables aquí.
Ejemplo 1. BACKUP Y RECUPERACION SIMPLE EN CASO DE PERDIDA DE LA BASE DE DATOS
En el siguiente ejemplo se hace una copia simple y una diferencial de la base de datos AdventureWorks2008. Una vez hecho se hace un drop de la base de datos para luego restaurar la base de datos al último estado antes de borrar la base de datos.
USE master;
--Modo de recuperació simple para la base de datos en cuestion
ALTER DATABASE AdventureWorks2008 SET RECOVERY SIMPLE;
GO
--Backup simple
BACKUP DATABASE AdventureWorks2008 TO DISK='F:\SQL08\BACKUPDATA\AW2008_Full.bak'
WITH FORMAT;
GO
--Backup diferencial
BACKUP DATABASE AdventureWorks2008 TO DISK='F:\SQL08\BACKUPDATA\AW2008_Diff.bak'
WITH DIFFERENTIAL;
GO
--Destrucción!!!!
DROP DATABASE AdventureWorks2008;
GO
--Restauración del último backup
RESTORE DATABASE AdventureWorks2008 FROM DISK='F:\SQL08\BACKUPDATA\AW2008_Full.bak'
WITH FILE=1, NORECOVERY;
--Restauración de los diferenciales
RESTORE DATABASE AdventureWorks2008 FROM DISK='F:\SQL08\BACKUPDATA\AW2008_Diff.bak'
WITH FILE=1, RECOVERY;
Para este ejemplo cabe destacar que cada vez que restauramos utilizamos la opción NORECOVERY hasta importar el último fichero. También es bueno saber que el parámetro WITH FILE=X indica que "fichero" se importa. En el caso de que estuvieramos añadiendo al primer fichero la copia diferencial en lugar de crear un nuevo fichero, en el momento de restaurar el segundo fichero deberíamos poner WITH FILE=2. Por último, WITH FORMAT elimina el contenido en lugar de añadir.
Ejemplo 2. BACKUP Y RECUPERACIÓN SIMPLE EN CASO DE PERDIDA DE UNA TABLA
Este es el típico caso en el que en una base de datos conviven distintas aplicaciones y hemos perdido alguna tabla o se han hecho modificaciones que requieren recuperar datos. En este caso es posible que por ello no podamos restaurar la base de datos actual con la copia. A continuación muestro como recuperar la base de datos en una nuevo con otro fichero y otro nombre para poder traspasar o comprobar datos:
Partimos de un caso como el anterior donde se tiene una copia full y diferenciales. Suponemos que sabemos cuando se ha producido el error.
USE master;
--Modo de recuperació simple para la base de datos en cuestion
ALTER DATABASE AdventureWorksDW2008 SET RECOVERY SIMPLE;
GO
--Backup simple
BACKUP DATABASE AdventureWorksDW2008 TO DISK='F:\SQL08\BACKUPDATA\AWDW2008.bak'
WITH FORMAT;
GO
--Creación de tabla
CREATE TABLE AdventureWorksDW2008.dbo.Prueba
(
F1 char(2000),
F2 char(2000)
)
--Backup diferencial
BACKUP DATABASE AdventureWorksDW2008 TO DISK='F:\SQL08\BACKUPDATA\AWDW2008_D1.bak'
WITH DIFFERENTIAL;
--Drop de la tabla
DROP TABLE AdventureWorksDW2008.dbo.Prueba;
--Segundo backup diferencial
BACKUP DATABASE AdventureWorksDW2008 TO DISK='F:\SQL08\BACKUPDATA\AWDW2008_D2.bak'
WITH DIFFERENTIAL;
GO
--Restauración del último backup completo en otra base de datos
RESTORE DATABASE AdventureWorks2008DWTemp FROM DISK='F:\SQL08\BACKUPDATA\AWDW2008.bak'
WITH FILE=1,
MOVE N'AdventureWorksDW2008_Data' TO 'F:\SQL08\DATA\MSSQL10.TEST\MSSQL\DATA\AdventureWorksDW2008Temp_Data.mdf',
MOVE N'AdventureWorksDW2008_Log' TO 'F:\SQL08\DATA\MSSQL10.TEST\MSSQL\DATA\AdventureWorksDW2008Temp_Log.mdf',
NORECOVERY;
--Restauración de los diferenciales
RESTORE DATABASE AdventureWorks2008DWTemp FROM DISK='F:\SQL08\BACKUPDATA\AWDW2008_D1.bak'
WITH FILE=1,
RECOVERY;
Este segundo caso sería más sencillo en un entorno donde se incluyera copia de los ficheros de transacciones y pudieramos hacer una restauración point-in-time.
El código que aquí encontramos perfectamente se puede partir y podemos programar ya las copias (full y/o diferenciales) en jobs del agente de sql server. Por ejemplo podríamos hacer la copia full el domingo y de lunes a sabado seguir con los diferenciales, pero esto también se puede programar usando SQL Server Management Studio y hacerlo de forma visual (aunque saber hacerlo desde código no está de más)...