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...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...asp?frame=true
> Cristian Lefter, SQL Server MVP
> "rupart" <rupart@.discussions.microsoft.com> wrote in message
> news:0F275721-28D6-4FB1-94BB-C407DD114D92@.microsoft.com...
>
>
|||> 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/inf...eral_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...asp?frame=true
> Cristian Lefter, SQL Server MVP
> "rupart" <rupart@.discussions.microsoft.com> wrote in message
> news:0F275721-28D6-4FB1-94BB-C407DD114D92@.microsoft.com...
>
>

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 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 .md
f,
i cant perform trans log backup using the BACKUP LOG statement(it gives erro
r
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)"
[url]http://msdn.microsoft.com/library/en-us/howtosql/ht_7_backpc_5a61.asp?frame=true[/
url]
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 i
f
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 n
o
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/e...me=true

> Cristian Lefter, SQL Server MVP
> "rupart" <rupart@.discussions.microsoft.com> wrote in message
> news:0F275721-28D6-4FB1-94BB-C407DD114D92@.microsoft.com...
>
>|||> 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 wil
l allow you do exactly
that. You might want to check out:
http://www.karaszi.com/SQLServer/in...veral_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 worke
d.
> 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 .m
df,
> i cant perform trans log backup using the BACKUP LOG statement(it gives er
ror
> 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.10p
m,
> 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/e...me=true

> Cristian Lefter, SQL Server MVP
> "rupart" <rupart@.discussions.microsoft.com> wrote in message
> news:0F275721-28D6-4FB1-94BB-C407DD114D92@.microsoft.com...
>
>

Restore Problem

Hi Guys:
I am restoring a databse backup from sql server 2000 cluster to another
server. The restore has been failing with message "RESTORE DATABASE is
terminating abnormally.". When I use restore verify, i get the following
message. Backup is being done on sql server 200 cluster, I am restoring on
another server which is not a cluster. Any help is greatly appreciated
because this is critical and urgent.
Server: Msg 3242, Level 16, State 2, Line 1
The file on device 'C:\temp\LancomeSIS_US_db_200603240243.BAK' is not a
valid Microsoft Tape Format backup set.
Server: Msg 3013, Level 16, State 1, Line 1
VERIFY DATABASE is terminating abnormally.
Thanks
Are you on Windows 2003?
> Hi Guys:
> I am restoring a databse backup from sql server 2000 cluster to another
> server. The restore has been failing with message "RESTORE DATABASE is
> terminating abnormally.". When I use restore verify, i get the following
> message. Backup is being done on sql server 200 cluster, I am restoring on
> another server which is not a cluster. Any help is greatly appreciated
> because this is critical and urgent.
>
> Server: Msg 3242, Level 16, State 2, Line 1
> The file on device 'C:\temp\LancomeSIS_US_db_200603240243.BAK' is not a
> valid Microsoft Tape Format backup set.
> Server: Msg 3013, Level 16, State 1, Line 1
> VERIFY DATABASE is terminating abnormally.
>
> Thanks
|||could you paste the output of restore headeronly
|||looks like your backup is corrupted, not sure of yet!
|||Thanks for replying. Below is the output of the Restore HeaderOnly. Looks
looks like db is corrupt but why. is it something to do with backups
happening on cluster (Raid drive).
BackupName
BackupDescription
BackupType ExpirationDate Compressed
Position DeviceType UserName
ServerName
DatabaseName
DatabaseVersion DatabaseCreationDate
BackupSize FirstLsn LastLsn
CheckpointLsn DifferentialBaseLsn
BackupStartDate BackupFinishDate
SortOrder CodePage UnicodeLocaleId
UnicodeComparisonStyle CompatibilityLevel SoftwareVendorId
SoftwareVersionMajor SoftwareVersionMinor SoftwareVersionBuild MachineName
Flags BindingId
RecoveryForkId Collation
------
-----------
-- --- --
-- --
------
------
------
-- ---
-- --
-- --
--- -- --
-- -- -- --
-- -- --
------
-- ---
------
*** INCOMPLETE ***
NULL
NULL NULL NULL
1 NULL NULL
NULL
NULL
NULL NULL
NULL NULL NULL
NULL NULL NULL
NULL
NULL NULL NULL NULL
NULL NULL NULL NULL
NULL NULL
NULL NULL NULL
NULL
"Shadow" wrote:

> looks like your backup is corrupted, not sure of yet!
>

Restore Problem

Hi Guys:
I am restoring a databse backup from sql server 2000 cluster to another
server. The restore has been failing with message "RESTORE DATABASE is
terminating abnormally.". When I use restore verify, i get the following
message. Backup is being done on sql server 200 cluster, I am restoring on
another server which is not a cluster. Any help is greatly appreciated
because this is critical and urgent.
Server: Msg 3242, Level 16, State 2, Line 1
The file on device 'C:\temp\LancomeSIS_US_db_200603240243.BAK' is not a
valid Microsoft Tape Format backup set.
Server: Msg 3013, Level 16, State 1, Line 1
VERIFY DATABASE is terminating abnormally.
ThanksAre you on Windows 2003?
> Hi Guys:
> I am restoring a databse backup from sql server 2000 cluster to another
> server. The restore has been failing with message "RESTORE DATABASE is
> terminating abnormally.". When I use restore verify, i get the following
> message. Backup is being done on sql server 200 cluster, I am restoring o
n
> another server which is not a cluster. Any help is greatly appreciated
> because this is critical and urgent.
>
> Server: Msg 3242, Level 16, State 2, Line 1
> The file on device 'C:\temp\LancomeSIS_US_db_200603240243.BAK' is not a
> valid Microsoft Tape Format backup set.
> Server: Msg 3013, Level 16, State 1, Line 1
> VERIFY DATABASE is terminating abnormally.
>
> Thanks|||could you paste the output of restore headeronly|||looks like your backup is corrupted, not sure of yet!|||Thanks for replying. Below is the output of the Restore HeaderOnly. Looks
looks like db is corrupt but why. is it something to do with backups
happening on cluster (Raid drive).
BackupName
BackupDescription
BackupType ExpirationDate Compressed
Position DeviceType UserName
ServerName
DatabaseName
DatabaseVersion DatabaseCreationDate
BackupSize FirstLsn LastLsn
CheckpointLsn DifferentialBaseLsn
BackupStartDate BackupFinishDate
SortOrder CodePage UnicodeLocaleId
UnicodeComparisonStyle CompatibilityLevel SoftwareVendorId
SoftwareVersionMajor SoftwareVersionMinor SoftwareVersionBuild MachineName
Flags BindingId
RecoveryForkId Collation
----
----
----
----
----
--
-- --- --
-- --
----
----
----
----
----
----
-- ---
-- --
-- --
--
---
--- -- --
-- -- -- --
-- -- --
----
----
-- ---
---
----
----
*** INCOMPLETE ***
NULL
NULL NULL NULL
1 NULL NULL
NULL
NULL
NULL NULL
NULL NULL NULL
NULL NULL NULL
NULL
NULL NULL NULL NULL
NULL NULL NULL NULL
NULL NULL
NULL NULL NULL
NULL
"Shadow" wrote:

> looks like your backup is corrupted, not sure of yet!
>

Restore Problem

Hi Guys:
I am restoring a databse backup from sql server 2000 cluster to another
server. The restore has been failing with message "RESTORE DATABASE is
terminating abnormally.". When I use restore verify, i get the following
message. Backup is being done on sql server 200 cluster, I am restoring on
another server which is not a cluster. Any help is greatly appreciated
because this is critical and urgent.
Server: Msg 3242, Level 16, State 2, Line 1
The file on device 'C:\temp\LancomeSIS_US_db_200603240243.BAK' is not a
valid Microsoft Tape Format backup set.
Server: Msg 3013, Level 16, State 1, Line 1
VERIFY DATABASE is terminating abnormally.
ThanksAre you on Windows 2003?
> Hi Guys:
> I am restoring a databse backup from sql server 2000 cluster to another
> server. The restore has been failing with message "RESTORE DATABASE is
> terminating abnormally.". When I use restore verify, i get the following
> message. Backup is being done on sql server 200 cluster, I am restoring on
> another server which is not a cluster. Any help is greatly appreciated
> because this is critical and urgent.
>
> Server: Msg 3242, Level 16, State 2, Line 1
> The file on device 'C:\temp\LancomeSIS_US_db_200603240243.BAK' is not a
> valid Microsoft Tape Format backup set.
> Server: Msg 3013, Level 16, State 1, Line 1
> VERIFY DATABASE is terminating abnormally.
>
> Thanks|||could you paste the output of restore headeronly|||looks like your backup is corrupted, not sure of yet!|||Thanks for replying. Below is the output of the Restore HeaderOnly. Looks
looks like db is corrupt but why. is it something to do with backups
happening on cluster (Raid drive).
BackupName
BackupDescription
BackupType ExpirationDate Compressed
Position DeviceType UserName
ServerName
DatabaseName
DatabaseVersion DatabaseCreationDate
BackupSize FirstLsn LastLsn
CheckpointLsn DifferentialBaseLsn
BackupStartDate BackupFinishDate
SortOrder CodePage UnicodeLocaleId
UnicodeComparisonStyle CompatibilityLevel SoftwareVendorId
SoftwareVersionMajor SoftwareVersionMinor SoftwareVersionBuild MachineName
Flags BindingId
RecoveryForkId Collation
------
-----------
-- --- --
-- --
------
------
------
-- ---
-- --
-- --
--
---
--- -- --
-- -- -- --
-- -- --
------
-- ---
---
------
*** INCOMPLETE ***
NULL
NULL NULL NULL
1 NULL NULL
NULL
NULL
NULL NULL
NULL NULL NULL
NULL NULL NULL
NULL
NULL NULL NULL NULL
NULL NULL NULL NULL
NULL NULL
NULL NULL NULL
NULL
"Shadow" wrote:
> looks like your backup is corrupted, not sure of yet!
>

Restore of Model databse

How can I restore my Model Database on SQL 2005 ?Check this out:
http://msdn2.microsoft.com/en-us/library/ms190749.aspx
--
Ekrem Önsoy
<msnews.microsoft.com> wrote in message
news:ej8xuWLIIHA.1208@.TK2MSFTNGP03.phx.gbl...
> How can I restore my Model Database on SQL 2005 ?
>|||Just do a restore like any other database.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"msnews.microsoft.com" wrote:
> How can I restore my Model Database on SQL 2005 ?
>
>