backup/restore process - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

backup/restore process

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2012-11-18 : 11:10:05

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

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-11-18 : 11:34:21

It depends on till what time you want to restore.

Were you able to take tail log backup? If yes then you can apply it to restore to point in time.

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2012-11-18 : 12:39:26

quote:

2-
schedule this transaction log backup to run every 15 minutes
BACKUP LOG AdventureWorks TO DISK = 'C:\AdventureWorks.TRN'
GO

I am new to this too. But I don't think that is a best practice. You can timestamp it - 'C:\AdventureWorks201206031201.TRN'.

I had trouble finding why, my guess is that if your system dies in the middle of the Tran Log backup.

Can anyone please comment on that too - why Transaction Logs are time-stamped rather than appended?

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2012-11-18 : 13:44:53

quote:
Originally posted by sodeep

It depends on till what time you want to restore.

Were you able to take tail log backup? If yes then you can apply it to restore to point in time.

Hi, I am basically practicing to learn and be prepared for a restore.
The scripts I mentioned are the hones which I am thinking of scheduling. Are they correct? do I need anything else? thanks

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2012-11-18 : 13:45:53

quote:
Originally posted by denis_the_thief

quote:

2-
schedule this transaction log backup to run every 15 minutes
BACKUP LOG AdventureWorks TO DISK = 'C:\AdventureWorks.TRN'
GO

I am new to this too. But I don't think that is a best practice. You can timestamp it - 'C:\AdventureWorks201206031201.TRN'.

I had trouble finding why, my guess is that if your system dies in the middle of the Tran Log backup.

Can anyone please comment on that too - why Transaction Logs are time-stamped rather than appended?

Please start a new thread and not mix it with this one.
Thank you

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-11-18 : 18:33:08

You Backup commands look good but you need to restore full backup for midnight and subsequent log backups till 11:30 PM in NORECOVERY MODE and last log backup with STOPAT OPTION to 11:45 if you want to restore till 11:45 PM

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-11-18 : 18:36:21

quote:
I
am new to this too. But I don't think that is a best practice. You can timestamp it - 'C:\AdventureWorks201206031201.TRN'.

I had trouble finding why, my guess is that if your system dies in the middle of the Tran Log backup.

Can anyone please comment on that too - why Transaction Logs are time-stamped rather than appended?

It depends if you are able to take tail log backup then you should be good otherwise you will lose data if tran log was not completed successfully. Make sure to create new thread for this one.

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2012-11-18 : 23:50:41

quote:

Please start a new thread and not mix it with this one.
Thank you

What are you talking about!!!

You asked if your script was correct. I don't think your approach is correct (i.e. by not time-stamping) and that this is an unsafe approach to disaster/recovery. I am just hoping to get someone more knowledgeable to comment!

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2012-11-19 : 09:16:48

quote:
Originally posted by sodeep

quote:
I
am new to this too. But I don't think that is a best practice. You can timestamp it - 'C:\AdventureWorks201206031201.TRN'.

I had trouble finding why, my guess is that if your system dies in the middle of the Tran Log backup.

Can anyone please comment on that too - why Transaction Logs are time-stamped rather than appended?

It depends if you are able to take tail log backup then you should be good otherwise you will lose data if tran log was not completed successfully. Make sure to create new thread for this one.

By tail log backup, do you mean every single transaction log backup in order?

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2012-11-19 : 11:34:15

quote:

By tail log backup, do you mean every single transaction log backup in order?

The Tail Log Backup is just a Transaction Log backup. It is the Transaction Log Backup called after the disaster. This will contain all transactions not backup in your most recent completed Transaction Log backup.

I looked all over to try to find any other reasons why you need to make separate trn log backup files rather than appending. If the name you give your scheduled backup log is static, this file will get bigger and bigger and bigger.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-11-19 : 12:21:30

Tail log backup is last tran log backup for those commited transactions so that you can restore to point in time.

Whether you have separate backup file or append it, it is going to have same size.

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2012-11-20 : 14:13:22

The reason you don't want to use a single file is because it will continually grow until it fills the drive. To avoid that, you then have to schedule an additional transaction log backup (right after the full) to initialize the file.

As soon as you initialize the file - everything in the file is gone. If you then find out that the current backup file is corrupted - and you go to the previous backup files (oops - nothing there, because you only have the one file), how do you recover?

By using time-stamped files, every full, tran and diff backup file is separate. If you lose a full backup file or it is corrupted, get the previous backup file and all transaction log backup files and you can recover your system with minimal (or no loss, if tail-log backup can be done).

By using a single device you have to manage the initialization. If you don't initialize, the file continues to grow - if you initialize, you lose everything in the file and have to hope you have it backed up to offline media with no gaps.

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2012-11-21 : 11:26:19

Thankyou. I appreciate the explanation.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources