SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 backup/restore process
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

arkiboys
Flowing Fount of Yak Knowledge

1406 Posts

Posted - 11/18/2012 :  11:10:05  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 11/18/2012 :  11:34:21  Show Profile  Reply with Quote
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.
Go to Top of Page

denis_the_thief
Aged Yak Warrior

Canada
591 Posts

Posted - 11/18/2012 :  12:39:26  Show Profile  Reply with Quote
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?

Edited by - denis_the_thief on 11/18/2012 12:48:32
Go to Top of Page

arkiboys
Flowing Fount of Yak Knowledge

1406 Posts

Posted - 11/18/2012 :  13:44:53  Show Profile  Reply with Quote
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
Go to Top of Page

arkiboys
Flowing Fount of Yak Knowledge

1406 Posts

Posted - 11/18/2012 :  13:45:53  Show Profile  Reply with Quote
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
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 11/18/2012 :  18:33:08  Show Profile  Reply with Quote
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
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 11/18/2012 :  18:36:21  Show Profile  Reply with Quote
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.
Go to Top of Page

denis_the_thief
Aged Yak Warrior

Canada
591 Posts

Posted - 11/18/2012 :  23:50:41  Show Profile  Reply with Quote

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!
Go to Top of Page

arkiboys
Flowing Fount of Yak Knowledge

1406 Posts

Posted - 11/19/2012 :  09:16:48  Show Profile  Reply with Quote
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?
Go to Top of Page

denis_the_thief
Aged Yak Warrior

Canada
591 Posts

Posted - 11/19/2012 :  11:34:15  Show Profile  Reply with Quote
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.
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 11/19/2012 :  12:21:30  Show Profile  Reply with Quote
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.
Go to Top of Page

jeffw8713
Aged Yak Warrior

USA
790 Posts

Posted - 11/20/2012 :  14:13:22  Show Profile  Reply with Quote
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.
Go to Top of Page

denis_the_thief
Aged Yak Warrior

Canada
591 Posts

Posted - 11/21/2012 :  11:26:19  Show Profile  Reply with Quote
Thankyou. I appreciate the explanation.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000