Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Disaster Recovery Expand / Collapse
Author
Message
Posted Friday, November 02, 2012 4:37 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, February 27, 2014 12:20 AM
Points: 244, Visits: 884
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!

Post #1380263
Posted Friday, November 02, 2012 5:34 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, August 01, 2013 10:17 AM
Points: 323, Visits: 984
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


-----------------------------------------------------------------------------
संकेत कोकणे
Post #1380290
Posted Friday, November 02, 2012 9:56 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 1:49 PM
Points: 32,768, Visits: 14,929
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.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1380474
Posted Friday, November 16, 2012 4:45 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, November 16, 2012 4:39 AM
Points: 31, Visits: 104
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
Post #1385602
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse