March 13, 2010 at 12:19 pm
Dear All,
we are using two diffirent level of sql server backup type First we are using SQL Server level backup and second one is legato
Legato backup:
Fullbackup in 2 week and rest of the days incremental backup daily.
SQL Server level backup :
Full backup Weekly + Log Backup each interval of 40 mints + Daily Differential Backup
I have two questions for backup and restore .
First question:Is there any way to restore all last fullbackup and diffrentional backup plus all log backup in one script in new installed SQL Server for example
i have fullback + Diff + 20 Log backup how can i restore in new server if server crash in one script as i tested on same sever as we know there is backup and restore history available so we can restore easly but how it is possible for new installed system and apply all logs backup in one script. or should we need to apply one by one if we apply one by one it takes to much time and also i need to know what format should i set for logbackup to track file with date time after desistor.
2- Question:
when legato backup admin take daily full backup using legato sql server module as i am taking diff backup on sql server level so my backup consider diff backup of last legato full backup not database full backup.
mean my backup chain is borken after taken fullbackup thorgh legato.
Database server :SQL 2005 cluster
No.Databases :30
Size of fullbackup for all database:62 GB
Instance :Single
So in summary :
How can i restore database to new system in one script as i have 30 plus TLOGS backups how can i minimize down time.second what is backup recommended method by Microsoft using sql server level backup or legato third party sql to tape directly.
Anyone give me reply very thanks.
March 13, 2010 at 3:21 pm
You need to stop one of the processes here - you are causing problems with the backup chain and won't be able to restore the system when you need to.
Differential backups are based upon the *last* full backup taken, regardless of how that backup is performed (native or legato). So tell me, which backup are the differential backups tied to? The one performed by Legato, or the native backup?
Generally, for SQL Server we like to perform native backups to a local directory and have the other tool backup that directory to tape.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 13, 2010 at 6:41 pm
Let me tell you how to make restore process easy in case you have to restore database.
From your questions looks like you have to change back plan.
You said back up size of databases is 60 GB, correct?
1: Microsoft recommends
a) Full backup every week
b) Differential backup every night
c) T-Logs every hour\30 mins depending on how much time data you afford lose.
2: I would recommend (If data changes is more, because Differential backup size depends on changes after full backup)
a) Full back up every night
b) T- Log back ups every 30 mins
Regarding Legato, either use native or legato for back ups if you both backup chain will break. Take backup to production fileshare first then copy files to tape from production fileshare.
March 13, 2010 at 9:43 pm
Thanks for the reply i clear my words we have two type backup one is legato and second is native backup yes i am taking diff backup as native backup and legato backup admin taking incremental file backup using legato networker sqlsever to tape.but problem as i explained i am taking full natvie backup weekly and diff backup daily when i see the backup and restore events history it showing me fullbackup daily from legato and my native diff backup consider last fullbackup from legato not sql datbase so native backup chain broken due to reason of legato last fullbackup.
before we are using only legato but legato admin taking backup in 2 week fullbackup rest of the days incremental.so in this policy we are having gap last 24 hour if any thing happen wrong so that reason i want to keep native backup also which is fullbackup weekly and diff daily plus each 40 mints interval of log backup.
March 13, 2010 at 9:50 pm
My diff backup size is only between 200 to 300MB.So i can use fullbackup weekly +diffbackup daily plus each 30 mints TLOG backup.
Question is if system crashed i have fullbackup +diff backup all + last 10 hours TLOG files which become 20 TLOGS backup how can restore easy in the new system mean should i need to restore one by one as with norecovey mode or any way to run in script in one short if yes share me any such scipt and tlog backup which should keep formant of file to easy understanding
Regards,
March 13, 2010 at 9:58 pm
zhayatali (3/13/2010)
My diff backup size is only between 200 to 300MB.So i can use fullbackup weekly +diffbackup daily plus each 30 mints TLOG backup.Question is if system crashed i have fullbackup +diff backup all + last 10 hours TLOG files which become 20 TLOGS backup how can restore easy in the new system mean should i need to restore one by one as with norecovey mode or any way to run in script in one short if yes share me any such scipt and tlog backup which should keep formant of file to easy understanding
Regards,
All depends your environment, In our environment we take only full and t-logs only. It is very rare you have to restore production databases. Yes, if you have to restore from t-logs restore all tran log back files with norecovery except last t-log with recovery. You said new system, are taking about migration here? If it is migration then it is totally different approach.
March 14, 2010 at 12:45 am
thanks for the quick reply we are not going to migration we wanted to test backup using copy command to our new sql server and restore backup get from our folder backup for this we need last full backup +diff +TLOGS backup . my question is if we restore backup in same server where we are taking backup backup history is there so we can easly restore backup like when we select last fullbackup it will automatically selected fullbackup+diff+TLoG backup for restore so we can easly restore if we go for restore to new server we need to restore full + diff + TLogsbackup right but we dont have any history in that sever we need to do manullay can we select in one time all trasaction log after last diff backup or we need to restore one by one from backup point or is there any procedure to export backup history to new installed server so we can get do automatically
Regards,
March 14, 2010 at 1:59 am
my question is if we restore backup in same server where we are taking backup backup history is there so we can easly restore backup like when we select last fullbackup it will automatically selected fullbackup+diff+TLoG backup for restore so we can easly restore if we go for restore to new server we need to restore full + diff + TLogsbackup right
No, it won't select all files. If you select full backup for restore.
but we dont have any history in that sever we need to do manullay can we select in one time all trasaction log after last diff backup or we need to restore one by one from backup point or is there any procedure to export backup history to new installed server so we can get do automatically
You have to select one by one, what are trying to achieve here? If you know when you want to restore just take full back at that time and restore on new server.
March 14, 2010 at 3:08 am
Sorry for miscommunication i more explain assume my production database server crashed for time may database backup on TAPE i have another test server and need to restore database from last fullbackup which i taken last night i have complte bacup chain fullbackup +diff + all logbackup it almost 30 files. so after restore fullbackup and then diff backup and also need to restore in norecovery mode how can do in easy way
March 14, 2010 at 3:19 am
bacup chain fullbackup +diff + all logbackup it almost 30 files. so after restore fullbackup and then diff backup and also need to restore in norecovery mode how can do in easy way
There is no alternate you have restore all files Full (Norecovery)+Diff (Norecovery)+all t-logs with norecovery except last t-log with recovery.
Hope this helps!
March 14, 2010 at 3:35 am
Thank you very much i got it.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply