Lab 2IS421 Winter 2011–Backup and Restore, etc

DUE February 10, 2011 – start ASAP please

****Questions related to this lab may show up in the midterm****

Instructions: Follow the steps and answer the questions.

Step 1: In the Handouts directory, there is a file named MovieClick.bak. It is generated from backing up a database. Copy the file to a directory you can access from your SQL Server 2008.

Question #1: When have you completed this step?

Step 2: In Management Studio, create a database named movieclick, right click on the database, select Tasks  Restore  Database. In the wizard window, you need to do three things: (1) select the file from a device so you can tell the system what file and where to find the file to restore, (2) which copy of the backup to store (since we only have one copy, just select the only one we have), and (3) select the overwrite option to overwrite existing database. If you run into problems, use Google or to search for solutions. You need to be able to find solutions with just books and the Internet, DBAs do that all the time because it is not possible that someone knows EVERYTHING .

Question #2: When the database was successfully installed, can you tell when was the database last backed up? When, if yes?

Question #3: What is the size of your database?

Question #4: What is the logical name of the database’s data file? What is the logical name of the database’s log file? Where is the log file stored?

Step 3. Create two tables: tblDAT_MyStar and tblDAT_MyStartHistory. The tblDAT_MyStar has two columns FirstName and LastName. They both take nVarchar of 128 characters. The tblDAT_MyStartHistory is used to store history of tblDAT_MyStar. It has four columns, ID_LLNNNNN, FirstName, LastName, and InsertedAt, where LLNNNNN takes the same rule as in Lab 1. The ID_LLNNNNN is an integer and identity. Set it to start at 1000 and increment at 5. Insert your name into the tblDAT_MyStar table.

Question #5: Show the SQL statement generated by SQL Server on how to create these two table.

Step 4. Create a Stored Procedure that copies the record(s) in tblDAT_MyStar into tblDAT_MyStartHistory every time the stored procedure is called. The example is given here to help you out. In your code, make sure that the value returned by GetDate() is assigned to a local variable first. That is, you need to declare a local variable of type that is suitable of storing a date together with time. Populate the header section with your info.

SETANSI_NULLSON

GO

SETQUOTED_IDENTIFIERON

GO

-- ======

-- Author:Jie Liu

-- Create date: 1/18/2011

-- Description:Show an example of SP and copying records

-- ======

createprocedure [sp_CopyingClass]

as

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SETNOCOUNTON;

declare @Status asint

-- Insert statements for procedure here

Begintransaction

INSERTINTO [dbo].[tblDAT_ClassHistory]

([ClassCode]

,[InsertedAt])

Select Classcode,GETDATE()from tblDAT_Class

select @Status =@@ERROR

if @Status = 0

commit

else

rollback

return @Status

END

GO

Question #6: Show the code of your stored procedure that accomplishes step 4.

Step 5. Use SQL Server Agent to run your stored procedure every eight hours on the hour for two days. Name the job CopyMyStart_LLNNNN(replace LL and NNNNN with your info). You may have to create a user for your database.

Question #7: Show screen dump of your job. Show screen dump of your schedule after setting is completed.

Question #8: Show screen dump of your history table.

Step 6. (Optional) Play with the option to show that adding Null to a string returns Null. Then change the option so adding Null to a string returns the string itself.

Question #9: Show the SQL statements and their result showing you have done this one (before and after).