SQL Server: DB Backup

• Transaction log backup:

USE [master]
BACKUP LOG [AdventureWorks]
TO DISK = N'C:\AdventureWorks_LOG.TRN' WITH NOFORMAT,
NOINIT,
NAME = N'AdventureWorks-Transaction log backup.BAK'
GO

• Backup data file:

USE [master]
BACKUP DATABASE [AdventureWorks]
TO DISK = N'C:\AdventureWorks_DATAS.BAK' WITH NOFORMAT,
NOINIT,
NAME = N'AdventureWorks-Backup data file.BAK'
GO

• Differential Backup

 In this example, AW.dif contains all the changes made since the last full backup. You can use it during the restore process in addition to transaction log backups. First, restore the full backup, then restore the latest differential, then restore any transaction logs that
follow it.

BACKUP DATABASE Adventureworks TO DISK = 'D:\data\AW.dif' WITH DIFFERENTIAL,INIT

• Mirrored Backup (SQL Server 2005)

BACKUP DATABASE AdventureWorks TO DISK='D:\data\AW.bak
MIRROR TO DISK = 'E:\data\AW.bak'
MIRROR TO DISK = 'F:\data\AW.bak'
WITH INIT,CHECKSUM,CONTINUE_ON_ERROR

•  BATCH Script

sqlcmd
-S MYSERVER\SQLEXPRESSINSTANCENAME -U sa -P MYPASSWORD
-Q "BACKUP DATABASE [AdventureWorks] TO DISK = N'C:\AdventureWorks_DATAS.BAK'
WITH NOFORMAT, NOINIT,
NAME = N'AdventureWorks-Backup.BAK'"

Or

SET J=%date:~-10,2%
SET A=%date:~-4%
SET M=%date:~-7,2%
SET H=%time:~0,2%
SET MN=%time:~3,2%
SET S=%time:~-5,2%

IF "%time:~0,1%"==" " SET H=0%HEURE:~1,1%

SET REPERTOIRE=C:\Repertoire_Sauvegarde\

SET FICHIER=%REPERTOIRE%\Nom_de_mon_fichier_%J%_%M%_%A%_A_%H%_%MN%_%S%.bak

IF NOT exist "%REPERTOIRE%" md "%REPERTOIRE%"

cd C:\Program Files\Microsoft SQL Server\90\Tools\Binn

sqlcmd -S NOM_DU_SERVEUR\SQLEXPRESS -Q "BACKUP DATABASE NOM_DE_LA BASE TO DISK = N'%FICHIER%' WITH INIT, NAME = N'Sauvegarde automatique de la base de données', STATS = 1"