SQL Server Pages Transaction vs Differential Backup Restore and Tail Log Crash Recovery скачать в хорошем качестве

SQL Server Pages Transaction vs Differential Backup Restore and Tail Log Crash Recovery

--Page and extend -- https://docs.microsoft.com/en-us/sql/... select page_type,count(1) from sys.dm_os_buffer_descriptors where database_id = DB_ID() group by page_type -- 1)When you create a new database best practice is to create an immediate backup after the creation -- why: Because once the full backup is set you can recover the data in case of disaster after 4/5 hours. else recovery not possible without backup --if the backup is in simple recovery then the last full backup is mandatory and in case of full recovery to enable transaction log backup last full backup is mandatory -- 2) when you switch the database to full from the simple recovery we must take full backup otherwise it will fail to truncate the log as a simple recovery model. drop database Dextor_Demo CREATE DATABASE Dextor_Demo ON ( NAME = Dextor_Demo_data, FILENAME = 'D:\SQL PRACTICE\DATA\dextordata.mdf', SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 ) LOG ON ( NAME = Dextor_Demo_log, FILENAME = 'D:\SQL PRACTICE\LOG\dextorlog.ldf', SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 0 ) ; GO use Dextor_Demo alter database Dextor_Demo modify file (name='Dextor_Demo_log',size=6mb,filegrowth =0) --full backup backup database Dextor_Demo to disk='D:\SQL PRACTICE\backup\Dextor_demo.bak' with init /* Diff backup fail without a full backup Msg 3035, Level 16, State 1, Line 26 Cannot perform a differential backup for database "Dextor_Demo", because a current database backup does not exist. Perform a full database backup by reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option. Msg 3013, Level 16, State 1, Line 26 BACKUP DATABASE is terminating abnormally. */ select total_log_size_in_bytes/1024 - used_log_space_in_bytes/1024 'Free space in MB' ,* from sys.dm_db_log_space_usage backup database Dextor_Demo to disk='D:\SQL PRACTICE\backup\Dextor_Demo_Diffe.bak' with init ,differential /* Msg 4214, Level 16, State 1, Line 34 BACKUP LOG cannot be performed because there is no current database backup. Msg 3013, Level 16, State 1, Line 34 BACKUP LOG is terminating abnormally. */ backup log Dextor_Demo to disk='D:\SQL PRACTICE\backup\Dextor_Demo_log.trn' with init select total_log_size_in_bytes/1024 - used_log_space_in_bytes/1024 'Free space in MB' ,* from sys.dm_db_log_space_usage dbcc shrinkfile('Dextor_Demo_log') backup log Dextor_Demo to disk='D:\SQL PRACTICE\backup\Dextor_Demo_log2.trn' with init -----recover using tail log if we have an only full backup. -- 1)Take tail log backup. Create table employee(empid int,name char(1040) ,city char(7000)); -- rowsize for employee table is 7000+1040+4 = 8044 byte max allowed is 8060 byte insert into employee values (1,'ramesh','mumbai') go 100 sp_helpdb Dextor_Demo --initial log file size select total_log_size_in_bytes/1024 - used_log_space_in_bytes/1024 'Free space in MB' ,* from sys.dm_db_log_space_usage -- dbcc loginfo -- exec sp_spaceused employee DBCC SQLPERF(logspace) select * from sys. databases -- after inserting records upto size of logfile select total_log_size_in_bytes/1024 - used_log_space_in_bytes/1024 'Free space in MB' ,* from sys.dm_db_log_space_usage /* Msg 9002, Level 17, State 2, Line 51 The transaction log for database 'Dextor_Demo' is full due to 'LOG_BACKUP'. */ --take transaction log backup to free space. backup log Dextor_Demo to disk='D:\SQL PRACTICE\backup\Dextor_Demo_log.bak' with init select total_log_size_in_bytes/1024 - used_log_space_in_bytes/1024 'Free space in MB' ,* from sys.dm_db_log_space_usage use Dextor_Demo select count(1) from employee --708 -- crash recovery to of database. -- make it offline and change the master data file name -- take a tail log backup backup log Dextor_Demo to disk='D:\SQL PRACTICE\backup\Dextor_Demo_taillog.bak' with init /* Msg 945, Level 14, State 2, Line 101 Database 'Dextor_Demo' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server error log for details. Msg 3013, Level 16, State 1, Line 101 BACKUP LOG is terminating abnormally. */ -- tail log backup backup log Dextor_Demo to disk='D:\SQL PRACTICE\backup\Dextor_Demo_taillog.bak' with init ,no_truncate drop database ---------------------Restore Backup. Restore database Dextor_Demo from disk='D:\SQL PRACTICE\backup\Dextor_demo.bak' with norecovery Restore database Dextor_Demo from disk='D:\SQL PRACTICE\backup\Dextor_Demo_Diffe.bak' with norecovery Restore log Dextor_Demo from disk='D:\SQL PRACTICE\backup\Dextor_Demo_log.trn' with norecovery Restore log Dextor_Demo from disk='D:\SQL PRACTICE\backup\Dextor_Demo_log2.trn' with norecovery restore log Dextor_Demo from disk='D:\SQL PRACTICE\backup\Dextor_Demo_taillog.bak' with norecovery restore database dextor_demo with recovery Restore headeronly from disk='D:\SQL PRACTICE\backup\Dextor_demo.bak'