Database Backup Failed with Timeout Expired from Enterprise Manager

Hi,

I have SQL Server 2000 with SP4. When we take backup from Enterprise manager its fails with error "timeout expired". But when we take backup from query analyzer it works.

It seems that through enterprise manager 30 sec is the non configurable timeout parameter set. Checked sysprocesses table during backup but there was no blocking during that time at least for that spid.

Is there a way to find out which process is blocking the database backup? Also, what are the possible scenarios where database backup can be blocked.

Thanks,

Ramesh

Hi rksingh,

When you run a backup or restore from the Enterprise manager, the querytimeout does not apply.

It timesout because the process is being blocked by another process.

It could be blocked by BACKUP LOG or ALTER DATABASE commands.

Open another QA session and use SP_WHO2 ACTIVE to check for blocking.

regards

Jag

|||

Check SQL error log what error it is recorded...all backup info recorded in sql error log.

|||

Problem is resolved with the help of Microsoft PSS.

There is query time out setting for SQL Server 2000 Enterprise Manager which was set to 100 in my case whereas set to 0 on other servers.

To change settings

1. Open enterprise Manager.

2. Click on Tools - > Options

3. Goto Advanced Tab

3. Under connection setting change the Query time out value.

Thanks,

Ramesh

Database Backup Failed with Timeout Expired from Enterprise Manager

Hi,

I have SQL Server 2000 with SP4. When we take backup from Enterprise manager its fails with error "timeout expired". But when we take backup from query analyzer it works.

It seems that through enterprise manager 30 sec is the non configurable timeout parameter set. Checked sysprocesses table during backup but there was no blocking during that time at least for that spid.

Is there a way to find out which process is blocking the database backup? Also, what are the possible scenarios where database backup can be blocked.

Thanks,

Ramesh

Hi rksingh,

When you run a backup or restore from the Enterprise manager, the querytimeout does not apply.

It timesout because the process is being blocked by another process.

It could be blocked by BACKUP LOG or ALTER DATABASE commands.

Open another QA session and use SP_WHO2 ACTIVE to check for blocking.

regards

Jag

|||

Check SQL error log what error it is recorded...all backup info recorded in sql error log.

|||

Problem is resolved with the help of Microsoft PSS.

There is query time out setting for SQL Server 2000 Enterprise Manager which was set to 100 in my case whereas set to 0 on other servers.

To change settings

1. Open enterprise Manager.

2. Click on Tools - > Options

3. Goto Advanced Tab

3. Under connection setting change the Query time out value.

Thanks,

Ramesh

sql

database and log files do not grow automatically

I have a database on SQL 2000 SP4 that is set via Enterprise Manager to grow
the database and log files automatically. It doesn't happen, however. They
run out of space and don't grow, even though there is plenty of physical disk
available. Any ideas what may be happening?
Thanks,
Roger
> They run out of space and don't grow
Can you be more specific?
Are you getting an error message? If so, what is it?
Otherwise, how do you know they need to grow?
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
|||Hi Aaron,
The web application that uses the databases starts to throw errors to the
end users, such as "timeout expired" when they try to save data. If I look at
the database in Enterprise Manager, the transaction log or the database will
have less than 1MB free. If I set allocated space to say 1GB larger and
restart the IIS on the web server then the web app starts working normally
again.
In the application log, errors similar to the following were present:
Event Type:Error
Event Source:MSSQLSERVER
Event Category:(2)
Event ID:17052
Date:5/9/2007
Time:11:08:18 AM
User:N/A
Computer:SQL01
Description:
Error: 1105, Severity: 17, State: 2
Could not allocate space for object 'EventLog' in database 'Harmony_Live'
because the 'PRIMARY' filegroup is full.
|||> Could not allocate space for object 'EventLog' in database 'Harmony_Live'
> because the 'PRIMARY' filegroup is full.
Well, that sounds to me like the disk is full. What is your autogrowth
setting for the data and log files? What is the current result of:
USE Harmony_Live;
GO
EXEC sp_helpfile;
EXEC sp_spaceused;
EXEC master..xp_fixeddrives;
GO
|||Sorry about the formatting, but if you paste it into a text document it will
open correctly. I ran the query and the results were:
name fileid filename filegroup
size maxsize growth usage
-- -- --
-- -- -- --
Bremwood_Live_Data 1 F:\Harmony_Live\Harmony_Live.mdf PRIMARY
8192000 KB unlimited 10% data only
Bremwood_Live_Log 2 F:\Harmony_Live\Harmony_Live_log.ldf NULL
1024000 KB Unlimited 76800 KB log only
database_name database_size unallocated space
Harmony_Live 9000.00 MB 1318.63 MB
reserved data index_size unused
-- -- -- --
6841720 KB 6424632 KB 392088 KB 25000 KB
drive MB free
-- --
C 31155
D 122125
F 12074
(3 row(s) affected)
|||Have you considered moving the log file to a different drive? You have
plenty of space on D:\
How about changing the growth on the data file from a % (since 800MB is a
lot to initialize, and it just gets worse every time) to a more reasonable
fixed number, like 10 MB? Unless the operations that are requiring growth
in the first place are going to need motre than that in one shot?
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Roger" <Roger@.discussions.microsoft.com> wrote in message
news:7293A561-541B-4D96-9849-1DC7CD0AA0E0@.microsoft.com...
> Sorry about the formatting, but if you paste it into a text document it
> will
> open correctly. I ran the query and the results were:
> name fileid filename filegroup
> size maxsize growth usage
> -- -- --
> -- -- -- --
> Bremwood_Live_Data 1 F:\Harmony_Live\Harmony_Live.mdf PRIMARY
> 8192000 KB unlimited 10% data only
> Bremwood_Live_Log 2 F:\Harmony_Live\Harmony_Live_log.ldf NULL
> 1024000 KB Unlimited 76800 KB log only
> database_name database_size unallocated space
> Harmony_Live 9000.00 MB 1318.63 MB
>
> reserved data index_size unused
> -- -- -- --
> 6841720 KB 6424632 KB 392088 KB 25000 KB
> drive MB free
> -- --
> C 31155
> D 122125
> F 12074
> (3 row(s) affected)
>
|||I've tried them at 10MB, then 75MB, then back to 10%. The same thing happened
with either setting. I have them on that F: drive because it's on our SAN,
whereas the D: drive is not. I can increase the size of that F: drive easily
enough, but since we weren't filling it up I didn't think it would fix the
problem.
Right now the db is at 10% growth and the transaction log is at 75MB growth.
I'll change them both to 75MB, but I think it'll continue to not grow.
Thanks for troubleshooting this with me. Do you have any other ideas?
Roger
"Aaron Bertrand [SQL Server MVP]" wrote:

> Have you considered moving the log file to a different drive? You have
> plenty of space on D:\
> How about changing the growth on the data file from a % (since 800MB is a
> lot to initialize, and it just gets worse every time) to a more reasonable
> fixed number, like 10 MB? Unless the operations that are requiring growth
> in the first place are going to need motre than that in one shot?
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
|||> Thanks for troubleshooting this with me. Do you have any other ideas?
Not right now, no. Is it possible that permissions on F:\ have changed, or
the user that SQL Server is running as has changed?
A
|||Anything's possible.
I logged into the SQL box as the user that SQL runs as and created/deleted a
text file in f:\harmony_live just to be sure.
Is there way I can send an alert if the database or transaction logs reach a
certain percentage of allocated space?
Thanks,
Roger

database and log files do not grow automatically

