November 18, 2012 at 9:09 am
Hi,
Hi,
Our business can afford to loose data up-to one hour.
So in-case of say if a table is dropped or a disaster, etc., I would like to be able to recover the data at lease up-to one hour ago.
Let's say the database failes at 11:55 pm
I am trying to set the scripts of backup and restore of the database as follows:
Are the sample scripts correct please? See below:
BACKUP
------
1-
Make sure the database recovery is set to full
--schedule this script to run every mid-night monday-friday
BACKUP DATABASE AdventureWorks TO DISK = 'C:\AdventureWorks.BAK'
GO
2-
schedule this transaction log backup to run every 15 minutes
BACKUP LOG AdventureWorks TO DISK = 'C:\AdventureWorks.TRN'
GO
Re-store
--------
1-
RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK'
WITH NORECOVERY
GO
2-
RESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks.TRN'
WITH RECOVERY,
STOPAT = 'Nov 12, 2012 11:45:00 PM'
GO
November 18, 2012 at 10:01 am
Don't append backups to one file. Backups should ideally each to their own file, preferably time-stamped (DatabaseName20121115211501.trn)
To restore to a point in time, you need a full backup taken before that point and all log backups since then (not just one of them)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 19, 2012 at 7:14 am
GilaMonster (11/18/2012)
Don't append backups to one file. Backups should ideally each to their own file, preferably time-stamped (DatabaseName20121115211501.trn)To restore to a point in time, you need a full backup taken before that point and all log backups since then (not just one of them)
1-Do you mean to overwrite?
2-I am thinking of having t he full backup of the database every mid-night and transaction log backups every 15 minutes.
Does this mean if there is a disaster at 11:55 pm, to restore to 11:45 pm I should a) get the previous midnight database backup, b) from that point I have to restore every singly transaction log in order until 11:45 pm?
November 19, 2012 at 7:30 am
arkiboys (11/19/2012)
1-Do you mean to overwrite?
No, write to a new file each time with a unique name
arkiboys (11/19/2012)
2-I am thinking of having t he full backup of the database every mid-night and transaction log backups every 15 minutes.Does this mean if there is a disaster at 11:55 pm, to restore to 11:45 pm I should a) get the previous midnight database backup, b) from that point I have to restore every singly transaction log in order until 11:45 pm?
Yes, the last full backup and any log backups up to the point you wish to restore to.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 19, 2012 at 7:34 am
Perry Whittle (11/19/2012)
arkiboys (11/19/2012)
1-Do you mean to overwrite?No, write to a new file each time with a unique name
arkiboys (11/19/2012)
2-I am thinking of having t he full backup of the database every mid-night and transaction log backups every 15 minutes.Does this mean if there is a disaster at 11:55 pm, to restore to 11:45 pm I should a) get the previous midnight database backup, b) from that point I have to restore every singly transaction log in order until 11:45 pm?
Yes, the last full backup and any log backups up to the point you wish to restore to.
1- if the backup of transaction log is scheduled to run say every 15 minutes then how can I "write to a new file each time with a unique name" ?
2- restoring each transaction log sinice the previous midnight seems to be a long process. How about introducing a differential backup?
November 19, 2012 at 9:05 am
arkiboys (11/19/2012)
1- if the backup of transaction log is scheduled to run say every 15 minutes then how can I "write to a new file each time with a unique name" ?
Have a look at Ola Hallengren's SQL Server Maintenance Solution. It's very easy to setup and use. Read and download from:
Many DBAs use it.
November 19, 2012 at 9:26 am
arkiboys (11/19/2012)
2- restoring each transaction log sinice the previous midnight seems to be a long process. How about introducing a differential backup?
Then you would need to restore the full backup, the latest differential and all log backups from the differential to the point you're restoring to.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 19, 2012 at 9:33 am
arkiboys (11/19/2012)
1- if the backup of transaction log is scheduled to run say every 15 minutes then how can I "write to a new file each time with a unique name" ?
Your backup script would use the timestamp to form part of the filename. It's common to append the database name, backup type and timestamp to form the backup filename.
E.g. a log backup run on Noddy database at 15:34pm would generate a file called
Noddy_TLog_20121119_153400.trn
arkiboys (11/19/2012)
2- restoring each transaction log sinice the previous midnight seems to be a long process. How about introducing a differential backup?
Now your learning, good idea 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 21, 2012 at 11:25 am
Thank you
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply