MSSQL backup e restore

backup

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

Verificare il Recovery Model del database:

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

Per backup del solo transaction log:

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

Per verificare svuotamento del transaction log:

SELECT * FROM sys.dm_db_log_space_usage;
GO

Con versioni per 2019:

DBCC SQLPERF(LOGSPACE);
GO

restore

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

Per restore database e transaction log:

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

RESTORE LOG MioDB FROM DISK='/backup/MioDB_log_002.trn' WITH NORECOVERY; GO

post restore

Eventualmente cambiare il compatibility level

select name,compatibility_level from sys.databases
go

alter database MioDatabase set compatibility_level=170
go