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 • 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.
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
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
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
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
Restoring system databases • Do the following Exercises • Page 291 • Page 298 • Page 303 through 306 • Any questions • Take a break
Restore using TSQL • Two main commands • RESTORE DATABASE • RESTORE LOG • Examples on Pages 306-307
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] ]
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] ]
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
Data manipulation • SELECT SQL statement - remember does not modify data • Modify data by using the following SQL commands: • INSERT, • UPDATE, • DELETE • TRUNCATE TABLE
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.
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
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
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
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!
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]
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’
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
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!
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]
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
TRUNCATE TABLE • Removes all records from a table. • TRUNCATE TABLE syntax: TRUNCATE TABLE [[database.]owner.]table_name • TRUNCATE TABLE TABLE1
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]
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
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
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