1 / 34

Today’s Topics

Today’s Topics. Backup Recap Restoration and Recovery T-SQL Commands INSERT UPDATE DELETE BEGIN TRAN COMMIT TRAN ROLLBACK TRAN. Backup Types. Full backup Differential backup Transaction log backup File/filegroup backup. Disaster Recovery in SQL.

deacon
Download Presentation

Today’s Topics

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

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

More Related