June 21, 2011 at 1:26 am
muthukkumaran Kaliyamoorthy (6/21/2011)
I need some suggestions of a backup plan that has backups for FULL, INCREMENTAL and LOG backups, which factors in the relationship of the 3 backups.SQL server doesn't has INCREMENTAL backup.
what i meant was differential backup . 🙂
June 21, 2011 at 2:16 am
kesmond (6/21/2011)
hmmm... how come now when i restore the transaction logs file, my database is gone? only have model db transaction logs and other db transaction logs? anybody know why? what did i did wrongly? thanks
What did you did? Could you elaborate step by step.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
June 21, 2011 at 2:27 am
I am rather new to sql so in terms of backup, would need your advice.
So i would do a restore from the full backup for Monday (with the restore option of Overwrite the existing data (WITH REPLACE) and also RESTORE WITH NORECOVERY), after that i do a differential restore on Thursday (with the restore option of Overwrite the existing data (WITH REPLACE and also RESTORE WITH NORECOVERY), lastly i will do a restore of the transaction log from thursday onwards every 5 mins (with the restore option of Overwrite the existing data (WITH REPLACE) and also RESTORE WITH RECOVERY), am i right? thanks.
Yeah you are right.
Additionally,
WITH REPLACE option remove the old data (DB). If you don't want old db then go ahead.
You may use the REPLACE option for first time restore (Full) No need to use this for all diff & log . If you used no problem it will work.
So the transaction log restore would start from the time the differential backup was done. For example, the differential backup was done on thursday 130pm, so the transaction log restore would start from thursday 130pm onwards (every 5 mins), is my understanding correct? thanks
Yes. you are right.
As told earlier it depends upon your RPO & RTO.
Just think your DB crashed on 1:30 PM thursday
You have 1:30 am differential backup
The restoration needs all every 5 minutes (all 144 bak) log backup from the 1:30 am differential backup onwards (every one hour 12 log backup). so restoration takes more time to bring the database online.
Thats why we told it depends.differential backup helps to bring the database online as quickly as possible.
You Got it.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
June 21, 2011 at 2:36 am
kesmond (6/21/2011)
Hi, i have a queries, i have do a testing (sample) to set the transaction logs backup to occur every 5 minutes. But i have check that how come the model db transaction log is being backup as well? And also there were some time gap between some of the transaction logs backup. (i.e the transaction log backup was at 215pm, the next one suppose to be 220pm, but it goes to 240pm, in between are model db transaction logs), am i still able to restore the transaction log as at 240pm as there are some missing logs in between? thanks
(i.e the transaction log backup was at 215pm, the next one suppose to be 220pm, but it goes to 240pm, in between are model db transaction logs),
SQL agent will run next schedule when the job completes.
If your jobs runs more than from 215 pm to 245pm then the run will be 3pm
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
June 21, 2011 at 4:10 am
Ok all this is hypothetical until you can actually connect to your production server to get the actual information and determine your requirements.
If your tapes can only hold 1 gb then what are you doing around your full db backup.
Your Tlog growth is dependent on the workload being undertaken. Once you understand the production usage then you can better determine your backup solution. To keep things simple and not knowing your system I would recommend the following as solution 1 - full db backup nightly with 4 hourly Tlog backups.
Your restore process is restore the full backup and all of the Tlog backups since your last full backup.
If you are concerned about the space usage then option 2 - full db backup on Sunday night, nightly differential, 4 hourly Tlog backup.
The easiest way to setup and schedule would be to create a maintenance plan. Ensure you select either the specific db or all user DBS. This will create a new file for each backup undertaken. You will need to setup a cleanup job to ensure you do not run out of space.
MCT
MCITP Database Admin 2008
MCITP Database Admin 2008
MCITP Database Dev 2008
www.jnrit.com.au/Blog.aspx
June 21, 2011 at 6:55 pm
muthukkumaran Kaliyamoorthy (6/21/2011)
kesmond (6/21/2011)
hmmm... how come now when i restore the transaction logs file, my database is gone? only have model db transaction logs and other db transaction logs? anybody know why? what did i did wrongly? thanksWhat did you did? Could you elaborate step by step.
frankly speaking, i do not know what happen also. Before that i can see the transaction logs backup for my database and model db . But now i can only see model db and other databases for the transaction logs backup. I never really want it to happen and hope it wont happen when i go live.
June 21, 2011 at 7:02 pm
muthukkumaran Kaliyamoorthy (6/21/2011)
I am rather new to sql so in terms of backup, would need your advice.
So i would do a restore from the full backup for Monday (with the restore option of Overwrite the existing data (WITH REPLACE) and also RESTORE WITH NORECOVERY), after that i do a differential restore on Thursday (with the restore option of Overwrite the existing data (WITH REPLACE and also RESTORE WITH NORECOVERY), lastly i will do a restore of the transaction log from thursday onwards every 5 mins (with the restore option of Overwrite the existing data (WITH REPLACE) and also RESTORE WITH RECOVERY), am i right? thanks.
Yeah you are right.
Additionally,
WITH REPLACE option remove the old data (DB). If you don't want old db then go ahead.
You may use the REPLACE option for first time restore (Full) No need to use this for all diff & log . If you used no problem it will work.
So the transaction log restore would start from the time the differential backup was done. For example, the differential backup was done on thursday 130pm, so the transaction log restore would start from thursday 130pm onwards (every 5 mins), is my understanding correct? thanks
Yes. you are right.
As told earlier it depends upon your RPO & RTO.
Just think your DB crashed on 1:30 PM thursday
You have 1:30 am differential backup
The restoration needs all every 5 minutes (all 144 bak) log backup from the 1:30 am differential backup onwards (every one hour 12 log backup). so restoration takes more time to bring the database online.
Thats why we told it depends.differential backup helps to bring the database online as quickly as possible.
You Got it.
Hi, you were saying with WITH REPLACE option, it will remove the old data (db), so by right it should be this way because we are doing restore, it should be removing the old data, so this option WITH REPLACE seems redundant, am i right?
June 21, 2011 at 7:07 pm
kesmond (6/21/2011)
muthukkumaran Kaliyamoorthy (6/21/2011)
kesmond (6/21/2011)
hmmm... how come now when i restore the transaction logs file, my database is gone? only have model db transaction logs and other db transaction logs? anybody know why? what did i did wrongly? thanksWhat did you did? Could you elaborate step by step.
frankly speaking, i do not know what happen also. Before that i can see the transaction logs backup for my database and model db . But now i can only see model db and other databases for the transaction logs backup. I never really want it to happen and hope it wont happen when i go live.
This is the error message that i have got. "the backup set holds a backup of a database other than the existing database"
June 21, 2011 at 7:15 pm
kesmond (6/21/2011)
kesmond (6/21/2011)
muthukkumaran Kaliyamoorthy (6/21/2011)
kesmond (6/21/2011)
hmmm... how come now when i restore the transaction logs file, my database is gone? only have model db transaction logs and other db transaction logs? anybody know why? what did i did wrongly? thanksWhat did you did? Could you elaborate step by step.
frankly speaking, i do not know what happen also. Before that i can see the transaction logs backup for my database and model db . But now i can only see model db and other databases for the transaction logs backup. I never really want it to happen and hope it wont happen when i go live.
This is the error message that i have got. "the backup set holds a backup of a database other than the existing database"
now even my full backup does not have my database, it contains all the databases except my database. What have i did? The full backup was done on monday and i did not even touch it.
June 21, 2011 at 7:58 pm
I have revised everything for the backup. Here is what i did.
For my own database:
1) Create a job schedule daily every 5 mins to backup Transaction log
2) Create a job schedule daily at 9am to backup database differential
3) Create a job schedule weekly at 9am full backup on every monday 9am
For system databases:
1) using Maintenance Plan to backup system databases weekly at 9am
Is that okay? I hope e same problem wont happen again.
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply