Assignment 5 – Backup & Recovery
EACH DELIVERABLE IS NOTED WITH TWO DOLLAR SIGNS IN FRONT ($$), IN RED AND WORTH 6.67 POINTS (partial credit will be given as appropriate)
1) USE THIS WORD DOC AND INSERT THE DELIVERABLES WHERE INDICATED
NOTE: You may want to create a special directory that is easy to remember to store these backups. This will make it simpler to perform the restores.
2) Change the AdventureWorks2014 database to Recovery Mode = FULL (database options)
3) Create a FULL backup of the AdventureWorks2014 database using the CHECKSUM and COMPRESSION options. This backup should include the tables and indexes created in assignment 3.
4) ($$)DELIVERABLE – Add the T-SQL used for the backup to the word doc below. Use SCRIPT function in SSMS, if performing backup through SSMS.
5) Perform the following inserts:
use AdventureWorks2014
insert into CustomerService.Reps values (276, 285)
insert into CustomerService.Reps values (277, 285)
insert into CustomerService.Reps values (278, 285)
insert into CustomerService.Reps values (279, 285)
insert into CustomerService.Reps values (280, 285)
insert into CustomerService.Reps values (281, 285)
insert into CustomerService.Reps values (282, 285)
6) Create a DIFFERENTIAL backup of the AdventureWorks2014 database using the CHECKSUM & COMPRESSION options.
7) ($$)DELIVERABLE – Add the T-SQL used for the backup to the word doc below. Use SCRIPT function in SSMS, if performing backup through SSMS.
8) Perform the following inserts:
use AdventureWorks2014
insert into CustomerService.Contacts values (384,2,CURRENT_TIMESTAMP,’Phone’,’612-555-4492′,NULL,’Customer called to ask about delivery delay with order #4531′)
insert into CustomerService.Contacts values (191,3,CURRENT_TIMESTAMP,’Email’,’734-555-9485′,NULL,’Customer called to discuss new order for product XYZ’)
9) Create a TRANSACTION LOG backup of the AdventureWorks2014 database using the CHECKSUM & COMPRESSION options.
10) ($$)DELIVERABLE – Add the T-SQL used for the backup to the word doc below. Use SCRIPT function in SSMS, if performing backup through SSMS.
11) Perform the following insert:
use AdventureWorks2014
DECLARE @jpg VARBINARY(MAX)
SELECT @jpg = BulkColumn
FROM OPENROWSET(BULK N’c:\pick a jpg file.jpg’, SINGLE_BLOB) AS Document
INSERT INTO CustomerService.Attachments (ContactID, AttachmentDateTime, AttachmentName, Attachment) values(2,CURRENT_TIMESTAMP,’Picture of damage’,@jpg)
12) ($$)DELIVERABLE – Take a screenshot of the “SELECT TOP 1000 ROWS” from each of the three tables and attach to the word doc below:
13) Create a FULL backup of the Master database using the CHECKSUM & COMPRESSION options.
14) ($$)DELIVERABLE – Add the T-SQL used for the backup to the word doc below. Use SCRIPT function in SSMS, if performing backup through SSMS.
15) Create a FULL backup of the MSDB database using the CHECKSUM & COMPRESSION options.
16) ($$)DELIVERABLE – Add the T-SQL used for the backup to the word doc below. Use SCRIPT function in SSMS, if performing backup through SSMS.
NOW, LET’S BREAK THE ADVENTUREWORKS2014 DATABASE
17) Perform the following command in a new query window:
DBCC IND (AdventureWorks2014, ‘CustomerService.Contacts’, 1)
18) ($$)DELIVERABLE – Take a screenshot of the output and attach it to the word doc below. It should look something like the following:
19) Identify the PageFID and PagePID for the row with a PageType of 1. In the example above, the PageFID=3 and the PagePID=12, but yours could be different.
20) With this information, execute the following commands replacing the PageFID (second parameter and PagePID (third parameter) with your values. All the rest can remain the same:
ALTER DATABASE AdventureWorks2014 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC WRITEPAGE(‘AdventureWorks2014’, 3, 12, 60, 1, 0x00, 1)
Alter DATABASE Adventureworks2014 SET MULTI_USER
21) Execute the following queries:
use AdventureWorks2014
select * from CustomerService.Reps
use AdventureWorks2014
select * from CustomerService.Contacts
22) ($$)DELIVERABLE – Take a screenshot of the output and attach it to the word doc below. QUESTION: Do both queries fail? What’s the error? Provide answers in the word doc below:
NOW, LET’S RECOVER THE ADVENTUREWORKS2014 DATABASE
23) Perform a TAIL LOG backup of the AdventureWorks2014 database.
24) ($$)DELIVERABLE – Add the T-SQL used for the backup to the word doc below. Use SCRIPT function in SSMS, if performing backup through SSMS.
25) Perform a FULL Restore of the AdventureWorks2014 database with the NORECOVERY option. NOTE: YOU WILL NEED TO CLOSE ALL OF YOUR ACTIVE CONNECTIONS.
26) ($$)DELIVERABLE – Add the T-SQL used for the restore to the word doc below. Use SCRIPT function in SSMS, if performing backup through SSMS.
27) Perform a Differential Restore of the AdventureWorks2014 database with the NORECOVERY option.
28) ($$)DELIVERABLE – Add the T-SQL used for the restore to the word doc below. Use SCRIPT function in SSMS, if performing backup through SSMS.
29) Perform both Transaction Log Restores of the AdventureWorks2014 database with the NORECOVERY option.
30) ($$)DELIVERABLE – Add the T-SQL used for the restore to the word doc below. Use SCRIPT function in SSMS, if performing backup through SSMS.
31) Perform a database restore of the AdventureWorks2014 database with the RECOVERY option.
32) ($$)DELIVERABLE – Add the T-SQL used for the restore to the word doc below. Use SCRIPT function in SSMS, if performing backup through SSMS.
33) ($$)DELIVERABLE – Take a screenshot of the “SELECT TOP 1000 ROWS” from each of the three CustomerService schema tables and attach to the word doc below. Any errors? Do they match previous values? Provide answers below:
34) Go to the backup and restore reports for the AdventureWorks2014 database in SSMS as follows:
35) Expand the “Successful Backup Operations” and “Successful Restore Operations” portions. It should look like the below:
36) ($$)DELIVERABLE – Take a screenshot (or two if necessary) showing all of the events in both sections and add it to the word doc below:
37) If working on a lab machine, remember to backup your databases onto the U: drive to restore for your next assignment. Lab machine users will not be able to continue to the extra credit exercise.
Extra Credit (3 points per deliverable) – perform a Master database recovery as detailed below:
NOTE: This extra credit cannot be done if you are using an FIU LAB machine. This can only be accomplished with a personal installation.
1) Shutdown all SQL Server services except the SQL Server Browser service.
2) Rename the master database file. It should be C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\master.mdf. Change it to master.old.
3) Attempt to start the SQL Server Service. It should fail.
4) Perform the steps in the “Backup, Recovery and Log Management” to rebuild the system databases (slide 70).
a. To verify that the rebuild was successful, simply check that there is a new Master.mdf file in the C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA directory. If the file is not there, then you can look at the latest directory in the C:\Program Files\Microsoft SQL Server\130\Setup Bootstrap\Log directory. There should be a “Summary_xxxx” along with several other log files to help you determine what went wrong.
5) ($$)DELIVERABLE – Take a screenshot of the command prompt window showing the setup.exe command. Add the screenshot below:
6) Perform the steps in the “Backup, Recovery and Log Management” to start the database in single user mode (slide 69)
7) ($$)DELIVERABLE – Take a screenshot of the command prompt window showing the sqlservr.exe output. It will not fit on one screen – that’s OK, just show as much as possible, but containing the end of the output. Add the screenshot below:
8) Go into SSMS and object explorer to see that the AdventureWorks2014 database does not appear.
9) Perform a restore of the master database (taken above in step 13). You will not be able to use the SSMS GUI because Object Explorer needs two connections to do this and you need to be in single user mode. Your can use SSMS, but only by opening a “New Query” without connecting to Object Explorer. Alternatively, you can the SQLCMD command line tool in a separate command prompt window.
10) ($$)DELIVERABLE – Regardless of the method used, take a screenshot of the restore command and the output from the command and add it to the word doc below:
11) You should be able to start SQL Server normally now and see all of your databases. However, try to perform a restore on the AdventureWorks2014 database. ($$)DELIVERABLE – What’s missing? Why and how do you recover it? Provide answers below:
