备份恢复那些事

Transaction Log Truncation
1. After a checkpoint under Simple recovery model
2. After a log backup under Full or Bulk-logged recovery model

check database size:

SELECT DB_NAME(database_id) AS DatabaseName,type_desc,
SUM((size*8)/1024) SizeMB
FROM sys.master_files
group by DB_NAME(database_id),type_desc
order by DB_NAME(database_id);

select DB_NAME(database_id) AS DatabaseName,type_desc,physical_name,(size*8)/1024 SizeMB
from sys.master_files
order by size desc;

Query why the log is not truncated

select name, recovery_model_desc, log_reuse_wait_desc from sys.databases;

Manual checkpoint:
CHECKPOINT ;


Manual truncate the Tx log:
USE db_name;
GO
dbcc sqlperf(logspace)
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE db_name SET RECOVERY SIMPLE;
GO
Declare @LogFileLogicalName sysname
select @LogFileLogicalName=Name from sys.database_files where Type=1
print @LogFileLogicalName
DBCC SHRINKFILE ( @LogFileLogicalName);
GO
-- Reset the database recovery model.
ALTER DATABASE db_name SET RECOVERY FULL;
GO
dbcc sqlperf(logspace)


Backup the database
Backup database db_name to disk='path';
go
Backup Log db_name to disk='path';
go

Backup with option
{NOINIT|INIT}
By default, BACKUP appends the backup to an existing media set, preserving existing backup sets.
To explicitly specify this, use the NOINIT option, otherwise use the INIT option

{ COMPRESSION | NO_COMPRESSION }
In SQL Server 2008 Enterprise and later onl. It is used normal for disk, tape has its own compress method.

{WITH RETAINDAYS = n}

Log-specific Options
NORECOVERY
Backs up the tail of the log and leaves the database in the RESTORING state. NORECOVERY is useful when failing over to a secondary database
use the NO_TRUNCATE and NORECOVERY options together.
{NO_TRUNCATE =COPY_ONLY,CONTINUE_AFTER_ERROR}
By default, the log will be truncated after the backup





Recover the database
USE master;
--Create tail-log backup.
BACKUP LOG AdventureWorks2012
TO DISK = 'Z:\SQLServerBackups\AdventureWorksFullRM.bak'
WITH NORECOVERY;
GO
--Restore the full database backup (from backup set 1).
RESTORE DATABASE AdventureWorks2012
FROM DISK = 'Z:\SQLServerBackups\AdventureWorksFullRM.bak'
WITH FILE=1,
NORECOVERY;

--Restore the regular log backup (from backup set 2).
RESTORE LOG AdventureWorks2012
FROM DISK = 'Z:\SQLServerBackups\AdventureWorksFullRM.bak'
WITH FILE=2,
NORECOVERY;

--Restore the tail-log backup (from backup set 3).
RESTORE LOG AdventureWorks2012
FROM DISK = 'Z:\SQLServerBackups\AdventureWorksFullRM.bak'
WITH FILE=3,
NORECOVERY;
GO
--recover the database:
RESTORE DATABASE AdventureWorks2012 WITH RECOVERY;
GO