Database – Restoration process:
While restoring database , the differential database backups will have a reference to the full database backup , i.e through LSN (Log sequence number)
If a full database backup is taken , any subsequent differential database backups
use this latest full database backup as their base .
Hence ,database is restored with the help of sequence of LSN .
LSN 's are stored with the backup file is to verify that the backup files are being restored in the proper sequence .
Copy-Only Backup:
Copy-only backup can be taken , when you want to get a copy of the database for testing purposes .
In SSMS 2005,Copy-only backups are not supported via SQL Server Management Studio (SSMS) and must be performed via the Transact-SQL (T-SQL) BACKUP command.
·
In SSMS 2008 ,it supports GUI or T-SQL
statement to create Copy Only backups.
Example:
To understand copy-only backup , take full back with & without copy-only option
Query
1:
SELECT DB_NAME(database_id) AS [DB Name],
differential_base_lsn AS 'differential backup LSN -
referencing full backup'
WHERE database_id
= DB_ID('AdventureWorks')
AND
type_desc = 'ROWS'
GO
Query
2:
SELECTdatabase_name, backup_start_date,
is_copy_only,
first_lsn as
'LSN of full backup'
FROM
msdb..backupset
WHERE
database_name
= 'AdventureWorks'
ORDER BY backup_start_date DESC
Backup with Copy-only option:
'differential backup LSN - referencing full backup’ from
query 1 will match with 'LSN of full backup' from query 2 , if you take
full back up with copy only option .Though you will observe LSN of full backup
taken with copy-only option as latest entry , it can be ignored by considering is_copy_only = 1
Backup without Copy-only option:
If you take full back up without copy only option, ‘LSN of
full backup' from query 2 will have new entry of latest full backup taken and
' differential backup LSN - referencing full backup’ from query 1 will get
updated accordingly , referencing the LSN of latest full backup taken .
To take copy-only backup,check "copy-only backup" as shown in below image:
Reference :
http://technet.microsoft.com/en-us/library/ms191495.aspx
To take copy-only backup,check "copy-only backup" as shown in below image:
Reference :
http://technet.microsoft.com/en-us/library/ms191495.aspx
No comments:
Post a Comment