最後更新: 2021-05-10

目錄

• Shrink
• Backup
• Restore

---

Shrink

 

SHRINKFILE

 

Usage:

DBCC SHRINKFILE ( file_name, [target_size] )

# 當沒有填 target_size 時, 那會縮到去 Default size
# target_size > data size 時, 那 target_size = data size

Remark:

用 DBCC SHRINKFILE 前記得用 USE <DB> 先 !!

Example

# shrinks the size of a data file named DataFile1 in the DB UserDB to 5 MB

USE UserDB;
GO
DBCC SHRINKFILE (DataFile1, 5);
GO

Remark

找出 data file 位置

SELECT
    db.name AS DBName,
    type_desc AS FileType,
    Physical_Name AS Location
FROM
    sys.master_files mf
INNER JOIN
    sys.databases db ON db.database_id = mf.database_id

 

---

How Does Shrinking the Log File Work?

 

Truncation log extension: LDF (當有幾個 ldf 時, 只有一個是 active)

Log truncation will occur automatically after a checkpoint (if the database is in SIMPLE recovery model)

or after a log backup (if the database is in FULL or BULK-LOGGED recovery model)

Simple recovery model

The older transactions are marked for reuse and will be overwritten with new ones automatically.

Transaction log file:

Each SQL Server transaction log file is made of smaller parts called virtual log files.

The number of virtual log files is not limited or fixed per transaction log file.

The number of virtual log files can be increased by an auto-grow event

The number of virtual log files can be decreased by shrinking

|  <--------------  Circular File  -------------->  |
|VirtualLog-1|VirtualLog-2|VirtualLog-3|VirtualLog-4|
   (Unused)  |               |         |  (Unused)
             |          Last Checkpoing|
             |       Logical log       |

These files are used to write/log each and every event happening in the database in a manner that first the changes are logged

and then those changes or events are actually executed.
 

Shrinking:

Shrinking the transaction log reduces its physical size by removing one or more inactive virtual log files

Example:

If you have a 600 megabyte (MB) log file that has been divided into six 100 MB virtual logs

the size of the log file can only be reduced in 100 MB increments.

A virtual log file that holds any active log records,

An active virtual log file, is part of the logical log, and it cannot be removed.

If no target size is specified, a shrink-file operation removes only the inactive virtual log files

beyond the last active virtual log file in the file.

Preformance Issue

Data that is moved to shrink a file can be scattered to any available location in the file.

This causes index fragmentation and can slow the performance of queries that search a range of the index.

To eliminate the fragmentation, consider rebuilding the indexes on the file after shrinking.

 

---

Shrinking a log file to a specified target size

 

USE AdventureWorks2008R2;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE AdventureWorks2008R2
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (AdventureWorks2008R2_Log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE AdventureWorks2008R2
SET RECOVERY FULL;
GO

 * A database can be switched to another recovery model at any time (不會影響當前運作)

 * switch recovery model

If you switch from the full or bulk-logged recovery model to the simple recovery model, you break the backup log chain.

所以要確認現在是"simple recovery model" 才好 shrink log files

GUI

Right click DB -> Select Tasks -> Shrink -> Shrink Files -> Select File type "Log"

選項:

• Release unused space
  (reducing the file size without moving any data)
• Reorganize files before releasing unused space (Optionally)
  (Equivalent to executing DBCC SHRINKFILE specifying the target file size)

 

---

---

Backup

 

Backup By Copy Data File

用 copy 去還原 DATA Folder 後, start service 時會有 error

# eventvwr

FCB::Open failed: D:\MSSQL13.MSSQLSERVER\MSSQL\DATA\mastlog.ldf for file number 2.  OS error: 5(Access is denied.).
FCB::Open failed: D:\MSSQL13.MSSQLSERVER\MSSQL\DATA\master.mdf for file number 1.  OS error: 5(Access is denied.).

原因是錯了 permission

Backup By SQL Script

mini:

BACKUP DATABASE AdventureWorks2008R2 
TO DISK = 'Z:\SQLServerBackups\AdventureWorks2008R2.bak';
GO

normal:

BACKUP DATABASE AdventureWorks2008R2
TO DISK = 'Z:\DB_Backup\AdventureWorks2008R2.Bak'
WITH FORMAT, NAME = 'Full Backup of AdventureWorks2008R2';
GO

 * Bold 是必要的選項

WITH 的選項:

• NAME = backup_set_name

If NAME is not specified, it is blank.

• FORMAT

如果沒有加入 FORMAT,  那 backup 是會添加到之前的 backup 上 (Default)
(Format = overwrite file)

backup 到 \\server

When you run Sqlservr.exe as a service, SQL Server runs in a separate session that has no relation to your login session.

The session in which a service runs can have its own mapped drives, although it usually does not.

=> 所以唔可以用 "Z:"

BACKUP DATABASE AdventureWorks2008R2 
   TO DISK = '\\BackupServer\DB_Backup\AdventureWorks2008R2Data.Bak';
GO

Daily Backup

DECLARE @DbName NVARCHAR(512) -- database name
DECLARE @BakStorage NVARCHAR(512) -- path for backup files  
DECLARE @BakFile NVARCHAR(1024) -- used for file name

SET @DbName = 'prod-db'
SET @BakStorage = 'C:\DB_Backup'

-- Code
SET @BakFile = @BakStorage + '\' + @DbName + '_' + DATENAME(dw,GETDATE()) + '.BAK'  
BACKUP DATABASE @DbName
TO DISK = @BakFile
WITH COPY_ONLY, FORMAT;
GO

Batch file run SQL script to Backup

REM # 
"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE" ^
  -S ".\SQLExpress" ^
  -i "C:\DB_Backup\SQL-Backups.sql"

More Info

Example2: More WITH

BACKUP DATABASE [MyDB] TO
DISK = N'Z:\manual_backup\MyDB.bak'
WITH COPY_ONLY, NAME = N'MyDB-Full Database Backup',
NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

'COPY_ONLY' backups

A copy-only backup cannot serve as a differential base

 => differential backup 不會以此作 base.

好處

a backup for a special purpose without affecting the overall backup and restore procedures for the database

DatabaseBackupLSN

When you take a Full backup, the DatabaseBackupLSN(Log Sequence Number) is updated.

Then from that point onwards each Differential backup you take via scheduled backup

     will reference that new LSN not the original one.

In the event of needing to restore, you could restore your scheduled Full backup,

     but when you tried to restore any of the Differential backups taken after the manual job

     you'd find it would fail as the LSNs no longer match.

Differential backup

You have an initial Full backup, and then a sequence of Differentials.

NOFORMAT(Default) | FORMAT

Specifies whether the media header should be written on the volumes used for this backup operation,
overwriting any existing media header and backup sets.

NOFORMAT

Specifies that the backup operation preserves the existing media header and
backup sets on the media volumes used for this backup operation. This is the default behavior.

FORMAT

Specifies that a new media set be created.

causes the backup operation to write a new media header on all media volumes used for the backup operation.

The existing contents of the volume become invalid, because any existing media header and backup sets are overwritten.

Specifying FORMAT implies SKIP

NOINIT(default) | INIT

Controls whether the backup operation appends to or overwrites the existing backup sets on the backup media.

The default is to append to the most recent backup set on the media (NOINIT).

NOSKIP(default) | SKIP

Controls whether a backup operation checks the expiration date and time of the backup sets on the media before overwriting them.

NOSKIP

Instructs the BACKUP statement to check the expiration date of all backup sets on the media before allowing them to be overwritten.

SKIP

Disables the checking of backup set expiration and

name that is usually performed by the BACKUP statement to prevent overwrites of backup sets.

Interaction of SKIP, NOSKIP, INIT, and NOINIT

NOINIT, SKIP

If the volume contains a valid media header, appends the backup set, preserving all existing backup sets.

Default: NOSKIP & NOINIT

If the volume contains a valid media header, verifies that the media name matches the given MEDIANAME, if any.
If it matches, appends the backup set, preserving all existing backup sets.
If the volume does not contain a valid media header, an error occurs.

STATS = 10 # Default

Monitoring Options

Displays a message each time another percentage completes, and is used to gauge progress.

NOREWIND & NOUNLOAD

These options are used only for TAPE devices.

If a nontape device is being used, these options are ignored.

 

---

---

Restore

 

Usage

RESTORE { DATABASE | LOG } database_name
FROM DISK = { 'physical_backup_device_name' | @physical_backup_device_name_var }

詳見:

http://msdn.microsoft.com/en-us/library/ms189621%28v=sql.105%29