Database Restore Causes Log Full

Hi,
I've 2 SQL Server, SQL1 as Primary and SQL2 as Stand-By Server. Database
size is about 5 GB.
I want to restore full and T-logs on SQL2, so my SQL1 Recovery model is set
to Full.
As I don't have off-peak hours, I can not detach/attach database and move it
to SQL2. once a day, I need to move a full backup from SQL1 to SQL2. There's
no problem in backup and copying it to SQL2, but when I want to restore it on
SQL2, restore progress starts and finishes (as I see in sql logs that restore
is finished successfuly) but in "recovery is checkpointing database ..."
step, my database log file starts to grow, it grows until my log become full
and sql get into errors.
in this stage, I can not "backup log" (probably because it's in checkpoiting
process).
Also I tried to "backup and shrink log" in SQL1 before Full Backup, to have
minimum log size, but still after restoring on SQL1, when restore progress
bar is still visible and it's on 100% position, log file on SQL2 starts to
grow...
I suspect that the problem might be because of Full Recovery Model in SQL1
during Full Backup, but because of T-Logs I need this recovery model.
How can I restore my full backup in this case?!!
Best Regards,
Pierre.Hi
http://www.sql-server-performance.com/sql_server_log_shipping.asp
"Pierre" <Pierre@.discussions.microsoft.com> wrote in message
news:EB57ED5A-ADE7-4ACC-898A-1B524B4A3AED@.microsoft.com...
> Hi,
> I've 2 SQL Server, SQL1 as Primary and SQL2 as Stand-By Server. Database
> size is about 5 GB.
> I want to restore full and T-logs on SQL2, so my SQL1 Recovery model is
> set
> to Full.
> As I don't have off-peak hours, I can not detach/attach database and move
> it
> to SQL2. once a day, I need to move a full backup from SQL1 to SQL2.
> There's
> no problem in backup and copying it to SQL2, but when I want to restore it
> on
> SQL2, restore progress starts and finishes (as I see in sql logs that
> restore
> is finished successfuly) but in "recovery is checkpointing database ..."
> step, my database log file starts to grow, it grows until my log become
> full
> and sql get into errors.
> in this stage, I can not "backup log" (probably because it's in
> checkpoiting
> process).
> Also I tried to "backup and shrink log" in SQL1 before Full Backup, to
> have
> minimum log size, but still after restoring on SQL1, when restore progress
> bar is still visible and it's on 100% position, log file on SQL2 starts to
> grow...
> I suspect that the problem might be because of Full Recovery Model in SQL1
> during Full Backup, but because of T-Logs I need this recovery model.
> How can I restore my full backup in this case?!!
> Best Regards,
> Pierre.|||Hi Uri,
Thanks for URL, the restore query in this article was:
RESTORE DATABASE database_name
FROM DISK = 'g:\mssql7\backup\database_name\database_name_backup_device.bak'
WITH
DBO_ONLY,
REPLACE,
STANDBY = 'g:\mssql7\backup\database_name\undo_database_name.ldf',
MOVE 'logical_name' TO 'h:\mssql7\data\database_name.mdf',
MOVE 'logical_name' TO 'f:\mssql7\log\database_name_log.ldf'
I checked it up, what option of this command will help me overcome this
problem?
What's difference between this command and restore wizard that I'm using
currently and get me to this problem?
Regards,
Pierre.
"Uri Dimant" wrote:
> Hi
> http://www.sql-server-performance.com/sql_server_log_shipping.asp
>
>
>
> "Pierre" <Pierre@.discussions.microsoft.com> wrote in message
> news:EB57ED5A-ADE7-4ACC-898A-1B524B4A3AED@.microsoft.com...
> > Hi,
> >
> > I've 2 SQL Server, SQL1 as Primary and SQL2 as Stand-By Server. Database
> > size is about 5 GB.
> > I want to restore full and T-logs on SQL2, so my SQL1 Recovery model is
> > set
> > to Full.
> > As I don't have off-peak hours, I can not detach/attach database and move
> > it
> > to SQL2. once a day, I need to move a full backup from SQL1 to SQL2.
> > There's
> > no problem in backup and copying it to SQL2, but when I want to restore it
> > on
> > SQL2, restore progress starts and finishes (as I see in sql logs that
> > restore
> > is finished successfuly) but in "recovery is checkpointing database ..."
> > step, my database log file starts to grow, it grows until my log become
> > full
> > and sql get into errors.
> > in this stage, I can not "backup log" (probably because it's in
> > checkpoiting
> > process).
> > Also I tried to "backup and shrink log" in SQL1 before Full Backup, to
> > have
> > minimum log size, but still after restoring on SQL1, when restore progress
> > bar is still visible and it's on 100% position, log file on SQL2 starts to
> > grow...
> >
> > I suspect that the problem might be because of Full Recovery Model in SQL1
> > during Full Backup, but because of T-Logs I need this recovery model.
> >
> > How can I restore my full backup in this case?!!
> >
> > Best Regards,
> > Pierre.
>
>|||Do you need the db in STANDBY mode?, if not, then use NORECOVERY option
instead, so can then restore the transaction log.
> STANDBY = 'g:\mssql7\backup\database_name\undo_database_name.ldf',
...
NORECOVERY,
...
AMB
"Pierre" wrote:
> Hi Uri,
> Thanks for URL, the restore query in this article was:
> RESTORE DATABASE database_name
> FROM DISK = 'g:\mssql7\backup\database_name\database_name_backup_device.bak'
> WITH
> DBO_ONLY,
> REPLACE,
> STANDBY = 'g:\mssql7\backup\database_name\undo_database_name.ldf',
> MOVE 'logical_name' TO 'h:\mssql7\data\database_name.mdf',
> MOVE 'logical_name' TO 'f:\mssql7\log\database_name_log.ldf'
> I checked it up, what option of this command will help me overcome this
> problem?
> What's difference between this command and restore wizard that I'm using
> currently and get me to this problem?
>
> Regards,
> Pierre.
> "Uri Dimant" wrote:
> > Hi
> > http://www.sql-server-performance.com/sql_server_log_shipping.asp
> >
> >
> >
> >
> >
> >
> > "Pierre" <Pierre@.discussions.microsoft.com> wrote in message
> > news:EB57ED5A-ADE7-4ACC-898A-1B524B4A3AED@.microsoft.com...
> > > Hi,
> > >
> > > I've 2 SQL Server, SQL1 as Primary and SQL2 as Stand-By Server. Database
> > > size is about 5 GB.
> > > I want to restore full and T-logs on SQL2, so my SQL1 Recovery model is
> > > set
> > > to Full.
> > > As I don't have off-peak hours, I can not detach/attach database and move
> > > it
> > > to SQL2. once a day, I need to move a full backup from SQL1 to SQL2.
> > > There's
> > > no problem in backup and copying it to SQL2, but when I want to restore it
> > > on
> > > SQL2, restore progress starts and finishes (as I see in sql logs that
> > > restore
> > > is finished successfuly) but in "recovery is checkpointing database ..."
> > > step, my database log file starts to grow, it grows until my log become
> > > full
> > > and sql get into errors.
> > > in this stage, I can not "backup log" (probably because it's in
> > > checkpoiting
> > > process).
> > > Also I tried to "backup and shrink log" in SQL1 before Full Backup, to
> > > have
> > > minimum log size, but still after restoring on SQL1, when restore progress
> > > bar is still visible and it's on 100% position, log file on SQL2 starts to
> > > grow...
> > >
> > > I suspect that the problem might be because of Full Recovery Model in SQL1
> > > during Full Backup, but because of T-Logs I need this recovery model.
> > >
> > > How can I restore my full backup in this case?!!
> > >
> > > Best Regards,
> > > Pierre.
> >
> >
> >|||Dear Alejandro,
At this time, I just want to restore my full backup on secondary server
(which growing log file causes restore wizard to fail), then if with STANDBY
options restore succeeded, I'll use NONRECOVERY to be able to restore my
t-logs.
Regards,
Pierre.
"Alejandro Mesa" wrote:
> Do you need the db in STANDBY mode?, if not, then use NORECOVERY option
> instead, so can then restore the transaction log.
> > STANDBY = 'g:\mssql7\backup\database_name\undo_database_name.ldf',
> ...
> NORECOVERY,
> ...
>
> AMB
> "Pierre" wrote:
> > Hi Uri,
> >
> > Thanks for URL, the restore query in this article was:
> >
> > RESTORE DATABASE database_name
> > FROM DISK = 'g:\mssql7\backup\database_name\database_name_backup_device.bak'
> > WITH
> > DBO_ONLY,
> > REPLACE,
> > STANDBY = 'g:\mssql7\backup\database_name\undo_database_name.ldf',
> > MOVE 'logical_name' TO 'h:\mssql7\data\database_name.mdf',
> > MOVE 'logical_name' TO 'f:\mssql7\log\database_name_log.ldf'
> >
> > I checked it up, what option of this command will help me overcome this
> > problem?
> >
> > What's difference between this command and restore wizard that I'm using
> > currently and get me to this problem?
> >
> >
> > Regards,
> > Pierre.
> >
> > "Uri Dimant" wrote:
> >
> > > Hi
> > > http://www.sql-server-performance.com/sql_server_log_shipping.asp
> > >
> > >
> > >
> > >
> > >
> > >
> > > "Pierre" <Pierre@.discussions.microsoft.com> wrote in message
> > > news:EB57ED5A-ADE7-4ACC-898A-1B524B4A3AED@.microsoft.com...
> > > > Hi,
> > > >
> > > > I've 2 SQL Server, SQL1 as Primary and SQL2 as Stand-By Server. Database
> > > > size is about 5 GB.
> > > > I want to restore full and T-logs on SQL2, so my SQL1 Recovery model is
> > > > set
> > > > to Full.
> > > > As I don't have off-peak hours, I can not detach/attach database and move
> > > > it
> > > > to SQL2. once a day, I need to move a full backup from SQL1 to SQL2.
> > > > There's
> > > > no problem in backup and copying it to SQL2, but when I want to restore it
> > > > on
> > > > SQL2, restore progress starts and finishes (as I see in sql logs that
> > > > restore
> > > > is finished successfuly) but in "recovery is checkpointing database ..."
> > > > step, my database log file starts to grow, it grows until my log become
> > > > full
> > > > and sql get into errors.
> > > > in this stage, I can not "backup log" (probably because it's in
> > > > checkpoiting
> > > > process).
> > > > Also I tried to "backup and shrink log" in SQL1 before Full Backup, to
> > > > have
> > > > minimum log size, but still after restoring on SQL1, when restore progress
> > > > bar is still visible and it's on 100% position, log file on SQL2 starts to
> > > > grow...
> > > >
> > > > I suspect that the problem might be because of Full Recovery Model in SQL1
> > > > during Full Backup, but because of T-Logs I need this recovery model.
> > > >
> > > > How can I restore my full backup in this case?!!
> > > >
> > > > Best Regards,
> > > > Pierre.
> > >
> > >
> > >|||Hi Again,
restore database db_name with standby ... was successfull and restore
compeleted (database became readonly). at this time I dont have any t-log
backups, and I want to bring this server online, as I suspect I should run
"restore database db_name with recovery" , but when I run this query log file
starts to grow and will become full...
why running "with recovery" fills my log file?! How can I make my database
operational?
Regards,
Pierre.
"Pierre" wrote:
> Dear Alejandro,
> At this time, I just want to restore my full backup on secondary server
> (which growing log file causes restore wizard to fail), then if with STANDBY
> options restore succeeded, I'll use NONRECOVERY to be able to restore my
> t-logs.
> Regards,
> Pierre.
> "Alejandro Mesa" wrote:
> > Do you need the db in STANDBY mode?, if not, then use NORECOVERY option
> > instead, so can then restore the transaction log.
> >
> > > STANDBY = 'g:\mssql7\backup\database_name\undo_database_name.ldf',
> >
> > ...
> > NORECOVERY,
> > ...
> >
> >
> > AMB
> >
> > "Pierre" wrote:
> >
> > > Hi Uri,
> > >
> > > Thanks for URL, the restore query in this article was:
> > >
> > > RESTORE DATABASE database_name
> > > FROM DISK = 'g:\mssql7\backup\database_name\database_name_backup_device.bak'
> > > WITH
> > > DBO_ONLY,
> > > REPLACE,
> > > STANDBY = 'g:\mssql7\backup\database_name\undo_database_name.ldf',
> > > MOVE 'logical_name' TO 'h:\mssql7\data\database_name.mdf',
> > > MOVE 'logical_name' TO 'f:\mssql7\log\database_name_log.ldf'
> > >
> > > I checked it up, what option of this command will help me overcome this
> > > problem?
> > >
> > > What's difference between this command and restore wizard that I'm using
> > > currently and get me to this problem?
> > >
> > >
> > > Regards,
> > > Pierre.
> > >
> > > "Uri Dimant" wrote:
> > >
> > > > Hi
> > > > http://www.sql-server-performance.com/sql_server_log_shipping.asp
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > "Pierre" <Pierre@.discussions.microsoft.com> wrote in message
> > > > news:EB57ED5A-ADE7-4ACC-898A-1B524B4A3AED@.microsoft.com...
> > > > > Hi,
> > > > >
> > > > > I've 2 SQL Server, SQL1 as Primary and SQL2 as Stand-By Server. Database
> > > > > size is about 5 GB.
> > > > > I want to restore full and T-logs on SQL2, so my SQL1 Recovery model is
> > > > > set
> > > > > to Full.
> > > > > As I don't have off-peak hours, I can not detach/attach database and move
> > > > > it
> > > > > to SQL2. once a day, I need to move a full backup from SQL1 to SQL2.
> > > > > There's
> > > > > no problem in backup and copying it to SQL2, but when I want to restore it
> > > > > on
> > > > > SQL2, restore progress starts and finishes (as I see in sql logs that
> > > > > restore
> > > > > is finished successfuly) but in "recovery is checkpointing database ..."
> > > > > step, my database log file starts to grow, it grows until my log become
> > > > > full
> > > > > and sql get into errors.
> > > > > in this stage, I can not "backup log" (probably because it's in
> > > > > checkpoiting
> > > > > process).
> > > > > Also I tried to "backup and shrink log" in SQL1 before Full Backup, to
> > > > > have
> > > > > minimum log size, but still after restoring on SQL1, when restore progress
> > > > > bar is still visible and it's on 100% position, log file on SQL2 starts to
> > > > > grow...
> > > > >
> > > > > I suspect that the problem might be because of Full Recovery Model in SQL1
> > > > > during Full Backup, but because of T-Logs I need this recovery model.
> > > > >
> > > > > How can I restore my full backup in this case?!!
> > > > >
> > > > > Best Regards,
> > > > > Pierre.
> > > >
> > > >
> > > >