Restore Question

I am trying the restore process.
Here is the test case that i am performing:
1) Full backup Nwind Databse at 5pm
2) Add some tables and after that did transaction log backup at 5.10pm
Try to initiate failure by renaming the .mdf.
Restored full with NORECOVERY followed by trans log with RECOVER. it worked.
My question is, after 5.10pm, i did changes without saving and renamed the
.mdf again. I wanted to try A Point in Time backup. After i renamed the .mdf,
i cant perform trans log backup using the BACKUP LOG statement(it gives error
that the database is not online. But, hey, i just renamed the .mdf and the
.ldy is perfectly alright). From my understanding, the changes,after 5.10pm,
are recorded in trans log and if i can save that, i can get all the data
back. So, how do i do that?Or did i overlook sth?
TIA"How to restore to the point of failure (Transact-SQL)"
http://msdn.microsoft.com/library/en-us/howtosql/ht_7_backpc_5a61.asp?frame=true
Cristian Lefter, SQL Server MVP
"rupart" <rupart@.discussions.microsoft.com> wrote in message
news:0F275721-28D6-4FB1-94BB-C407DD114D92@.microsoft.com...
>I am trying the restore process.
> Here is the test case that i am performing:
> 1) Full backup Nwind Databse at 5pm
> 2) Add some tables and after that did transaction log backup at 5.10pm
> Try to initiate failure by renaming the .mdf.
> Restored full with NORECOVERY followed by trans log with RECOVER. it
> worked.
> My question is, after 5.10pm, i did changes without saving and renamed the
> .mdf again. I wanted to try A Point in Time backup. After i renamed the
> .mdf,
> i cant perform trans log backup using the BACKUP LOG statement(it gives
> error
> that the database is not online. But, hey, i just renamed the .mdf and the
> .ldy is perfectly alright). From my understanding, the changes,after
> 5.10pm,
> are recorded in trans log and if i can save that, i can get all the data
> back. So, how do i do that?Or did i overlook sth?
> TIA|||Rupart
You can only restore from a backup of the transaction log. You can not
restore direct from the transaction log.
In a real situation where you are going to have to either go to your DR
solution (If you have one) or do a restore, it's always a good idea to see if
you can perform a final transaction log backup before proceding. In your
case, renaming the .mdf, you won't be able to, but sometimes you can. This
will allow you to get as up to date as possible. In your scenario there is no
way for you to restore the database to any point after 5:10pm.
Regards
John
"Cristian Lefter" wrote:
> "How to restore to the point of failure (Transact-SQL)"
> http://msdn.microsoft.com/library/en-us/howtosql/ht_7_backpc_5a61.asp?frame=true
> Cristian Lefter, SQL Server MVP
> "rupart" <rupart@.discussions.microsoft.com> wrote in message
> news:0F275721-28D6-4FB1-94BB-C407DD114D92@.microsoft.com...
> >I am trying the restore process.
> > Here is the test case that i am performing:
> > 1) Full backup Nwind Databse at 5pm
> > 2) Add some tables and after that did transaction log backup at 5.10pm
> > Try to initiate failure by renaming the .mdf.
> > Restored full with NORECOVERY followed by trans log with RECOVER. it
> > worked.
> >
> > My question is, after 5.10pm, i did changes without saving and renamed the
> > .mdf again. I wanted to try A Point in Time backup. After i renamed the
> > .mdf,
> > i cant perform trans log backup using the BACKUP LOG statement(it gives
> > error
> > that the database is not online. But, hey, i just renamed the .mdf and the
> > .ldy is perfectly alright). From my understanding, the changes,after
> > 5.10pm,
> > are recorded in trans log and if i can save that, i can get all the data
> > back. So, how do i do that?Or did i overlook sth?
> >
> > TIA
>
>|||> After i renamed the .mdf,
> i cant perform trans log backup using the BACKUP LOG
This is what the NO_TRUNCATE option for the BAKUP LOG command is for. It will allow you do exactly
that. You might want to check out:
http://www.karaszi.com/SQLServer/info_restore_log_several_times.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"rupart" <rupart@.discussions.microsoft.com> wrote in message
news:0F275721-28D6-4FB1-94BB-C407DD114D92@.microsoft.com...
>I am trying the restore process.
> Here is the test case that i am performing:
> 1) Full backup Nwind Databse at 5pm
> 2) Add some tables and after that did transaction log backup at 5.10pm
> Try to initiate failure by renaming the .mdf.
> Restored full with NORECOVERY followed by trans log with RECOVER. it worked.
> My question is, after 5.10pm, i did changes without saving and renamed the
> .mdf again. I wanted to try A Point in Time backup. After i renamed the .mdf,
> i cant perform trans log backup using the BACKUP LOG statement(it gives error
> that the database is not online. But, hey, i just renamed the .mdf and the
> .ldy is perfectly alright). From my understanding, the changes,after 5.10pm,
> are recorded in trans log and if i can save that, i can get all the data
> back. So, how do i do that?Or did i overlook sth?
> TIA|||cool..
thx guys..let me check it out
"Cristian Lefter" wrote:
> "How to restore to the point of failure (Transact-SQL)"
> http://msdn.microsoft.com/library/en-us/howtosql/ht_7_backpc_5a61.asp?frame=true
> Cristian Lefter, SQL Server MVP
> "rupart" <rupart@.discussions.microsoft.com> wrote in message
> news:0F275721-28D6-4FB1-94BB-C407DD114D92@.microsoft.com...
> >I am trying the restore process.
> > Here is the test case that i am performing:
> > 1) Full backup Nwind Databse at 5pm
> > 2) Add some tables and after that did transaction log backup at 5.10pm
> > Try to initiate failure by renaming the .mdf.
> > Restored full with NORECOVERY followed by trans log with RECOVER. it
> > worked.
> >
> > My question is, after 5.10pm, i did changes without saving and renamed the
> > .mdf again. I wanted to try A Point in Time backup. After i renamed the
> > .mdf,
> > i cant perform trans log backup using the BACKUP LOG statement(it gives
> > error
> > that the database is not online. But, hey, i just renamed the .mdf and the
> > .ldy is perfectly alright). From my understanding, the changes,after
> > 5.10pm,
> > are recorded in trans log and if i can save that, i can get all the data
> > back. So, how do i do that?Or did i overlook sth?
> >
> > TIA
>
>

