Do sequences of transactions logs are broken when a restore occur ?

Hi,
Do sequences of transactions logs are broken when a restore occur ?
Thank you
danny> Do sequences of transactions logs are broken when a restore occur ?
I am not sure if I understand the question. When you do the restore db and
log over an existing database, you overwrite the data files and the log
files. Sequence is constant and not broken in the restored database, but of
course in the original one you could have had different LSN. It is
overwritten, so why care?
--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org|||Thank you Dejan.
<<So why care ?>>
I am going to expose to you a fictive situation to explain why I asked th=is
question:
This is not a problem that encountered. This is a theorical question
Suppose this
> >
> >Sat. June 07 FULL database Backup
> >Sun. June 08 Transaction log backup
> >Mon. June 09 Transaction log backup
> >Tues. June 10 Transaction log backup
> >Wed. June 11 Transaction log backup
> >Thu. June 12 Transaction log backup
> >Fri. June 13 Transaction log backup
> >
> >Sat. June 14 FULL database Backup
> >Sun. June 15 Transaction log backup
> >Mon. June 16 Transaction log backup
> >Tues. June 17 Transaction log backup
> >Wed. June 18 Transaction log backup
> >Thu. June 19 Transaction log backup
> >Fri. June 20 Transaction log backup
> >
> >Sat. June 21 Full Database Backup
> >Sat June 21 The first Restore from June 14 has been executed (with=out
losing data)
> >Sat. June 21 Full Database Backup after the restore
> >Sun. june 22 Transaction log backup
> >Mon. june 23 Transaction log backup
> >Tues. June 24 Transaction log backup
> >Wed. June 25 Transaction log backup
> >Thu. June 27 Transaction log backup
> >Fri. June 27 Transaction log backup
> >
> >Sat June 28 Full Database Backup
> >Sat June 28 Does another Restore from June 07 is possible witout l=osing
data ?
On June 21, suppose the DBA is not at the office (vacancy) and the
operator realize at 10:00 that a table is corrupted. Suppose the
operator don't know if the table was OK when the last FULL backup
occured on June 21 (at 01:00). Suppose the operator decide to restore
the Full Database from June 14 and all transactions log sequentially
until now (June 21) (without losing data). Suppose the table was also cor=rupted
on June 14. Suppose the operator forgot to execute DBCC CheckDB after the=
Restor to check the table.
On June 28, one week later, suppose the DBA is back to the office and he
realize that a table is corrupted (the same table). The DBA knows that
the table has been corrupted on June 08. The DBA also know that the
operator has restored, one week ago, the database from June 14.
The DBA don't want to lose data.
Question 1:
On June 28, does the DBA can still restore FULL backup from June 07 and r=estore
all transactions log (sequentially) from june 07 untill now without losin=g data
?
Question 2
Do the sequences of transactions logs are going to be OK from June 7 to =June
28 even though the operator has restored the database (from June 14) on J=une 21
? In others words, do sequences of transactions logs has been broken on J=une 21
when the operator executed the first restore ?
Thank you
Danny
Dejan Sarka a =E9crit :
> > Do sequences of transactions logs are broken when a restore occur ?
> I am not sure if I understand the question. When you do the restore db =and
> log over an existing database, you overwrite the data files and the log=
> files. Sequence is constant and not broken in the restored database, bu=t of
> course in the original one you could have had different LSN. It is
> overwritten, so why care?
> --
> Dejan Sarka, SQL Server MVP
> FAQ from Neil & others at: http://www.sqlserverfaq.com
> Please reply only to the newsgroups.
> PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org|||Danny, now I see what you mean. When you issue the recovery process (i.e.
put the database in operational mode after last restore), SQL Server
restarts LSN (Log Sequence Number) from different number from the last
backup. Thus your log backups from
Fri. June 20 Transaction log backup
and
Sun. june 22 Transaction log backup
are not connected anymore- you have a hole in LSN's. So it is not possible
to restore the 2nd log backup mentioned after the 1st mentioned.
--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Danny Presse" <danny.presse@.sympatico.ca> wrote in message
news:3F0037BF.200A1DBD@.sympatico.ca...
Thank you Dejan.
<<So why care ?>>
I am going to expose to you a fictive situation to explain why I asked this
question:
This is not a problem that encountered. This is a theorical question
Suppose this
> >
> >Sat. June 07 FULL database Backup
> >Sun. June 08 Transaction log backup
> >Mon. June 09 Transaction log backup
> >Tues. June 10 Transaction log backup
> >Wed. June 11 Transaction log backup
> >Thu. June 12 Transaction log backup
> >Fri. June 13 Transaction log backup
> >
> >Sat. June 14 FULL database Backup
> >Sun. June 15 Transaction log backup
> >Mon. June 16 Transaction log backup
> >Tues. June 17 Transaction log backup
> >Wed. June 18 Transaction log backup
> >Thu. June 19 Transaction log backup
> >Fri. June 20 Transaction log backup
> >
> >Sat. June 21 Full Database Backup
> >Sat June 21 The first Restore from June 14 has been executed (without
losing data)
> >Sat. June 21 Full Database Backup after the restore
> >Sun. june 22 Transaction log backup
> >Mon. june 23 Transaction log backup
> >Tues. June 24 Transaction log backup
> >Wed. June 25 Transaction log backup
> >Thu. June 27 Transaction log backup
> >Fri. June 27 Transaction log backup
> >
> >Sat June 28 Full Database Backup
> >Sat June 28 Does another Restore from June 07 is possible witout
losing
data ?
On June 21, suppose the DBA is not at the office (vacancy) and the
operator realize at 10:00 that a table is corrupted. Suppose the
operator don't know if the table was OK when the last FULL backup
occured on June 21 (at 01:00). Suppose the operator decide to restore
the Full Database from June 14 and all transactions log sequentially
until now (June 21) (without losing data). Suppose the table was also
corrupted
on June 14. Suppose the operator forgot to execute DBCC CheckDB after the
Restor to check the table.
On June 28, one week later, suppose the DBA is back to the office and he
realize that a table is corrupted (the same table). The DBA knows that
the table has been corrupted on June 08. The DBA also know that the
operator has restored, one week ago, the database from June 14.
The DBA don't want to lose data.
Question 1:
On June 28, does the DBA can still restore FULL backup from June 07 and
restore
all transactions log (sequentially) from june 07 untill now without losing
data
?
Question 2
Do the sequences of transactions logs are going to be OK from June 7 to
June
28 even though the operator has restored the database (from June 14) on June
21
? In others words, do sequences of transactions logs has been broken on June
21
when the operator executed the first restore ?
Thank you
Danny
Dejan Sarka a écrit :
> > Do sequences of transactions logs are broken when a restore occur ?
> I am not sure if I understand the question. When you do the restore db and
> log over an existing database, you overwrite the data files and the log
> files. Sequence is constant and not broken in the restored database, but
of
> course in the original one you could have had different LSN. It is
> overwritten, so why care?
> --
> Dejan Sarka, SQL Server MVP
> FAQ from Neil & others at: http://www.sqlserverfaq.com
> Please reply only to the newsgroups.
> PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.orgsql

Do I need to start the service..

Hi,
I am providing a UI in my application (which uses MSDE) for the user to back
up/restore the application database. Today I tried to test it and it didn't
work complaining that sql server does not exist. I checked and saw that I
had not started the MSSQL$M
yApp service.. when I started it, the backup and restore started working fin
e. So does that mean that I have to programatically start the service in my
app before backup/restore. Generally my application does not need to start
the service while talking
to the database.
ThanksIf you set the service to automatic, it will start when the computer
starts. There is always a possibility that it will get stopped manually. So
you could check to see if it is started in your app and if not start it.
Rand
This posting is provided "as is" with no warranties and confers no rights.

Do I need to start the service..

Hi,
I am providing a UI in my application (which uses MSDE) for the user to backup/restore the application database. Today I tried to test it and it didn't work complaining that sql server does not exist. I checked and saw that I had not started the MSSQL$M
yApp service.. when I started it, the backup and restore started working fine. So does that mean that I have to programatically start the service in my app before backup/restore. Generally my application does not need to start the service while talking
to the database.
Thanks
If you set the service to automatic, it will start when the computer
starts. There is always a possibility that it will get stopped manually. So
you could check to see if it is started in your app and if not start it.
Rand
This posting is provided "as is" with no warranties and confers no rights.

do 2005 backups restore into 2000?

Can sql server 2005 database backups restore to sql server 2000 (assuming no
sql server 2005 specific elements in are the database, such as clr stuff or
new transaction sql features)?
Thank you
No. You would have to export the data from 2005 and import it into 2000
table by table.
Andrew J. Kelly SQL MVP
"SandpointGuy" <SandpointGuy@.discussions.microsoft.com> wrote in message
news:36D3BFC2-32F8-48C4-94FF-BE5E15CF4399@.microsoft.com...
> Can sql server 2005 database backups restore to sql server 2000 (assuming
> no
> sql server 2005 specific elements in are the database, such as clr stuff
> or
> new transaction sql features)?
> Thank you

do 2005 backups restore into 2000?

Can sql server 2005 database backups restore to sql server 2000 (assuming no
sql server 2005 specific elements in are the database, such as clr stuff or
new transaction sql features)?
Thank you"SandpointGuy" <SandpointGuy@.discussions.microsoft.com> wrote in message
news:36D3BFC2-32F8-48C4-94FF-BE5E15CF4399@.microsoft.com...
> Can sql server 2005 database backups restore to sql server 2000 (assuming
> no
> sql server 2005 specific elements in are the database, such as clr stuff
> or
> new transaction sql features)?
> Thank you
No. Instead you could copy database objects between the serves using SSIS
for example, or scripting and BCP.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||No. You would have to export the data from 2005 and import it into 2000
table by table.
Andrew J. Kelly SQL MVP
"SandpointGuy" <SandpointGuy@.discussions.microsoft.com> wrote in message
news:36D3BFC2-32F8-48C4-94FF-BE5E15CF4399@.microsoft.com...
> Can sql server 2005 database backups restore to sql server 2000 (assuming
> no
> sql server 2005 specific elements in are the database, such as clr stuff
> or
> new transaction sql features)?
> Thank yousql

do 2005 backups restore into 2000?

Can sql server 2005 database backups restore to sql server 2000 (assuming no
sql server 2005 specific elements in are the database, such as clr stuff or
new transaction sql features)?
Thank you"SandpointGuy" <SandpointGuy@.discussions.microsoft.com> wrote in message
news:36D3BFC2-32F8-48C4-94FF-BE5E15CF4399@.microsoft.com...
> Can sql server 2005 database backups restore to sql server 2000 (assuming
> no
> sql server 2005 specific elements in are the database, such as clr stuff
> or
> new transaction sql features)?
> Thank you
No. Instead you could copy database objects between the serves using SSIS
for example, or scripting and BCP.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||No. You would have to export the data from 2005 and import it into 2000
table by table.
--
Andrew J. Kelly SQL MVP
"SandpointGuy" <SandpointGuy@.discussions.microsoft.com> wrote in message
news:36D3BFC2-32F8-48C4-94FF-BE5E15CF4399@.microsoft.com...
> Can sql server 2005 database backups restore to sql server 2000 (assuming
> no
> sql server 2005 specific elements in are the database, such as clr stuff
> or
> new transaction sql features)?
> Thank you

DMO restore problem

I have a C++ app that is using SQLDMO with SqlServer2000. I am Restoring a database using only the .bak file, moving the .mdf and .ldf files. The following is the code:

#ifdef USE_SQLDMO
try
{
if(m_cpServer == NULL)
return E_FAIL;

_RestorePtr cpRestore;
CheckError(cpRestore.CreateInstance(_T("SQLDMO.Restore")));
cpRestore->Action = SQLDMORestore_Database;
cpRestore->Database = _bstr_t(sDbName);
cpRestore->Files = _bstr_t(sBUFile);
cpRestore->LastRestore = false;
cpRestore->ReplaceDatabase = true;
cpRestore->PercentCompleteNotification= 10;
RestoreSinkPtr cpSinkPtr;
//get data path for current server
GetSQLDataPath(sDataPath);
QueryResultsPtr cpFileList;
cpFileList = cpRestore->ReadFileList( m_cpServer);
//get info to change for move from cpFileList and move items
for(int i = 1; i <= cpFileList->Rows; i++)
{
_bstr_t bstrLogicalName = cpFileList->GetColumnString(i,1);
sLogicalName = (LPCTSTR)bstrLogicalName;
sLogicalName.Trim();
_bstr_t bstrPhysicalName = cpFileList->GetColumnString(i,2);
sPhysicalName = (LPCTSTR)bstrPhysicalName;
sPhysicalName.MakeUpper();
if(sPhysicalName.Find(".MDF") > 0)
{
sPhysicalName = sDataPath + "\\" + sDbName;
sFiles.Format("%i",nDatafiles);
if(nDatafiles == 0)
sPhysicalName += ".mdf";
else
sPhysicalName += "_" + sFiles + ".mdf";

nDatafiles++;
}
else if(sPhysicalName.Find(".LDF") > 0)
{
sFiles.Format("%i",nLogFiles);
sPhysicalName = sDataPath + "\\" + sDbName;
if(nLogFiles == 0)
sPhysicalName += ".ldf";
else
sPhysicalName += "_" + sFiles + ".ldf";

nLogFiles++;
}
sNew += "[" + sLogicalName + "],[" + sPhysicalName + "],";
}
sNew = sNew.Left(sNew.GetLength()-1);
sPhysicalName = (LPCTSTR)bstrPhysicalName;

cpRestore->SQLRestore( m_cpServer);
cpRestore->SQLVerify(m_cpServer);
cpRestore = NULL;
bSuccess = true;
}
catch(_com_error& err)
{
PrintComError(err);
PrintProviderError(m_cpServer);
hr = err.Error();
}
#endif

The problem is that the functions appears to work. The Restore command does not return and error and neither does the verify. When I open enterprise manager, the database created but is greyed out and shows a status of loading but never completes.
Any ideas/help would be greatly appreciated.

Sandy

I haven't tried your script, but I suspect that the LastRestore=false is the culprit, as this indicates that this is not the last backup on the restore chain and keeps the database in a loading state as it is expecting a final log backup will be applied.

DMO restore problem

I have a C++ app that is using SQLDMO with SqlServer2000. I am Restoring a database using only the .bak file, moving the .mdf and .ldf files. The following is the code:

#ifdef USE_SQLDMO
try
{
if(m_cpServer == NULL)
return E_FAIL;
_RestorePtr cpRestore;
CheckError(cpRestore.CreateInstance(_T("SQLDMO.Restore")));
cpRestore->Action = SQLDMORestore_Database;
cpRestore->Database = _bstr_t(sDbName);
cpRestore->Files = _bstr_t(sBUFile);
cpRestore->LastRestore = false;
cpRestore->ReplaceDatabase = true;
cpRestore->PercentCompleteNotification= 10;
RestoreSinkPtr cpSinkPtr;
//get data path for current server
GetSQLDataPath(sDataPath);
QueryResultsPtr cpFileList;
cpFileList = cpRestore->ReadFileList( m_cpServer);
//get info to change for move from cpFileList and move items
for(int i = 1; i <= cpFileList->Rows; i++)
{
_bstr_t bstrLogicalName = cpFileList->GetColumnString(i,1);
sLogicalName = (LPCTSTR)bstrLogicalName;
sLogicalName.Trim();
_bstr_t bstrPhysicalName = cpFileList->GetColumnString(i,2);
sPhysicalName = (LPCTSTR)bstrPhysicalName;
sPhysicalName.MakeUpper();
if(sPhysicalName.Find(".MDF") > 0)
{
sPhysicalName = sDataPath + "\\" + sDbName;
sFiles.Format("%i",nDatafiles);
if(nDatafiles == 0)
sPhysicalName += ".mdf";
else
sPhysicalName += "_" + sFiles + ".mdf";

nDatafiles++;
}
else if(sPhysicalName.Find(".LDF") > 0)
{
sFiles.Format("%i",nLogFiles);
sPhysicalName = sDataPath + "\\" + sDbName;
if(nLogFiles == 0)
sPhysicalName += ".ldf";
else
sPhysicalName += "_" + sFiles + ".ldf";

nLogFiles++;
}
sNew += "[" + sLogicalName + "],[" + sPhysicalName + "],";
}
sNew = sNew.Left(sNew.GetLength()-1);
sPhysicalName = (LPCTSTR)bstrPhysicalName;
cpRestore->SQLRestore( m_cpServer);
cpRestore->SQLVerify(m_cpServer);
cpRestore = NULL;
bSuccess = true;
}
catch(_com_error& err)
{
PrintComError(err);
PrintProviderError(m_cpServer);
hr = err.Error();
}
#endif

The problem is that the functions appears to work. The Restore command does not return and error and neither does the verify. When I open enterprise manager, the database created but is greyed out and shows a status of loading but never completes.
Any ideas/help would be greatly appreciated.

Sandy

I haven't tried your script, but I suspect that the LastRestore=false is the culprit, as this indicates that this is not the last backup on the restore chain and keeps the database in a loading state as it is expecting a final log backup will be applied.

Distribution server restore on standby server

Hi,
we have developed an application using transactional replication, with
several publication servers (push) and a distribution server (which is
the only subscriber too). It seems to work fine.
Now we would "protect" the distribution/subscriber server by using a
standby server (note: only the distribution/subscriver server must be
protected, not publication servers) which should replace the working
server in the case it crashes.
Could someone suggest us the best strategy to do this? Thanks in
advance...
Marco
You can manually set this up. Have a look at Strategies for Backing Up and
Restoring Transactional Replication in BOL.
The problem is that this adds to the latency. Transactions remain in your
tlog until the log is dumped. Then they are read from the tlog and written
to the distirbution database.
"Marco69" <marcosindona@.virgilio.it> wrote in message
news:ce7beb14.0403250541.5f374a8f@.posting.google.c om...
> Hi,
> we have developed an application using transactional replication, with
> several publication servers (push) and a distribution server (which is
> the only subscriber too). It seems to work fine.
> Now we would "protect" the distribution/subscriber server by using a
> standby server (note: only the distribution/subscriver server must be
> protected, not publication servers) which should replace the working
> server in the case it crashes.
> Could someone suggest us the best strategy to do this? Thanks in
> advance...
> Marco

distributing subscriber database on multiple machine - install

Hi,

Is it possible to create a 'master' subscriber, back-up the database with the replication triggers and subscription intact, then restore this subscriber database on multiple subscribers during an install? The subscription is for an anonymous web-synced publication, up to 40 subscribers, and I am trying to avoid the initial snapshot download.

Thanks,

Darrell Young
Hi Darrell,

Yes, it is possible to avoid the initial snapshot downlad on your subscribers. An alternative maybe, to initialize snapshot from backup. More information can be found in book online.

Initializing a Merge Subscription Without a Snapshot
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rpldata9/html/ee16af24-d7e2-4b65-a25f-dc89caba2ea2.htm

Initializing a Transactional Subscription Without a Snapshot
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rpldata9/html/75c8c1f8-60bc-44a8-944b-d18d1f6bda11.htm

Regards,

Gary Chen|||Gary,

Thanks for the reply. The issue is have is that the subscribers will only have a subset of the data and the schema of the published database, so wholly backing up and restoring the published database is not practical. I had tried taking a subscriber database, then removing the subscription and creating a backup from that - the issue is that the rowguids are removed from the tables. I suppose the choice I have left is to create a backup of the published database, restore on a subscriber, manually remove the data and schema I am not including in the publication, then create a backup of that.
Thanks,

Darrell Young
|||

Hi Darrell,

Try the following,

- Create a snapshot at the publisher

- Download the initial snapshot to one subscriber S1

- Back up the subscriber database at S1

- Before you restore the back up database to other subscriber S2, drop the subscription that you have created before at subscriber S2

- Restore the S1 backup to S2 with KEEP_REPLICATION off (I believe by default it is off)

- Re-create the subcription with no-sync option.

You probably want to make sure that no data get updated to your publisher or subscriber database while you perform this backup-restore.

Try it on one or two machines to see if this work before you restore to all 40 machines.

Regards,

Gary Chen