Tuesday, March 27, 2012
SQL Server : Restoring Transaction Log Backups with Transact-SQL
Transaction log backups must be applied to a database that is in RECOVERY or STANDBY mode; that is, you cannot restore the transaction log backup while the database is operational. Full and differential database backups must be applied prior to restoring transaction log backups.
For example, the following commands restore a full backup of the pubs database, followed by restoring the transaction log:
view source
print?
/* restore the full backup first with NO RECOVERY */
RESTORE DATABASE pubs FROM DISK = 'e:\pubs.bak' WITH NORECOVERY
/* then restore the log WITH RECOVERY */
RESTORE LOG pubs FROM DISK = 'e:\pubs_2300.trn' WITH RECOVERY
Now suppose that you had two transaction log backups to restore, one from 2200 hours and another from 2300. You'd restore the full backup and the first log backup with NORECOVERY and the last transaction log backup with recovery, as follows:
/* restore the full backup first with NO RECOVERY */
<p>RESTORE DATABASE pubs FROM DISK = 'e:\pubs.bak' WITH NORECOVERY
/* then restore the first log WITH NORECOVERY */
RESTORE LOG pubs FROM DISK = 'e:\pubs_2200.trn' WITH NORECOVERY
/* finally restore the second log backup WITH RECOVERY */
</p>
RESTORE LOG pubs FROM DISK = 'e:\pubs_2300.trn' WITH RECOVERY
If you have a differential backup, it must be restored prior to restoring any transaction log backups, as shown in the following example:
RESTORE DATABASE pubs FROM DISK = 'e:\pubs_full.bak' WITH NORECOVERY
<p>RESTORE DATABASE pubs FROM DISK = 'e:\pubs_differential.bak' WITH NORECOVERY
</p>
RESTORE LOG pubs FROM DISK = 'e:\pubs_2300.trn' WITH RECOVERY
The next example shows how to restore transaction logs up to a point in time.
RESTORE DATABASE pubs FROM DISK = 'e:\pubs.bak' WITH NORECOVERY
RESTORE LOG pubs FROM DISK = 'e:\pubs_2300.trn' WITH STOPAT = 'Aug 4, 2003 11:55:55AM', RECOVERY
If you specify a point in time in the future (that is, a point in time that is not recorded in the transaction log backup) you will get the following message:
This log file contains records logged before the designated point-in-time. The database is being left in load state so you can apply another log file.
The next example shows how to restore a transaction log up to a marked transaction. First, let's suppose that we've taken a full backup of pubs database as follows:
BACKUP DATABASE pubs TO DISK = 'e:\pubs.bak' WITH INIT
Next we run a marked transaction, adding a record to the authors table as follows:
BEGIN TRANSACTION TransactionName WITH MARK 'TransactionMark'
<p>INSERT authors ( au_id, au_lname, au_fname, phone, address, city, state, zip, contract)
</p>
SELECT '237-44-4502', 'brown', 'annie', '234-32-3334', '223 amd st', 'namecity', 'tn', '33949', 0 COMMIT TRAN
Further suppose that we ran other transactions which compromised the integrity of the pubs database. Now we wish to restore pubs up to the transaction called 'TransactionName'. To do so, first restore the full backup and then apply the transaction log backup with the STOPATMARK keyword:
RESTORE DATABASE pubs FROM DISK = 'e:\pubs.bak' WITH NORECOVERY
RESTORE LOG pubs FROM DISK = 'e:\pubs_log23.trn' WITH STOPATMARK = 'TransactionName', RECOVERY
To exclude the transaction called 'TransactionName', we would use the same command, except replace STOPATMARK with STOPBEFOREMARK:
RESTORE LOG pubs FROM DISK = 'e:\pubs_log23.trn' WITH STOPBEFOREMARK = 'TransactionName', RECOVERY
SQL Server : sp_MSforeachtable
sp_MSforeachtable is an undocumented stored procedure.
Use:
sp_MSforeachtable can be used to run a query against each table in the database.
Examples:
Updating statestics for each table in the database
Logging the space used by each table in the database
finding the number of rows for each table in database
Syntax:
EXEC sp_MSforeachtable @command1, @replacechar, @command2, @command3, @whereand, @precommand, @postcommand
@command1 – Is the command that hs to be run against each table.
@replacechar – Is the replace charecter for table name. Default value is ?
@command2 – This is the second command that is run after @command1.
@command3 – This is the third command that is run after @command1 and @command2.
@whereand – This is the where clasue that can be used to filter tables.
@precommand – This command is run first by the procedure.It is run before processing any table
@postcommand – This command is run last by the procedure.It is run after processing all the tables.
Note: ? Can be used for table name in @command1,@command2 and @command3.
Example TSQL Queries:
Query1:
Query to list all the tables in the database
EXEC sp_MSforeachtable 'SELECT ''?'''
? – Represents table name
"?" is used as the name of the table is a string and has to be opened and closed in quotes in select clause.
Query2:
Query to save and list all the tables in the database:
CREATE TABLE #Tables (TableName VARCHAR(255)) EXEC sp_MSforeachtable 'INSERT INTO #Tables SELECT ''?''' SELECT * FROM #Tables
Table names are saved into the temporary table #Tables.
Note: There will be a chage in resultsets for query1 and query2.
You can observe that you get multiple resultsets one for each table with query1 and a single resultset containing all table names for query2.
Query3:
Query to update statistics of all the tables in the database.
EXEC sp_MSforeachtable 'UPDATE STATISTICS ?'
The above query executes below commands if you are using Adwentureworks.
UPDATE STATISTICS [Sales].[Store] UPDATE STATISTICS [Production].[ProductPhoto] .... etc
Query4:
Query to calculate the space used for each table.
CREATE TABLE #Tables ( TableName VARCHAR(255), rows int, reserved VARCHAR(255), data VARCHAR(255), index_size VARCHAR(255), unused VARCHAR(255) ); EXEC sp_MSforeachtable 'INSERT INTO #Tables EXEC sp_spaceused ''?''' SELECT * FROM #Tables SELECT sum(rows) from #Tables
The first resultset would be the detailed space used data for each table.
The second resultset will give the total number of rows in all the tables in the database.
Query5:
Using where clause with sp_MSforeachtable(@whereand clause).
Query to execute sp_spaceused procedure and fetch the resulset of all the tables in the database that begin with p.
--drop table #Tables CREATE TABLE #Tables ( TableName VARCHAR(255), rows int, reserved VARCHAR(255), data VARCHAR(255), index_size VARCHAR(255), unused VARCHAR(255) ); EXEC sp_MSforeachtable @command1 = 'INSERT INTO #Tables EXEC sp_spaceused ''?''', @whereand = ' and object_name(object_id) like ''p%'' ' SELECT * FROM #Tables
Query6:
Query to delete data in all the tables in the database.
EXEC sp_MSforeachtable 'delete ?'
Query7:
Query to disable triggers on all tables
EXEC sp_MSforeachtable ' RAISERROR(''Started disabling triggers on table ?'',1,1) ALTER TABLE ? DISABLE TRIGGER ALL '
Query8:
Query to truncate all the tables in the database.
EXEC sp_MSforeachtable 'TRUNCATE TABLE ?'
Query9:
Query to disable constaints on all tables
EXEC sp_MSforeachtable ' RAISERROR(''Started disabling constraints on table ?'',1,1) ALTER TABLE ? NOCHECK CONSTRAINT ALL'
Query10:
Query to enable change tracking on all the tables
EXEC sp_MSforeachtable ' RAISERROR(''Started enabling change tracking on table ?'',1,1) ALTER TABLE ? ENABLE CHANGE_TRACKING;'
Query11:
Query to rebuild clustered index/all non clusted indexes on all the tables
EXEC sp_MSforeachtable ' RAISERROR(''Started rebuild index on table ?'',1,1) ALTER TABLE ? REBUILD WITH (DATA_COMPRESSION = PAGE);'
Query12:
Query to drop all the tables in the database
EXEC sp_MSforeachtable ' RAISERROR(''Started dropping table ?'',1,1) DROP TABLE ?'