I use Enterprise Manager to backup and restore various SQL Server databases
moving them around on my different dev boxes. I have a case now where a
database is on drive d on one system and it's on drive c on another system.
It won't let me restore to drive C - says I need to use the With Move
option.
I don't see a With Move option in enterprise manager and I don't want to
move a file anyway, I just want to restore it.
Thanks,
T
Technically, the "With Move" option is how SQL implements changing the
restore location of a database. Even if you use Enterprise Mangler to set
up the restores, it is a good idea to read about the BACKUP and RESTORE
commands in BOL (Books On-Line), just so you know what is happening. In
this case, it would have made the error message much clearer.
To use the WITH MOVE option in EM, choose the backup file you want to
restore and any other options that need setting on the General tab. Then
edit the RESTORE AS fields on the Options tab of the restore database popup
to point to the desired file locations.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Tina" <tinamseaburn@.nospammeexcite.com> wrote in message
news:evZhdzLrFHA.1168@.TK2MSFTNGP10.phx.gbl...
>I use Enterprise Manager to backup and restore various SQL Server databases
>moving them around on my different dev boxes. I have a case now where a
>database is on drive d on one system and it's on drive c on another system.
>It won't let me restore to drive C - says I need to use the With Move
>option.
> I don't see a With Move option in enterprise manager and I don't want to
> move a file anyway, I just want to restore it.
> Thanks,
> T
>
|||Tina,
When the restore dialog box appears, go to the "options tab" an change
"Restore As" for each file. If they are pointing to "D" and the server does
not have "D" drive or the specified folder does not exosts in the "D" drive,
then you have to change this arguments.
AMB
"Tina" wrote:
> I use Enterprise Manager to backup and restore various SQL Server databases
> moving them around on my different dev boxes. I have a case now where a
> database is on drive d on one system and it's on drive c on another system.
> It won't let me restore to drive C - says I need to use the With Move
> option.
> I don't see a With Move option in enterprise manager and I don't want to
> move a file anyway, I just want to restore it.
> Thanks,
> T
>
>
sql
Showing posts with label case. Show all posts
Showing posts with label case. Show all posts
Monday, March 26, 2012
Restore to different drive
I use Enterprise Manager to backup and restore various SQL Server databases
moving them around on my different dev boxes. I have a case now where a
database is on drive d on one system and it's on drive c on another system.
It won't let me restore to drive C - says I need to use the With Move
option.
I don't see a With Move option in enterprise manager and I don't want to
move a file anyway, I just want to restore it.
Thanks,
TTechnically, the "With Move" option is how SQL implements changing the
restore location of a database. Even if you use Enterprise Mangler to set
up the restores, it is a good idea to read about the BACKUP and RESTORE
commands in BOL (Books On-Line), just so you know what is happening. In
this case, it would have made the error message much clearer.
To use the WITH MOVE option in EM, choose the backup file you want to
restore and any other options that need setting on the General tab. Then
edit the RESTORE AS fields on the Options tab of the restore database popup
to point to the desired file locations.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Tina" <tinamseaburn@.nospammeexcite.com> wrote in message
news:evZhdzLrFHA.1168@.TK2MSFTNGP10.phx.gbl...
>I use Enterprise Manager to backup and restore various SQL Server databases
>moving them around on my different dev boxes. I have a case now where a
>database is on drive d on one system and it's on drive c on another system.
>It won't let me restore to drive C - says I need to use the With Move
>option.
> I don't see a With Move option in enterprise manager and I don't want to
> move a file anyway, I just want to restore it.
> Thanks,
> T
>|||Tina,
When the restore dialog box appears, go to the "options tab" an change
"Restore As" for each file. If they are pointing to "D" and the server does
not have "D" drive or the specified folder does not exosts in the "D" drive,
then you have to change this arguments.
AMB
"Tina" wrote:
> I use Enterprise Manager to backup and restore various SQL Server database
s
> moving them around on my different dev boxes. I have a case now where a
> database is on drive d on one system and it's on drive c on another system
.
> It won't let me restore to drive C - says I need to use the With Move
> option.
> I don't see a With Move option in enterprise manager and I don't want to
> move a file anyway, I just want to restore it.
> Thanks,
> T
>
>
moving them around on my different dev boxes. I have a case now where a
database is on drive d on one system and it's on drive c on another system.
It won't let me restore to drive C - says I need to use the With Move
option.
I don't see a With Move option in enterprise manager and I don't want to
move a file anyway, I just want to restore it.
Thanks,
TTechnically, the "With Move" option is how SQL implements changing the
restore location of a database. Even if you use Enterprise Mangler to set
up the restores, it is a good idea to read about the BACKUP and RESTORE
commands in BOL (Books On-Line), just so you know what is happening. In
this case, it would have made the error message much clearer.
To use the WITH MOVE option in EM, choose the backup file you want to
restore and any other options that need setting on the General tab. Then
edit the RESTORE AS fields on the Options tab of the restore database popup
to point to the desired file locations.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Tina" <tinamseaburn@.nospammeexcite.com> wrote in message
news:evZhdzLrFHA.1168@.TK2MSFTNGP10.phx.gbl...
>I use Enterprise Manager to backup and restore various SQL Server databases
>moving them around on my different dev boxes. I have a case now where a
>database is on drive d on one system and it's on drive c on another system.
>It won't let me restore to drive C - says I need to use the With Move
>option.
> I don't see a With Move option in enterprise manager and I don't want to
> move a file anyway, I just want to restore it.
> Thanks,
> T
>|||Tina,
When the restore dialog box appears, go to the "options tab" an change
"Restore As" for each file. If they are pointing to "D" and the server does
not have "D" drive or the specified folder does not exosts in the "D" drive,
then you have to change this arguments.
AMB
"Tina" wrote:
> I use Enterprise Manager to backup and restore various SQL Server database
s
> moving them around on my different dev boxes. I have a case now where a
> database is on drive d on one system and it's on drive c on another system
.
> It won't let me restore to drive C - says I need to use the With Move
> option.
> I don't see a With Move option in enterprise manager and I don't want to
> move a file anyway, I just want to restore it.
> Thanks,
> T
>
>
Restore to different drive
I use Enterprise Manager to backup and restore various SQL Server databases
moving them around on my different dev boxes. I have a case now where a
database is on drive d on one system and it's on drive c on another system.
It won't let me restore to drive C - says I need to use the With Move
option.
I don't see a With Move option in enterprise manager and I don't want to
move a file anyway, I just want to restore it.
Thanks,
TTechnically, the "With Move" option is how SQL implements changing the
restore location of a database. Even if you use Enterprise Mangler to set
up the restores, it is a good idea to read about the BACKUP and RESTORE
commands in BOL (Books On-Line), just so you know what is happening. In
this case, it would have made the error message much clearer.
To use the WITH MOVE option in EM, choose the backup file you want to
restore and any other options that need setting on the General tab. Then
edit the RESTORE AS fields on the Options tab of the restore database popup
to point to the desired file locations.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Tina" <tinamseaburn@.nospammeexcite.com> wrote in message
news:evZhdzLrFHA.1168@.TK2MSFTNGP10.phx.gbl...
>I use Enterprise Manager to backup and restore various SQL Server databases
>moving them around on my different dev boxes. I have a case now where a
>database is on drive d on one system and it's on drive c on another system.
>It won't let me restore to drive C - says I need to use the With Move
>option.
> I don't see a With Move option in enterprise manager and I don't want to
> move a file anyway, I just want to restore it.
> Thanks,
> T
>|||Tina,
When the restore dialog box appears, go to the "options tab" an change
"Restore As" for each file. If they are pointing to "D" and the server does
not have "D" drive or the specified folder does not exosts in the "D" drive,
then you have to change this arguments.
AMB
"Tina" wrote:
> I use Enterprise Manager to backup and restore various SQL Server databases
> moving them around on my different dev boxes. I have a case now where a
> database is on drive d on one system and it's on drive c on another system.
> It won't let me restore to drive C - says I need to use the With Move
> option.
> I don't see a With Move option in enterprise manager and I don't want to
> move a file anyway, I just want to restore it.
> Thanks,
> T
>
>
moving them around on my different dev boxes. I have a case now where a
database is on drive d on one system and it's on drive c on another system.
It won't let me restore to drive C - says I need to use the With Move
option.
I don't see a With Move option in enterprise manager and I don't want to
move a file anyway, I just want to restore it.
Thanks,
TTechnically, the "With Move" option is how SQL implements changing the
restore location of a database. Even if you use Enterprise Mangler to set
up the restores, it is a good idea to read about the BACKUP and RESTORE
commands in BOL (Books On-Line), just so you know what is happening. In
this case, it would have made the error message much clearer.
To use the WITH MOVE option in EM, choose the backup file you want to
restore and any other options that need setting on the General tab. Then
edit the RESTORE AS fields on the Options tab of the restore database popup
to point to the desired file locations.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Tina" <tinamseaburn@.nospammeexcite.com> wrote in message
news:evZhdzLrFHA.1168@.TK2MSFTNGP10.phx.gbl...
>I use Enterprise Manager to backup and restore various SQL Server databases
>moving them around on my different dev boxes. I have a case now where a
>database is on drive d on one system and it's on drive c on another system.
>It won't let me restore to drive C - says I need to use the With Move
>option.
> I don't see a With Move option in enterprise manager and I don't want to
> move a file anyway, I just want to restore it.
> Thanks,
> T
>|||Tina,
When the restore dialog box appears, go to the "options tab" an change
"Restore As" for each file. If they are pointing to "D" and the server does
not have "D" drive or the specified folder does not exosts in the "D" drive,
then you have to change this arguments.
AMB
"Tina" wrote:
> I use Enterprise Manager to backup and restore various SQL Server databases
> moving them around on my different dev boxes. I have a case now where a
> database is on drive d on one system and it's on drive c on another system.
> It won't let me restore to drive C - says I need to use the With Move
> option.
> I don't see a With Move option in enterprise manager and I don't want to
> move a file anyway, I just want to restore it.
> Thanks,
> T
>
>
Tuesday, March 20, 2012
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...
>
>
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
>
>
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...
>
>
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...
>
>
Friday, March 9, 2012
Restore Order
Hi NG,
I have a question regarding the restore order of databases in case of
total desaster on the same server. I use KEEP_REPLICATION.
1. master
2. msdb
--> Stop all jobs in msdb
3. distribution
4. replication database
Is these sequence OK?
thanks in advance
Thomas
Is my question incorrectly?
On Tue, 08 Mar 2005 16:48:12 GMT, tohas@.freenet.de (Thomas Hase)
wrote:
>Hi NG,
>I have a question regarding the restore order of databases in case of
>total desaster on the same server. I use KEEP_REPLICATION.
>1. master
>2. msdb
>--> Stop all jobs in msdb
>3. distribution
>4. replication database
>Is these sequence OK?
>thanks in advance
>Thomas
|||I would tend to restore MSDB last, in case any jobs start
before everything's ready. Also if you are talking about
transactional publications, the 'sync with backup' option
is important. Finally, the servername must be identical
on the newly rebuilt server.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
I have a question regarding the restore order of databases in case of
total desaster on the same server. I use KEEP_REPLICATION.
1. master
2. msdb
--> Stop all jobs in msdb
3. distribution
4. replication database
Is these sequence OK?
thanks in advance
Thomas
Is my question incorrectly?
On Tue, 08 Mar 2005 16:48:12 GMT, tohas@.freenet.de (Thomas Hase)
wrote:
>Hi NG,
>I have a question regarding the restore order of databases in case of
>total desaster on the same server. I use KEEP_REPLICATION.
>1. master
>2. msdb
>--> Stop all jobs in msdb
>3. distribution
>4. replication database
>Is these sequence OK?
>thanks in advance
>Thomas
|||I would tend to restore MSDB last, in case any jobs start
before everything's ready. Also if you are talking about
transactional publications, the 'sync with backup' option
is important. Finally, the servername must be identical
on the newly rebuilt server.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Restore of the SQL Server Program Files
Hello Group,
My Network Admin an myself are trying to come up with a way to quickly
restore our SQL Server in case of partial disk failure. What I am looking to
do is to resore only the Program Files Folder on the Database Server (if that
is possible). We have had good success with using NT Back Up and restoring
the complete server (OS and all).
What he wants me to do next is use a Shadow Copy to restore just the
Programs Folder holding the SQL Server install. This ahs not worked very
well. We can restore the folders and instances and databases but the SQL
Server Service does not start.
Does Microsoft have any instructions on this? The other option is I just
install SQL Server again and dump my BAK files back into the server but they
want a solution that does not include using the SQL Server install CD.
Rich
Hi
There are registry settings that need to come along too.
Files need to be registered also.
If you setup your SQL Server instances using the unattended install scripts
and you save them away, you could possibly install SQL Server from a scratch
faster than looking for a tape.
AFAIK, only full machine restores are supported to restore program files.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:C865BFA9-33DE-4E99-9CA3-42AE7DFF3F84@.microsoft.com...
> Hello Group,
> My Network Admin an myself are trying to come up with a way to quickly
> restore our SQL Server in case of partial disk failure. What I am looking
> to
> do is to resore only the Program Files Folder on the Database Server (if
> that
> is possible). We have had good success with using NT Back Up and
> restoring
> the complete server (OS and all).
> What he wants me to do next is use a Shadow Copy to restore just the
> Programs Folder holding the SQL Server install. This ahs not worked very
> well. We can restore the folders and instances and databases but the SQL
> Server Service does not start.
> Does Microsoft have any instructions on this? The other option is I just
> install SQL Server again and dump my BAK files back into the server but
> they
> want a solution that does not include using the SQL Server install CD.
> Rich
>
My Network Admin an myself are trying to come up with a way to quickly
restore our SQL Server in case of partial disk failure. What I am looking to
do is to resore only the Program Files Folder on the Database Server (if that
is possible). We have had good success with using NT Back Up and restoring
the complete server (OS and all).
What he wants me to do next is use a Shadow Copy to restore just the
Programs Folder holding the SQL Server install. This ahs not worked very
well. We can restore the folders and instances and databases but the SQL
Server Service does not start.
Does Microsoft have any instructions on this? The other option is I just
install SQL Server again and dump my BAK files back into the server but they
want a solution that does not include using the SQL Server install CD.
Rich
Hi
There are registry settings that need to come along too.
Files need to be registered also.
If you setup your SQL Server instances using the unattended install scripts
and you save them away, you could possibly install SQL Server from a scratch
faster than looking for a tape.
AFAIK, only full machine restores are supported to restore program files.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:C865BFA9-33DE-4E99-9CA3-42AE7DFF3F84@.microsoft.com...
> Hello Group,
> My Network Admin an myself are trying to come up with a way to quickly
> restore our SQL Server in case of partial disk failure. What I am looking
> to
> do is to resore only the Program Files Folder on the Database Server (if
> that
> is possible). We have had good success with using NT Back Up and
> restoring
> the complete server (OS and all).
> What he wants me to do next is use a Shadow Copy to restore just the
> Programs Folder holding the SQL Server install. This ahs not worked very
> well. We can restore the folders and instances and databases but the SQL
> Server Service does not start.
> Does Microsoft have any instructions on this? The other option is I just
> install SQL Server again and dump my BAK files back into the server but
> they
> want a solution that does not include using the SQL Server install CD.
> Rich
>
Restore of the SQL Server Program Files
Hello Group,
My Network Admin an myself are trying to come up with a way to quickly
restore our SQL Server in case of partial disk failure. What I am looking t
o
do is to resore only the Program Files Folder on the Database Server (if tha
t
is possible). We have had good success with using NT Back Up and restoring
the complete server (OS and all).
What he wants me to do next is use a Shadow Copy to restore just the
Programs Folder holding the SQL Server install. This ahs not worked very
well. We can restore the folders and instances and databases but the SQL
Server Service does not start.
Does Microsoft have any instructions on this? The other option is I just
install SQL Server again and dump my BAK files back into the server but they
want a solution that does not include using the SQL Server install CD.
RichHi
There are registry settings that need to come along too.
Files need to be registered also.
If you setup your SQL Server instances using the unattended install scripts
and you save them away, you could possibly install SQL Server from a scratch
faster than looking for a tape.
AFAIK, only full machine restores are supported to restore program files.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:C865BFA9-33DE-4E99-9CA3-42AE7DFF3F84@.microsoft.com...
> Hello Group,
> My Network Admin an myself are trying to come up with a way to quickly
> restore our SQL Server in case of partial disk failure. What I am looking
> to
> do is to resore only the Program Files Folder on the Database Server (if
> that
> is possible). We have had good success with using NT Back Up and
> restoring
> the complete server (OS and all).
> What he wants me to do next is use a Shadow Copy to restore just the
> Programs Folder holding the SQL Server install. This ahs not worked very
> well. We can restore the folders and instances and databases but the SQL
> Server Service does not start.
> Does Microsoft have any instructions on this? The other option is I just
> install SQL Server again and dump my BAK files back into the server but
> they
> want a solution that does not include using the SQL Server install CD.
> Rich
>
My Network Admin an myself are trying to come up with a way to quickly
restore our SQL Server in case of partial disk failure. What I am looking t
o
do is to resore only the Program Files Folder on the Database Server (if tha
t
is possible). We have had good success with using NT Back Up and restoring
the complete server (OS and all).
What he wants me to do next is use a Shadow Copy to restore just the
Programs Folder holding the SQL Server install. This ahs not worked very
well. We can restore the folders and instances and databases but the SQL
Server Service does not start.
Does Microsoft have any instructions on this? The other option is I just
install SQL Server again and dump my BAK files back into the server but they
want a solution that does not include using the SQL Server install CD.
RichHi
There are registry settings that need to come along too.
Files need to be registered also.
If you setup your SQL Server instances using the unattended install scripts
and you save them away, you could possibly install SQL Server from a scratch
faster than looking for a tape.
AFAIK, only full machine restores are supported to restore program files.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:C865BFA9-33DE-4E99-9CA3-42AE7DFF3F84@.microsoft.com...
> Hello Group,
> My Network Admin an myself are trying to come up with a way to quickly
> restore our SQL Server in case of partial disk failure. What I am looking
> to
> do is to resore only the Program Files Folder on the Database Server (if
> that
> is possible). We have had good success with using NT Back Up and
> restoring
> the complete server (OS and all).
> What he wants me to do next is use a Shadow Copy to restore just the
> Programs Folder holding the SQL Server install. This ahs not worked very
> well. We can restore the folders and instances and databases but the SQL
> Server Service does not start.
> Does Microsoft have any instructions on this? The other option is I just
> install SQL Server again and dump my BAK files back into the server but
> they
> want a solution that does not include using the SQL Server install CD.
> Rich
>
Restore of the SQL Server Program Files
Hello Group,
My Network Admin an myself are trying to come up with a way to quickly
restore our SQL Server in case of partial disk failure. What I am looking to
do is to resore only the Program Files Folder on the Database Server (if that
is possible). We have had good success with using NT Back Up and restoring
the complete server (OS and all).
What he wants me to do next is use a Shadow Copy to restore just the
Programs Folder holding the SQL Server install. This ahs not worked very
well. We can restore the folders and instances and databases but the SQL
Server Service does not start.
Does Microsoft have any instructions on this? The other option is I just
install SQL Server again and dump my BAK files back into the server but they
want a solution that does not include using the SQL Server install CD.
RichHi
There are registry settings that need to come along too.
Files need to be registered also.
If you setup your SQL Server instances using the unattended install scripts
and you save them away, you could possibly install SQL Server from a scratch
faster than looking for a tape.
AFAIK, only full machine restores are supported to restore program files.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:C865BFA9-33DE-4E99-9CA3-42AE7DFF3F84@.microsoft.com...
> Hello Group,
> My Network Admin an myself are trying to come up with a way to quickly
> restore our SQL Server in case of partial disk failure. What I am looking
> to
> do is to resore only the Program Files Folder on the Database Server (if
> that
> is possible). We have had good success with using NT Back Up and
> restoring
> the complete server (OS and all).
> What he wants me to do next is use a Shadow Copy to restore just the
> Programs Folder holding the SQL Server install. This ahs not worked very
> well. We can restore the folders and instances and databases but the SQL
> Server Service does not start.
> Does Microsoft have any instructions on this? The other option is I just
> install SQL Server again and dump my BAK files back into the server but
> they
> want a solution that does not include using the SQL Server install CD.
> Rich
>
My Network Admin an myself are trying to come up with a way to quickly
restore our SQL Server in case of partial disk failure. What I am looking to
do is to resore only the Program Files Folder on the Database Server (if that
is possible). We have had good success with using NT Back Up and restoring
the complete server (OS and all).
What he wants me to do next is use a Shadow Copy to restore just the
Programs Folder holding the SQL Server install. This ahs not worked very
well. We can restore the folders and instances and databases but the SQL
Server Service does not start.
Does Microsoft have any instructions on this? The other option is I just
install SQL Server again and dump my BAK files back into the server but they
want a solution that does not include using the SQL Server install CD.
RichHi
There are registry settings that need to come along too.
Files need to be registered also.
If you setup your SQL Server instances using the unattended install scripts
and you save them away, you could possibly install SQL Server from a scratch
faster than looking for a tape.
AFAIK, only full machine restores are supported to restore program files.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:C865BFA9-33DE-4E99-9CA3-42AE7DFF3F84@.microsoft.com...
> Hello Group,
> My Network Admin an myself are trying to come up with a way to quickly
> restore our SQL Server in case of partial disk failure. What I am looking
> to
> do is to resore only the Program Files Folder on the Database Server (if
> that
> is possible). We have had good success with using NT Back Up and
> restoring
> the complete server (OS and all).
> What he wants me to do next is use a Shadow Copy to restore just the
> Programs Folder holding the SQL Server install. This ahs not worked very
> well. We can restore the folders and instances and databases but the SQL
> Server Service does not start.
> Does Microsoft have any instructions on this? The other option is I just
> install SQL Server again and dump my BAK files back into the server but
> they
> want a solution that does not include using the SQL Server install CD.
> Rich
>
Wednesday, March 7, 2012
Restore of Case Insensitive Database to a Case Sensitive Database - SQL Server 2000
Yesterday I received a response to my CI/CS Collation problem and the
recommendation was to try and restore a CI Collation database to a CS
Collation database. After creating a blank CS database a full restore
(Force restore over existing database) does change the Collation to
CI. I'm unsure as to how I can restore without changing the
Collation. Any suggestions?Hi. Check out this article:
recommendation was to try and restore a CI Collation database to a CS
Collation database. After creating a blank CS database a full restore
(Force restore over existing database) does change the Collation to
CI. I'm unsure as to how I can restore without changing the
Collation. Any suggestions?Hi. Check out this article:
http://www.sql-server-performance.c...mparison_sp.asp
If appears that if you can put the CI db on another box and create a
linked server to it you should be able to bring over everything to the
CS db and avoid all your collation issues by setting one flag.
I've recently been wrestling with collation issues too due to
developing with SQL2K PersEd and then trying to migrate to SQL2K for
production. What a nightmare. In my case, I want to use the
production server's default collation and I saw an article about
restoring one collation over another (it sounds like that's what you
tried) but it didn't work for me either. I resorted to renaming the
old db and scripting all the objects in it, removing any DDL
references to collation settings (so that running the scripts would
use the new db's default), creating a new database with the original
db name, and then running the scripts to create the new objects. Then
via DTS I ran queries to bring all the data over. My databases are
fairly small and this was still a lot of work but it succeeded. It may
not be a viable option for you though.
Good luck, and if you find an easier way of doing this please let me
know.
JT
swansons@.optimalinternet.com (Sue Swanson) wrote in message news:<c93054c8.0307010414.44475a5@.posting.google.com>...
> Yesterday I received a response to my CI/CS Collation problem and the
> recommendation was to try and restore a CI Collation database to a CS
> Collation database. After creating a blank CS database a full restore
> (Force restore over existing database) does change the Collation to
> CI. I'm unsure as to how I can restore without changing the
> Collation. Any suggestions?|||Sue Swanson (swansons@.optimalinternet.com) writes:
> Yesterday I received a response to my CI/CS Collation problem and the
> recommendation was to try and restore a CI Collation database to a CS
> Collation database. After creating a blank CS database a full restore
> (Force restore over existing database) does change the Collation to
> CI. I'm unsure as to how I can restore without changing the
> Collation. Any suggestions?
This does not seem like a workable way to me. It is possible
that the default for new tables in the new database would be CS, but
the existing tables in the backup would retain their CI collation.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Saturday, February 25, 2012
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
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
Subscribe to:
Posts (Atom)