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

Labels:

Comments

Post a Comment

Popular posts from this blog

Identify the duplicates in MySQL

To identify the duplicate entries in MySQL, use below query.                      SELECT COUNT(*) as repetitions, group_concat(id, ' (', startTime, ', ', endTime, ') ' SEPARATOR ' | ') as row_data FROM GROUP BY startTime, endTime HAVING repetitions > 1 FYI, id, startTime and endTime columns should be changed based on your table schema. you can change the output format here like alias and the separator.
Post a Comment
Read more

T-SQL to set Max memory in MSSQL

 To set max memory for Microsoft SQL server, use the below Transact SQL. USE master EXEC sp_configure ''show advanced options'', 1 RECONFIGURE WITH OVERRIDE GO --To set a maximum memory limit, type the following, pressing Enter after each line: USE master EXEC sp_configure ''max server memory (MB)'', <MaxServerMemory> RECONFIGURE WITH OVERRIDE GO --MaxServerMemory is the value of the physical memory in megabytes (MB) that you want to allocate. --To hide the maximum memory setting, type the following, pressing Enter after each line: USE master EXEC sp_configure ''show advanced options'', 0 RECONFIGURE WITH OVERRIDE GO exit
Post a Comment
Read more