Transcript TRUNCATE TABLE

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
EM - Restore using database
Restore using filegroup/file
Restore using device
Restore options
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 readonly 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
Rebuilding system databases
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