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

Backup process hanging?

Hi everybody
OS is Win2000 with latest SP.
Microsoft SQL Server 7.00 - 7.00.842.
The problem is that there is a backup-process that is
runnable but wont finish or timeout. When i am trying to
kill the process nothing happens. It is still there!?!
Can someone maybe give some pointers to what i should do
to get ride of this process.
As it is now i cannot take a backup of the database the
backup process is hanged on.
/RisunHi,
From the SQL Server machine, Open Query Analyzer and execute the below
comamnd and see whether it suceeds.
Ensure that an existing Backup process is not going on.
BACKUP database <dbname> to disk='d:\backup\dbname.bak' with init , stats=10
Init parameter will overwrite the backup filr every time and stats=10 , will
show the percentage completed.
Note:
See the CPU and Memory usage of server.
--
Thanks
Hari
MCDBA
"Risun" <risun@.wmdata.com> wrote in message
news:1f69101c45781$a986adb0$a001280a@.phx.gbl...
> Hi everybody
> OS is Win2000 with latest SP.
> Microsoft SQL Server 7.00 - 7.00.842.
> The problem is that there is a backup-process that is
> runnable but wont finish or timeout. When i am trying to
> kill the process nothing happens. It is still there!?!
> Can someone maybe give some pointers to what i should do
> to get ride of this process.
> As it is now i cannot take a backup of the database the
> backup process is hanged on.
> /Risun|||Ehh, but the problem is that the previous backup-process
will not die/go away/disappear. I have tried to kill the
process from the EM and with T-sql. It still remains.
The solution i am trying to avoid is stopping and starting
the MSSQLSERVER service. But as it is now i cannot see
another solution.
Trying to take a backup isnt possible as long as this
process is still alive.
Please, how can i kill this sucker.
/Risun
>--Original Message--
>Hi,
>From the SQL Server machine, Open Query Analyzer and
execute the below
>comamnd and see whether it suceeds.
>Ensure that an existing Backup process is not going on.
>BACKUP database <dbname> to disk='d:\backup\dbname.bak'
with init , stats=10
>Init parameter will overwrite the backup filr every time
and stats=10 , will
>show the percentage completed.
>Note:
>See the CPU and Memory usage of server.
>--
>Thanks
>Hari
>MCDBA
>"Risun" <risun@.wmdata.com> wrote in message
>news:1f69101c45781$a986adb0$a001280a@.phx.gbl...
>> Hi everybody
>> OS is Win2000 with latest SP.
>> Microsoft SQL Server 7.00 - 7.00.842.
>> The problem is that there is a backup-process that is
>> runnable but wont finish or timeout. When i am trying to
>> kill the process nothing happens. It is still there!?!
>> Can someone maybe give some pointers to what i should do
>> to get ride of this process.
>> As it is now i cannot take a backup of the database the
>> backup process is hanged on.
>> /Risun
>
>.
>|||If you can not kill it from SQL, you might try to kil the process from Task
manager, but I suspect you will have to stop/start SQL Server service..
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Risun" <risun@.wmdata.com> wrote in message
news:1f69101c45781$a986adb0$a001280a@.phx.gbl...
> Hi everybody
> OS is Win2000 with latest SP.
> Microsoft SQL Server 7.00 - 7.00.842.
> The problem is that there is a backup-process that is
> runnable but wont finish or timeout. When i am trying to
> kill the process nothing happens. It is still there!?!
> Can someone maybe give some pointers to what i should do
> to get ride of this process.
> As it is now i cannot take a backup of the database the
> backup process is hanged on.
> /Risun|||Hi,
If you kill the BACKUP process mostly that process will not be removed from
the process list. The only solution is to stop and start the MSSQL Server
service.
Why do you need to kill the backup process. Use my previous post (BACKUP
DATABASE) command
to backup the database. STATS=10 parameter will give you the percentage of
backup completed.
INF: Understanding How the Transact-SQL KILL Command Works
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:
80/support/kb/articles/Q171/2/24.ASP&NoWebContent=1
Thanks
Hari
MCDBA
"Risun" <risun@.wmdata.com> wrote in message
news:1ef1e01c4578c$55d2e8e0$a301280a@.phx.gbl...
> Ehh, but the problem is that the previous backup-process
> will not die/go away/disappear. I have tried to kill the
> process from the EM and with T-sql. It still remains.
> The solution i am trying to avoid is stopping and starting
> the MSSQLSERVER service. But as it is now i cannot see
> another solution.
> Trying to take a backup isnt possible as long as this
> process is still alive.
> Please, how can i kill this sucker.
> /Risun
> >--Original Message--
> >Hi,
> >
> >From the SQL Server machine, Open Query Analyzer and
> execute the below
> >comamnd and see whether it suceeds.
> >Ensure that an existing Backup process is not going on.
> >
> >BACKUP database <dbname> to disk='d:\backup\dbname.bak'
> with init , stats=10
> >
> >Init parameter will overwrite the backup filr every time
> and stats=10 , will
> >show the percentage completed.
> >
> >Note:
> >
> >See the CPU and Memory usage of server.
> >
> >--
> >Thanks
> >Hari
> >MCDBA
> >"Risun" <risun@.wmdata.com> wrote in message
> >news:1f69101c45781$a986adb0$a001280a@.phx.gbl...
> >> Hi everybody
> >>
> >> OS is Win2000 with latest SP.
> >> Microsoft SQL Server 7.00 - 7.00.842.
> >>
> >> The problem is that there is a backup-process that is
> >> runnable but wont finish or timeout. When i am trying to
> >> kill the process nothing happens. It is still there!?!
> >>
> >> Can someone maybe give some pointers to what i should do
> >> to get ride of this process.
> >> As it is now i cannot take a backup of the database the
> >> backup process is hanged on.
> >>
> >> /Risun
> >
> >
> >.
> >