Restore MDF problem

typical "user didn't backup database before performing transactions" case. (note: below I have renamed my mdf file as my_data.mdf for privacy purposes)

I'm trying to attach an mdf that I pulled off my server into my local database in SQL 2005 but get this error during the attach process:

TITLE: Microsoft SQL Server Management Studio

Attach database failed for Server 'BG-PC43'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:1022464; actual 0:0). It occurred during a read of page (1:1022464) in database ID 9 at offset 0x000001f3400000 in file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\my_Data.MDF'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
Could not open new database 'RMTEST'. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 824)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=824&LinkId=20476

BUTTONS:

OK

looks like that database is corrupted. You should restore from an earlier/different backup.

Restore MDF problem

typical "user didn't backup database before performing transactions" case. (note: below I have renamed my mdf file as my_data.mdf for privacy purposes)

I'm trying to attach an mdf that I pulled off my server into my local database in SQL 2005 but get this error during the attach process:

TITLE: Microsoft SQL Server Management Studio

Attach database failed for Server 'BG-PC43'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:1022464; actual 0:0). It occurred during a read of page (1:1022464) in database ID 9 at offset 0x000001f3400000 in file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\my_Data.MDF'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
Could not open new database 'RMTEST'. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 824)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=824&LinkId=20476

BUTTONS:

OK

looks like that database is corrupted. You should restore from an earlier/different backup.