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

Backup and restore database in SQL Server

To backup the database, use below query. Backup database <databaseName> to disk ='<backupLocation>.bak' with init; To backup the logs, use below query. Backup log <databaseName> to disk =' <backupLocation> .trn' with init; To restore from the database backup, use below query. restore database <databaseName> from disk = <backupLocation>.bak ' with norecovery, replace, move 'testmirror' to 'DATA.mdf', move 'testmirror_log' to 'DATA_log.ldf'; To restore from the transaction log, use below query. restore log <databaseName> from disk =' <backupLocation> .trn ' with recovery, replace, move 'testmirror' to 'DATA.mdf', move 'testmirror_log' to 'DATA_log.ldf'
Post a Comment
Read more

Execute queries from SQL files

To execute queries from a SQL file, use below query. MySQL: There are 2 options: If you are in MySQL command line, execute below query. source 'SQLfilePath'; If you are in command line/shell, execute below query. mysql -u userName -p password database < 'SQLfilePath' MSSQL: Use T-SQL, execute below query. sqlcmd -S databaseName -i "SQLfilePath" -o "outputFilePath"
Post a Comment
Read more

Rename database name in MSSQL

To rename the database is MSSQL, use below queries. ALTER DATABASE srcDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE; GO sp_rename ' srcDatabaseName' , ' dest DatabaseName ' ,'DATABASE'; GO ALTER DATABASE dest DatabaseName SET MULTI_USER;
Post a Comment
Read more