Todays Topics n Backup Recap n Restoration and
Today’s Topics n Backup Recap n Restoration and Recovery n T-SQL Commands – – – INSERT UPDATE DELETE BEGIN TRAN COMMIT TRAN ROLLBACK TRAN
Backup Types n n Full backup Differential backup Transaction log backup File/filegroup backup
Disaster Recovery in SQL Server examines the Transaction log, going from the last checkpoint to the point at which the server was shut down or failed. n Committed transactions that have not been written to the database are rolled forward and written to the database. n Uncommitted transactions are rolled back and not written to the database.
Recovery Types n Automatic Recovery – occurs when you start SQL Server – ensures data in each db is consistent – checks the transaction log n Manual Recovery – this is done by the administrator – involves restoring one of the backups l Full Backup l Differential Backup l Log Backup n Page 258 has more info on Recovery
Verifying backup files n In Enterprise Manager, Backup folder under Management n Backup/Restore info in msdb system database n Transact SQL commands – RESTORE LABELONLY (media info) – RESTORE HEADERONLY (backup set info) – RESTORE FILELISTONLY (file info) n Table 9 -2 on Page 291
Restoring a Database n Restoring is the process of recovering the database to its original form n Restoring can be done using n Enterprise Manager n Transact SQL commands – RESTORE DATABASE – RESTORE LOG
EM - Restore using database
Restore using filegroup/file
Restore using device
Restore options
Recovery completion options n RECOVERY - Instructs the restore operation to rollback any uncommitted transactions - you cannot apply any more transaction logs n NORECOVERY - Instructs the restore operation to not roll back any uncommitted transactions - lets you apply more transaction logs n STAND BY - Leave database readonly option - lets you apply additional transaction logs
Restoring system databases n n n Do the following Exercises Page 291 Page 298 Page 303 through 306 Any questions Take a break
Restore using TSQL n n Two main commands RESTORE DATABASE RESTORE LOG Examples on Pages 306 -307
RESTORE DATABASE n 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 | STANDBY = undofile] [[, ] NOREWIND | REWIND] [[, ] NOUNLOAD | UNLOAD] [[, ] REPLACE] [[, ] RESTART] [[, ] STATS = percentage] ]
RESTORE LOG n 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 | 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 n 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 n Try exercise on Page 308 only
Rebuilding system databases
Data manipulation n SELECT SQL statement - remember does not modify data n Modify data by using the following SQL commands: – – INSERT, UPDATE, DELETE TRUNCATE TABLE
INSERT statement n Inserts one or more new rows into the specified table or query. n When you use the VALUES clause, only a single row is inserted. n If you use a sub-select statement, the number of rows inserted equals the number of rows returned by the select statement.
INSERT SYNTAX n INSERT one record INSERT [INTO] table_name [WITH table_hints ] | view_name | rowset function [(column_list)] VALUES (values_list) | select_statement | DEFAULT VALUES n INSERT with Sub-Select - multiple records INSERT [INTO] new table_name SELECT fields FROM old table_name
Inserting data n Values can be generated for: – – IDENTITY columns Columns with the timestamp data type Columns with a default value Nullable columns n 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 n Insert a new Record into the Region table in Northwind database using the following – INSERT INTO REGION (Region. ID, Region. Description) VALUES (5, ‘Midwest’) n Create a table Table 1 in Northwind first. The structure should be the same as Shippers table. n Then execute this SQL – INSERT INTO TABLE 1 SELECT * FROM SHIPPERS
UPDATE statement n This statement is used to update data in the tables n Uses a WHERE clause to seek the rows that need to be updated n Can use a sub-select to update data n In the absence of WHERE clause, all the records are updated - Therefore Be careful with this!
UPDATE SYNTAX n 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 n Lets say that we need to update the Contact for ‘Eastern Connection’ in the Customers table from Ann Devon to Jim Smith n We can do this using the following n UPDATE CUSTOMERS SET CONTACTNAME='Jim Smith’ WHERE COMPANYNAME=‘EASTERN CONNECTION’ n Now you can review the change by doing n SELECT * FROM CUSTOMERS WHERE COMPANYNAME=‘EASTERN CONNECTION’
Deleting rows n DELETE – Remove all rows from the table – Reclaim space from table rows and indexes – Leave the table structure and all indexes in place n TRUNCATE TABLE: – Runs a non-logged – Resets the identity value
DELETE statement n Used to delete data from tables n Like the Update statement uses the WHERE clause to located the records to delete. n In case of No WHERE clause, deletes all the records in the table n Be very careful with this one also!
DELETE SYNTAX n 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 n 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 n Removes all records from a table. n TRUNCATE TABLE syntax: TRUNCATE TABLE [[database. ]owner. ]table_name n TRUNCATE TABLE 1
Transaction statements n BEGIN TRANSACTION BEGIN TRAN[SACTION] [transaction_name | @tran_name_var] [WITH MARK ['description'] n COMMIT TRANSACTION COMMIT [TRAN[SACTION]] [transaction_name | @tran_name_var]] n ROLLBACK TRANSACTION ROLLBACK TRAN[SACTION] [transaction_name | @tran_name_var | savepoint_name | @savepoint_name_var]
Using transactions n Lets say we want to update the title for employees with title ‘Sales Representative’ to ‘Sales Associate’. n 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 n Try the following n begin tran n update employees set title='Sales Associate’ n Now check the data - Notice all the titles have been changed by mistake n Try this n rollback tran n Now check it again n select * from employees
Using transactions - Part 2 n Now try the following n begin tran n update employees set title='Sales Associate’ where title='Sales Representative' n Now check the data again - Notice all the titles have been changed correctly n Go ahead and apply the change by using n commit tran
- Slides: 34