Here is the script for the backup and recovery presentation:
-- change recovery model
ALTER DATABASE Automart SET RECOVERY SIMPLE;
--ALTER DATABASE Automart SET RECOVERY BulkLogged;
ALTER DATABASE Automart SET RECOVERY full;
-- create a full backup of Automart
BACKUP DATABASE Automart
TO DISK = 'C:\Users\ITStudent\Documents\Backups\Automart.bak'
with init;
-- create a differential backup of Automart appending to the last full backup
BACKUP DATABASE Automart
TO DISK = 'C:\Users\ITStudent\Documents\Backups\Automart.bak'
with differential;
-- create a backup of the log
use master;
BACKUP LOG Automart
TO disk = 'C:\Users\ITStudent\Documents\Backups\AutomartLog4.bak'
WITH NORECOVERY, NO_TRUNCATE;
create table TestTable
(
ID int identity(1,1) primary key,
MyTimestamp datetime
);
insert into TestTable values
(
GETDATE()
);
use Automart;
select * from TestTable;
-- restore from the full backup
use master;
RESTORE DATABASE Automart
FROM disk = 'C:\Users\ITStudent\Documents\Backups\Automart.bak'
with norecovery, file = 1;
-- restore from the differential backup on file 2
RESTORE DATABASE Automart
FROM disk = 'C:\Users\ITStudent\Documents\Backups\Automart.bak'
with norecovery, file = 2;
-- restore from the differential backup on file 3
RESTORE DATABASE Automart
FROM disk = 'C:\Users\ITStudent\Documents\Backups\Automart.bak'
with norecovery, file = 3;
-- restore from the log
use master;
RESTORE LOG Automart
FROM disk = 'C:\Users\ITStudent\Documents\Backups\AutomartLog.bak'
WITH NORECOVERY;
restore database Automart;
----------------------------------------------------------------------------------------------------------------------------
--############### CREATE A COMPRESSED, MIRRORED, FULL BACKUP ###########--
BACKUP DATABASE Automart
TO DISK = 'C:\Users\Tri\My Documents\Backups\Automart_backup_20110810.bak'
MIRROR TO DISK = 'C:\Users\Tri\My Documents\Backups1\Automart_backup2_20110810.bak'
WITH COMPRESSION, INIT, FORMAT, CHECKSUM, STOP_ON_ERROR
GO
-- MIRROR TO DISK - TO CREATE A COPY OF BACKUP INTO ANOTHER FOLDER
-- WITH COMPRESSION (option) - TO SAVE SPACE
--CREATE A TRANSACTION LOG BACKUP--
USE Automart
GO
--CREATE A TEST TABLE--
create table Test_DataBACKUP
(
ID int identity(1,1) primary key,
BackUpTime datetime
)
--drop table Test_DataBACKUP
INSERT INTO Test_DataBACKUP
VALUES
(
GETDATE()
)
GO
--SELECT * FROM Test_DataBACKUP
BACKUP LOG Automart
TO DISK = 'C:\Users\Tri\My Documents\Backups\Automart_backup_lOG_20110810.trn'
WITH COMPRESSION, INIT, CHECKSUM, STOP_ON_ERROR
GO
--INSERT INTO TEST TABLE AGAIN TO PERFORM A 2ND TRANSACTION LOG BACK UP--
--INSERT STATEMENT--
--THEN--
BACKUP LOG Automart
TO DISK = 'C:\Users\Tri\My Documents\Backups\Automart_backup2_lOG_20110810.trn'
WITH COMPRESSION, INIT, CHECKSUM, STOP_ON_ERROR
GO
-------------- DIFFERENTIAL BACKUPS ---------------
BACKUP DATABASE Automart
TO DISK = 'C:\Users\Tri\My Documents\Backups\Automart_backup_20110810.dif'
MIRROR TO DISK = 'C:\Users\Tri\My Documents\Backups\Automart_backup2_20110810.dif'
WITH DIFFERENTIAL, COMPRESSION, INIT, FORMAT, CHECKSUM, STOP_ON_ERROR
GO
--################## SET THE RECOVERY MODEL #################--
ALTER DATABASE Automart
SET RECOVERY FULL
GO
--Can also check inside Database Properties in Option.
-------RESTORE A FULL BACK UP--------
--First step in restore Process is to back up the tail of the Log--
BACKUP LOG Automart
TO DISK = 'C:\Users\Tri\My Documents\Backups\Automart_backup3_log_20110810.trn'
WITH COMPRESSION, INIT, NO_TRUNCATE
GO
--Have you ever wonder how we manage to back up the transaction log--
--even when every data file for the database no longer exists.--
--As long as the transaction log has not benn damaged, it is possible to back up the log, even in the--
--absence of every data file within the database.
--Now that you have the tail of the log, execute the following code to restore the full backup--
USE master
GO
RESTORE DATABASE Automart
FROM DISK = 'C:\Users\Tri\My Documents\Backups\Automart_backup_20110810.bak'
WITH FILE = 1,
NOUNLOAD, STATS = 10
Go
--#### WITH STANDBY ? ####--
--RESTORE A DIFFERENTIAL BACKUP--
RESTORE DATABASE Automart
FROM DISK = 'C:\Users\Tri\My Documents\Backups\Automart_backup2_20110810.dif'
WITH RECOVERY
GO
-- WITH RECOVERY - RECOVER A DATABASE TO MAKE IT ACCESSIBLE FOR TRANSACTIONS
--RESTORE LOG BACKUP--
RESTORE LOG Automart
FROM DISK = 'C:\Users\Tri\My Documents\Backups\Automart_backup3_log_20110810.trn'
WITH FILE = 1, NORECOVERY, STATS = 10
GO
Here is the script for the presentation of Dynamic management views
--Retrieve Information about Database Objects
SELECT * FROM sys.databases
SELECT * FROM sys.schemas
SELECT * FROM sys.objects
SELECT * FROM sys.tables
SELECT * FROM sys.columns
SELECT * FROM sys.identity_columns
SELECT * FROM sys.foreign_keys
SELECT * FROM sys.foreign_key_columns
SELECT * FROM sys.default_constraints
SELECT * FROM sys.check_constraints
SELECT * FROM sys.indexes
SELECT * FROM sys.index_columns
SELECT * FROM sys.triggers
SELECT * FROM sys.views
SELECT * FROM sys.procedures
--retrive database and object size
SELECT * FROM sys.database_files
SELECT * FROM sys.partitions
SELECT * FROM sys.allocation_units
SELECT object_name(a.object_id), c.name, SUM(rows) rows,
SUM(total_pages) total_pages, SUM(used_pages) used_pages,
SUM(data_pages) data_pages
FROM sys.partitions a INNER JOIN sys.allocation_units b ON a.hobt_id = b.container_id
INNER JOIN sys.indexes c ON a.object_id = c.object_id and a.index_id = c.index_id
GROUP BY object_name(a.object_id), c.name
ORDER BY object_name(a.object_id), c.name
--Retrieve Index Statistics
SELECT * FROM sys.dm_db_index_operational_stats(NULL,NULL,NULL,NULL)
SELECT * FROM sys.dm_db_index_physical_stats(NULL,NULL,NULL,NULL,NULL)
SELECT * FROM sys.dm_db_index_usage_stats
--Determine Indexes to Create
SELECT * FROM sys.dm_db_missing_index_details
SELECT * FROM sys.dm_db_missing_index_group_stats
SELECT * FROM sys.dm_db_missing_index_groups
--Execute the following aggregation script and review the results
SELECT *
FROM (SELECT user_seeks * avg_total_user_cost * (avg_user_impact * 0.01)
AS index_advantage, migs.*
FROM sys.dm_db_missing_index_group_stats migs) AS migs_adv
INNER JOIN sys.dm_db_missing_index_groups AS mig
ON migs_adv.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid
ON mig.index_handle = mid.index_handle
ORDER BY migs_adv.index_advantage
--Execute the following code to force an index miss against the AdventureWorks database
SELECT City,PostalCode
FROM Person.Address
WHERE City IN ('Seattle','Atlanta')
SELECT City,PostalCode,AddressLine1
FROM Person.Address
WHERE City = 'Atlanta'
SELECT City,PostalCode,AddressLine1
FROM Person.Address
WHERE City like 'Atlan%'
--Execute the aggregation script again and review the results
SELECT *
FROM (SELECT user_seeks * avg_total_user_cost * (avg_user_impact * 0.01)
AS index_advantage, migs.*
FROM sys.dm_db_missing_index_group_stats migs) AS migs_adv
INNER JOIN sys.dm_db_missing_index_groups AS mig
ON migs_adv.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid
ON mig.index_handle = mid.index_handle
ORDER BY migs_adv.index_advantage
--Execute the following script to repeatedly run a SELECT statement and review the new results of the aggregation script
SELECT City,PostalCode,AddressLine1
FROM Person.Address
WHERE City like 'Atlan%'
GO 100
SELECT *
FROM (SELECT user_seeks * avg_total_user_cost * (avg_user_impact * 0.01)
AS index_advantage, migs.*
FROM sys.dm_db_missing_index_group_stats migs) AS migs_adv
INNER JOIN sys.dm_db_missing_index_groups AS mig
ON migs_adv.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid
ON mig.index_handle = mid.index_handle
ORDER BY migs_adv.index_advantage
--Determine Execution Statistics
SELECT query_plan, text, *
FROM sys.dm_exec_requests CROSS APPLY sys.dm_exec_query_plan(plan_handle)
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
SELECT City,PostalCode,AddressLine1
FROM Person.Address
WHERE City like 'Atlan%'
GO 100
--Execute the following script to repeatedly run a SELECT statement and review the new results of the aggregation script
SELECT *
FROM (SELECT user_seeks * avg_total_user_cost * (avg_user_impact * 0.01)
AS index_advantage, migs.*
FROM sys.dm_db_missing_index_group_stats migs) AS migs_adv
INNER JOIN sys.dm_db_missing_index_groups AS mig
ON migs_adv.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid
ON mig.index_handle = mid.index_handle
ORDER BY migs_adv.index_advantage
No comments:
Post a Comment