Skip to main content

Point - in - time restore


  1. Point in time restore requires a full log chain. This means you need to restore that last full backup and all log backups including the one which contains the point you would like to restore to.RESTORE DATABASE <databaseName> FROM DISK = 'E:\Backup\test.bak' WITH FILE = 2 ,REPLACE ,NORECOVERY;From the above, we will restore the full backup from the file 'E:\Backup\test.bak'. Since this file contains multiple backups, we need to specify which FILE to use.
    You can identify the file number by looking at the Position column when executing this command:
    RESTORE HEADERONLY FROM DISK = 'E:\Backup\test.bak';
  2. Restore each log, specifying the date and time to STOP AT. This needs to be specified in each RESTORE LOG statement
    RESTORE LOG <databaseName> FROM DISK = 'E:\Backup\test.bak' WITH FILE = 4 ,NORECOVERY ,STOPAT = '2013-09-28 10:16:28.873’;
  3. And finally recover the database
    RESTORE DATABASE < databaseName> WITH RECOVERY;
Labels:

Comments

Post a Comment

Popular posts from this blog

Table and its count

Table and its count To view all tables and its count by executing command and immediately, use below commands - MSSQL SELECT      sc. name   + '.' + ta. name   TableName,  SUM (pa. rows ) RowCnt FROM      sys.tables ta INNER   JOIN   sys.partitions pa      ON   pa.OBJECT_ID = ta.OBJECT_ID INNER   JOIN   sys.schemas sc      ON   ta.schema_id = sc.schema_id WHERE   ta.is_ms_shipped = 0  AND   pa.index_id  IN   (1,0) GROUP   BY   sc. name ,ta. name ORDER   BY   SUM (pa. rows )  DESC  
Post a Comment
Read more