MSSQL backup e restore

salvataggio

Recovery Model

Verificare il Recovery Model del database:

SELECT name,recovery_model_desc FROM sys.databases WHERE name = 'MioDatabase'
GO

Eventualmente modificarlo:

ALTER DATABASE <database_name> SET RECOVERY FULL
go

Salvataggio

Salvataggio

backup database MioDatabase to disk = '/backup/MioDatabase.bak'
go

Salvataggio differenziale (i delta dall'ultimo full)

BACKUP DATABASE [NomeDatabase]  TO DISK = 'C:\Backup\NomeDatabase_diff.bak'  WITH DIFFERENTIAL, INIT, FORMAT, COMPRESSION
GO

Per per salvare solo il transaction log:

BACKUP LOG MioDatabase TO DISK = '/backup/MioDatabase_20260611.trn' WITH INIT, FORMAT, COMPRESSION
go

Per verificare svuotamento del transaction log:

SELECT * FROM sys.dm_db_log_space_usage;
go

Con versioni precedenti alla 2019:

DBCC SQLPERF(LOGSPACE);
go

Riferimenti: [1]

ripristino

caso semplice

Per leggere il contenuto di un backup, serve eventualmente per modificare i percorsi dei files da restorare

RESTORE HEADERONLY FROM DISK = '/backup/MioDatabase.bak';
GO

restore database MioDatabase from disk = '/backup/MioDatabase.bak' with recovery
go

con spostamento di percorso

RESTORE FILELISTONLY FROM DISK = '/backup/MioDatabase.bak';

RESTORE DATABASE MioDatabase
  FROM DISK = '/backup/MioDatabase.bak';
  WITH RECOVERY,
  MOVE 'MioDatabase_Data' TO '/nuovo_percorso/MioDatabase_Data.mdf',
  MOVE 'MioDatabase_Log' TO '/nuovo_percorso/MioDatabase_Log.ldf';
GO

ripristino da completo, incrementali e transaction log

Per restore database e transaction log:

Utile per capire quando è stato eseguito il backup dei log e quale intervallo di tempo copre:

RESTORE HEADERONLY FROM DISK='/backup/MioDB_log_001.trn';
GO

restore database MioDatabase from disk = '/backup/MioDatabase_full.bak' with norecovery
go
restore database MioDatabase from disk = '/backup/MioDatabase_incremental.bak' with norecovery
go
restore log MioDatabase from disk = '/backup/MioDatabase_log01.bak' with norecovery
go

L'ultimo backup del transaction log viene ripristinato con l'opzione recovery:

restore log MioDatabase from disk = '/backup/MioDatabase_log02.bak' with recovery
go

Per point in time:

restore log MioDatabase from disk = '/backup/MioDatabase_log02.bak' with stopat='2026-06-10T15:30:00',recovery
go

post restore

SELECT name,state_desc,recovery_model_desc FROM sys.databases WHERE name = 'MioDatabase';
GO

Eventualmente cambiare il compatibility level

select name,compatibility_level from sys.databases
go

alter database MioDatabase set compatibility_level=170
go

snapshot

Possibile solo con enterprise Edition e dalla versione 2012.

Creazione snapshot

CREATE DATABASE [SCHTCPOS_SS_20261012_110304] ON (NAME = [sofleo_atg_migration_01], FILENAME = 'G:\snapshot\SCHTCPOS_SS_20261012_110304_sofleo_atg_migration_01.ss') AS SNAPSHOT OF [SCHTCPOS];

Restore da snapshot

ALTER DATABASE SCHTCPOS SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
RESTORE DATABASE [SCHTCPOS] from database_snapshot='SCHTCPOS_SS_20261012_110304';
ALTER DATABASE SCHTCPOS SET MULTI_USER;

Cancellazione snapshot

DROP DATABASE [SCHTCPOS_SS_20261012_110304];

Per generare comando di esecuzione snapshot

-- ============================================================================
-- Script per la generazione dinamica del comando CREATE DATABASE SNAPSHOT
-- Target directory: G:\snapshot\
-- ============================================================================

DECLARE @SourceDB SYSNAME = 'TuoNomeDatabase'; -- Sostituisci con il nome del tuo database
DECLARE @SnapshotName SYSNAME = @SourceDB + '_SS_' + CONVERT(VARCHAR(8), GETDATE(), 112) + '_' + REPLACE(CONVERT(VARCHAR(8), GETDATE(), 108), ':', '');
DECLARE @SnapshotPath NVARCHAR(512) = N'G:\snapshot\';

DECLARE @SQL NVARCHAR(MAX) = N'CREATE DATABASE ' + QUOTENAME(@SnapshotName) + ' ON ';

-- Recupera dinamicamente tutti i file di dati (tipo 0 = Row data) del database sorgente
SELECT @SQL = @SQL + STUFF((
    SELECT 
        N', (NAME = ' + QUOTENAME(f.name) + 
        N', FILENAME = ''' + @SnapshotPath + @SnapshotName + N'_' + f.name + N'.ss'')'
    FROM sys.master_files f
    WHERE f.database_id = DB_ID(@SourceDB)
      AND f.type = 0 -- Solo file di dati (esclude i file di log)
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, N'') + 
    N' AS SNAPSHOT OF ' + QUOTENAME(@SourceDB) + N';';

-- Stampa a schermo il comando generato (utile per verifica o log)
PRINT '------------------------------------------------------------';
PRINT 'Comando generato per lo Snapshot:';
PRINT '------------------------------------------------------------';
PRINT @SQL;

storico backup

Interroga gli eventi di backup via VSS

SELECT 
    bs.database_name,
    bs.backup_start_date,
    bs.backup_finish_date,
    CASE bs.type 
        WHEN 'D' THEN 'Full'
        WHEN 'I' THEN 'Differential'
        WHEN 'L' THEN 'Log'
    END AS backup_type,
    bs.is_snapshot,
    bs.is_copy_only,
    bs.name AS backup_set_name,
    bmf.physical_device_name
FROM msdb.dbo.backupset bs
JOIN msdb.dbo.backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
WHERE (bs.is_snapshot = 1 -- solo backup via snapshot VSS
   OR bmf.physical_device_name LIKE '{%') -- I backup VSS generano spesso GUID virtuali come dispositivo
  AND (bs.backup_start_date between '2026-09-22 01:00:00' and '2026-09-22 05:00:00')
ORDER BY bs.backup_start_date DESC;

Eventi di snapshot

EXEC sp_readerrorlog 0, 1, 'I/O is frozen';

EXEC sp_readerrorlog 0, 1, 'I/O was resumed';

Per filtrare per data

DECLARE @StartDate DATETIME = '2026-09-25 00:00:00';
DECLARE @EndDate   DATETIME = '2026-09-25 10:00:00';
EXEC sys.xp_readerrorlog 
   0,                      -- Log attuale
   1,                      -- SQL Server Log
   N'I/O was resumed',     -- Testo da cercare
   NULL,                   -- Secondo testo (opzionale)
   @StartDate,             -- Data/Ora inizio
   @EndDate,               -- Data/Ora fine
   N'DESC';                -- Ordine di visualizzazione