Thursday, August 11, 2011

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