Backup SQL 2005 Plan

Hello everybody,
I'm using SQL 2005 for few weeks, and I'd like to ask you something about
the Backup and Restore Plan. I need to create a complete DR solution for my
client.
So, what I did was: (using Management Studio-Maintenance Plan)
- All the user database are on Full Recovery Mode.
- Run a Full User Database backup every day at 1:00am and save it on day's
folder, it means the backup for Monday will go to Monday's backup folder and
so on.
- At 6:00am (normally the time they start to work), I start to do a Log
Backup every 15 minutes until 11:00pm aprox.
My question is:
- Every time that I ran the log backup, I'm truncating the LOG file? or I
have to run other task to truncate the log?
- Using that Plan, I'll be able to recover the data for the last 15 minutes
in case the database crashed?
- How often I have to shrink the database file and logs?
The Primary data file is : 570Gb
The Secondary data file is : 130Gb
The Log file is : 850Gb
I'm not worry about the space because the server and the backup unit have a
lot of space.
Any ideas or opinions'
Thank you!
JoseHola Jose,
Some comments here:
> The Log file is : 850Gb
Is this a typo? This is too big for a transaction log file. You should
manually shrink it, but probably only once. Do not schedule a job to shrink
it periodically.
> - Every time that I ran the log backup, I'm truncating the LOG file? or I
> have to run other task to truncate the log?
After the transaction log is shrunk manually doing a backup every 15 minutes
should keep it in a acceptable size.
> - Using that Plan, I'll be able to recover the data for the last 15 minutes
> in case the database crashed?
Yes, if you still have access to the backup folder. Are these folders on the
same computer? Also, if you are lucky and have access to the tail of the log
you can recover your database to the point of failure, losing no data at all.
> - How often I have to shrink the database file and logs?
Do not shrink any file on a job or maintenance plan. Do it only manually
when you really need it.
Hope this helps,
Ben Nevarez
"Jose" wrote:
> Hello everybody,
> I'm using SQL 2005 for few weeks, and I'd like to ask you something about
> the Backup and Restore Plan. I need to create a complete DR solution for my
> client.
> So, what I did was: (using Management Studio-Maintenance Plan)
> - All the user database are on Full Recovery Mode.
> - Run a Full User Database backup every day at 1:00am and save it on day's
> folder, it means the backup for Monday will go to Monday's backup folder and
> so on.
> - At 6:00am (normally the time they start to work), I start to do a Log
> Backup every 15 minutes until 11:00pm aprox.
> My question is:
> - Every time that I ran the log backup, I'm truncating the LOG file? or I
> have to run other task to truncate the log?
> - Using that Plan, I'll be able to recover the data for the last 15 minutes
> in case the database crashed?
> - How often I have to shrink the database file and logs?
> The Primary data file is : 570Gb
> The Secondary data file is : 130Gb
> The Log file is : 850Gb
> I'm not worry about the space because the server and the backup unit have a
> lot of space.
> Any ideas or opinions'
> Thank you!
> Jose|||Jose
> - Every time that I ran the log backup, I'm truncating the LOG file? or I
> have to run other task to truncate the log?
You can run BACKUP LOG with or without WITH INIT option (for more details
please see BOL)
If you choose using WITH INIT option that means SQL Server creates one file
per media set and you need to give the file separate name
Like log_20080101_17:00.log
log_20080101_17:30.log
On the other hand if you use WITH NOINIT you can create one file and SQL
Server adds one file to the media set and you will have to refer thopse
file when you restore database
RESTORE DATABASE test FROM disk = 'd:\db.bak' WITH FILE = 1,
norecovery --full database
RESTORE LOG test FROM disk = 'd:\log.bak' WITH FILE = 1, norecovery
RESTORE LOG test FROM disk = 'd:\log.bak' WITH FILE = 2, recovery
> - Using that Plan, I'll be able to recover the data for the last 15
> minutes
> in case the database crashed?
You will be able to restore even at point of time
> - How often I have to shrink the database file and logs?
Do not shrink them at all
"Jose" <Jose@.discussions.microsoft.com> wrote in message
news:A3D3CD10-2D80-422A-AD68-09F9E510CDCB@.microsoft.com...
> Hello everybody,
> I'm using SQL 2005 for few weeks, and I'd like to ask you something about
> the Backup and Restore Plan. I need to create a complete DR solution for
> my
> client.
> So, what I did was: (using Management Studio-Maintenance Plan)
> - All the user database are on Full Recovery Mode.
> - Run a Full User Database backup every day at 1:00am and save it on day's
> folder, it means the backup for Monday will go to Monday's backup folder
> and
> so on.
> - At 6:00am (normally the time they start to work), I start to do a Log
> Backup every 15 minutes until 11:00pm aprox.
> My question is:
> - Every time that I ran the log backup, I'm truncating the LOG file? or I
> have to run other task to truncate the log?
> - Using that Plan, I'll be able to recover the data for the last 15
> minutes
> in case the database crashed?
> - How often I have to shrink the database file and logs?
> The Primary data file is : 570Gb
> The Secondary data file is : 130Gb
> The Log file is : 850Gb
> I'm not worry about the space because the server and the backup unit have
> a
> lot of space.
> Any ideas or opinions'
> Thank you!
> Jose|||Hi Ben and Uri.
Thanks for your comments!.
And I made a mistake, is not "GB" is "MB"..sorry.
I've asked if every time that I run the backup for the LOG files I'm
truncating the log, because my client is using NAVISION from Microsoft, and
the company who did the installation, gave me some instructions for the
backup, and to be honest I dont know too much about SQL commands, that is way
I did using the Management Studio. And they told me to create :
- 1 full backup every day
- 1 log backup every 15 minutes
- at the end of the day truncate the log to keep the log small...
But if backing up the log and I'm truncating it at the same time, I'll need
to create other job to truncate the log at the end of the day? I guess not.
Right now, when I run the log backup, SQL creates a directory for each
database and it creates every log file for each database every 15 minutes and
the size of the log is 500Kb.
All the files backup are on the server and I have a task who move files and
logs to other (external) unit backup, so, at least I have 2 places to find if
something happens to the datasbase and 1 place if something happens to the
server and to be more protected, those database are been replicated online to
1 external server outside of the company.
When I will really need shrink a database and/or Log File?
Thank you so much for all your comments!
Have a nice day!
Jose
"Ben Nevarez" wrote:
> Hola Jose,
> Some comments here:
> > The Log file is : 850Gb
> Is this a typo? This is too big for a transaction log file. You should
> manually shrink it, but probably only once. Do not schedule a job to shrink
> it periodically.
> > - Every time that I ran the log backup, I'm truncating the LOG file? or I
> > have to run other task to truncate the log?
> After the transaction log is shrunk manually doing a backup every 15 minutes
> should keep it in a acceptable size.
> > - Using that Plan, I'll be able to recover the data for the last 15 minutes
> > in case the database crashed?
> Yes, if you still have access to the backup folder. Are these folders on the
> same computer? Also, if you are lucky and have access to the tail of the log
> you can recover your database to the point of failure, losing no data at all.
> > - How often I have to shrink the database file and logs?
> Do not shrink any file on a job or maintenance plan. Do it only manually
> when you really need it.
> Hope this helps,
> Ben Nevarez
>
>
> "Jose" wrote:
> > Hello everybody,
> > I'm using SQL 2005 for few weeks, and I'd like to ask you something about
> > the Backup and Restore Plan. I need to create a complete DR solution for my
> > client.
> > So, what I did was: (using Management Studio-Maintenance Plan)
> > - All the user database are on Full Recovery Mode.
> > - Run a Full User Database backup every day at 1:00am and save it on day's
> > folder, it means the backup for Monday will go to Monday's backup folder and
> > so on.
> > - At 6:00am (normally the time they start to work), I start to do a Log
> > Backup every 15 minutes until 11:00pm aprox.
> >
> > My question is:
> > - Every time that I ran the log backup, I'm truncating the LOG file? or I
> > have to run other task to truncate the log?
> > - Using that Plan, I'll be able to recover the data for the last 15 minutes
> > in case the database crashed?
> > - How often I have to shrink the database file and logs?
> >
> > The Primary data file is : 570Gb
> > The Secondary data file is : 130Gb
> > The Log file is : 850Gb
> >
> > I'm not worry about the space because the server and the backup unit have a
> > lot of space.
> >
> > Any ideas or opinions'
> > Thank you!
> >
> > Jose|||> When I will really need shrink a database and/or Log File?
If, and only if, it becomes exceptionally large. Larger that it would have to be for your normal
operation. And only if you actually gain something by doing the shrink (i.e., you really need the
disk space). See below:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
http://sqlblog.com/blogs/tibor_karaszi/archive/2007/02/25/leaking-roof-and-file-shrinking.aspx
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jose" <Jose@.discussions.microsoft.com> wrote in message
news:7D430938-4FC7-4D15-A4EF-6ED05EF6E386@.microsoft.com...
> Hi Ben and Uri.
> Thanks for your comments!.
> And I made a mistake, is not "GB" is "MB"..sorry.
> I've asked if every time that I run the backup for the LOG files I'm
> truncating the log, because my client is using NAVISION from Microsoft, and
> the company who did the installation, gave me some instructions for the
> backup, and to be honest I dont know too much about SQL commands, that is way
> I did using the Management Studio. And they told me to create :
> - 1 full backup every day
> - 1 log backup every 15 minutes
> - at the end of the day truncate the log to keep the log small...
> But if backing up the log and I'm truncating it at the same time, I'll need
> to create other job to truncate the log at the end of the day? I guess not.
> Right now, when I run the log backup, SQL creates a directory for each
> database and it creates every log file for each database every 15 minutes and
> the size of the log is 500Kb.
> All the files backup are on the server and I have a task who move files and
> logs to other (external) unit backup, so, at least I have 2 places to find if
> something happens to the datasbase and 1 place if something happens to the
> server and to be more protected, those database are been replicated online to
> 1 external server outside of the company.
> When I will really need shrink a database and/or Log File?
> Thank you so much for all your comments!
> Have a nice day!
> Jose
> "Ben Nevarez" wrote:
>> Hola Jose,
>> Some comments here:
>> > The Log file is : 850Gb
>> Is this a typo? This is too big for a transaction log file. You should
>> manually shrink it, but probably only once. Do not schedule a job to shrink
>> it periodically.
>> > - Every time that I ran the log backup, I'm truncating the LOG file? or I
>> > have to run other task to truncate the log?
>> After the transaction log is shrunk manually doing a backup every 15 minutes
>> should keep it in a acceptable size.
>> > - Using that Plan, I'll be able to recover the data for the last 15 minutes
>> > in case the database crashed?
>> Yes, if you still have access to the backup folder. Are these folders on the
>> same computer? Also, if you are lucky and have access to the tail of the log
>> you can recover your database to the point of failure, losing no data at all.
>> > - How often I have to shrink the database file and logs?
>> Do not shrink any file on a job or maintenance plan. Do it only manually
>> when you really need it.
>> Hope this helps,
>> Ben Nevarez
>>
>>
>> "Jose" wrote:
>> > Hello everybody,
>> > I'm using SQL 2005 for few weeks, and I'd like to ask you something about
>> > the Backup and Restore Plan. I need to create a complete DR solution for my
>> > client.
>> > So, what I did was: (using Management Studio-Maintenance Plan)
>> > - All the user database are on Full Recovery Mode.
>> > - Run a Full User Database backup every day at 1:00am and save it on day's
>> > folder, it means the backup for Monday will go to Monday's backup folder and
>> > so on.
>> > - At 6:00am (normally the time they start to work), I start to do a Log
>> > Backup every 15 minutes until 11:00pm aprox.
>> >
>> > My question is:
>> > - Every time that I ran the log backup, I'm truncating the LOG file? or I
>> > have to run other task to truncate the log?
>> > - Using that Plan, I'll be able to recover the data for the last 15 minutes
>> > in case the database crashed?
>> > - How often I have to shrink the database file and logs?
>> >
>> > The Primary data file is : 570Gb
>> > The Secondary data file is : 130Gb
>> > The Log file is : 850Gb
>> >
>> > I'm not worry about the space because the server and the backup unit have a
>> > lot of space.
>> >
>> > Any ideas or opinions'
>> > Thank you!
>> >
>> > Jose