November 2, 2012 at 4:37 am
Scenario:
I have Databases which are backed up every night (Full Backup); the Backups are saved on a Tape then deleted from the Sever after three days.
1.The Backups are taken at 8pm the previous day and if something goes wrong, for example at 4ppm the next day I would have to Restore the last Backup then rerestorell the Log files since the last Backup (The Log Backups are done every 15 minutes), imagining the Database will need to be Back online as soon as possible; the procedure will take a very long time.
I wanted to know if there is a better way of performing a Disaster Recovery other than the following Procedure:
RESTORE DATABASE DB_TestLSN_Restore
FROM DISK = 'c:\Backup\DB_TestLSN.bak'
WITH MOVE 'DB_TestLSN' TO 'E:\MSSQL\DATADB_TestLSN_Restore.mdf',
MOVE 'DB_TestLSN_log' TO 'F:\MSSQL\LOG\DB_TestLSN_log_Restore.ldf',
NORECOVERY, NOUNLOAD, STATS = 10
GO
RESTORE LOG DB_TestLSN_Restore
FROM DISK = N'C:\Backup\DB_TestLSN_LOG1'
WITH NORECOVERY, NOUNLOAD, STATS = 10
GO
RESTORE LOG DB_TestLSN_Restore
FROM DISK = N'C:\Backup\DB_TestLSN_LOG2'
WITH RECOVERY, NOUNLOAD, STATS = 10
GO
And so on until the last log is Restored.
2. Is there a way, if possible just to restore a single table if for example something gets deleted or if the table becomes corrupted?
Thank you in advance!
November 2, 2012 at 5:34 am
you can schedule Differential backup according to your environment ,
it can reduces your restore steps.
2. No ,you can not ..not sure if any third party tool available for it.
for table backup you can use
select * into backup_table from original_table
-----------------------------------------------------------------------------
संकेत कोकणे
November 2, 2012 at 9:56 am
The advice above on differentials is correct. One other note, please keep all log backups for all full backups. There is always a possibility that your latest full backup does not work and you go back two days to 8pm. Then you would restore all log backups since then.
I can only speak for Red Gate tools, but our SQL Backup supports object recovery and we have an Object Level Recovery tool. (http://www.red-gate.com/products/dba/sql-backup/). Disclosure, I work for Red Gate.
November 16, 2012 at 4:45 am
Hi
If speed is the issue, then use the right tools, right?
Research VDI. In my experience writing from VDI device connected to a seperate server hosting the backup file was 80% faster than not using VDI.
Keep an eye on the log files. When the db is restored, and it's created as the default size, when the restore happens and you have a 100GB log file the OS will create a file sized 100GB and format it to be available for the database.
Better ways?
So long, and thanks for all the fishpaste 😉
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply