Is this a good backup strategy?

Question

Is this a good backup strategy?

Viewing 7 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Michael Valentine Jones (5/3/2011)
Raymond van Laake (5/3/2011)
Hi,
I would appreciate any feedback on my backup strategy. I also have a question or three. Note that there is little or no activity between 22:00 and 07:00 (SQLServer 2008 R2 web edition). I keep all the files for 70 hours.
Full Backup - daily at 01:00
Differential Backups - every 3 hours, from 7:00 to 22:00
Transaction Log backups - every hour, from 7:30 to 22:30
Question 1:
If disaster happens at 12:45, I have to restore the last full backup, then the last differential backup, then the last transaction log backups, right? So, after I restore the full backup and the differential backup from 10:00, should I run log backups from 9:30, 10:30, 11:30 and 12:30? Does SQL recognize that the first half hour from the 9:30 log, is already back in place because the 10:00 differential backup was restored?
Question 2:
Using "Maintenance Plans", I can check "Back up the tail of the log, and leave the database in the restoring state". Beats me, should I check this or not?
Question 3:
Is there a good book on Backup and Restoring?
Thanks!
Ray
I would change it to do transaction log backups every 15 minutes, 24x7. It doesn't really cost you extra for performance, and it helps to minimize possible data loss. I personally would not bother with the diff backups, unless there is a large amount of activity, but they won't really hurt.

How much quicker are the diffs to do the restore (excluding time to code in the command when outside the gui)?

I can't imagine it being much faster unless you overwrite the same data over and over again like 100 times / day. Then the diff might be loads faster than all the logs combined.

Does that make sens? Any other considerations?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

SQLRNNR (5/3/2011)
Ninja's_RGR'us (5/3/2011)
SQLRNNR (5/3/2011)
Ninja's_RGR'us (5/3/2011)
GilaMonster (5/3/2011)
Just to pile on, an untested backup strategy is not a backup strategy. It's a backup desperate hope.
I've seen someone cause 4 hours additional downtime during a disaster because he hadn't practised doing restores of full, diff and log, and he restored the diff WITH RECOVERY, resulting in the restore having to be restarted from scratch.
Needless to say, no one was very impressed.
The exact reason why I have a script to generate the restore script at point in time. Tested, works like a charm. Sleeping better at night :w00t:
Care to share?:Whistling:
Sure, but tomorrow when I have access to it.
Basic idea is that you can use the GUI to generate that script.
<Side track>
I once had an issue that could be resolved by adding indexes in the msdb tables where the restore screen would take 15-30 minutes to load because there was a crapload of history in that table (technical term).
Anyhow I don't know if this is still an issue in 2K5 but I used that pretext to build another script that does the same thing
</ Side track>.
In all my jobs I have a specific structure for the backup names. All the files end with the date/time of the file.
From there it's just a matter of loading the filenames with cmdshell and figuring out what needs to be restored to satisfy the PIT.
ATM, the script only deals with fulls and logs since I don't use diffs here.
The other benefit of the script is that I can now leave log backups at 15 minutes 24/7 even if not realllllly required. Since it doesn't take any more time to generate the script or restore the DB.
Remind me to post it tommorrow if I forget.
Thanks

Here's the script. Run in output to text mode, copy / paste, exec.

SET IMPLICIT_TRANSACTIONS ON

SET DATEFORMAT YMD

SET STATISTICS IO OFF

SET NOCOUNT ON

--À quelle date / heure restaurer les DB?

DECLARE @pit AS DATETIME

SET @pit = '2011-05-02 10:07:15.000'

IF OBJECT_ID('tempdb..#out') > 0 DROP TABLE #out

IF OBJECT_ID('tempdb..#cmds') > 0 DROP TABLE #cmds

CREATE TABLE #out (t VARCHAR(500) NULL, dt DATETIME NULL, TYPE CHAR(1))

INSERT INTO #out (t)

