Restore Timing Transaction Logs versus Differentials

Currently we backup out SQL Server 2005 databases with a combination of full, differential and transaction log backups and plan on continuing this practice. Currently all backups are written to physical servers other than the servers where the DBs are running and all backups are also written to tape. Our tape backups are costly and I am considering no longer writing the differential backups to tape to reduce costs. Before making this decision I would like to understand how much longer it would take to recover several days of transaction log backups, versus a single differential and only a few hours of transation log backups. Does anyone have any any information about the time difference? Any ratios or rules of thumb?

Thanks,
Julia

Stopping the tape copies of differentials seems like a reasonable plan IF you kept two or three of the DIFFERENTIAL BACKUP copies on disk. (I prefer to keep all differentials since the last FULL BACKUP, tossing them only after the next FULL BACKUP is verified.

Time to recover: Seems like the major time issue is the manual process of handling each individual restore. The fewer files to restore, the less 'manual' time. The greated the number of files involved, the greater the risk of a corrupt or damaged file.

|||
Thanks for your reply. Any thoughts about how much longer it would take to restore the transaction log backups?
|||

There are so many variables on that, I wouldn't hazard a guess.

You could run a comparision test on another server, comparing the timings.

|||Has anyone run these types of timed tests comparing the restore times from 2 types of backups?|||

That is purely depends upon the number of transactions on your database to restore, see this blog http://sqlserver-qa.net/blogs/perftune/archive/2007/06/12/get-backup-and-restore-performance-stats-with-a-dmv.aspx on the performance stats for backup & restore tasks.

EgleK wrote:

Thanks for your reply. Any thoughts about how much longer it would take to restore the transaction log backups?

Restore SQL 2000 different domain?

Hello. Would anyone know of the most advisable method in moving our SQL
2000 on ServerA to another SQL 2000 machine ServerB where we plan on
retiring ServerA and ServerB will take on the same name and IP address as
ServerA? I'm mainly wondering since we have some windows authentication
logins on our SQL Server and moving it to a different domain/machine would
affect anything.
Thanks in advance.
J
Hi
"J" wrote:

> Hello. Would anyone know of the most advisable method in moving our SQL
> 2000 on ServerA to another SQL 2000 machine ServerB where we plan on
> retiring ServerA and ServerB will take on the same name and IP address as
> ServerA? I'm mainly wondering since we have some windows authentication
> logins on our SQL Server and moving it to a different domain/machine would
> affect anything.
>
See http://support.microsoft.com/kb/246133/ on how to transfer logins from
one server to another.
If you rename the server you will need to drop it and re-add it. See
http://support.microsoft.com/kb/303774/
General information about moving databases see
http://support.microsoft.com/kb/314546

> Thanks in advance.
> J
John
|||Thanks for the info. Much appreciated.
Take care.
J
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:FC96D7E6-AEC9-4CC7-8602-75B61F1F571E@.microsoft.com...
> Hi
> "J" wrote:
> See http://support.microsoft.com/kb/246133/ on how to transfer logins from
> one server to another.
> If you rename the server you will need to drop it and re-add it. See
> http://support.microsoft.com/kb/303774/
> General information about moving databases see
> http://support.microsoft.com/kb/314546
>
> John

Restore SQL 2000 different domain?

Hello. Would anyone know of the most advisable method in moving our SQL
2000 on ServerA to another SQL 2000 machine ServerB where we plan on
retiring ServerA and ServerB will take on the same name and IP address as
ServerA? I'm mainly wondering since we have some windows authentication
logins on our SQL Server and moving it to a different domain/machine would
affect anything.
Thanks in advance.
JHi
"J" wrote:
> Hello. Would anyone know of the most advisable method in moving our SQL
> 2000 on ServerA to another SQL 2000 machine ServerB where we plan on
> retiring ServerA and ServerB will take on the same name and IP address as
> ServerA? I'm mainly wondering since we have some windows authentication
> logins on our SQL Server and moving it to a different domain/machine would
> affect anything.
>
See http://support.microsoft.com/kb/246133/ on how to transfer logins from
one server to another.
If you rename the server you will need to drop it and re-add it. See
http://support.microsoft.com/kb/303774/
General information about moving databases see
http://support.microsoft.com/kb/314546
> Thanks in advance.
> J
John|||Thanks for the info. Much appreciated.
Take care.
J
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:FC96D7E6-AEC9-4CC7-8602-75B61F1F571E@.microsoft.com...
> Hi
> "J" wrote:
>> Hello. Would anyone know of the most advisable method in moving our SQL
>> 2000 on ServerA to another SQL 2000 machine ServerB where we plan on
>> retiring ServerA and ServerB will take on the same name and IP address as
>> ServerA? I'm mainly wondering since we have some windows authentication
>> logins on our SQL Server and moving it to a different domain/machine
>> would
>> affect anything.
> See http://support.microsoft.com/kb/246133/ on how to transfer logins from
> one server to another.
> If you rename the server you will need to drop it and re-add it. See
> http://support.microsoft.com/kb/303774/
> General information about moving databases see
> http://support.microsoft.com/kb/314546
>> Thanks in advance.
>> J
> John

Restore SQL 2000 different domain?

Hello. Would anyone know of the most advisable method in moving our SQL
2000 on ServerA to another SQL 2000 machine ServerB where we plan on
retiring ServerA and ServerB will take on the same name and IP address as
ServerA? I'm mainly wondering since we have some windows authentication
logins on our SQL Server and moving it to a different domain/machine would
affect anything.
Thanks in advance.
JHi
"J" wrote:

> Hello. Would anyone know of the most advisable method in moving our SQL
> 2000 on ServerA to another SQL 2000 machine ServerB where we plan on
> retiring ServerA and ServerB will take on the same name and IP address as
> ServerA? I'm mainly wondering since we have some windows authentication
> logins on our SQL Server and moving it to a different domain/machine would
> affect anything.
>
See http://support.microsoft.com/kb/246133/ on how to transfer logins from
one server to another.
If you rename the server you will need to drop it and re-add it. See
http://support.microsoft.com/kb/303774/
General information about moving databases see
http://support.microsoft.com/kb/314546

> Thanks in advance.
> J
John|||Thanks for the info. Much appreciated.
Take care.
J
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:FC96D7E6-AEC9-4CC7-8602-75B61F1F571E@.microsoft.com...
> Hi
> "J" wrote:
>
> See http://support.microsoft.com/kb/246133/ on how to transfer logins from
> one server to another.
> If you rename the server you will need to drop it and re-add it. See
> http://support.microsoft.com/kb/303774/
> General information about moving databases see
> http://support.microsoft.com/kb/314546
>
> Johnsql

restore question

I have SQL2000 server with 300 databases. I have a maintenance plan that
performs a full backup of all databases every night.
I have performed test restores of some of those databases but now I'm
wondering in case that something catastrophic happens and I have to restore
entire SQL server, how can I do that?
Would I have to restore every database separately or is there a way to
restore all of them in one step (some transact SQL script ...)?
Tom
Hi,
Best option is to restore the system databases first and then restore the
User databases one by one. You could write the RESTORE DATABASE
Script and execute it from Query Analyzer in a single execution.
See the below URLS for the step by step approach with different
methodologies:-
http://support.microsoft.com/default...b;en-us;304692
http://support.microsoft.com/default...b;en-us;224071
http://support.microsoft.com/default...b;en-us;314546
Thanks
Hari
SQL Server MVP
"Tom" <mcseman2002@.hotmail.com> wrote in message
news:%23XIEZ1VjFHA.2904@.tk2msftngp13.phx.gbl...
>I have SQL2000 server with 300 databases. I have a maintenance plan that
>performs a full backup of all databases every night.
> I have performed test restores of some of those databases but now I'm
> wondering in case that something catastrophic happens and I have to
> restore entire SQL server, how can I do that?
> Would I have to restore every database separately or is there a way to
> restore all of them in one step (some transact SQL script ...)?
> Tom
>
|||Hi Tom,
This gives you an opportunity to move some of the databases to different
servers, to mitigate the risk of running all the databases on the same server.
Also make sure that you are running the server on disk array's, to avoid any
disk failures.
For avoid dependency on OS, you can look at fail-over clusterting also.
- - - - - - - - -
Thanks
Yogish
"Tom" wrote:

> I have SQL2000 server with 300 databases. I have a maintenance plan that
> performs a full backup of all databases every night.
> I have performed test restores of some of those databases but now I'm
> wondering in case that something catastrophic happens and I have to restore
> entire SQL server, how can I do that?
> Would I have to restore every database separately or is there a way to
> restore all of them in one step (some transact SQL script ...)?
> Tom
>
>

restore question

I have SQL2000 server with 300 databases. I have a maintenance plan that
performs a full backup of all databases every night.
I have performed test restores of some of those databases but now I'm
wondering in case that something catastrophic happens and I have to restore
entire SQL server, how can I do that?
Would I have to restore every database separately or is there a way to
restore all of them in one step (some transact SQL script ...)?
TomHi,
Best option is to restore the system databases first and then restore the
User databases one by one. You could write the RESTORE DATABASE
Script and execute it from Query Analyzer in a single execution.
See the below URLS for the step by step approach with different
methodologies:-
http://support.microsoft.com/default.aspx?scid=kb;en-us;304692
http://support.microsoft.com/default.aspx?scid=kb;en-us;224071
http://support.microsoft.com/default.aspx?scid=kb;en-us;314546
Thanks
Hari
SQL Server MVP
"Tom" <mcseman2002@.hotmail.com> wrote in message
news:%23XIEZ1VjFHA.2904@.tk2msftngp13.phx.gbl...
>I have SQL2000 server with 300 databases. I have a maintenance plan that
>performs a full backup of all databases every night.
> I have performed test restores of some of those databases but now I'm
> wondering in case that something catastrophic happens and I have to
> restore entire SQL server, how can I do that?
> Would I have to restore every database separately or is there a way to
> restore all of them in one step (some transact SQL script ...)?
> Tom
>|||Hi Tom,
This gives you an opportunity to move some of the databases to different
servers, to mitigate the risk of running all the databases on the same server.
Also make sure that you are running the server on disk array's, to avoid any
disk failures.
For avoid dependency on OS, you can look at fail-over clusterting also.
--
- - - - - - - - -
Thanks
Yogish
"Tom" wrote:
> I have SQL2000 server with 300 databases. I have a maintenance plan that
> performs a full backup of all databases every night.
> I have performed test restores of some of those databases but now I'm
> wondering in case that something catastrophic happens and I have to restore
> entire SQL server, how can I do that?
> Would I have to restore every database separately or is there a way to
> restore all of them in one step (some transact SQL script ...)?
> Tom
>
>

restore question

I have SQL2000 server with 300 databases. I have a maintenance plan that
performs a full backup of all databases every night.
I have performed test restores of some of those databases but now I'm
wondering in case that something catastrophic happens and I have to restore
entire SQL server, how can I do that?
Would I have to restore every database separately or is there a way to
restore all of them in one step (some transact SQL script ...)?
TomHi,
Best option is to restore the system databases first and then restore the
User databases one by one. You could write the RESTORE DATABASE
Script and execute it from Query Analyzer in a single execution.
See the below URLS for the step by step approach with different
methodologies:-
http://support.microsoft.com/defaul...kb;en-us;304692
http://support.microsoft.com/defaul...kb;en-us;224071
http://support.microsoft.com/defaul...kb;en-us;314546
Thanks
Hari
SQL Server MVP
"Tom" <mcseman2002@.hotmail.com> wrote in message
news:%23XIEZ1VjFHA.2904@.tk2msftngp13.phx.gbl...
>I have SQL2000 server with 300 databases. I have a maintenance plan that
>performs a full backup of all databases every night.
> I have performed test restores of some of those databases but now I'm
> wondering in case that something catastrophic happens and I have to
> restore entire SQL server, how can I do that?
> Would I have to restore every database separately or is there a way to
> restore all of them in one step (some transact SQL script ...)?
> Tom
>|||Hi Tom,
This gives you an opportunity to move some of the databases to different
servers, to mitigate the risk of running all the databases on the same serve
r.
Also make sure that you are running the server on disk array's, to avoid any
disk failures.
For avoid dependency on OS, you can look at fail-over clusterting also.
--
- - - - - - - - -
Thanks
Yogish
"Tom" wrote:

> I have SQL2000 server with 300 databases. I have a maintenance plan that
> performs a full backup of all databases every night.
> I have performed test restores of some of those databases but now I'm
> wondering in case that something catastrophic happens and I have to restor
e
> entire SQL server, how can I do that?
> Would I have to restore every database separately or is there a way to
> restore all of them in one step (some transact SQL script ...)?
> Tom
>
>

Restore Problem

Hi,

I am trying to restore a point in time restore of backup. My bkp plan includes full bkp-weekly, daily diff bkp and every 2 hr TLog bkp. I would like to restore the latest bkp as on ''2007-10-18 ' with minimul data loss. Hence i follow the following seqence of restores;

--Restore Full backup
RESTORE DATABASE [ABC] FROM ABC_BackUp WITH FILE = 1, NORECOVERY
GO
--Restore Diff backup
RESTORE DATABASE ABC FROM ABC_BackUp WITH FILE = 3
--Restore TLog backup
RESTORE LOG ABC FROM ABC_BackUp WITH FILE=4, NORECOVERY, STOPAT = '2007-10-18 15:11:00'
GO

The system works well with Full bkp and Diff bkp restore but throws following error while TLog restore;

ERROR:
Server: Msg 4305, Level 16, State 1, Line 1
The log in this backup set begins at LSN 86001000000103200001, which is too late to apply to the database. An earlier log backup that includes LSN 86001000000081300001 can be restored.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.

The following is the backup information of my database:-

Position Bkp Type Bkp Size First LSN Last LSN 1 1 1923229696 86001000000001600002 86001000000005900001 2 2 7284736 85999000000278900001 86001000000081100001 3 5 534528 86001000000081100001 86001000000081300001 4 2 598016 86001000000103200001 86001000000176700001 5 2 729088 86001000000176700001 86001000000276800001 6 2 598016 86001000000276800001 86001000000353100001 7 2 663552 86001000000353100001 86001000000432900001

Pls. guide why i get this error. I don't do any shrinking between Full bkp and TLog bkps. Thanks in advance.

Please mention the timings for Full, Differential and Transaction log backups from the above sequence, it seems the error is by default as the LSN is mismatched.|||

Full backp : every sunday 7.00 am

Diff backup: daily 11.00 pm

trn backup: every 2 hrs mon - sat

|||

hello,

i just wanted to say that i have the same promblem when trying to restore poin-in-time on a different server... maybe this helps.

i can do point-in-time restores on the same server.

i believe the log sequences get mixed up on other servers.

is there a workaround?

thank you

Hugo

|||As explained it refers on the LSN matchup, also mention the restore sequence as it seems the backup database & log are not matched up when restored.|||

i was wrong... i was backing up the log to two different files... sorry. everything is working now.