Download
today s topics n.
Skip this Video
Loading SlideShow in 5 Seconds..
Today’s Topics PowerPoint Presentation
Download Presentation
Today’s Topics

Today’s Topics

166 Views Download Presentation
Download Presentation

Today’s Topics

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

  1. Today’s Topics • Backup Recap • Restoration and Recovery • T-SQL Commands • INSERT • UPDATE • DELETE • BEGIN TRAN • COMMIT TRAN • ROLLBACK TRAN

  2. Backup Types • Full backup • Differential backup • Transaction log backup • File/filegroup backup

  3. Disaster Recovery in SQL • SQL Server examines the Transaction log, going from the last checkpoint to the point at which the server was shut down or failed. • Committed transactions that have not been written to the database are rolled forward and written to the database. • Uncommitted transactions are rolled back and not written to the database.

  4. Recovery Types • Automatic Recovery • occurs when you start SQL Server • ensures data in each db is consistent • checks the transaction log • Manual Recovery • this is done by the administrator • involves restoring one of the backups • Full Backup • Differential Backup • Log Backup • Page 258 has more info on Recovery

  5. Verifying backup files • In Enterprise Manager, Backup folder under Management • Backup/Restore info in msdb system database • Transact SQL commands • RESTORE LABELONLY (media info) • RESTORE HEADERONLY (backup set info) • RESTORE FILELISTONLY (file info) • Table 9-2 on Page 291

  6. Restoring a Database • Restoring is the process of recovering the database to its original form • Restoring can be done using • Enterprise Manager • Transact SQL commands • RESTORE DATABASE • RESTORE LOG

  7. EM - Restore using database

  8. Restore using filegroup/file

  9. Restore using device

  10. Restore options

  11. Recovery completion options • RECOVERY - Instructs the restore operation to rollback any uncommitted transactions - you cannot apply any more transaction logs • NORECOVERY - Instructs the restore operation to not roll back any uncommitted transactions - lets you apply more transaction logs • STAND BY - Leave database read-only option - lets you apply additional transaction logs

  12. Restoring system databases • Do the following Exercises • Page 291 • Page 298 • Page 303 through 306 • Any questions • Take a break

  13. Restore using TSQL • Two main commands • RESTORE DATABASE • RESTORE LOG • Examples on Pages 306-307

  14. RESTORE DATABASE • RESTORE DATABASE syntax: RESTORE DATABASE database_name [file_or_filegroup] [FROM backup_device] [WITH [RESTRICTED_USER | DBO_ONLY] [[,] FILE = file_number] [[,] PASSWORD = password] [[,] MEDIANAME = medianame] [[,] MEDIAPASSWORD = password] [[,] MOVE 'logical_filename' TO 'os_filename'] [[,] KEEP_REPLICATION] [[,] NORECOVERY | RECOVERY | STANDBY = undofile] [[,] NOREWIND | REWIND] [[,] NOUNLOAD | UNLOAD] [[,] REPLACE] [[,] RESTART] [[,] STATS = percentage] ]

  15. RESTORE LOG • RESTORE LOG syntax: RESTORE LOG database_name [FROM backup_device] [WITH [RESTRICTED_USER | DBO_ONLY] [[,] FILE = file_number] [[,] PASSWORD = password] [[,] MEDIANAME = medianame] [[,] MEDIAPASSWORD = password] [[,] KEEP_REPLICATION] [[,] MOVE 'logical_filename' TO 'os_filename'] [[,] NORECOVERY | RECOVERY | STANDBY = undofile] [[,] NOREWIND | REWIND] [[,] NOUNLOAD | UNLOAD] [[,] RESTART] [[,] STATS = percentage] [[,] STOPAT = date_time] | [,] STOPATMARK = 'markname'] [AFTER date_time] [,] STOPBEFOREMARK = 'markname'[AFTER date_time] ]

  16. Restoring system databases • When the master db becomes corrupt or if the Server dies on you, you will have to • Rebuilding the master database - Page 310 • Rebuilding System databases - Page 312 • Try exercise on Page 308 only

  17. Rebuilding system databases

  18. Data manipulation • SELECT SQL statement - remember does not modify data • Modify data by using the following SQL commands: • INSERT, • UPDATE, • DELETE • TRUNCATE TABLE

  19. INSERT statement • Inserts one or more new rows into the specified table or query. • When you use the VALUES clause, only a single row is inserted. • If you use a sub-select statement, the number of rows inserted equals the number of rows returned by the select statement.

  20. INSERT SYNTAX • INSERT one record INSERT [INTO] table_name [WITH table_hints ] | view_name | rowset function [(column_list)] VALUES (values_list) | select_statement | DEFAULT VALUES • INSERT with Sub-Select - multiple records INSERT [INTO] new table_name SELECT fields FROM old table_name

  21. Inserting data • Values can be generated for: • IDENTITY columns • Columns with the timestamp data type • Columns with a default value • Nullable columns • When DEFAULT VALUES is specified: • Defaults will be inserted in columns with defined defaults • IDENTITY columns will receive the next identity value • Columns with a timestamp data type will receive the next appropriate values • All other columns will receive a NULL

  22. Inserting data • Insert a new Record into the Region table in Northwind database using the following • INSERT INTO REGION (RegionID, RegionDescription) VALUES (5, ‘Midwest’) • Create a table Table1 in Northwind first. The structure should be the same as Shippers table. • Then execute this SQL • INSERT INTO TABLE1 SELECT * FROM SHIPPERS

  23. UPDATE statement • This statement is used to update data in the tables • Uses a WHERE clause to seek the rows that need to be updated • Can use a sub-select to update data • In the absence of WHERE clause, all the records are updated - Therefore Be careful with this!

  24. UPDATE SYNTAX • UPDATE syntax: UPDATE table_name [WITH table_hint] | view_name | rowset_function SET column_name = expression | DEFAULT | NULL | @variable_name = expression | @variable_name = column = expression [FROM table_name] [WHERE search_conditions] [WHERE CURRNT OF [GLOBAL] cursor_name | cursor_variable] [OPTION query_options]

  25. UPDATE EXAMPLES • Lets say that we need to update the Contact for ‘Eastern Connection’ in the Customers table from Ann Devon to Jim Smith • We can do this using the following • UPDATE CUSTOMERS SET CONTACTNAME='Jim Smith’ WHERE COMPANYNAME=‘EASTERN CONNECTION’ • Now you can review the change by doing • SELECT * FROM CUSTOMERS WHERE COMPANYNAME=‘EASTERN CONNECTION’

  26. Deleting rows • DELETE • Remove all rows from the table • Reclaim space from table rows and indexes • Leave the table structure and all indexes in place • TRUNCATE TABLE: • Runs a non-logged • Resets the identity value

  27. DELETE statement • Used to delete data from tables • Like the Update statement uses the WHERE clause to located the records to delete. • In case of No WHERE clause, deletes all the records in the table • Be very careful with this one also!

  28. DELETE SYNTAX • DELETE syntax: DELETE [FROM] table_name [WITH table_hint] | view_name | rowset_function [FROM table_source] WHERE search_conditions [WHERE CURRNT OF [GLOBAL] cursor_name | cursor_variable] [OPTION query_options]

  29. DELETE EXAMPLE • Lets say we want to remove the row we added in Region table earlier. • SELECT * FROM REGION will show all the records • Get the Region Id which is 5 • DELETE FROM REGION WHERE REGIONID=5

  30. TRUNCATE TABLE • Removes all records from a table. • TRUNCATE TABLE syntax: TRUNCATE TABLE [[database.]owner.]table_name • TRUNCATE TABLE TABLE1

  31. Transaction statements • BEGIN TRANSACTION BEGIN TRAN[SACTION] [transaction_name | @tran_name_var] [WITH MARK ['description'] • COMMIT TRANSACTION COMMIT [TRAN[SACTION]] [transaction_name | @tran_name_var]] • ROLLBACK TRANSACTION ROLLBACK TRAN[SACTION] [transaction_name | @tran_name_var | savepoint_name | @savepoint_name_var]

  32. Using transactions • Lets say we want to update the title for employees with title ‘Sales Representative’ to ‘Sales Associate’. • We will do this twice • First time we will assume that we forgot the WHERE clause and then recover using ROLLBACK TRAN • Second time we will do it the right way and use COMMIT TRAN

  33. Using transactions - Part 1 • Try the following • begin tran • update employees set title='Sales Associate’ • Now check the data - Notice all the titles have been changed by mistake • Try this • rollback tran • Now check it again • select * from employees

  34. Using transactions - Part 2 • Now try the following • begin tran • update employees set title='Sales Associate’ where title='Sales Representative' • Now check the data again - Notice all the titles have been changed correctly • Go ahead and apply the change by using • commit tran