I have a database on SQL 2000 SP4 that is set via Enterprise Manager to grow
the database and log files automatically. It doesn't happen, however. They
run out of space and don't grow, even though there is plenty of physical dis
k
available. Any ideas what may be happening?
Thanks,
Roger> They run out of space and don't grow
Can you be more specific?
Are you getting an error message? If so, what is it?
Otherwise, how do you know they need to grow?
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006|||Hi Aaron,
The web application that uses the databases starts to throw errors to the
end users, such as "timeout expired" when they try to save data. If I look a
t
the database in Enterprise Manager, the transaction log or the database will
have less than 1MB free. If I set allocated space to say 1GB larger and
restart the IIS on the web server then the web app starts working normally
again.
In the application log, errors similar to the following were present:
Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17052
Date: 5/9/2007
Time: 11:08:18 AM
User: N/A
Computer: SQL01
Description:
Error: 1105, Severity: 17, State: 2
Could not allocate space for object 'EventLog' in database 'Harmony_Live'
because the 'PRIMARY' filegroup is full.|||> Could not allocate space for object 'EventLog' in database 'Harmony_Live'
> because the 'PRIMARY' filegroup is full.
Well, that sounds to me like the disk is full. What is your autogrowth
setting for the data and log files? What is the current result of:
USE Harmony_Live;
GO
EXEC sp_helpfile;
EXEC sp_spaceused;
EXEC master..xp_fixeddrives;
GO|||Sorry about the formatting, but if you paste it into a text document it will
open correctly. I ran the query and the results were:
name fileid filename filegroup
size maxsize growth usage
-- -- --
-- -- -- --
Bremwood_Live_Data 1 F:\Harmony_Live\Harmony_Live.mdf PRIMARY
8192000 KB unlimited 10% data only
Bremwood_Live_Log 2 F:\Harmony_Live\Harmony_Live_log.ldf NULL
1024000 KB Unlimited 76800 KB log only
database_name database_size unallocated space
---
Harmony_Live 9000.00 MB 1318.63 MB
reserved data index_size unused
-- -- -- --
6841720 KB 6424632 KB 392088 KB 25000 KB
drive MB free
-- --
C 31155
D 122125
F 12074
(3 row(s) affected)|||Have you considered moving the log file to a different drive? You have
plenty of space on D:\
How about changing the growth on the data file from a % (since 800MB is a
lot to initialize, and it just gets worse every time) to a more reasonable
fixed number, like 10 MB? Unless the operations that are requiring growth
in the first place are going to need motre than that in one shot?
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Roger" <Roger@.discussions.microsoft.com> wrote in message
news:7293A561-541B-4D96-9849-1DC7CD0AA0E0@.microsoft.com...
> Sorry about the formatting, but if you paste it into a text document it
> will
> open correctly. I ran the query and the results were:
> name fileid filename filegroup
> size maxsize growth usage
> -- -- --
-
> -- -- -- --
> Bremwood_Live_Data 1 F:\Harmony_Live\Harmony_Live.mdf PRIMARY
> 8192000 KB unlimited 10% data only
> Bremwood_Live_Log 2 F:\Harmony_Live\Harmony_Live_log.ldf NULL
> 1024000 KB Unlimited 76800 KB log only
> database_name database_size unallocated space
> ---
> Harmony_Live 9000.00 MB 1318.63 MB
>
> reserved data index_size unused
> -- -- -- --
-
> 6841720 KB 6424632 KB 392088 KB 25000 KB
> drive MB free
> -- --
> C 31155
> D 122125
> F 12074
> (3 row(s) affected)
>|||I've tried them at 10MB, then 75MB, then back to 10%. The same thing happene
d
with either setting. I have them on that F: drive because it's on our SAN,
whereas the D: drive is not. I can increase the size of that F: drive easily
enough, but since we weren't filling it up I didn't think it would fix the
problem.
Right now the db is at 10% growth and the transaction log is at 75MB growth.
I'll change them both to 75MB, but I think it'll continue to not grow.
Thanks for troubleshooting this with me. Do you have any other ideas?
Roger
"Aaron Bertrand [SQL Server MVP]" wrote:

> Have you considered moving the log file to a different drive? You have
> plenty of space on D:\
> How about changing the growth on the data file from a % (since 800MB is a
> lot to initialize, and it just gets worse every time) to a more reasonable
> fixed number, like 10 MB? Unless the operations that are requiring growth
> in the first place are going to need motre than that in one shot?
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006|||> Thanks for troubleshooting this with me. Do you have any other ideas?
Not right now, no. Is it possible that permissions on F:\ have changed, or
the user that SQL Server is running as has changed?
A|||Anything's possible.
I logged into the SQL box as the user that SQL runs as and created/deleted a
text file in f:\harmony_live just to be sure.
Is there way I can send an alert if the database or transaction logs reach a
certain percentage of allocated space?
Thanks,
Roger

database and log files do not grow automatically

I have a database on SQL 2000 SP4 that is set via Enterprise Manager to grow
the database and log files automatically. It doesn't happen, however. They
run out of space and don't grow, even though there is plenty of physical disk
available. Any ideas what may be happening?
Thanks,
Roger> They run out of space and don't grow
Can you be more specific?
Are you getting an error message? If so, what is it?
Otherwise, how do you know they need to grow?
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006|||Hi Aaron,
The web application that uses the databases starts to throw errors to the
end users, such as "timeout expired" when they try to save data. If I look at
the database in Enterprise Manager, the transaction log or the database will
have less than 1MB free. If I set allocated space to say 1GB larger and
restart the IIS on the web server then the web app starts working normally
again.
In the application log, errors similar to the following were present:
Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17052
Date: 5/9/2007
Time: 11:08:18 AM
User: N/A
Computer: SQL01
Description:
Error: 1105, Severity: 17, State: 2
Could not allocate space for object 'EventLog' in database 'Harmony_Live'
because the 'PRIMARY' filegroup is full.|||> Could not allocate space for object 'EventLog' in database 'Harmony_Live'
> because the 'PRIMARY' filegroup is full.
Well, that sounds to me like the disk is full. What is your autogrowth
setting for the data and log files? What is the current result of:
USE Harmony_Live;
GO
EXEC sp_helpfile;
EXEC sp_spaceused;
EXEC master..xp_fixeddrives;
GO|||Sorry about the formatting, but if you paste it into a text document it will
open correctly. I ran the query and the results were:
name fileid filename filegroup
size maxsize growth usage
-- -- --
-- -- -- --
Bremwood_Live_Data 1 F:\Harmony_Live\Harmony_Live.mdf PRIMARY
8192000 KB unlimited 10% data only
Bremwood_Live_Log 2 F:\Harmony_Live\Harmony_Live_log.ldf NULL
1024000 KB Unlimited 76800 KB log only
database_name database_size unallocated space
---
Harmony_Live 9000.00 MB 1318.63 MB
reserved data index_size unused
-- -- -- --
6841720 KB 6424632 KB 392088 KB 25000 KB
drive MB free
-- --
C 31155
D 122125
F 12074
(3 row(s) affected)|||Have you considered moving the log file to a different drive? You have
plenty of space on D:\
How about changing the growth on the data file from a % (since 800MB is a
lot to initialize, and it just gets worse every time) to a more reasonable
fixed number, like 10 MB? Unless the operations that are requiring growth
in the first place are going to need motre than that in one shot?
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Roger" <Roger@.discussions.microsoft.com> wrote in message
news:7293A561-541B-4D96-9849-1DC7CD0AA0E0@.microsoft.com...
> Sorry about the formatting, but if you paste it into a text document it
> will
> open correctly. I ran the query and the results were:
> name fileid filename filegroup
> size maxsize growth usage
> -- -- --
> -- -- -- --
> Bremwood_Live_Data 1 F:\Harmony_Live\Harmony_Live.mdf PRIMARY
> 8192000 KB unlimited 10% data only
> Bremwood_Live_Log 2 F:\Harmony_Live\Harmony_Live_log.ldf NULL
> 1024000 KB Unlimited 76800 KB log only
> database_name database_size unallocated space
> ---
> Harmony_Live 9000.00 MB 1318.63 MB
>
> reserved data index_size unused
> -- -- -- --
> 6841720 KB 6424632 KB 392088 KB 25000 KB
> drive MB free
> -- --
> C 31155
> D 122125
> F 12074
> (3 row(s) affected)
>|||I've tried them at 10MB, then 75MB, then back to 10%. The same thing happened
with either setting. I have them on that F: drive because it's on our SAN,
whereas the D: drive is not. I can increase the size of that F: drive easily
enough, but since we weren't filling it up I didn't think it would fix the
problem.
Right now the db is at 10% growth and the transaction log is at 75MB growth.
I'll change them both to 75MB, but I think it'll continue to not grow.
Thanks for troubleshooting this with me. Do you have any other ideas?
Roger
"Aaron Bertrand [SQL Server MVP]" wrote:
> Have you considered moving the log file to a different drive? You have
> plenty of space on D:\
> How about changing the growth on the data file from a % (since 800MB is a
> lot to initialize, and it just gets worse every time) to a more reasonable
> fixed number, like 10 MB? Unless the operations that are requiring growth
> in the first place are going to need motre than that in one shot?
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006|||> Thanks for troubleshooting this with me. Do you have any other ideas?
Not right now, no. Is it possible that permissions on F:\ have changed, or
the user that SQL Server is running as has changed?
A|||Anything's possible. :)
I logged into the SQL box as the user that SQL runs as and created/deleted a
text file in f:\harmony_live just to be sure.
Is there way I can send an alert if the database or transaction logs reach a
certain percentage of allocated space?
Thanks,
Roger

database and log backup

We are using SQL 2K with sp4. I like someone to verify what I am doing is
correct. and there is no potential problem.
I am backing up my main database from one server (A) and restoring it to two
different servers (B and C). Here is the process.
#1. On server A, I do a full DB backup once a day and transaction log backup
once an hour. Before the next daily full backup occurs, the transaction log
file will be copied to a different folder and renamed to log2. Then the
tranaction log will be reinitialized.
#2. On server B, I do the database full restore and each transaction log
restore as soon as the backup is complete in server A. For example, if the
full backup happens at 2:00 am, the restore will occur as soon as it is done.
The transaction backup is happening hourlty so the restore will happen
hourly.
#3. On server C, I will only do the restore of the full backup once. Then I
will restore the transaction log from the log2 that I copied in step 1
above.
I do a simple test, it seems to be working fine. My 'unclear' part is on
#3. The source database is having a full backup daily. In my step 3, once
the initial full restore is done, I only rely on the transaction log (log2)
to keep server C data up to date. There will be no full database restore on
server C unless it is necessary. I believe it is ok unless someone can tell
me there is a hole somewhere in my thinking.
Any help on this is very much appreciated.
Wingman
Let me re-phrase your question, so you can see if I understand it:
On one of the restore server, you only want to restore the database backup once, and then skip the
database backups performed on the production server and only restore the log backups performed on
the production server?
Yes, that is fine. A database backup doesn't break the chain of log backups. Just be careful with
setting the production database to simple recovery or explicitly empty the log without backing up,
as that will break the log backups. This will of course affect #2 as well as #3, but since you are
periodically restoring the database backups on #2 it will self-repair within 24 hours.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Wingman" <Wingman@.discussions.microsoft.com> wrote in message
news:E3376109-4065-4705-AB71-A6EDA5104B64@.microsoft.com...
> We are using SQL 2K with sp4. I like someone to verify what I am doing is
> correct. and there is no potential problem.
> I am backing up my main database from one server (A) and restoring it to two
> different servers (B and C). Here is the process.
> #1. On server A, I do a full DB backup once a day and transaction log backup
> once an hour. Before the next daily full backup occurs, the transaction log
> file will be copied to a different folder and renamed to log2. Then the
> tranaction log will be reinitialized.
> #2. On server B, I do the database full restore and each transaction log
> restore as soon as the backup is complete in server A. For example, if the
> full backup happens at 2:00 am, the restore will occur as soon as it is done.
> The transaction backup is happening hourlty so the restore will happen
> hourly.
> #3. On server C, I will only do the restore of the full backup once. Then I
> will restore the transaction log from the log2 that I copied in step 1
> above.
> I do a simple test, it seems to be working fine. My 'unclear' part is on
> #3. The source database is having a full backup daily. In my step 3, once
> the initial full restore is done, I only rely on the transaction log (log2)
> to keep server C data up to date. There will be no full database restore on
> server C unless it is necessary. I believe it is ok unless someone can tell
> me there is a hole somewhere in my thinking.
> Any help on this is very much appreciated.
> Wingman
|||Yes, that is exactly what I want to ask. You read my mind. And your answer
is exactly what I thought it would be. Thank man!!
Wingman
"Wingman" wrote:

> We are using SQL 2K with sp4. I like someone to verify what I am doing is
> correct. and there is no potential problem.
> I am backing up my main database from one server (A) and restoring it to two
> different servers (B and C). Here is the process.
> #1. On server A, I do a full DB backup once a day and transaction log backup
> once an hour. Before the next daily full backup occurs, the transaction log
> file will be copied to a different folder and renamed to log2. Then the
> tranaction log will be reinitialized.
> #2. On server B, I do the database full restore and each transaction log
> restore as soon as the backup is complete in server A. For example, if the
> full backup happens at 2:00 am, the restore will occur as soon as it is done.
> The transaction backup is happening hourlty so the restore will happen
> hourly.
> #3. On server C, I will only do the restore of the full backup once. Then I
> will restore the transaction log from the log2 that I copied in step 1
> above.
> I do a simple test, it seems to be working fine. My 'unclear' part is on
> #3. The source database is having a full backup daily. In my step 3, once
> the initial full restore is done, I only rely on the transaction log (log2)
> to keep server C data up to date. There will be no full database restore on
> server C unless it is necessary. I believe it is ok unless someone can tell
> me there is a hole somewhere in my thinking.
> Any help on this is very much appreciated.
> Wingman

database and log backup

We are using SQL 2K with sp4. I like someone to verify what I am doing is
correct. and there is no potential problem.
I am backing up my main database from one server (A) and restoring it to two
different servers (B and C). Here is the process.
#1. On server A, I do a full DB backup once a day and transaction log backup
once an hour. Before the next daily full backup occurs, the transaction log
file will be copied to a different folder and renamed to log2. Then the
tranaction log will be reinitialized.
#2. On server B, I do the database full restore and each transaction log
restore as soon as the backup is complete in server A. For example, if the
full backup happens at 2:00 am, the restore will occur as soon as it is done
.
The transaction backup is happening hourlty so the restore will happen
hourly.
#3. On server C, I will only do the restore of the full backup once. Then I
will restore the transaction log from the log2 that I copied in step 1
above.
I do a simple test, it seems to be working fine. My 'unclear' part is on
#3. The source database is having a full backup daily. In my step 3, once
the initial full restore is done, I only rely on the transaction log (log2)
to keep server C data up to date. There will be no full database restore o
n
server C unless it is necessary. I believe it is ok unless someone can tell
me there is a hole somewhere in my thinking.
Any help on this is very much appreciated.
WingmanLet me re-phrase your question, so you can see if I understand it:
On one of the restore server, you only want to restore the database backup o
nce, and then skip the
database backups performed on the production server and only restore the log
backups performed on
the production server?
Yes, that is fine. A database backup doesn't break the chain of log backups.
Just be careful with
setting the production database to simple recovery or explicitly empty the l
og without backing up,
as that will break the log backups. This will of course affect #2 as well as
#3, but since you are
periodically restoring the database backups on #2 it will self-repair within
24 hours.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Wingman" <Wingman@.discussions.microsoft.com> wrote in message
news:E3376109-4065-4705-AB71-A6EDA5104B64@.microsoft.com...
> We are using SQL 2K with sp4. I like someone to verify what I am doing is
> correct. and there is no potential problem.
> I am backing up my main database from one server (A) and restoring it to t
wo
> different servers (B and C). Here is the process.
> #1. On server A, I do a full DB backup once a day and transaction log back
up
> once an hour. Before the next daily full backup occurs, the transaction l
og
> file will be copied to a different folder and renamed to log2. Then the
> tranaction log will be reinitialized.
> #2. On server B, I do the database full restore and each transaction log
> restore as soon as the backup is complete in server A. For example, if th
e
> full backup happens at 2:00 am, the restore will occur as soon as it is do
ne.
> The transaction backup is happening hourlty so the restore will happen
> hourly.
> #3. On server C, I will only do the restore of the full backup once. Then
I
> will restore the transaction log from the log2 that I copied in step 1
> above.
> I do a simple test, it seems to be working fine. My 'unclear' part is on
> #3. The source database is having a full backup daily. In my step 3, onc
e
> the initial full restore is done, I only rely on the transaction log (log2
)
> to keep server C data up to date. There will be no full database restore
on
> server C unless it is necessary. I believe it is ok unless someone can te
ll
> me there is a hole somewhere in my thinking.
> Any help on this is very much appreciated.
> Wingman|||Yes, that is exactly what I want to ask. You read my mind. And your answer
is exactly what I thought it would be. Thank man!!
Wingman
"Wingman" wrote:

> We are using SQL 2K with sp4. I like someone to verify what I am doing is
> correct. and there is no potential problem.
> I am backing up my main database from one server (A) and restoring it to t
wo
> different servers (B and C). Here is the process.
> #1. On server A, I do a full DB backup once a day and transaction log back
up
> once an hour. Before the next daily full backup occurs, the transaction l
og
> file will be copied to a different folder and renamed to log2. Then the
> tranaction log will be reinitialized.
> #2. On server B, I do the database full restore and each transaction log
> restore as soon as the backup is complete in server A. For example, if th
e
> full backup happens at 2:00 am, the restore will occur as soon as it is do
ne.
> The transaction backup is happening hourlty so the restore will happen
> hourly.
> #3. On server C, I will only do the restore of the full backup once. Then
I
> will restore the transaction log from the log2 that I copied in step 1
> above.
> I do a simple test, it seems to be working fine. My 'unclear' part is on
> #3. The source database is having a full backup daily. In my step 3, onc
e
> the initial full restore is done, I only rely on the transaction log (log2
)
> to keep server C data up to date. There will be no full database restore
on
> server C unless it is necessary. I believe it is ok unless someone can te
ll
> me there is a hole somewhere in my thinking.
> Any help on this is very much appreciated.
> Wingman

database and log backup

We are using SQL 2K with sp4. I like someone to verify what I am doing is
correct. and there is no potential problem.
I am backing up my main database from one server (A) and restoring it to two
different servers (B and C). Here is the process.
#1. On server A, I do a full DB backup once a day and transaction log backup
once an hour. Before the next daily full backup occurs, the transaction log
file will be copied to a different folder and renamed to log2. Then the
tranaction log will be reinitialized.
#2. On server B, I do the database full restore and each transaction log
restore as soon as the backup is complete in server A. For example, if the
full backup happens at 2:00 am, the restore will occur as soon as it is done.
The transaction backup is happening hourlty so the restore will happen
hourly.
#3. On server C, I will only do the restore of the full backup once. Then I
will restore the transaction log from the log2 that I copied in step 1
above.
I do a simple test, it seems to be working fine. My 'unclear' part is on
#3. The source database is having a full backup daily. In my step 3, once
the initial full restore is done, I only rely on the transaction log (log2)
to keep server C data up to date. There will be no full database restore on
server C unless it is necessary. I believe it is ok unless someone can tell
me there is a hole somewhere in my thinking.
Any help on this is very much appreciated.
WingmanLet me re-phrase your question, so you can see if I understand it:
On one of the restore server, you only want to restore the database backup once, and then skip the
database backups performed on the production server and only restore the log backups performed on
the production server?
Yes, that is fine. A database backup doesn't break the chain of log backups. Just be careful with
setting the production database to simple recovery or explicitly empty the log without backing up,
as that will break the log backups. This will of course affect #2 as well as #3, but since you are
periodically restoring the database backups on #2 it will self-repair within 24 hours.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Wingman" <Wingman@.discussions.microsoft.com> wrote in message
news:E3376109-4065-4705-AB71-A6EDA5104B64@.microsoft.com...
> We are using SQL 2K with sp4. I like someone to verify what I am doing is
> correct. and there is no potential problem.
> I am backing up my main database from one server (A) and restoring it to two
> different servers (B and C). Here is the process.
> #1. On server A, I do a full DB backup once a day and transaction log backup
> once an hour. Before the next daily full backup occurs, the transaction log
> file will be copied to a different folder and renamed to log2. Then the
> tranaction log will be reinitialized.
> #2. On server B, I do the database full restore and each transaction log
> restore as soon as the backup is complete in server A. For example, if the
> full backup happens at 2:00 am, the restore will occur as soon as it is done.
> The transaction backup is happening hourlty so the restore will happen
> hourly.
> #3. On server C, I will only do the restore of the full backup once. Then I
> will restore the transaction log from the log2 that I copied in step 1
> above.
> I do a simple test, it seems to be working fine. My 'unclear' part is on
> #3. The source database is having a full backup daily. In my step 3, once
> the initial full restore is done, I only rely on the transaction log (log2)
> to keep server C data up to date. There will be no full database restore on
> server C unless it is necessary. I believe it is ok unless someone can tell
> me there is a hole somewhere in my thinking.
> Any help on this is very much appreciated.
> Wingman|||what you are doing is known as log shipping but you are doing it
manually , if you are using Enterprise Edition you can configure it as
automated process using Wizard.
I feel there is no need of task of Step #2 of daily restoring full
backup.
Books online has much more information reagrding logshipping.
Regards
Amish Shah|||Yes, that is exactly what I want to ask. You read my mind. And your answer
is exactly what I thought it would be. Thank man!!
Wingman
"Wingman" wrote:
> We are using SQL 2K with sp4. I like someone to verify what I am doing is
> correct. and there is no potential problem.
> I am backing up my main database from one server (A) and restoring it to two
> different servers (B and C). Here is the process.
> #1. On server A, I do a full DB backup once a day and transaction log backup
> once an hour. Before the next daily full backup occurs, the transaction log
> file will be copied to a different folder and renamed to log2. Then the
> tranaction log will be reinitialized.
> #2. On server B, I do the database full restore and each transaction log
> restore as soon as the backup is complete in server A. For example, if the
> full backup happens at 2:00 am, the restore will occur as soon as it is done.
> The transaction backup is happening hourlty so the restore will happen
> hourly.
> #3. On server C, I will only do the restore of the full backup once. Then I
> will restore the transaction log from the log2 that I copied in step 1
> above.
> I do a simple test, it seems to be working fine. My 'unclear' part is on
> #3. The source database is having a full backup daily. In my step 3, once
> the initial full restore is done, I only rely on the transaction log (log2)
> to keep server C data up to date. There will be no full database restore on
> server C unless it is necessary. I believe it is ok unless someone can tell
> me there is a hole somewhere in my thinking.
> Any help on this is very much appreciated.
> Wingman