EXEC xp_cmdshell 'dir / B /OD Q:\*FORDIA*.bak'

DELETE t FROM #out t WHERE t.t NOT LIKE '%.bak' OR t IS NULL

UPDATE #out SET t = 'Q:\' + t, dt = REPLACE(LEFT(RIGHT(t, 20), 16), 'H', ':'), TYPE = CASE WHEN LOWER(t) LIKE '%full%' THEN 'D' ELSE 'L' END

DECLARE @Exec VARCHAR(8000)

SET @Exec = NULL

;

WITH CTE_BK (t, dt, TYPE)

AS

(

SELECT TOP 1 t, dt, TYPE FROM #out WHERE [TYPE] = 'D' AND dt <= @pit

ORDER BY dt DESC

)

SELECT --t, dt, TYPE ,

CASE dtRestore_seq.[TYPE]

WHEN 'D' THEN 'RESTORE DATABASE [PROD-FORDIA_test_restore] FROM DISK = N''' + t + ''' WITH FILE = 1

, MOVE N''PROD-FORDIA_Data'' TO N''Q:\Test restore\PROD-FORDIA_test_restore.mdf'', MOVE N''PROD-FORDIA_Log'' TO N''Q:\Test restore\PROD-FORDIA_test_restore_1.ldf'', MOVE N''PROD-FORDIA_1_Data'' TO N''Q:\Test restore\PROD-FORDIA_test_restore_2.ndf''

, NORECOVERY, NOUNLOAD, STATS = 10'

WHEN 'L' THEN 'RESTORE LOG [PROD-FORDIA_test_restore] FROM DISK = N''' + t + ''' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10'

WHEN 'STOP_AT' THEN 'RESTORE LOG [PROD-FORDIA_test_restore] FROM DISK = N''' + t + ''' WITH FILE = 1, NOUNLOAD, STATS = 10, STOPAT = N''' + CONVERT(VARCHAR(100), @pit, 126) + ''''

END AS cmd

FROM (

SELECT t, dt, TYPE FROM CTE_BK

UNION ALL

SELECT t, dt, TYPE FROM #out WHERE TYPE = 'L' AND dt > (SELECT dt FROM CTE_BK) AND dt <= @pit

UNION ALL

SELECT TOP 1 t, dt, 'STOP_AT' AS TYPE FROM #out WHERE TYPE = 'L' AND dt > @pit AND EXISTS (SELECT * FROM CTE_BK) ORDER BY dt

) dtRestore_seq

ORDER BY dt

IF OBJECT_ID('tempdb..#out') > 0 DROP TABLE #out

IF OBJECT_ID('tempdb..#cmds') > 0 DROP TABLE #cmds

ROLLBACK

Returns for me :

RESTORE DATABASE [PROD-FORDIA_test_restore] FROM DISK = N'Q:\PROD-FORDIA-FULL 2011-05-02 05H00.bak' WITH FILE = 1

, MOVE N'PROD-FORDIA_Data' TO N'Q:\Test restore\PROD-FORDIA_test_restore.mdf', MOVE N'PROD-FORDIA_Log' TO N'Q:\Test restore\PROD-FORDIA_test_restore_1.ldf', MOVE N'PROD-FORDIA_1_Data' TO N'Q:\Test restore\PROD-FORDIA_test_restore_2.ndf'

, NORECOVERY, NOUNLOAD, STATS = 10

RESTORE LOG [PROD-FORDIA_test_restore] FROM DISK = N'Q:\PROD-FORDIA-TransactionLogs 2011-05-02 05H15.bak' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10

RESTORE LOG [PROD-FORDIA_test_restore] FROM DISK = N'Q:\PROD-FORDIA-TransactionLogs 2011-05-02 05H30.bak' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10

RESTORE LOG [PROD-FORDIA_test_restore] FROM DISK = N'Q:\PROD-FORDIA-TransactionLogs 2011-05-02 05H45.bak' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10

RESTORE LOG [PROD-FORDIA_test_restore] FROM DISK = N'Q:\PROD-FORDIA-TransactionLogs 2011-05-02 06H00.bak' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10

RESTORE LOG [PROD-FORDIA_test_restore] FROM DISK = N'Q:\PROD-FORDIA-TransactionLogs 2011-05-02 06H15.bak' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10

RESTORE LOG [PROD-FORDIA_test_restore] FROM DISK = N'Q:\PROD-FORDIA-TransactionLogs 2011-05-02 06H30.bak' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10

RESTORE LOG [PROD-FORDIA_test_restore] FROM DISK = N'Q:\PROD-FORDIA-TransactionLogs 2011-05-02 06H45.bak' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10

RESTORE LOG [PROD-FORDIA_test_restore] FROM DISK = N'Q:\PROD-FORDIA-TransactionLogs 2011-05-02 07H00.bak' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10

RESTORE LOG [PROD-FORDIA_test_restore] FROM DISK = N'Q:\PROD-FORDIA-TransactionLogs 2011-05-02 07H15.bak' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10

RESTORE LOG [PROD-FORDIA_test_restore] FROM DISK = N'Q:\PROD-FORDIA-TransactionLogs 2011-05-02 07H30.bak' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10

RESTORE LOG [PROD-FORDIA_test_restore] FROM DISK = N'Q:\PROD-FORDIA-TransactionLogs 2011-05-02 07H45.bak' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10

RESTORE LOG [PROD-FORDIA_test_restore] FROM DISK = N'Q:\PROD-FORDIA-TransactionLogs 2011-05-02 08H00.bak' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10

RESTORE LOG [PROD-FORDIA_test_restore] FROM DISK = N'Q:\PROD-FORDIA-TransactionLogs 2011-05-02 08H15.bak' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10

RESTORE LOG [PROD-FORDIA_test_restore] FROM DISK = N'Q:\PROD-FORDIA-TransactionLogs 2011-05-02 08H30.bak' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10

RESTORE LOG [PROD-FORDIA_test_restore] FROM DISK = N'Q:\PROD-FORDIA-TransactionLogs 2011-05-02 08H45.bak' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10

RESTORE LOG [PROD-FORDIA_test_restore] FROM DISK = N'Q:\PROD-FORDIA-TransactionLogs 2011-05-02 09H00.bak' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10

RESTORE LOG [PROD-FORDIA_test_restore] FROM DISK = N'Q:\PROD-FORDIA-TransactionLogs 2011-05-02 09H15.bak' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10

RESTORE LOG [PROD-FORDIA_test_restore] FROM DISK = N'Q:\PROD-FORDIA-TransactionLogs 2011-05-02 09H30.bak' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10

RESTORE LOG [PROD-FORDIA_test_restore] FROM DISK = N'Q:\PROD-FORDIA-TransactionLogs 2011-05-02 09H45.bak' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10

RESTORE LOG [PROD-FORDIA_test_restore] FROM DISK = N'Q:\PROD-FORDIA-TransactionLogs 2011-05-02 10H00.bak' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10

RESTORE LOG [PROD-FORDIA_test_restore] FROM DISK = N'Q:\PROD-FORDIA-TransactionLogs 2011-05-02 10H15.bak' WITH FILE = 1, NOUNLOAD, STATS = 10, STOPAT = N'2011-05-02T10:07:15'

I put an extra safeguard in this script so that you manually have to edit the final script.

In this case you have the comment out the with move line (saves prod db from involontary desctruction).

Then you have to do a quick search / replace for the db name, again for the same reason.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

@ninja - very nice

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

SQLRNNR (5/4/2011)
@Ninja - very nice

Just for the fun of it... I added a filter to ignore my latest 2 backups to simulate lost or corrupt backups files...

Still runs in less than 1 sec and the PIT restore works nicely. The only difference is that it took 9 minute instead of 5 to restore an extra 2.5 days of log backups.

--cmd

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------