SQL Server - Copia de seguridad de todas las bases de datos

Este es un script que realiza una copia de seguridad de todas las bases de datos (excepto las bases de datos de sistema) de un servidor SQL Server. 

Los archivos se guardan en formato NameOfDatabase_YYYYMMDDHHMMSS.bak, donde:

NameOfDatabase = nombre de la base de datos

YYYY = año en formato de 4 digitos

MM = mes

DD = día

HH = hora

MM = minutos

SS = segundos.

 

 

-Name of database
DECLARE @DatabaseName VARCHAR(50)

-Path of backup folder
DECLARE @BackupPath VARCHAR(256)

-Name of backup file
DECLARE @BackUpFileName VARCHAR(256)

-Get current date used for suffixing with file name
DECLARE @Date VARCHAR(20)

-Specify path for database backup directory. 
-Make sure directory exists before executing script, else script will give error
SET @BackupPath = ‘C:\Backup\’

-Get current date used for suffixing with file name
SELECT @Date = CONVERT(VARCHAR(20),GETDATE(),112) + REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),‘:’,”)

-Declaring cursor for storing database names
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases

-Excluding system databases
WHERE name NOT IN (‘master’,‘model’,‘msdb’,‘tempdb’)

-For specific database, replace TestDB with 
-required database name in the below line and uncomment it: 
AND name IN (‘TestDB’)

-Fetching database names from cursor to local variable
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @DatabaseName

-Loop for all databases
WHILE @@FETCH_STATUS = 0
BEGIN

    -Setting file name in format NameOfDatabase_YYYYMMDDHHMMSS
    SET @BackUpFileName = @BackupPath + @DatabaseName + ‘_’ + @Date + ‘.bak’

    -Creating back up
    BACKUP DATABASE @DatabaseName TO DISK = @BackUpFileName

    -Fetching next database name
    FETCH NEXT FROM db_cursor INTO @DatabaseName

END

-Close and deallocate cursor
CLOSE db_cursor
DEALLOCATE db_cursor