Hi
I want to alter Model so that its transaction logs and
datafiles are on different drives,so that new dbs created
on this server get the same configuration.
When I try to restore Model with a with_move option it
says I can't do that.
How can I acheive what I want?
Would remaning Model, and the creating a new db called
Model in the config I want work?Have a look at this old post :-
http://groups.google.co.uk/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=co4joN3eCHA.1308%40cpmsftngxa08
--
HTH
Ryan Waight, MCDBA, MCSE
"felix" <felix@.hotmail.com> wrote in message
news:007a01c38ce8$52b4d570$a301280a@.phx.gbl...
> Hi
> I want to alter Model so that its transaction logs and
> datafiles are on different drives,so that new dbs created
> on this server get the same configuration.
> When I try to restore Model with a with_move option it
> says I can't do that.
> How can I acheive what I want?
> Would remaning Model, and the creating a new db called
> Model in the config I want work?
>|||To move the model database, SQL Server must be started
with trace flag 3608 so that
it does not recover any database except
the master.
NOTE: You will not be able to access any user databases at
this time.
You should not perform any operations
other than the steps below while using
this trace flag.
To add trace flag 3608 as a SQL Server startup
parameter:
After adding trace flag 3608, perform the following
steps:
1. Stop and restart SQL Server.
2. Detach the model database as follows:
use master
go
sp_detach_db 'model'
go
3. Move the Model.mdf and Modellog.ldf files from D:\Mssql7
\Data to
E:\Sqldata(or any other drives).
4. Reattach the model database as follows:
use master
go
sp_attach_db 'model','E:\Sqldata\model.mdf','E:\Sqldata\mod
ellog.ldf'
go
--e:\ or any other drives
5. Remove the -T3608 trace flag from the startup
parameters box in the
Enterprise Manager.
6. Stop and restart SQL Server. You can verify the change
in
file locations using
sp_helpfile:
use model
go
sp_helpfile
go
Koohyar
This posting is provided "AS IS" with no warranties, and
confers no rights.
http://www.microsoft.com/info/cpyright.htm
>--Original Message--
>Hi
>I want to alter Model so that its transaction logs and
>datafiles are on different drives,so that new dbs created
>on this server get the same configuration.
>When I try to restore Model with a with_move option it
>says I can't do that.
>How can I acheive what I want?
>Would remaning Model, and the creating a new db called
>Model in the config I want work?
>.
>|||To add to the other responses, the default location of new database data
and log files is not determined by the model database file locations.
The default file locations can be specified via Enterprise Manager under
server properties --> database settings.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"felix" <felix@.hotmail.com> wrote in message
news:007a01c38ce8$52b4d570$a301280a@.phx.gbl...
> Hi
> I want to alter Model so that its transaction logs and
> datafiles are on different drives,so that new dbs created
> on this server get the same configuration.
> When I try to restore Model with a with_move option it
> says I can't do that.
> How can I acheive what I want?
> Would remaning Model, and the creating a new db called
> Model in the config I want work?
>|||Thanks Dan,
Much more simple than I thought then! Your suggestion
worked a treat.
Felix
>--Original Message--
>To add to the other responses, the default location of
new database data
>and log files is not determined by the model database
file locations.
>The default file locations can be specified via
Enterprise Manager under
>server properties --> database settings.
>--
>Hope this helps.
>Dan Guzman
>SQL Server MVP
>--
>SQL FAQ links (courtesy Neil Pike):
>http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
>http://www.sqlserverfaq.com
>http://www.mssqlserver.com/faq
>--
>"felix" <felix@.hotmail.com> wrote in message
>news:007a01c38ce8$52b4d570$a301280a@.phx.gbl...
>> Hi
>> I want to alter Model so that its transaction logs and
>> datafiles are on different drives,so that new dbs
created
>> on this server get the same configuration.
>> When I try to restore Model with a with_move option it
>> says I can't do that.
>> How can I acheive what I want?
>> Would remaning Model, and the creating a new db called
>> Model in the config I want work?
>
>.
>
Showing posts with label restoring. Show all posts
Showing posts with label restoring. Show all posts
Wednesday, March 28, 2012
Restoring.............
Hi,
I am very new to Microsoft Technologies, infact new to database world
:)I have a SQL-Server 7.0 back. How can i restore it? Moreover , can i
restore this backup by using SQL-Server 2000Enterprise edition, how
should i go about it?
Any help or comments will be highly appreciated.
-ErlandEasiest in the login run is to read about the RESTORE command in Books Onlin
e. After you understand
how that RESTORE command work, you will find it natural how to work the GUI
dialog in Enterprise
Manager (if you chose to use it instead of executing the RESTORE command in
Query Analyzer).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Erland" <Erland.Erikson@.gmail.com> wrote in message
news:1132234090.437642.38470@.f14g2000cwb.googlegroups.com...
> Hi,
> I am very new to Microsoft Technologies, infact new to database world
> :)I have a SQL-Server 7.0 back. How can i restore it? Moreover , can i
> restore this backup by using SQL-Server 2000Enterprise edition, how
> should i go about it?
> Any help or comments will be highly appreciated.
> -Erland
>|||Tibor Karaszi wrote:
> Easiest in the login run is to read about the RESTORE command in Books Onl
ine. After you understand
> how that RESTORE command work, you will find it natural how to work the GU
I dialog in Enterprise
> Manager (if you chose to use it instead of executing the RESTORE command i
n Query Analyzer).
>
osql
restore database blah from disk = 'c:\blah.dat'
something like that
I am very new to Microsoft Technologies, infact new to database world
:)I have a SQL-Server 7.0 back. How can i restore it? Moreover , can i
restore this backup by using SQL-Server 2000Enterprise edition, how
should i go about it?
Any help or comments will be highly appreciated.
-ErlandEasiest in the login run is to read about the RESTORE command in Books Onlin
e. After you understand
how that RESTORE command work, you will find it natural how to work the GUI
dialog in Enterprise
Manager (if you chose to use it instead of executing the RESTORE command in
Query Analyzer).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Erland" <Erland.Erikson@.gmail.com> wrote in message
news:1132234090.437642.38470@.f14g2000cwb.googlegroups.com...
> Hi,
> I am very new to Microsoft Technologies, infact new to database world
> :)I have a SQL-Server 7.0 back. How can i restore it? Moreover , can i
> restore this backup by using SQL-Server 2000Enterprise edition, how
> should i go about it?
> Any help or comments will be highly appreciated.
> -Erland
>|||Tibor Karaszi wrote:
> Easiest in the login run is to read about the RESTORE command in Books Onl
ine. After you understand
> how that RESTORE command work, you will find it natural how to work the GU
I dialog in Enterprise
> Manager (if you chose to use it instead of executing the RESTORE command i
n Query Analyzer).
>
osql
restore database blah from disk = 'c:\blah.dat'
something like that
restoring without log
Our sql server 2000 keeps the mdf files on one drive and the ldf files
on another. We lost our data on the ldf drive and had to restore from
a day earlier. Now the mdf and ldf are out of synch and can't access
our data. How do we rebuild our data with just the mdf file?
Thanks,
RickRestore from the most recent SQL Server backup (not file system backups). You can try
sp_attach_single_file_db, but as you didn't detach first, it might not work. If you're still out of
luck, open a case with MS Support and see if they have any tricks up their sleeves.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Rick" <rick@.abasoftware.com> wrote in message
news:1124387611.085354.43780@.g47g2000cwa.googlegroups.com...
> Our sql server 2000 keeps the mdf files on one drive and the ldf files
> on another. We lost our data on the ldf drive and had to restore from
> a day earlier. Now the mdf and ldf are out of synch and can't access
> our data. How do we rebuild our data with just the mdf file?
> Thanks,
> Rick
>
on another. We lost our data on the ldf drive and had to restore from
a day earlier. Now the mdf and ldf are out of synch and can't access
our data. How do we rebuild our data with just the mdf file?
Thanks,
RickRestore from the most recent SQL Server backup (not file system backups). You can try
sp_attach_single_file_db, but as you didn't detach first, it might not work. If you're still out of
luck, open a case with MS Support and see if they have any tricks up their sleeves.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Rick" <rick@.abasoftware.com> wrote in message
news:1124387611.085354.43780@.g47g2000cwa.googlegroups.com...
> Our sql server 2000 keeps the mdf files on one drive and the ldf files
> on another. We lost our data on the ldf drive and had to restore from
> a day earlier. Now the mdf and ldf are out of synch and can't access
> our data. How do we rebuild our data with just the mdf file?
> Thanks,
> Rick
>
restoring without log
Our sql server 2000 keeps the mdf files on one drive and the ldf files
on another. We lost our data on the ldf drive and had to restore from
a day earlier. Now the mdf and ldf are out of synch and can't access
our data. How do we rebuild our data with just the mdf file?
Thanks,
Rick
Restore from the most recent SQL Server backup (not file system backups). You can try
sp_attach_single_file_db, but as you didn't detach first, it might not work. If you're still out of
luck, open a case with MS Support and see if they have any tricks up their sleeves.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Rick" <rick@.abasoftware.com> wrote in message
news:1124387611.085354.43780@.g47g2000cwa.googlegro ups.com...
> Our sql server 2000 keeps the mdf files on one drive and the ldf files
> on another. We lost our data on the ldf drive and had to restore from
> a day earlier. Now the mdf and ldf are out of synch and can't access
> our data. How do we rebuild our data with just the mdf file?
> Thanks,
> Rick
>
sql
on another. We lost our data on the ldf drive and had to restore from
a day earlier. Now the mdf and ldf are out of synch and can't access
our data. How do we rebuild our data with just the mdf file?
Thanks,
Rick
Restore from the most recent SQL Server backup (not file system backups). You can try
sp_attach_single_file_db, but as you didn't detach first, it might not work. If you're still out of
luck, open a case with MS Support and see if they have any tricks up their sleeves.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Rick" <rick@.abasoftware.com> wrote in message
news:1124387611.085354.43780@.g47g2000cwa.googlegro ups.com...
> Our sql server 2000 keeps the mdf files on one drive and the ldf files
> on another. We lost our data on the ldf drive and had to restore from
> a day earlier. Now the mdf and ldf are out of synch and can't access
> our data. How do we rebuild our data with just the mdf file?
> Thanks,
> Rick
>
sql
restoring without log
Our sql server 2000 keeps the mdf files on one drive and the ldf files
on another. We lost our data on the ldf drive and had to restore from
a day earlier. Now the mdf and ldf are out of synch and can't access
our data. How do we rebuild our data with just the mdf file?
Thanks,
RickRestore from the most recent SQL Server backup (not file system backups). Yo
u can try
sp_attach_single_file_db, but as you didn't detach first, it might not work.
If you're still out of
luck, open a case with MS Support and see if they have any tricks up their s
leeves.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Rick" <rick@.abasoftware.com> wrote in message
news:1124387611.085354.43780@.g47g2000cwa.googlegroups.com...
> Our sql server 2000 keeps the mdf files on one drive and the ldf files
> on another. We lost our data on the ldf drive and had to restore from
> a day earlier. Now the mdf and ldf are out of synch and can't access
> our data. How do we rebuild our data with just the mdf file?
> Thanks,
> Rick
>
on another. We lost our data on the ldf drive and had to restore from
a day earlier. Now the mdf and ldf are out of synch and can't access
our data. How do we rebuild our data with just the mdf file?
Thanks,
RickRestore from the most recent SQL Server backup (not file system backups). Yo
u can try
sp_attach_single_file_db, but as you didn't detach first, it might not work.
If you're still out of
luck, open a case with MS Support and see if they have any tricks up their s
leeves.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Rick" <rick@.abasoftware.com> wrote in message
news:1124387611.085354.43780@.g47g2000cwa.googlegroups.com...
> Our sql server 2000 keeps the mdf files on one drive and the ldf files
> on another. We lost our data on the ldf drive and had to restore from
> a day earlier. Now the mdf and ldf are out of synch and can't access
> our data. How do we rebuild our data with just the mdf file?
> Thanks,
> Rick
>
restoring with StopBeforeMark
hi
i did :
create table pp ( id int )
backup full
insert into pp values ( 1 )
backup diff
insert into pp values ( 2 )
backup log 1
insert into pp values ( 3 )
backup log 2
insert into pp values ( 4 )
backup log 3 -- tail
i want to restore
the rows 1, 2, and 3
the way i'm trying is this :
restore database [xx] from TheBackUp with
NOrecovery,
StopBeforeMark= 'LSN of backup log 4'
but,
i get a table with no rows
what is wrong ?
TIA
atte,
Hernn
atte,
Hernn
ohhhh...
i understand now...
i have to restore
the whole the backup chain
from the first to the last
but using the StopBeforeClause...
is there a way to restore
the whole backup chain in one line ?
should i to write a "loop"
for restore each row
that RESTORE HEADERONLY bring me ?
atte,
Hernn
"bajopalabra" <bajopalabra@.hotmail.com> escribi en el mensaje
news:%23kiu9On3GHA.696@.TK2MSFTNGP03.phx.gbl...
| hi
| i did :
|
| create table pp ( id int )
| backup full
| insert into pp values ( 1 )
| backup diff
| insert into pp values ( 2 )
| backup log 1
| insert into pp values ( 3 )
| backup log 2
| insert into pp values ( 4 )
| backup log 3 -- tail
|
| i want to restore
| the rows 1, 2, and 3
|
| the way i'm trying is this :
|
| restore database [xx] from TheBackUp with
| NOrecovery,
| StopBeforeMark= 'LSN of backup log 4'
|
| but,
| i get a table with no rows
|
| what is wrong ?
|
| TIA
|
| --
| atte,
| Hernn
|
| --
| atte,
| Hernn
|
|
|||Already replied in .programming. Please don't multi-post.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"bajopalabra" <bajopalabra@.hotmail.com> wrote in message
news:uVMa5gn3GHA.4976@.TK2MSFTNGP02.phx.gbl...
> ohhhh...
> i understand now...
> i have to restore
> the whole the backup chain
> from the first to the last
> but using the StopBeforeClause...
> is there a way to restore
> the whole backup chain in one line ?
> should i to write a "loop"
> for restore each row
> that RESTORE HEADERONLY bring me ?
> --
> atte,
> Hernn
> "bajopalabra" <bajopalabra@.hotmail.com> escribi en el mensaje
> news:%23kiu9On3GHA.696@.TK2MSFTNGP03.phx.gbl...
> | hi
> | i did :
> |
> | create table pp ( id int )
> | backup full
> | insert into pp values ( 1 )
> | backup diff
> | insert into pp values ( 2 )
> | backup log 1
> | insert into pp values ( 3 )
> | backup log 2
> | insert into pp values ( 4 )
> | backup log 3 -- tail
> |
> | i want to restore
> | the rows 1, 2, and 3
> |
> | the way i'm trying is this :
> |
> | restore database [xx] from TheBackUp with
> | NOrecovery,
> | StopBeforeMark= 'LSN of backup log 4'
> |
> | but,
> | i get a table with no rows
> |
> | what is wrong ?
> |
> | TIA
> |
> | --
> | atte,
> | Hernn
> |
> | --
> | atte,
> | Hernn
> |
> |
>
i did :
create table pp ( id int )
backup full
insert into pp values ( 1 )
backup diff
insert into pp values ( 2 )
backup log 1
insert into pp values ( 3 )
backup log 2
insert into pp values ( 4 )
backup log 3 -- tail
i want to restore
the rows 1, 2, and 3
the way i'm trying is this :
restore database [xx] from TheBackUp with
NOrecovery,
StopBeforeMark= 'LSN of backup log 4'
but,
i get a table with no rows
what is wrong ?
TIA
atte,
Hernn
atte,
Hernn
ohhhh...
i understand now...
i have to restore
the whole the backup chain
from the first to the last
but using the StopBeforeClause...
is there a way to restore
the whole backup chain in one line ?
should i to write a "loop"
for restore each row
that RESTORE HEADERONLY bring me ?
atte,
Hernn
"bajopalabra" <bajopalabra@.hotmail.com> escribi en el mensaje
news:%23kiu9On3GHA.696@.TK2MSFTNGP03.phx.gbl...
| hi
| i did :
|
| create table pp ( id int )
| backup full
| insert into pp values ( 1 )
| backup diff
| insert into pp values ( 2 )
| backup log 1
| insert into pp values ( 3 )
| backup log 2
| insert into pp values ( 4 )
| backup log 3 -- tail
|
| i want to restore
| the rows 1, 2, and 3
|
| the way i'm trying is this :
|
| restore database [xx] from TheBackUp with
| NOrecovery,
| StopBeforeMark= 'LSN of backup log 4'
|
| but,
| i get a table with no rows
|
| what is wrong ?
|
| TIA
|
| --
| atte,
| Hernn
|
| --
| atte,
| Hernn
|
|
|||Already replied in .programming. Please don't multi-post.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"bajopalabra" <bajopalabra@.hotmail.com> wrote in message
news:uVMa5gn3GHA.4976@.TK2MSFTNGP02.phx.gbl...
> ohhhh...
> i understand now...
> i have to restore
> the whole the backup chain
> from the first to the last
> but using the StopBeforeClause...
> is there a way to restore
> the whole backup chain in one line ?
> should i to write a "loop"
> for restore each row
> that RESTORE HEADERONLY bring me ?
> --
> atte,
> Hernn
> "bajopalabra" <bajopalabra@.hotmail.com> escribi en el mensaje
> news:%23kiu9On3GHA.696@.TK2MSFTNGP03.phx.gbl...
> | hi
> | i did :
> |
> | create table pp ( id int )
> | backup full
> | insert into pp values ( 1 )
> | backup diff
> | insert into pp values ( 2 )
> | backup log 1
> | insert into pp values ( 3 )
> | backup log 2
> | insert into pp values ( 4 )
> | backup log 3 -- tail
> |
> | i want to restore
> | the rows 1, 2, and 3
> |
> | the way i'm trying is this :
> |
> | restore database [xx] from TheBackUp with
> | NOrecovery,
> | StopBeforeMark= 'LSN of backup log 4'
> |
> | but,
> | i get a table with no rows
> |
> | what is wrong ?
> |
> | TIA
> |
> | --
> | atte,
> | Hernn
> |
> | --
> | atte,
> | Hernn
> |
> |
>
restoring with StopBeforeMark
hi
i did :
create table pp ( id int )
backup full
insert into pp values ( 1 )
backup diff
insert into pp values ( 2 )
backup log 1
insert into pp values ( 3 )
backup log 2
insert into pp values ( 4 )
backup log 3 -- tail
i want to restore
the rows 1, 2, and 3
the way i'm trying is this :
restore database [xx] from TheBackUp with
NOrecovery,
StopBeforeMark= 'LSN of backup log 4'
but,
i get a table with no rows
what is wrong '
TIA
atte,
Hernn
atte,
Hernnohhhh...
i understand now...
i have to restore
the whole the backup chain
from the first to the last
but using the StopBeforeClause...
is there a way to restore
the whole backup chain in one line ?
should i to write a "loop"
for restore each row
that RESTORE HEADERONLY bring me '
atte,
Hernn
"bajopalabra" <bajopalabra@.hotmail.com> escribi en el mensaje
news:%23kiu9On3GHA.696@.TK2MSFTNGP03.phx.gbl...
| hi
| i did :
|
| create table pp ( id int )
| backup full
| insert into pp values ( 1 )
| backup diff
| insert into pp values ( 2 )
| backup log 1
| insert into pp values ( 3 )
| backup log 2
| insert into pp values ( 4 )
| backup log 3 -- tail
|
| i want to restore
| the rows 1, 2, and 3
|
| the way i'm trying is this :
|
| restore database [xx] from TheBackUp with
| NOrecovery,
| StopBeforeMark= 'LSN of backup log 4'
|
| but,
| i get a table with no rows
|
| what is wrong '
|
| TIA
|
| --
| atte,
| Hernn
|
| --
| atte,
| Hernn
|
||||Already replied in .programming. Please don't multi-post.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"bajopalabra" <bajopalabra@.hotmail.com> wrote in message
news:uVMa5gn3GHA.4976@.TK2MSFTNGP02.phx.gbl...
> ohhhh...
> i understand now...
> i have to restore
> the whole the backup chain
> from the first to the last
> but using the StopBeforeClause...
> is there a way to restore
> the whole backup chain in one line ?
> should i to write a "loop"
> for restore each row
> that RESTORE HEADERONLY bring me '
> --
> atte,
> Hernn
> "bajopalabra" <bajopalabra@.hotmail.com> escribi en el mensaje
> news:%23kiu9On3GHA.696@.TK2MSFTNGP03.phx.gbl...
> | hi
> | i did :
> |
> | create table pp ( id int )
> | backup full
> | insert into pp values ( 1 )
> | backup diff
> | insert into pp values ( 2 )
> | backup log 1
> | insert into pp values ( 3 )
> | backup log 2
> | insert into pp values ( 4 )
> | backup log 3 -- tail
> |
> | i want to restore
> | the rows 1, 2, and 3
> |
> | the way i'm trying is this :
> |
> | restore database [xx] from TheBackUp with
> | NOrecovery,
> | StopBeforeMark= 'LSN of backup log 4'
> |
> | but,
> | i get a table with no rows
> |
> | what is wrong '
> |
> | TIA
> |
> | --
> | atte,
> | Hernn
> |
> | --
> | atte,
> | Hernn
> |
> |
>
i did :
create table pp ( id int )
backup full
insert into pp values ( 1 )
backup diff
insert into pp values ( 2 )
backup log 1
insert into pp values ( 3 )
backup log 2
insert into pp values ( 4 )
backup log 3 -- tail
i want to restore
the rows 1, 2, and 3
the way i'm trying is this :
restore database [xx] from TheBackUp with
NOrecovery,
StopBeforeMark= 'LSN of backup log 4'
but,
i get a table with no rows
what is wrong '
TIA
atte,
Hernn
atte,
Hernnohhhh...
i understand now...
i have to restore
the whole the backup chain
from the first to the last
but using the StopBeforeClause...
is there a way to restore
the whole backup chain in one line ?
should i to write a "loop"
for restore each row
that RESTORE HEADERONLY bring me '
atte,
Hernn
"bajopalabra" <bajopalabra@.hotmail.com> escribi en el mensaje
news:%23kiu9On3GHA.696@.TK2MSFTNGP03.phx.gbl...
| hi
| i did :
|
| create table pp ( id int )
| backup full
| insert into pp values ( 1 )
| backup diff
| insert into pp values ( 2 )
| backup log 1
| insert into pp values ( 3 )
| backup log 2
| insert into pp values ( 4 )
| backup log 3 -- tail
|
| i want to restore
| the rows 1, 2, and 3
|
| the way i'm trying is this :
|
| restore database [xx] from TheBackUp with
| NOrecovery,
| StopBeforeMark= 'LSN of backup log 4'
|
| but,
| i get a table with no rows
|
| what is wrong '
|
| TIA
|
| --
| atte,
| Hernn
|
| --
| atte,
| Hernn
|
||||Already replied in .programming. Please don't multi-post.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"bajopalabra" <bajopalabra@.hotmail.com> wrote in message
news:uVMa5gn3GHA.4976@.TK2MSFTNGP02.phx.gbl...
> ohhhh...
> i understand now...
> i have to restore
> the whole the backup chain
> from the first to the last
> but using the StopBeforeClause...
> is there a way to restore
> the whole backup chain in one line ?
> should i to write a "loop"
> for restore each row
> that RESTORE HEADERONLY bring me '
> --
> atte,
> Hernn
> "bajopalabra" <bajopalabra@.hotmail.com> escribi en el mensaje
> news:%23kiu9On3GHA.696@.TK2MSFTNGP03.phx.gbl...
> | hi
> | i did :
> |
> | create table pp ( id int )
> | backup full
> | insert into pp values ( 1 )
> | backup diff
> | insert into pp values ( 2 )
> | backup log 1
> | insert into pp values ( 3 )
> | backup log 2
> | insert into pp values ( 4 )
> | backup log 3 -- tail
> |
> | i want to restore
> | the rows 1, 2, and 3
> |
> | the way i'm trying is this :
> |
> | restore database [xx] from TheBackUp with
> | NOrecovery,
> | StopBeforeMark= 'LSN of backup log 4'
> |
> | but,
> | i get a table with no rows
> |
> | what is wrong '
> |
> | TIA
> |
> | --
> | atte,
> | Hernn
> |
> | --
> | atte,
> | Hernn
> |
> |
>
restoring with StopBeforeMark
hi
i did :
create table pp ( id int )
backup full
insert into pp values ( 1 )
backup diff
insert into pp values ( 2 )
backup log 1
insert into pp values ( 3 )
backup log 2
insert into pp values ( 4 )
backup log 3 -- tail
i want to restore
the rows 1, 2, and 3
the way i'm trying is this :
restore database [xx] from TheBackUp with
NOrecovery,
StopBeforeMark= 'LSN of backup log 4'
but,
i get a table with no rows
what is wrong '
TIA
--
atte,
Hernán
--
atte,
Hernánohhhh...
i understand now...
i have to restore
the whole the backup chain
from the first to the last
but using the StopBeforeClause...
is there a way to restore
the whole backup chain in one line ?
should i to write a "loop"
for restore each row
that RESTORE HEADERONLY bring me '
--
atte,
Hernán
"bajopalabra" <bajopalabra@.hotmail.com> escribió en el mensaje
news:%23kiu9On3GHA.696@.TK2MSFTNGP03.phx.gbl...
| hi
| i did :
|
| create table pp ( id int )
| backup full
| insert into pp values ( 1 )
| backup diff
| insert into pp values ( 2 )
| backup log 1
| insert into pp values ( 3 )
| backup log 2
| insert into pp values ( 4 )
| backup log 3 -- tail
|
| i want to restore
| the rows 1, 2, and 3
|
| the way i'm trying is this :
|
| restore database [xx] from TheBackUp with
| NOrecovery,
| StopBeforeMark= 'LSN of backup log 4'
|
| but,
| i get a table with no rows
|
| what is wrong '
|
| TIA
|
| --
| atte,
| Hernán
|
| --
| atte,
| Hernán
|
||||Already replied in .programming. Please don't multi-post.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"bajopalabra" <bajopalabra@.hotmail.com> wrote in message
news:uVMa5gn3GHA.4976@.TK2MSFTNGP02.phx.gbl...
> ohhhh...
> i understand now...
> i have to restore
> the whole the backup chain
> from the first to the last
> but using the StopBeforeClause...
> is there a way to restore
> the whole backup chain in one line ?
> should i to write a "loop"
> for restore each row
> that RESTORE HEADERONLY bring me '
> --
> atte,
> Hernán
> "bajopalabra" <bajopalabra@.hotmail.com> escribió en el mensaje
> news:%23kiu9On3GHA.696@.TK2MSFTNGP03.phx.gbl...
> | hi
> | i did :
> |
> | create table pp ( id int )
> | backup full
> | insert into pp values ( 1 )
> | backup diff
> | insert into pp values ( 2 )
> | backup log 1
> | insert into pp values ( 3 )
> | backup log 2
> | insert into pp values ( 4 )
> | backup log 3 -- tail
> |
> | i want to restore
> | the rows 1, 2, and 3
> |
> | the way i'm trying is this :
> |
> | restore database [xx] from TheBackUp with
> | NOrecovery,
> | StopBeforeMark= 'LSN of backup log 4'
> |
> | but,
> | i get a table with no rows
> |
> | what is wrong '
> |
> | TIA
> |
> | --
> | atte,
> | Hernán
> |
> | --
> | atte,
> | Hernán
> |
> |
>
i did :
create table pp ( id int )
backup full
insert into pp values ( 1 )
backup diff
insert into pp values ( 2 )
backup log 1
insert into pp values ( 3 )
backup log 2
insert into pp values ( 4 )
backup log 3 -- tail
i want to restore
the rows 1, 2, and 3
the way i'm trying is this :
restore database [xx] from TheBackUp with
NOrecovery,
StopBeforeMark= 'LSN of backup log 4'
but,
i get a table with no rows
what is wrong '
TIA
--
atte,
Hernán
--
atte,
Hernánohhhh...
i understand now...
i have to restore
the whole the backup chain
from the first to the last
but using the StopBeforeClause...
is there a way to restore
the whole backup chain in one line ?
should i to write a "loop"
for restore each row
that RESTORE HEADERONLY bring me '
--
atte,
Hernán
"bajopalabra" <bajopalabra@.hotmail.com> escribió en el mensaje
news:%23kiu9On3GHA.696@.TK2MSFTNGP03.phx.gbl...
| hi
| i did :
|
| create table pp ( id int )
| backup full
| insert into pp values ( 1 )
| backup diff
| insert into pp values ( 2 )
| backup log 1
| insert into pp values ( 3 )
| backup log 2
| insert into pp values ( 4 )
| backup log 3 -- tail
|
| i want to restore
| the rows 1, 2, and 3
|
| the way i'm trying is this :
|
| restore database [xx] from TheBackUp with
| NOrecovery,
| StopBeforeMark= 'LSN of backup log 4'
|
| but,
| i get a table with no rows
|
| what is wrong '
|
| TIA
|
| --
| atte,
| Hernán
|
| --
| atte,
| Hernán
|
||||Already replied in .programming. Please don't multi-post.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"bajopalabra" <bajopalabra@.hotmail.com> wrote in message
news:uVMa5gn3GHA.4976@.TK2MSFTNGP02.phx.gbl...
> ohhhh...
> i understand now...
> i have to restore
> the whole the backup chain
> from the first to the last
> but using the StopBeforeClause...
> is there a way to restore
> the whole backup chain in one line ?
> should i to write a "loop"
> for restore each row
> that RESTORE HEADERONLY bring me '
> --
> atte,
> Hernán
> "bajopalabra" <bajopalabra@.hotmail.com> escribió en el mensaje
> news:%23kiu9On3GHA.696@.TK2MSFTNGP03.phx.gbl...
> | hi
> | i did :
> |
> | create table pp ( id int )
> | backup full
> | insert into pp values ( 1 )
> | backup diff
> | insert into pp values ( 2 )
> | backup log 1
> | insert into pp values ( 3 )
> | backup log 2
> | insert into pp values ( 4 )
> | backup log 3 -- tail
> |
> | i want to restore
> | the rows 1, 2, and 3
> |
> | the way i'm trying is this :
> |
> | restore database [xx] from TheBackUp with
> | NOrecovery,
> | StopBeforeMark= 'LSN of backup log 4'
> |
> | but,
> | i get a table with no rows
> |
> | what is wrong '
> |
> | TIA
> |
> | --
> | atte,
> | Hernán
> |
> | --
> | atte,
> | Hernán
> |
> |
>
Restoring using smo
I have built a VB.NET app that uses SMO to restore a database from a backup device to a database. This works great if I restore the backup into the original database. If, however, i try to restore the backup into ANOTHER database, it fails. My code looks like this (only important parts are included):
db1 = New Management.Smo.Database
db1.Name = Me.lstDatabase.SelectedItem
restore = New Management.Smo.Restore
restore.Action = Management.Smo.RestoreActionType.Database
restore.Database = db1.Name
backDeviceItem = New Management.Smo.BackupDeviceItem(Me.lstBackups.Text, Management.Smo.DeviceType.LogicalDevice)
restore.Devices.Add(backDeviceItem)
restore.ReplaceDatabase = True
restore.SqlRestore(SqlServerSelection)
In this example, the lstBackups contains the list of backup devices. lstDatabase contains the list of databases to restore into. I can successfully connect to the database, but the restore fails. Any ideas?
Thanks...
Scott
What privilege level is the user attempting to do the restore? While a db_owner user can back up a database, the restore is in essence a create database process, which requires sysadmin level privileges.|||Also the database has to be in restricted mode.db1.DatabaseOptions.UserAccess = DatabseUserAccess.Restrictedsql
Restoring using smo
I have built a VB.NET app that uses SMO to restore a database from a backup device to a database. This works great if I restore the backup into the original database. If, however, i try to restore the backup into ANOTHER database, it fails. My code looks like this (only important parts are included):
db1 = New Management.Smo.Database
db1.Name = Me.lstDatabase.SelectedItem
restore = New Management.Smo.Restore
restore.Action = Management.Smo.RestoreActionType.Database
restore.Database = db1.Name
backDeviceItem = New Management.Smo.BackupDeviceItem(Me.lstBackups.Text, Management.Smo.DeviceType.LogicalDevice)
restore.Devices.Add(backDeviceItem)
restore.ReplaceDatabase = True
restore.SqlRestore(SqlServerSelection)
In this example, the lstBackups contains the list of backup devices. lstDatabase contains the list of databases to restore into. I can successfully connect to the database, but the restore fails. Any ideas?
Thanks...
Scott
What privilege level is the user attempting to do the restore? While a db_owner user can back up a database, the restore is in essence a create database process, which requires sysadmin level privileges.|||Also the database has to be in restricted mode.db1.DatabaseOptions.UserAccess = DatabseUserAccess.Restricted
Restoring Transaction Logs Into Backups of MDF and LDF
Hello,
Here's a brief outline of my scenario:
I am trying to recover from a database corruption problem. I have a
backup of the db's physical MDF and LDF files from before the
corruption, but no actual backup (ie bak file). I also have daily
transaction log backups right up to today, and my live db is still
operation despite the corruption, so I can take another transaction
log backup when I do the repair.
The problem I have is I don't know how to, or if it's possible to
attach the backup MDF & LDF files and then somehow apply my
transaction log backups to build a new up to date database.
Thanks,
AlexYou would need Attach with NORECOVERY, which currently is not a possibility.
There is a connect item you can vote in for a future feature request.
https://connect.microsoft.com/SQLSe...=2706
51
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
<cosmasdev@.yahoo.com> wrote in message
news:1177078845.141070.271000@.n59g2000hsh.googlegroups.com...
> Hello,
> Here's a brief outline of my scenario:
> I am trying to recover from a database corruption problem. I have a
> backup of the db's physical MDF and LDF files from before the
> corruption, but no actual backup (ie bak file). I also have daily
> transaction log backups right up to today, and my live db is still
> operation despite the corruption, so I can take another transaction
> log backup when I do the repair.
> The problem I have is I don't know how to, or if it's possible to
> attach the backup MDF & LDF files and then somehow apply my
> transaction log backups to build a new up to date database.
> Thanks,
> Alex
>|||Alex,
If you don't mind my asking, what kind of corruption did you get and how did
it happen. We're just moving to SQLServer to avoid corruptions and I'd hate
to find that we did not leave the problem behind.
Regards
Brian Morris
<cosmasdev@.yahoo.com> wrote in message
news:1177078845.141070.271000@.n59g2000hsh.googlegroups.com...
> Hello,
> Here's a brief outline of my scenario:
> I am trying to recover from a database corruption problem. I have a
> backup of the db's physical MDF and LDF files from before the
> corruption, but no actual backup (ie bak file). I also have daily
> transaction log backups right up to today, and my live db is still
> operation despite the corruption, so I can take another transaction
> log backup when I do the repair.
> The problem I have is I don't know how to, or if it's possible to
> attach the backup MDF & LDF files and then somehow apply my
> transaction log backups to build a new up to date database.
> Thanks,
> Alex
>
Here's a brief outline of my scenario:
I am trying to recover from a database corruption problem. I have a
backup of the db's physical MDF and LDF files from before the
corruption, but no actual backup (ie bak file). I also have daily
transaction log backups right up to today, and my live db is still
operation despite the corruption, so I can take another transaction
log backup when I do the repair.
The problem I have is I don't know how to, or if it's possible to
attach the backup MDF & LDF files and then somehow apply my
transaction log backups to build a new up to date database.
Thanks,
AlexYou would need Attach with NORECOVERY, which currently is not a possibility.
There is a connect item you can vote in for a future feature request.
https://connect.microsoft.com/SQLSe...=2706
51
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
<cosmasdev@.yahoo.com> wrote in message
news:1177078845.141070.271000@.n59g2000hsh.googlegroups.com...
> Hello,
> Here's a brief outline of my scenario:
> I am trying to recover from a database corruption problem. I have a
> backup of the db's physical MDF and LDF files from before the
> corruption, but no actual backup (ie bak file). I also have daily
> transaction log backups right up to today, and my live db is still
> operation despite the corruption, so I can take another transaction
> log backup when I do the repair.
> The problem I have is I don't know how to, or if it's possible to
> attach the backup MDF & LDF files and then somehow apply my
> transaction log backups to build a new up to date database.
> Thanks,
> Alex
>|||Alex,
If you don't mind my asking, what kind of corruption did you get and how did
it happen. We're just moving to SQLServer to avoid corruptions and I'd hate
to find that we did not leave the problem behind.
Regards
Brian Morris
<cosmasdev@.yahoo.com> wrote in message
news:1177078845.141070.271000@.n59g2000hsh.googlegroups.com...
> Hello,
> Here's a brief outline of my scenario:
> I am trying to recover from a database corruption problem. I have a
> backup of the db's physical MDF and LDF files from before the
> corruption, but no actual backup (ie bak file). I also have daily
> transaction log backups right up to today, and my live db is still
> operation despite the corruption, so I can take another transaction
> log backup when I do the repair.
> The problem I have is I don't know how to, or if it's possible to
> attach the backup MDF & LDF files and then somehow apply my
> transaction log backups to build a new up to date database.
> Thanks,
> Alex
>
Restoring Transaction Logs Into Backups of MDF and LDF
Hello,
Here's a brief outline of my scenario:
I am trying to recover from a database corruption problem. I have a
backup of the db's physical MDF and LDF files from before the
corruption, but no actual backup (ie bak file). I also have daily
transaction log backups right up to today, and my live db is still
operation despite the corruption, so I can take another transaction
log backup when I do the repair.
The problem I have is I don't know how to, or if it's possible to
attach the backup MDF & LDF files and then somehow apply my
transaction log backups to build a new up to date database.
Thanks,
AlexYou would need Attach with NORECOVERY, which currently is not a possibility.
There is a connect item you can vote in for a future feature request.
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=270651
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
<cosmasdev@.yahoo.com> wrote in message
news:1177078845.141070.271000@.n59g2000hsh.googlegroups.com...
> Hello,
> Here's a brief outline of my scenario:
> I am trying to recover from a database corruption problem. I have a
> backup of the db's physical MDF and LDF files from before the
> corruption, but no actual backup (ie bak file). I also have daily
> transaction log backups right up to today, and my live db is still
> operation despite the corruption, so I can take another transaction
> log backup when I do the repair.
> The problem I have is I don't know how to, or if it's possible to
> attach the backup MDF & LDF files and then somehow apply my
> transaction log backups to build a new up to date database.
> Thanks,
> Alex
>|||Alex,
If you don't mind my asking, what kind of corruption did you get and how did
it happen. We're just moving to SQLServer to avoid corruptions and I'd hate
to find that we did not leave the problem behind.
Regards
Brian Morris
<cosmasdev@.yahoo.com> wrote in message
news:1177078845.141070.271000@.n59g2000hsh.googlegroups.com...
> Hello,
> Here's a brief outline of my scenario:
> I am trying to recover from a database corruption problem. I have a
> backup of the db's physical MDF and LDF files from before the
> corruption, but no actual backup (ie bak file). I also have daily
> transaction log backups right up to today, and my live db is still
> operation despite the corruption, so I can take another transaction
> log backup when I do the repair.
> The problem I have is I don't know how to, or if it's possible to
> attach the backup MDF & LDF files and then somehow apply my
> transaction log backups to build a new up to date database.
> Thanks,
> Alex
>
Here's a brief outline of my scenario:
I am trying to recover from a database corruption problem. I have a
backup of the db's physical MDF and LDF files from before the
corruption, but no actual backup (ie bak file). I also have daily
transaction log backups right up to today, and my live db is still
operation despite the corruption, so I can take another transaction
log backup when I do the repair.
The problem I have is I don't know how to, or if it's possible to
attach the backup MDF & LDF files and then somehow apply my
transaction log backups to build a new up to date database.
Thanks,
AlexYou would need Attach with NORECOVERY, which currently is not a possibility.
There is a connect item you can vote in for a future feature request.
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=270651
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
<cosmasdev@.yahoo.com> wrote in message
news:1177078845.141070.271000@.n59g2000hsh.googlegroups.com...
> Hello,
> Here's a brief outline of my scenario:
> I am trying to recover from a database corruption problem. I have a
> backup of the db's physical MDF and LDF files from before the
> corruption, but no actual backup (ie bak file). I also have daily
> transaction log backups right up to today, and my live db is still
> operation despite the corruption, so I can take another transaction
> log backup when I do the repair.
> The problem I have is I don't know how to, or if it's possible to
> attach the backup MDF & LDF files and then somehow apply my
> transaction log backups to build a new up to date database.
> Thanks,
> Alex
>|||Alex,
If you don't mind my asking, what kind of corruption did you get and how did
it happen. We're just moving to SQLServer to avoid corruptions and I'd hate
to find that we did not leave the problem behind.
Regards
Brian Morris
<cosmasdev@.yahoo.com> wrote in message
news:1177078845.141070.271000@.n59g2000hsh.googlegroups.com...
> Hello,
> Here's a brief outline of my scenario:
> I am trying to recover from a database corruption problem. I have a
> backup of the db's physical MDF and LDF files from before the
> corruption, but no actual backup (ie bak file). I also have daily
> transaction log backups right up to today, and my live db is still
> operation despite the corruption, so I can take another transaction
> log backup when I do the repair.
> The problem I have is I don't know how to, or if it's possible to
> attach the backup MDF & LDF files and then somehow apply my
> transaction log backups to build a new up to date database.
> Thanks,
> Alex
>
Restoring Transaction Logs
I have a customer that needs to restore a database to
midnite on 12/31. They have a full database backup at
3pm, 2 transaction log without truncate backups and a
transaction log with truncate at midnite. I would assume
that you would run a restore on the full database backup
and then run a restore on the midnite trans log with
truncate with recover database selected. Would it be
necessary to include any of the transaction logs without
tuncate in the restore?
What is confusing to me is the transaction log backups
keep decreasing in size. I would think that if you don't
truncate, the sizes of the transaction log backups would
keep increasing until you truncated. Any help would be
appriciated.
ZackIn principle you will need to restore the most recent full db backup, =then ALL log backups since the full backup. The cause of your confusion =is (I guess) that backing up a log makes the space available for re-use, =The final log backup (that you describe as "transaction log with =truncate at midnite" is I expect not really a log backup at all, can you =confirm if this was taken via the SQL statement BACKUP LOG ... WITH =TRUNCATE ONLY, if so it has truncated the log not backed it up. In which =case the best you can get is to restore the full backup (WITH =NORECOVERY), the first log (WITH NORECOVERY) and the second log (WITH =RECOVERY).
If youre midnight log backup is a 'TRUNCATE ONLY" you are expposed from =a recovery point of view until 3pm the following day, since once the log =has been truncated it cannot be used to rollforward transactions until =the next full backup has been taken.
Hope that helps
Mike John
"Zack Godwin" <zgodwin@.freightsystemsinc.com> wrote in message =news:052b01c3d46b$0a7254c0$a001280a@.phx.gbl...
> I have a customer that needs to restore a database to > midnite on 12/31. They have a full database backup at > 3pm, 2 transaction log without truncate backups and a > transaction log with truncate at midnite. I would assume > that you would run a restore on the full database backup > and then run a restore on the midnite trans log with > truncate with recover database selected. Would it be > necessary to include any of the transaction logs without > tuncate in the restore? > > What is confusing to me is the transaction log backups > keep decreasing in size. I would think that if you don't > truncate, the sizes of the transaction log backups would > keep increasing until you truncated. Any help would be > appriciated.
> > Zack
midnite on 12/31. They have a full database backup at
3pm, 2 transaction log without truncate backups and a
transaction log with truncate at midnite. I would assume
that you would run a restore on the full database backup
and then run a restore on the midnite trans log with
truncate with recover database selected. Would it be
necessary to include any of the transaction logs without
tuncate in the restore?
What is confusing to me is the transaction log backups
keep decreasing in size. I would think that if you don't
truncate, the sizes of the transaction log backups would
keep increasing until you truncated. Any help would be
appriciated.
ZackIn principle you will need to restore the most recent full db backup, =then ALL log backups since the full backup. The cause of your confusion =is (I guess) that backing up a log makes the space available for re-use, =The final log backup (that you describe as "transaction log with =truncate at midnite" is I expect not really a log backup at all, can you =confirm if this was taken via the SQL statement BACKUP LOG ... WITH =TRUNCATE ONLY, if so it has truncated the log not backed it up. In which =case the best you can get is to restore the full backup (WITH =NORECOVERY), the first log (WITH NORECOVERY) and the second log (WITH =RECOVERY).
If youre midnight log backup is a 'TRUNCATE ONLY" you are expposed from =a recovery point of view until 3pm the following day, since once the log =has been truncated it cannot be used to rollforward transactions until =the next full backup has been taken.
Hope that helps
Mike John
"Zack Godwin" <zgodwin@.freightsystemsinc.com> wrote in message =news:052b01c3d46b$0a7254c0$a001280a@.phx.gbl...
> I have a customer that needs to restore a database to > midnite on 12/31. They have a full database backup at > 3pm, 2 transaction log without truncate backups and a > transaction log with truncate at midnite. I would assume > that you would run a restore on the full database backup > and then run a restore on the midnite trans log with > truncate with recover database selected. Would it be > necessary to include any of the transaction logs without > tuncate in the restore? > > What is confusing to me is the transaction log backups > keep decreasing in size. I would think that if you don't > truncate, the sizes of the transaction log backups would > keep increasing until you truncated. Any help would be > appriciated.
> > Zack
Restoring Transaction log
Hi everyone,
First off, any help will be appreciated.
The scenario is that a chunk of data from one of the tables in a
database have been mistakenly deleted. I have done a complete backup of
the database and the backed up the log. I attempted restoring the
Database first with teh Norecpvery option and that worked. when I tried
restoring the log file with the STOPAT clause I get an error. I am
reproducing the script at the error below. Please help if you can
'Restoring the Database - Successful, with message shown below
RESTORE DATABASE MatriEdu2006
FROM DISK = 'C:\EducMatri06.db' WITH NORECOVERY,
MOVE 'EduMatri_Data' TO 'C:\MatriEdu2006.mdf',
MOVE 'EduMatri_Log' TO 'C:\MatriEdu2006.ldf'
Processed 920 pages for database 'MatriEdu2006', file 'Edu_Data' on
file 1.
Processed 1 pages for database 'MatriEdu2006', file 'Edu_Log' on file
1.
RESTORE DATABASE successfully processed 921 pages in 1.114 seconds
(6.766 MB/sec).
'Restore Log - Failed, with error message shown below
RESTORE LOG MatriEdu2006
FROM DISK = 'C:\EducationMatrix_Log'
WITH RECOVERY, STOPAT = 'Dec 11, 2006 03:14 PM'
Server: Msg 4326, Level 16, State 1, Line 1
The log in this backup set terminates at LSN 537000000112000001, which
is too early to apply to the database. A more recent log backup that
includes LSN 553000000066000001 can be restored.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
>>> On 12/10/2006 at 5:40 PM, in message
<1165797639.616739.281890@.80g2000cwy.googlegroups. com>,
highflier<nbarnard@.au.loreal.com> wrote:
> Server: Msg 4326, Level 16, State 1, Line 1
> The log in this backup set terminates at LSN 537000000112000001,
> which
> is too early to apply to the database. A more recent log backup that
> includes LSN 553000000066000001 can be restored.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE LOG is terminating abnormally.
You need to find a full backup made *before* the data loss, along with
all the logs made between that full and the time of the data loss.
|||This also indicates that something else is wrong here. If an LSN terminates
with a number that precedes the LSN of an earlier transaction log, that
transaction log needs to be skipped during a restore.
In your case below, the 537000000112000001 does exactly this so figure out
which TRN this is and when you run the restore and it comes time to restore
from this TRN log, skip it and go on to the next one. The database choked
on a transaction before one log completed but fixed itself during the next
one so all the stuff in that log is not needed as the transaction completed
during the execution of the next log.
Regards,
Jamie
"highflier" wrote:
> Hi everyone,
> First off, any help will be appreciated.
> The scenario is that a chunk of data from one of the tables in a
> database have been mistakenly deleted. I have done a complete backup of
> the database and the backed up the log. I attempted restoring the
> Database first with teh Norecpvery option and that worked. when I tried
> restoring the log file with the STOPAT clause I get an error. I am
> reproducing the script at the error below. Please help if you can
> 'Restoring the Database - Successful, with message shown below
> RESTORE DATABASE MatriEdu2006
> FROM DISK = 'C:\EducMatri06.db' WITH NORECOVERY,
> MOVE 'EduMatri_Data' TO 'C:\MatriEdu2006.mdf',
> MOVE 'EduMatri_Log' TO 'C:\MatriEdu2006.ldf'
> Processed 920 pages for database 'MatriEdu2006', file 'Edu_Data' on
> file 1.
> Processed 1 pages for database 'MatriEdu2006', file 'Edu_Log' on file
> 1.
> RESTORE DATABASE successfully processed 921 pages in 1.114 seconds
> (6.766 MB/sec).
>
> 'Restore Log - Failed, with error message shown below
> RESTORE LOG MatriEdu2006
> FROM DISK = 'C:\EducationMatrix_Log'
> WITH RECOVERY, STOPAT = 'Dec 11, 2006 03:14 PM'
> Server: Msg 4326, Level 16, State 1, Line 1
> The log in this backup set terminates at LSN 537000000112000001, which
> is too early to apply to the database. A more recent log backup that
> includes LSN 553000000066000001 can be restored.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE LOG is terminating abnormally.
>
sql
First off, any help will be appreciated.
The scenario is that a chunk of data from one of the tables in a
database have been mistakenly deleted. I have done a complete backup of
the database and the backed up the log. I attempted restoring the
Database first with teh Norecpvery option and that worked. when I tried
restoring the log file with the STOPAT clause I get an error. I am
reproducing the script at the error below. Please help if you can
'Restoring the Database - Successful, with message shown below
RESTORE DATABASE MatriEdu2006
FROM DISK = 'C:\EducMatri06.db' WITH NORECOVERY,
MOVE 'EduMatri_Data' TO 'C:\MatriEdu2006.mdf',
MOVE 'EduMatri_Log' TO 'C:\MatriEdu2006.ldf'
Processed 920 pages for database 'MatriEdu2006', file 'Edu_Data' on
file 1.
Processed 1 pages for database 'MatriEdu2006', file 'Edu_Log' on file
1.
RESTORE DATABASE successfully processed 921 pages in 1.114 seconds
(6.766 MB/sec).
'Restore Log - Failed, with error message shown below
RESTORE LOG MatriEdu2006
FROM DISK = 'C:\EducationMatrix_Log'
WITH RECOVERY, STOPAT = 'Dec 11, 2006 03:14 PM'
Server: Msg 4326, Level 16, State 1, Line 1
The log in this backup set terminates at LSN 537000000112000001, which
is too early to apply to the database. A more recent log backup that
includes LSN 553000000066000001 can be restored.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
>>> On 12/10/2006 at 5:40 PM, in message
<1165797639.616739.281890@.80g2000cwy.googlegroups. com>,
highflier<nbarnard@.au.loreal.com> wrote:
> Server: Msg 4326, Level 16, State 1, Line 1
> The log in this backup set terminates at LSN 537000000112000001,
> which
> is too early to apply to the database. A more recent log backup that
> includes LSN 553000000066000001 can be restored.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE LOG is terminating abnormally.
You need to find a full backup made *before* the data loss, along with
all the logs made between that full and the time of the data loss.
|||This also indicates that something else is wrong here. If an LSN terminates
with a number that precedes the LSN of an earlier transaction log, that
transaction log needs to be skipped during a restore.
In your case below, the 537000000112000001 does exactly this so figure out
which TRN this is and when you run the restore and it comes time to restore
from this TRN log, skip it and go on to the next one. The database choked
on a transaction before one log completed but fixed itself during the next
one so all the stuff in that log is not needed as the transaction completed
during the execution of the next log.
Regards,
Jamie
"highflier" wrote:
> Hi everyone,
> First off, any help will be appreciated.
> The scenario is that a chunk of data from one of the tables in a
> database have been mistakenly deleted. I have done a complete backup of
> the database and the backed up the log. I attempted restoring the
> Database first with teh Norecpvery option and that worked. when I tried
> restoring the log file with the STOPAT clause I get an error. I am
> reproducing the script at the error below. Please help if you can
> 'Restoring the Database - Successful, with message shown below
> RESTORE DATABASE MatriEdu2006
> FROM DISK = 'C:\EducMatri06.db' WITH NORECOVERY,
> MOVE 'EduMatri_Data' TO 'C:\MatriEdu2006.mdf',
> MOVE 'EduMatri_Log' TO 'C:\MatriEdu2006.ldf'
> Processed 920 pages for database 'MatriEdu2006', file 'Edu_Data' on
> file 1.
> Processed 1 pages for database 'MatriEdu2006', file 'Edu_Log' on file
> 1.
> RESTORE DATABASE successfully processed 921 pages in 1.114 seconds
> (6.766 MB/sec).
>
> 'Restore Log - Failed, with error message shown below
> RESTORE LOG MatriEdu2006
> FROM DISK = 'C:\EducationMatrix_Log'
> WITH RECOVERY, STOPAT = 'Dec 11, 2006 03:14 PM'
> Server: Msg 4326, Level 16, State 1, Line 1
> The log in this backup set terminates at LSN 537000000112000001, which
> is too early to apply to the database. A more recent log backup that
> includes LSN 553000000066000001 can be restored.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE LOG is terminating abnormally.
>
sql
Restoring Transaction log
Hi everyone,
First off, any help will be appreciated.
The scenario is that a chunk of data from one of the tables in a
database have been mistakenly deleted. I have done a complete backup of
the database and the backed up the log. I attempted restoring the
Database first with teh Norecpvery option and that worked. when I tried
restoring the log file with the STOPAT clause I get an error. I am
reproducing the script at the error below. Please help if you can
'Restoring the Database - Successful, with message shown below
RESTORE DATABASE MatriEdu2006
FROM DISK = 'C:\EducMatri06.db' WITH NORECOVERY,
MOVE 'EduMatri_Data' TO 'C:\MatriEdu2006.mdf',
MOVE 'EduMatri_Log' TO 'C:\MatriEdu2006.ldf'
Processed 920 pages for database 'MatriEdu2006', file 'Edu_Data' on
file 1.
Processed 1 pages for database 'MatriEdu2006', file 'Edu_Log' on file
1.
RESTORE DATABASE successfully processed 921 pages in 1.114 seconds
(6.766 MB/sec).
'Restore Log - Failed, with error message shown below
RESTORE LOG MatriEdu2006
FROM DISK = 'C:\EducationMatrix_Log'
WITH RECOVERY, STOPAT = 'Dec 11, 2006 03:14 PM'
Server: Msg 4326, Level 16, State 1, Line 1
The log in this backup set terminates at LSN 537000000112000001, which
is too early to apply to the database. A more recent log backup that
includes LSN 553000000066000001 can be restored.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.>>> On 12/10/2006 at 5:40 PM, in message
<1165797639.616739.281890@.80g2000cwy.googlegroups.com>,
highflier<nbarnard@.au.loreal.com> wrote:
> Server: Msg 4326, Level 16, State 1, Line 1
> The log in this backup set terminates at LSN 537000000112000001,
> which
> is too early to apply to the database. A more recent log backup that
> includes LSN 553000000066000001 can be restored.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE LOG is terminating abnormally.
You need to find a full backup made *before* the data loss, along with
all the logs made between that full and the time of the data loss.|||This also indicates that something else is wrong here. If an LSN terminates
with a number that precedes the LSN of an earlier transaction log, that
transaction log needs to be skipped during a restore.
In your case below, the 537000000112000001 does exactly this so figure out
which TRN this is and when you run the restore and it comes time to restore
from this TRN log, skip it and go on to the next one. The database choked
on a transaction before one log completed but fixed itself during the next
one so all the stuff in that log is not needed as the transaction completed
during the execution of the next log.
Regards,
Jamie
"highflier" wrote:
> Hi everyone,
> First off, any help will be appreciated.
> The scenario is that a chunk of data from one of the tables in a
> database have been mistakenly deleted. I have done a complete backup of
> the database and the backed up the log. I attempted restoring the
> Database first with teh Norecpvery option and that worked. when I tried
> restoring the log file with the STOPAT clause I get an error. I am
> reproducing the script at the error below. Please help if you can
> 'Restoring the Database - Successful, with message shown below
> RESTORE DATABASE MatriEdu2006
> FROM DISK = 'C:\EducMatri06.db' WITH NORECOVERY,
> MOVE 'EduMatri_Data' TO 'C:\MatriEdu2006.mdf',
> MOVE 'EduMatri_Log' TO 'C:\MatriEdu2006.ldf'
> Processed 920 pages for database 'MatriEdu2006', file 'Edu_Data' on
> file 1.
> Processed 1 pages for database 'MatriEdu2006', file 'Edu_Log' on file
> 1.
> RESTORE DATABASE successfully processed 921 pages in 1.114 seconds
> (6.766 MB/sec).
>
> 'Restore Log - Failed, with error message shown below
> RESTORE LOG MatriEdu2006
> FROM DISK = 'C:\EducationMatrix_Log'
> WITH RECOVERY, STOPAT = 'Dec 11, 2006 03:14 PM'
> Server: Msg 4326, Level 16, State 1, Line 1
> The log in this backup set terminates at LSN 537000000112000001, which
> is too early to apply to the database. A more recent log backup that
> includes LSN 553000000066000001 can be restored.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE LOG is terminating abnormally.
>
First off, any help will be appreciated.
The scenario is that a chunk of data from one of the tables in a
database have been mistakenly deleted. I have done a complete backup of
the database and the backed up the log. I attempted restoring the
Database first with teh Norecpvery option and that worked. when I tried
restoring the log file with the STOPAT clause I get an error. I am
reproducing the script at the error below. Please help if you can
'Restoring the Database - Successful, with message shown below
RESTORE DATABASE MatriEdu2006
FROM DISK = 'C:\EducMatri06.db' WITH NORECOVERY,
MOVE 'EduMatri_Data' TO 'C:\MatriEdu2006.mdf',
MOVE 'EduMatri_Log' TO 'C:\MatriEdu2006.ldf'
Processed 920 pages for database 'MatriEdu2006', file 'Edu_Data' on
file 1.
Processed 1 pages for database 'MatriEdu2006', file 'Edu_Log' on file
1.
RESTORE DATABASE successfully processed 921 pages in 1.114 seconds
(6.766 MB/sec).
'Restore Log - Failed, with error message shown below
RESTORE LOG MatriEdu2006
FROM DISK = 'C:\EducationMatrix_Log'
WITH RECOVERY, STOPAT = 'Dec 11, 2006 03:14 PM'
Server: Msg 4326, Level 16, State 1, Line 1
The log in this backup set terminates at LSN 537000000112000001, which
is too early to apply to the database. A more recent log backup that
includes LSN 553000000066000001 can be restored.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.>>> On 12/10/2006 at 5:40 PM, in message
<1165797639.616739.281890@.80g2000cwy.googlegroups.com>,
highflier<nbarnard@.au.loreal.com> wrote:
> Server: Msg 4326, Level 16, State 1, Line 1
> The log in this backup set terminates at LSN 537000000112000001,
> which
> is too early to apply to the database. A more recent log backup that
> includes LSN 553000000066000001 can be restored.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE LOG is terminating abnormally.
You need to find a full backup made *before* the data loss, along with
all the logs made between that full and the time of the data loss.|||This also indicates that something else is wrong here. If an LSN terminates
with a number that precedes the LSN of an earlier transaction log, that
transaction log needs to be skipped during a restore.
In your case below, the 537000000112000001 does exactly this so figure out
which TRN this is and when you run the restore and it comes time to restore
from this TRN log, skip it and go on to the next one. The database choked
on a transaction before one log completed but fixed itself during the next
one so all the stuff in that log is not needed as the transaction completed
during the execution of the next log.
Regards,
Jamie
"highflier" wrote:
> Hi everyone,
> First off, any help will be appreciated.
> The scenario is that a chunk of data from one of the tables in a
> database have been mistakenly deleted. I have done a complete backup of
> the database and the backed up the log. I attempted restoring the
> Database first with teh Norecpvery option and that worked. when I tried
> restoring the log file with the STOPAT clause I get an error. I am
> reproducing the script at the error below. Please help if you can
> 'Restoring the Database - Successful, with message shown below
> RESTORE DATABASE MatriEdu2006
> FROM DISK = 'C:\EducMatri06.db' WITH NORECOVERY,
> MOVE 'EduMatri_Data' TO 'C:\MatriEdu2006.mdf',
> MOVE 'EduMatri_Log' TO 'C:\MatriEdu2006.ldf'
> Processed 920 pages for database 'MatriEdu2006', file 'Edu_Data' on
> file 1.
> Processed 1 pages for database 'MatriEdu2006', file 'Edu_Log' on file
> 1.
> RESTORE DATABASE successfully processed 921 pages in 1.114 seconds
> (6.766 MB/sec).
>
> 'Restore Log - Failed, with error message shown below
> RESTORE LOG MatriEdu2006
> FROM DISK = 'C:\EducationMatrix_Log'
> WITH RECOVERY, STOPAT = 'Dec 11, 2006 03:14 PM'
> Server: Msg 4326, Level 16, State 1, Line 1
> The log in this backup set terminates at LSN 537000000112000001, which
> is too early to apply to the database. A more recent log backup that
> includes LSN 553000000066000001 can be restored.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE LOG is terminating abnormally.
>
Restoring Transaction log
Hi everyone,
First off, any help will be appreciated.
The scenario is that a chunk of data from one of the tables in a
database have been mistakenly deleted. I have done a complete backup of
the database and the backed up the log. I attempted restoring the
Database first with teh Norecpvery option and that worked. when I tried
restoring the log file with the STOPAT clause I get an error. I am
reproducing the script at the error below. Please help if you can
'Restoring the Database - Successful, with message shown below
RESTORE DATABASE MatriEdu2006
FROM DISK = 'C:\EducMatri06.db' WITH NORECOVERY,
MOVE 'EduMatri_Data' TO 'C:\MatriEdu2006.mdf',
MOVE 'EduMatri_Log' TO 'C:\MatriEdu2006.ldf'
Processed 920 pages for database 'MatriEdu2006', file 'Edu_Data' on
file 1.
Processed 1 pages for database 'MatriEdu2006', file 'Edu_Log' on file
1.
RESTORE DATABASE successfully processed 921 pages in 1.114 seconds
(6.766 MB/sec).
'Restore Log - Failed, with error message shown below
RESTORE LOG MatriEdu2006
FROM DISK = 'C:\EducationMatrix_Log'
WITH RECOVERY, STOPAT = 'Dec 11, 2006 03:14 PM'
Server: Msg 4326, Level 16, State 1, Line 1
The log in this backup set terminates at LSN 537000000112000001, which
is too early to apply to the database. A more recent log backup that
includes LSN 553000000066000001 can be restored.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.>> On 12/10/2006 at 5:40 PM, in message
<1165797639.616739.281890@.80g2000cwy.googlegroups.com>,
highflier<nbarnard@.au.loreal.com> wrote:
> Server: Msg 4326, Level 16, State 1, Line 1
> The log in this backup set terminates at LSN 537000000112000001,
> which
> is too early to apply to the database. A more recent log backup that
> includes LSN 553000000066000001 can be restored.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE LOG is terminating abnormally.
You need to find a full backup made *before* the data loss, along with
all the logs made between that full and the time of the data loss.
First off, any help will be appreciated.
The scenario is that a chunk of data from one of the tables in a
database have been mistakenly deleted. I have done a complete backup of
the database and the backed up the log. I attempted restoring the
Database first with teh Norecpvery option and that worked. when I tried
restoring the log file with the STOPAT clause I get an error. I am
reproducing the script at the error below. Please help if you can
'Restoring the Database - Successful, with message shown below
RESTORE DATABASE MatriEdu2006
FROM DISK = 'C:\EducMatri06.db' WITH NORECOVERY,
MOVE 'EduMatri_Data' TO 'C:\MatriEdu2006.mdf',
MOVE 'EduMatri_Log' TO 'C:\MatriEdu2006.ldf'
Processed 920 pages for database 'MatriEdu2006', file 'Edu_Data' on
file 1.
Processed 1 pages for database 'MatriEdu2006', file 'Edu_Log' on file
1.
RESTORE DATABASE successfully processed 921 pages in 1.114 seconds
(6.766 MB/sec).
'Restore Log - Failed, with error message shown below
RESTORE LOG MatriEdu2006
FROM DISK = 'C:\EducationMatrix_Log'
WITH RECOVERY, STOPAT = 'Dec 11, 2006 03:14 PM'
Server: Msg 4326, Level 16, State 1, Line 1
The log in this backup set terminates at LSN 537000000112000001, which
is too early to apply to the database. A more recent log backup that
includes LSN 553000000066000001 can be restored.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.>> On 12/10/2006 at 5:40 PM, in message
<1165797639.616739.281890@.80g2000cwy.googlegroups.com>,
highflier<nbarnard@.au.loreal.com> wrote:
> Server: Msg 4326, Level 16, State 1, Line 1
> The log in this backup set terminates at LSN 537000000112000001,
> which
> is too early to apply to the database. A more recent log backup that
> includes LSN 553000000066000001 can be restored.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE LOG is terminating abnormally.
You need to find a full backup made *before* the data loss, along with
all the logs made between that full and the time of the data loss.
Restoring Tran. Log files
I need to restore a database on my test server from that on my production
server.
Here's what i've done so far:
1) Created a new db on my testing server named 'main_test'
2) Restored 'main_test' using .bak file from my production db.
Now, I have several transaction log files I wan to incorporate as well.
When I use Enterprise Manager and select my first trn log file and select
'Transaction Log' on the 'General' tab, I get the following error:
"The preceeding resotre operation did not specify WITH NORECOVERY or WITH
STANDBY....."
Should I use Query Analyzer for this? What is the syntax?Eric,
After applying the trnasaction log, go to "options" tab and select "Leave
database nonoperational, but able to restore additional transaction logs", o
r
"Leave database operational. No additional transaction logs can be restored"
if you are restoring the last transaction log backup.
AMB
"Eric" wrote:
> I need to restore a database on my test server from that on my production
> server.
> Here's what i've done so far:
> 1) Created a new db on my testing server named 'main_test'
> 2) Restored 'main_test' using .bak file from my production db.
> Now, I have several transaction log files I wan to incorporate as well.
> When I use Enterprise Manager and select my first trn log file and select
> 'Transaction Log' on the 'General' tab, I get the following error:
> "The preceeding resotre operation did not specify WITH NORECOVERY or WITH
> STANDBY....."
> Should I use Query Analyzer for this? What is the syntax?
>|||Correction,
> After applying the trnasaction log, go to "options" tab and select "Leave
Before applying ...
AMB
"Alejandro Mesa" wrote:
> Eric,
> After applying the trnasaction log, go to "options" tab and select "Leave
> database nonoperational, but able to restore additional transaction logs",
or
> "Leave database operational. No additional transaction logs can be restore
d"
> if you are restoring the last transaction log backup.
>
> AMB
> "Eric" wrote:
>
server.
Here's what i've done so far:
1) Created a new db on my testing server named 'main_test'
2) Restored 'main_test' using .bak file from my production db.
Now, I have several transaction log files I wan to incorporate as well.
When I use Enterprise Manager and select my first trn log file and select
'Transaction Log' on the 'General' tab, I get the following error:
"The preceeding resotre operation did not specify WITH NORECOVERY or WITH
STANDBY....."
Should I use Query Analyzer for this? What is the syntax?Eric,
After applying the trnasaction log, go to "options" tab and select "Leave
database nonoperational, but able to restore additional transaction logs", o
r
"Leave database operational. No additional transaction logs can be restored"
if you are restoring the last transaction log backup.
AMB
"Eric" wrote:
> I need to restore a database on my test server from that on my production
> server.
> Here's what i've done so far:
> 1) Created a new db on my testing server named 'main_test'
> 2) Restored 'main_test' using .bak file from my production db.
> Now, I have several transaction log files I wan to incorporate as well.
> When I use Enterprise Manager and select my first trn log file and select
> 'Transaction Log' on the 'General' tab, I get the following error:
> "The preceeding resotre operation did not specify WITH NORECOVERY or WITH
> STANDBY....."
> Should I use Query Analyzer for this? What is the syntax?
>|||Correction,
> After applying the trnasaction log, go to "options" tab and select "Leave
Before applying ...
AMB
"Alejandro Mesa" wrote:
> Eric,
> After applying the trnasaction log, go to "options" tab and select "Leave
> database nonoperational, but able to restore additional transaction logs",
or
> "Leave database operational. No additional transaction logs can be restore
d"
> if you are restoring the last transaction log backup.
>
> AMB
> "Eric" wrote:
>
Restoring to remove standy/read-only mode.
Hi all,
I have a database that is restored with the following command:
RESTORE DATABASE [<databasename>] FROM DISK=
'\\<path>\<databasename>_diff.bak' WITH MOVE '<databasename>_Data' TO
'C:\<databasename>_Data.mdf', MOVE '<databasename>_Log' TO
'C:\<databasename>_Log.ldf', STANDBY = 'C:\<databasename>_standby.rdo'
So, the database is in standy/read-only mode. Now, I want to make sure that
I can change it over so that it's not longer in standy/read-only mode. Is
the following synatx/script correct?
RESTORE DATABASE [<databasename>] WITH RECOVERY
Is that enough? It seems to work, but I'm not sure if I'm missing an
important element.
Thanks!
Wade
That is all you need. Note that once it is recovered you cannot apply
subsequent transaction log or differential backups.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Wade" <wwegner23NOEMAILhotmail.com> wrote in message
news:e9PjMuYrFHA.2072@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> I have a database that is restored with the following command:
> RESTORE DATABASE [<databasename>] FROM DISK=
> '\\<path>\<databasename>_diff.bak' WITH MOVE '<databasename>_Data' TO
> 'C:\<databasename>_Data.mdf', MOVE '<databasename>_Log' TO
> 'C:\<databasename>_Log.ldf', STANDBY = 'C:\<databasename>_standby.rdo'
> So, the database is in standy/read-only mode. Now, I want to make sure
> that I can change it over so that it's not longer in standy/read-only
> mode. Is the following synatx/script correct?
> RESTORE DATABASE [<databasename>] WITH RECOVERY
> Is that enough? It seems to work, but I'm not sure if I'm missing an
> important element.
> Thanks!
> Wade
>
|||Yes, excellent -- thanks!
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:eOaFmBZrFHA.1984@.tk2msftngp13.phx.gbl...
> That is all you need. Note that once it is recovered you cannot apply
> subsequent transaction log or differential backups.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Wade" <wwegner23NOEMAILhotmail.com> wrote in message
> news:e9PjMuYrFHA.2072@.TK2MSFTNGP14.phx.gbl...
>
|||Another question ... is there a query I can use to determine if the database
is in standby/read-only mode? This way I can turn off my diff and trans
restores.
Thanks!
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:eOaFmBZrFHA.1984@.tk2msftngp13.phx.gbl...
> That is all you need. Note that once it is recovered you cannot apply
> subsequent transaction log or differential backups.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Wade" <wwegner23NOEMAILhotmail.com> wrote in message
> news:e9PjMuYrFHA.2072@.TK2MSFTNGP14.phx.gbl...
>
|||You continue to do diff and/or log restores while the database is in
standby. You stop the restores when it is recovered.
The information you seek is in the master.dbo.sysdatabases table,
specifically the status column. Look up sysdatabases in BOL for what the
status bit flags mean.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Wade" <wwegner23NOEMAILhotmail.com> wrote in message
news:OI8mAJZrFHA.248@.TK2MSFTNGP14.phx.gbl...
> Another question ... is there a query I can use to determine if the
> database is in standby/read-only mode? This way I can turn off my diff
> and trans restores.
> Thanks!
> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
> news:eOaFmBZrFHA.1984@.tk2msftngp13.phx.gbl...
>
|||Thanks, I already found it:
use [master]
select name, DATABASEPROPERTY(name, N'IsReadOnly') from
master.dbo.sysdatabases
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:uvylHUZrFHA.2008@.TK2MSFTNGP10.phx.gbl...
> You continue to do diff and/or log restores while the database is in
> standby. You stop the restores when it is recovered.
> The information you seek is in the master.dbo.sysdatabases table,
> specifically the status column. Look up sysdatabases in BOL for what the
> status bit flags mean.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Wade" <wwegner23NOEMAILhotmail.com> wrote in message
> news:OI8mAJZrFHA.248@.TK2MSFTNGP14.phx.gbl...
>
|||FYI, DATABASEPROPERTYEX() is a newer version of DATABASEPROPERTY()
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Wade" <wwegner23NOEMAILhotmail.com> wrote in message news:OySXXXZrFHA.3424@.TK2MSFTNGP14.phx.gbl...
> Thanks, I already found it:
> use [master]
> select name, DATABASEPROPERTY(name, N'IsReadOnly') from
> master.dbo.sysdatabases
> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
> news:uvylHUZrFHA.2008@.TK2MSFTNGP10.phx.gbl...
>
I have a database that is restored with the following command:
RESTORE DATABASE [<databasename>] FROM DISK=
'\\<path>\<databasename>_diff.bak' WITH MOVE '<databasename>_Data' TO
'C:\<databasename>_Data.mdf', MOVE '<databasename>_Log' TO
'C:\<databasename>_Log.ldf', STANDBY = 'C:\<databasename>_standby.rdo'
So, the database is in standy/read-only mode. Now, I want to make sure that
I can change it over so that it's not longer in standy/read-only mode. Is
the following synatx/script correct?
RESTORE DATABASE [<databasename>] WITH RECOVERY
Is that enough? It seems to work, but I'm not sure if I'm missing an
important element.
Thanks!
Wade
That is all you need. Note that once it is recovered you cannot apply
subsequent transaction log or differential backups.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Wade" <wwegner23NOEMAILhotmail.com> wrote in message
news:e9PjMuYrFHA.2072@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> I have a database that is restored with the following command:
> RESTORE DATABASE [<databasename>] FROM DISK=
> '\\<path>\<databasename>_diff.bak' WITH MOVE '<databasename>_Data' TO
> 'C:\<databasename>_Data.mdf', MOVE '<databasename>_Log' TO
> 'C:\<databasename>_Log.ldf', STANDBY = 'C:\<databasename>_standby.rdo'
> So, the database is in standy/read-only mode. Now, I want to make sure
> that I can change it over so that it's not longer in standy/read-only
> mode. Is the following synatx/script correct?
> RESTORE DATABASE [<databasename>] WITH RECOVERY
> Is that enough? It seems to work, but I'm not sure if I'm missing an
> important element.
> Thanks!
> Wade
>
|||Yes, excellent -- thanks!
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:eOaFmBZrFHA.1984@.tk2msftngp13.phx.gbl...
> That is all you need. Note that once it is recovered you cannot apply
> subsequent transaction log or differential backups.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Wade" <wwegner23NOEMAILhotmail.com> wrote in message
> news:e9PjMuYrFHA.2072@.TK2MSFTNGP14.phx.gbl...
>
|||Another question ... is there a query I can use to determine if the database
is in standby/read-only mode? This way I can turn off my diff and trans
restores.
Thanks!
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:eOaFmBZrFHA.1984@.tk2msftngp13.phx.gbl...
> That is all you need. Note that once it is recovered you cannot apply
> subsequent transaction log or differential backups.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Wade" <wwegner23NOEMAILhotmail.com> wrote in message
> news:e9PjMuYrFHA.2072@.TK2MSFTNGP14.phx.gbl...
>
|||You continue to do diff and/or log restores while the database is in
standby. You stop the restores when it is recovered.
The information you seek is in the master.dbo.sysdatabases table,
specifically the status column. Look up sysdatabases in BOL for what the
status bit flags mean.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Wade" <wwegner23NOEMAILhotmail.com> wrote in message
news:OI8mAJZrFHA.248@.TK2MSFTNGP14.phx.gbl...
> Another question ... is there a query I can use to determine if the
> database is in standby/read-only mode? This way I can turn off my diff
> and trans restores.
> Thanks!
> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
> news:eOaFmBZrFHA.1984@.tk2msftngp13.phx.gbl...
>
|||Thanks, I already found it:
use [master]
select name, DATABASEPROPERTY(name, N'IsReadOnly') from
master.dbo.sysdatabases
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:uvylHUZrFHA.2008@.TK2MSFTNGP10.phx.gbl...
> You continue to do diff and/or log restores while the database is in
> standby. You stop the restores when it is recovered.
> The information you seek is in the master.dbo.sysdatabases table,
> specifically the status column. Look up sysdatabases in BOL for what the
> status bit flags mean.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Wade" <wwegner23NOEMAILhotmail.com> wrote in message
> news:OI8mAJZrFHA.248@.TK2MSFTNGP14.phx.gbl...
>
|||FYI, DATABASEPROPERTYEX() is a newer version of DATABASEPROPERTY()
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Wade" <wwegner23NOEMAILhotmail.com> wrote in message news:OySXXXZrFHA.3424@.TK2MSFTNGP14.phx.gbl...
> Thanks, I already found it:
> use [master]
> select name, DATABASEPROPERTY(name, N'IsReadOnly') from
> master.dbo.sysdatabases
> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
> news:uvylHUZrFHA.2008@.TK2MSFTNGP10.phx.gbl...
>
Restoring to remove standy/read-only mode.
Hi all,
I have a database that is restored with the following command:
RESTORE DATABASE [<databasename>] FROM DISK=
'\\<path>\<databasename>_diff.bak' WITH MOVE '<databasename>_Data' TO
'C:\<databasename>_Data.mdf', MOVE '<databasename>_Log' TO
'C:\<databasename>_Log.ldf', STANDBY = 'C:\<databasename>_standby.rdo'
So, the database is in standy/read-only mode. Now, I want to make sure that
I can change it over so that it's not longer in standy/read-only mode. Is
the following synatx/script correct?
RESTORE DATABASE [<databasename>] WITH RECOVERY
Is that enough? It seems to work, but I'm not sure if I'm missing an
important element.
Thanks!
WadeThat is all you need. Note that once it is recovered you cannot apply
subsequent transaction log or differential backups.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Wade" <wwegner23NOEMAILhotmail.com> wrote in message
news:e9PjMuYrFHA.2072@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> I have a database that is restored with the following command:
> RESTORE DATABASE [<databasename>] FROM DISK=
> '\\<path>\<databasename>_diff.bak' WITH MOVE '<databasename>_Data' TO
> 'C:\<databasename>_Data.mdf', MOVE '<databasename>_Log' TO
> 'C:\<databasename>_Log.ldf', STANDBY = 'C:\<databasename>_standby.rdo'
> So, the database is in standy/read-only mode. Now, I want to make sure
> that I can change it over so that it's not longer in standy/read-only
> mode. Is the following synatx/script correct?
> RESTORE DATABASE [<databasename>] WITH RECOVERY
> Is that enough? It seems to work, but I'm not sure if I'm missing an
> important element.
> Thanks!
> Wade
>|||Yes, excellent -- thanks!
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:eOaFmBZrFHA.1984@.tk2msftngp13.phx.gbl...
> That is all you need. Note that once it is recovered you cannot apply
> subsequent transaction log or differential backups.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Wade" <wwegner23NOEMAILhotmail.com> wrote in message
> news:e9PjMuYrFHA.2072@.TK2MSFTNGP14.phx.gbl...
>|||Another question ... is there a query I can use to determine if the database
is in standby/read-only mode? This way I can turn off my diff and trans
restores.
Thanks!
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:eOaFmBZrFHA.1984@.tk2msftngp13.phx.gbl...
> That is all you need. Note that once it is recovered you cannot apply
> subsequent transaction log or differential backups.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Wade" <wwegner23NOEMAILhotmail.com> wrote in message
> news:e9PjMuYrFHA.2072@.TK2MSFTNGP14.phx.gbl...
>|||You continue to do diff and/or log restores while the database is in
standby. You stop the restores when it is recovered.
The information you seek is in the master.dbo.sysdatabases table,
specifically the status column. Look up sysdatabases in BOL for what the
status bit flags mean.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Wade" <wwegner23NOEMAILhotmail.com> wrote in message
news:OI8mAJZrFHA.248@.TK2MSFTNGP14.phx.gbl...
> Another question ... is there a query I can use to determine if the
> database is in standby/read-only mode? This way I can turn off my diff
> and trans restores.
> Thanks!
> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
> news:eOaFmBZrFHA.1984@.tk2msftngp13.phx.gbl...
>|||Thanks, I already found it:
use [master]
select name, DATABASEPROPERTY(name, N'IsReadOnly') from
master.dbo.sysdatabases
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:uvylHUZrFHA.2008@.TK2MSFTNGP10.phx.gbl...
> You continue to do diff and/or log restores while the database is in
> standby. You stop the restores when it is recovered.
> The information you seek is in the master.dbo.sysdatabases table,
> specifically the status column. Look up sysdatabases in BOL for what the
> status bit flags mean.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Wade" <wwegner23NOEMAILhotmail.com> wrote in message
> news:OI8mAJZrFHA.248@.TK2MSFTNGP14.phx.gbl...
>|||FYI, DATABASEPROPERTYEX() is a newer version of DATABASEPROPERTY()
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Wade" <wwegner23NOEMAILhotmail.com> wrote in message news:OySXXXZrFHA.3424@.TK2MSFTNGP14.phx
.gbl...
> Thanks, I already found it:
> use [master]
> select name, DATABASEPROPERTY(name, N'IsReadOnly') from
> master.dbo.sysdatabases
> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
> news:uvylHUZrFHA.2008@.TK2MSFTNGP10.phx.gbl...
>sql
I have a database that is restored with the following command:
RESTORE DATABASE [<databasename>] FROM DISK=
'\\<path>\<databasename>_diff.bak' WITH MOVE '<databasename>_Data' TO
'C:\<databasename>_Data.mdf', MOVE '<databasename>_Log' TO
'C:\<databasename>_Log.ldf', STANDBY = 'C:\<databasename>_standby.rdo'
So, the database is in standy/read-only mode. Now, I want to make sure that
I can change it over so that it's not longer in standy/read-only mode. Is
the following synatx/script correct?
RESTORE DATABASE [<databasename>] WITH RECOVERY
Is that enough? It seems to work, but I'm not sure if I'm missing an
important element.
Thanks!
WadeThat is all you need. Note that once it is recovered you cannot apply
subsequent transaction log or differential backups.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Wade" <wwegner23NOEMAILhotmail.com> wrote in message
news:e9PjMuYrFHA.2072@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> I have a database that is restored with the following command:
> RESTORE DATABASE [<databasename>] FROM DISK=
> '\\<path>\<databasename>_diff.bak' WITH MOVE '<databasename>_Data' TO
> 'C:\<databasename>_Data.mdf', MOVE '<databasename>_Log' TO
> 'C:\<databasename>_Log.ldf', STANDBY = 'C:\<databasename>_standby.rdo'
> So, the database is in standy/read-only mode. Now, I want to make sure
> that I can change it over so that it's not longer in standy/read-only
> mode. Is the following synatx/script correct?
> RESTORE DATABASE [<databasename>] WITH RECOVERY
> Is that enough? It seems to work, but I'm not sure if I'm missing an
> important element.
> Thanks!
> Wade
>|||Yes, excellent -- thanks!
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:eOaFmBZrFHA.1984@.tk2msftngp13.phx.gbl...
> That is all you need. Note that once it is recovered you cannot apply
> subsequent transaction log or differential backups.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Wade" <wwegner23NOEMAILhotmail.com> wrote in message
> news:e9PjMuYrFHA.2072@.TK2MSFTNGP14.phx.gbl...
>|||Another question ... is there a query I can use to determine if the database
is in standby/read-only mode? This way I can turn off my diff and trans
restores.
Thanks!
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:eOaFmBZrFHA.1984@.tk2msftngp13.phx.gbl...
> That is all you need. Note that once it is recovered you cannot apply
> subsequent transaction log or differential backups.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Wade" <wwegner23NOEMAILhotmail.com> wrote in message
> news:e9PjMuYrFHA.2072@.TK2MSFTNGP14.phx.gbl...
>|||You continue to do diff and/or log restores while the database is in
standby. You stop the restores when it is recovered.
The information you seek is in the master.dbo.sysdatabases table,
specifically the status column. Look up sysdatabases in BOL for what the
status bit flags mean.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Wade" <wwegner23NOEMAILhotmail.com> wrote in message
news:OI8mAJZrFHA.248@.TK2MSFTNGP14.phx.gbl...
> Another question ... is there a query I can use to determine if the
> database is in standby/read-only mode? This way I can turn off my diff
> and trans restores.
> Thanks!
> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
> news:eOaFmBZrFHA.1984@.tk2msftngp13.phx.gbl...
>|||Thanks, I already found it:
use [master]
select name, DATABASEPROPERTY(name, N'IsReadOnly') from
master.dbo.sysdatabases
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:uvylHUZrFHA.2008@.TK2MSFTNGP10.phx.gbl...
> You continue to do diff and/or log restores while the database is in
> standby. You stop the restores when it is recovered.
> The information you seek is in the master.dbo.sysdatabases table,
> specifically the status column. Look up sysdatabases in BOL for what the
> status bit flags mean.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Wade" <wwegner23NOEMAILhotmail.com> wrote in message
> news:OI8mAJZrFHA.248@.TK2MSFTNGP14.phx.gbl...
>|||FYI, DATABASEPROPERTYEX() is a newer version of DATABASEPROPERTY()
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Wade" <wwegner23NOEMAILhotmail.com> wrote in message news:OySXXXZrFHA.3424@.TK2MSFTNGP14.phx
.gbl...
> Thanks, I already found it:
> use [master]
> select name, DATABASEPROPERTY(name, N'IsReadOnly') from
> master.dbo.sysdatabases
> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
> news:uvylHUZrFHA.2008@.TK2MSFTNGP10.phx.gbl...
>sql
Restoring to remove standy/read-only mode.
Hi all,
I have a database that is restored with the following command:
RESTORE DATABASE [<databasename>] FROM DISK= '\\<path>\<databasename>_diff.bak' WITH MOVE '<databasename>_Data' TO
'C:\<databasename>_Data.mdf', MOVE '<databasename>_Log' TO
'C:\<databasename>_Log.ldf', STANDBY = 'C:\<databasename>_standby.rdo'
So, the database is in standy/read-only mode. Now, I want to make sure that
I can change it over so that it's not longer in standy/read-only mode. Is
the following synatx/script correct?
RESTORE DATABASE [<databasename>] WITH RECOVERY
Is that enough? It seems to work, but I'm not sure if I'm missing an
important element.
Thanks!
WadeThat is all you need. Note that once it is recovered you cannot apply
subsequent transaction log or differential backups.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Wade" <wwegner23NOEMAILhotmail.com> wrote in message
news:e9PjMuYrFHA.2072@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> I have a database that is restored with the following command:
> RESTORE DATABASE [<databasename>] FROM DISK=> '\\<path>\<databasename>_diff.bak' WITH MOVE '<databasename>_Data' TO
> 'C:\<databasename>_Data.mdf', MOVE '<databasename>_Log' TO
> 'C:\<databasename>_Log.ldf', STANDBY = 'C:\<databasename>_standby.rdo'
> So, the database is in standy/read-only mode. Now, I want to make sure
> that I can change it over so that it's not longer in standy/read-only
> mode. Is the following synatx/script correct?
> RESTORE DATABASE [<databasename>] WITH RECOVERY
> Is that enough? It seems to work, but I'm not sure if I'm missing an
> important element.
> Thanks!
> Wade
>|||Yes, excellent -- thanks! :)
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:eOaFmBZrFHA.1984@.tk2msftngp13.phx.gbl...
> That is all you need. Note that once it is recovered you cannot apply
> subsequent transaction log or differential backups.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Wade" <wwegner23NOEMAILhotmail.com> wrote in message
> news:e9PjMuYrFHA.2072@.TK2MSFTNGP14.phx.gbl...
>> Hi all,
>> I have a database that is restored with the following command:
>> RESTORE DATABASE [<databasename>] FROM DISK=>> '\\<path>\<databasename>_diff.bak' WITH MOVE '<databasename>_Data' TO
>> 'C:\<databasename>_Data.mdf', MOVE '<databasename>_Log' TO
>> 'C:\<databasename>_Log.ldf', STANDBY = 'C:\<databasename>_standby.rdo'
>> So, the database is in standy/read-only mode. Now, I want to make sure
>> that I can change it over so that it's not longer in standy/read-only
>> mode. Is the following synatx/script correct?
>> RESTORE DATABASE [<databasename>] WITH RECOVERY
>> Is that enough? It seems to work, but I'm not sure if I'm missing an
>> important element.
>> Thanks!
>> Wade
>>
>|||Another question ... is there a query I can use to determine if the database
is in standby/read-only mode? This way I can turn off my diff and trans
restores.
Thanks!
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:eOaFmBZrFHA.1984@.tk2msftngp13.phx.gbl...
> That is all you need. Note that once it is recovered you cannot apply
> subsequent transaction log or differential backups.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Wade" <wwegner23NOEMAILhotmail.com> wrote in message
> news:e9PjMuYrFHA.2072@.TK2MSFTNGP14.phx.gbl...
>> Hi all,
>> I have a database that is restored with the following command:
>> RESTORE DATABASE [<databasename>] FROM DISK=>> '\\<path>\<databasename>_diff.bak' WITH MOVE '<databasename>_Data' TO
>> 'C:\<databasename>_Data.mdf', MOVE '<databasename>_Log' TO
>> 'C:\<databasename>_Log.ldf', STANDBY = 'C:\<databasename>_standby.rdo'
>> So, the database is in standy/read-only mode. Now, I want to make sure
>> that I can change it over so that it's not longer in standy/read-only
>> mode. Is the following synatx/script correct?
>> RESTORE DATABASE [<databasename>] WITH RECOVERY
>> Is that enough? It seems to work, but I'm not sure if I'm missing an
>> important element.
>> Thanks!
>> Wade
>>
>|||You continue to do diff and/or log restores while the database is in
standby. You stop the restores when it is recovered.
The information you seek is in the master.dbo.sysdatabases table,
specifically the status column. Look up sysdatabases in BOL for what the
status bit flags mean.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Wade" <wwegner23NOEMAILhotmail.com> wrote in message
news:OI8mAJZrFHA.248@.TK2MSFTNGP14.phx.gbl...
> Another question ... is there a query I can use to determine if the
> database is in standby/read-only mode? This way I can turn off my diff
> and trans restores.
> Thanks!
> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
> news:eOaFmBZrFHA.1984@.tk2msftngp13.phx.gbl...
>> That is all you need. Note that once it is recovered you cannot apply
>> subsequent transaction log or differential backups.
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>> "Wade" <wwegner23NOEMAILhotmail.com> wrote in message
>> news:e9PjMuYrFHA.2072@.TK2MSFTNGP14.phx.gbl...
>> Hi all,
>> I have a database that is restored with the following command:
>> RESTORE DATABASE [<databasename>] FROM DISK=>> '\\<path>\<databasename>_diff.bak' WITH MOVE '<databasename>_Data' TO
>> 'C:\<databasename>_Data.mdf', MOVE '<databasename>_Log' TO
>> 'C:\<databasename>_Log.ldf', STANDBY = 'C:\<databasename>_standby.rdo'
>> So, the database is in standy/read-only mode. Now, I want to make sure
>> that I can change it over so that it's not longer in standy/read-only
>> mode. Is the following synatx/script correct?
>> RESTORE DATABASE [<databasename>] WITH RECOVERY
>> Is that enough? It seems to work, but I'm not sure if I'm missing an
>> important element.
>> Thanks!
>> Wade
>>
>>
>|||Thanks, I already found it:
use [master]
select name, DATABASEPROPERTY(name, N'IsReadOnly') from
master.dbo.sysdatabases
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:uvylHUZrFHA.2008@.TK2MSFTNGP10.phx.gbl...
> You continue to do diff and/or log restores while the database is in
> standby. You stop the restores when it is recovered.
> The information you seek is in the master.dbo.sysdatabases table,
> specifically the status column. Look up sysdatabases in BOL for what the
> status bit flags mean.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Wade" <wwegner23NOEMAILhotmail.com> wrote in message
> news:OI8mAJZrFHA.248@.TK2MSFTNGP14.phx.gbl...
>> Another question ... is there a query I can use to determine if the
>> database is in standby/read-only mode? This way I can turn off my diff
>> and trans restores.
>> Thanks!
>> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
>> news:eOaFmBZrFHA.1984@.tk2msftngp13.phx.gbl...
>> That is all you need. Note that once it is recovered you cannot apply
>> subsequent transaction log or differential backups.
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>> "Wade" <wwegner23NOEMAILhotmail.com> wrote in message
>> news:e9PjMuYrFHA.2072@.TK2MSFTNGP14.phx.gbl...
>> Hi all,
>> I have a database that is restored with the following command:
>> RESTORE DATABASE [<databasename>] FROM DISK=>> '\\<path>\<databasename>_diff.bak' WITH MOVE '<databasename>_Data' TO
>> 'C:\<databasename>_Data.mdf', MOVE '<databasename>_Log' TO
>> 'C:\<databasename>_Log.ldf', STANDBY = 'C:\<databasename>_standby.rdo'
>> So, the database is in standy/read-only mode. Now, I want to make sure
>> that I can change it over so that it's not longer in standy/read-only
>> mode. Is the following synatx/script correct?
>> RESTORE DATABASE [<databasename>] WITH RECOVERY
>> Is that enough? It seems to work, but I'm not sure if I'm missing an
>> important element.
>> Thanks!
>> Wade
>>
>>
>>
>|||FYI, DATABASEPROPERTYEX() is a newer version of DATABASEPROPERTY()
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Wade" <wwegner23NOEMAILhotmail.com> wrote in message news:OySXXXZrFHA.3424@.TK2MSFTNGP14.phx.gbl...
> Thanks, I already found it:
> use [master]
> select name, DATABASEPROPERTY(name, N'IsReadOnly') from
> master.dbo.sysdatabases
> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
> news:uvylHUZrFHA.2008@.TK2MSFTNGP10.phx.gbl...
>> You continue to do diff and/or log restores while the database is in
>> standby. You stop the restores when it is recovered.
>> The information you seek is in the master.dbo.sysdatabases table,
>> specifically the status column. Look up sysdatabases in BOL for what the
>> status bit flags mean.
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>> "Wade" <wwegner23NOEMAILhotmail.com> wrote in message
>> news:OI8mAJZrFHA.248@.TK2MSFTNGP14.phx.gbl...
>> Another question ... is there a query I can use to determine if the
>> database is in standby/read-only mode? This way I can turn off my diff
>> and trans restores.
>> Thanks!
>> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
>> news:eOaFmBZrFHA.1984@.tk2msftngp13.phx.gbl...
>> That is all you need. Note that once it is recovered you cannot apply
>> subsequent transaction log or differential backups.
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>> "Wade" <wwegner23NOEMAILhotmail.com> wrote in message
>> news:e9PjMuYrFHA.2072@.TK2MSFTNGP14.phx.gbl...
>> Hi all,
>> I have a database that is restored with the following command:
>> RESTORE DATABASE [<databasename>] FROM DISK=>> '\\<path>\<databasename>_diff.bak' WITH MOVE '<databasename>_Data' TO
>> 'C:\<databasename>_Data.mdf', MOVE '<databasename>_Log' TO
>> 'C:\<databasename>_Log.ldf', STANDBY = 'C:\<databasename>_standby.rdo'
>> So, the database is in standy/read-only mode. Now, I want to make sure
>> that I can change it over so that it's not longer in standy/read-only
>> mode. Is the following synatx/script correct?
>> RESTORE DATABASE [<databasename>] WITH RECOVERY
>> Is that enough? It seems to work, but I'm not sure if I'm missing an
>> important element.
>> Thanks!
>> Wade
>>
>>
>>
>>
>
I have a database that is restored with the following command:
RESTORE DATABASE [<databasename>] FROM DISK= '\\<path>\<databasename>_diff.bak' WITH MOVE '<databasename>_Data' TO
'C:\<databasename>_Data.mdf', MOVE '<databasename>_Log' TO
'C:\<databasename>_Log.ldf', STANDBY = 'C:\<databasename>_standby.rdo'
So, the database is in standy/read-only mode. Now, I want to make sure that
I can change it over so that it's not longer in standy/read-only mode. Is
the following synatx/script correct?
RESTORE DATABASE [<databasename>] WITH RECOVERY
Is that enough? It seems to work, but I'm not sure if I'm missing an
important element.
Thanks!
WadeThat is all you need. Note that once it is recovered you cannot apply
subsequent transaction log or differential backups.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Wade" <wwegner23NOEMAILhotmail.com> wrote in message
news:e9PjMuYrFHA.2072@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> I have a database that is restored with the following command:
> RESTORE DATABASE [<databasename>] FROM DISK=> '\\<path>\<databasename>_diff.bak' WITH MOVE '<databasename>_Data' TO
> 'C:\<databasename>_Data.mdf', MOVE '<databasename>_Log' TO
> 'C:\<databasename>_Log.ldf', STANDBY = 'C:\<databasename>_standby.rdo'
> So, the database is in standy/read-only mode. Now, I want to make sure
> that I can change it over so that it's not longer in standy/read-only
> mode. Is the following synatx/script correct?
> RESTORE DATABASE [<databasename>] WITH RECOVERY
> Is that enough? It seems to work, but I'm not sure if I'm missing an
> important element.
> Thanks!
> Wade
>|||Yes, excellent -- thanks! :)
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:eOaFmBZrFHA.1984@.tk2msftngp13.phx.gbl...
> That is all you need. Note that once it is recovered you cannot apply
> subsequent transaction log or differential backups.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Wade" <wwegner23NOEMAILhotmail.com> wrote in message
> news:e9PjMuYrFHA.2072@.TK2MSFTNGP14.phx.gbl...
>> Hi all,
>> I have a database that is restored with the following command:
>> RESTORE DATABASE [<databasename>] FROM DISK=>> '\\<path>\<databasename>_diff.bak' WITH MOVE '<databasename>_Data' TO
>> 'C:\<databasename>_Data.mdf', MOVE '<databasename>_Log' TO
>> 'C:\<databasename>_Log.ldf', STANDBY = 'C:\<databasename>_standby.rdo'
>> So, the database is in standy/read-only mode. Now, I want to make sure
>> that I can change it over so that it's not longer in standy/read-only
>> mode. Is the following synatx/script correct?
>> RESTORE DATABASE [<databasename>] WITH RECOVERY
>> Is that enough? It seems to work, but I'm not sure if I'm missing an
>> important element.
>> Thanks!
>> Wade
>>
>|||Another question ... is there a query I can use to determine if the database
is in standby/read-only mode? This way I can turn off my diff and trans
restores.
Thanks!
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:eOaFmBZrFHA.1984@.tk2msftngp13.phx.gbl...
> That is all you need. Note that once it is recovered you cannot apply
> subsequent transaction log or differential backups.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Wade" <wwegner23NOEMAILhotmail.com> wrote in message
> news:e9PjMuYrFHA.2072@.TK2MSFTNGP14.phx.gbl...
>> Hi all,
>> I have a database that is restored with the following command:
>> RESTORE DATABASE [<databasename>] FROM DISK=>> '\\<path>\<databasename>_diff.bak' WITH MOVE '<databasename>_Data' TO
>> 'C:\<databasename>_Data.mdf', MOVE '<databasename>_Log' TO
>> 'C:\<databasename>_Log.ldf', STANDBY = 'C:\<databasename>_standby.rdo'
>> So, the database is in standy/read-only mode. Now, I want to make sure
>> that I can change it over so that it's not longer in standy/read-only
>> mode. Is the following synatx/script correct?
>> RESTORE DATABASE [<databasename>] WITH RECOVERY
>> Is that enough? It seems to work, but I'm not sure if I'm missing an
>> important element.
>> Thanks!
>> Wade
>>
>|||You continue to do diff and/or log restores while the database is in
standby. You stop the restores when it is recovered.
The information you seek is in the master.dbo.sysdatabases table,
specifically the status column. Look up sysdatabases in BOL for what the
status bit flags mean.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Wade" <wwegner23NOEMAILhotmail.com> wrote in message
news:OI8mAJZrFHA.248@.TK2MSFTNGP14.phx.gbl...
> Another question ... is there a query I can use to determine if the
> database is in standby/read-only mode? This way I can turn off my diff
> and trans restores.
> Thanks!
> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
> news:eOaFmBZrFHA.1984@.tk2msftngp13.phx.gbl...
>> That is all you need. Note that once it is recovered you cannot apply
>> subsequent transaction log or differential backups.
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>> "Wade" <wwegner23NOEMAILhotmail.com> wrote in message
>> news:e9PjMuYrFHA.2072@.TK2MSFTNGP14.phx.gbl...
>> Hi all,
>> I have a database that is restored with the following command:
>> RESTORE DATABASE [<databasename>] FROM DISK=>> '\\<path>\<databasename>_diff.bak' WITH MOVE '<databasename>_Data' TO
>> 'C:\<databasename>_Data.mdf', MOVE '<databasename>_Log' TO
>> 'C:\<databasename>_Log.ldf', STANDBY = 'C:\<databasename>_standby.rdo'
>> So, the database is in standy/read-only mode. Now, I want to make sure
>> that I can change it over so that it's not longer in standy/read-only
>> mode. Is the following synatx/script correct?
>> RESTORE DATABASE [<databasename>] WITH RECOVERY
>> Is that enough? It seems to work, but I'm not sure if I'm missing an
>> important element.
>> Thanks!
>> Wade
>>
>>
>|||Thanks, I already found it:
use [master]
select name, DATABASEPROPERTY(name, N'IsReadOnly') from
master.dbo.sysdatabases
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:uvylHUZrFHA.2008@.TK2MSFTNGP10.phx.gbl...
> You continue to do diff and/or log restores while the database is in
> standby. You stop the restores when it is recovered.
> The information you seek is in the master.dbo.sysdatabases table,
> specifically the status column. Look up sysdatabases in BOL for what the
> status bit flags mean.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Wade" <wwegner23NOEMAILhotmail.com> wrote in message
> news:OI8mAJZrFHA.248@.TK2MSFTNGP14.phx.gbl...
>> Another question ... is there a query I can use to determine if the
>> database is in standby/read-only mode? This way I can turn off my diff
>> and trans restores.
>> Thanks!
>> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
>> news:eOaFmBZrFHA.1984@.tk2msftngp13.phx.gbl...
>> That is all you need. Note that once it is recovered you cannot apply
>> subsequent transaction log or differential backups.
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>> "Wade" <wwegner23NOEMAILhotmail.com> wrote in message
>> news:e9PjMuYrFHA.2072@.TK2MSFTNGP14.phx.gbl...
>> Hi all,
>> I have a database that is restored with the following command:
>> RESTORE DATABASE [<databasename>] FROM DISK=>> '\\<path>\<databasename>_diff.bak' WITH MOVE '<databasename>_Data' TO
>> 'C:\<databasename>_Data.mdf', MOVE '<databasename>_Log' TO
>> 'C:\<databasename>_Log.ldf', STANDBY = 'C:\<databasename>_standby.rdo'
>> So, the database is in standy/read-only mode. Now, I want to make sure
>> that I can change it over so that it's not longer in standy/read-only
>> mode. Is the following synatx/script correct?
>> RESTORE DATABASE [<databasename>] WITH RECOVERY
>> Is that enough? It seems to work, but I'm not sure if I'm missing an
>> important element.
>> Thanks!
>> Wade
>>
>>
>>
>|||FYI, DATABASEPROPERTYEX() is a newer version of DATABASEPROPERTY()
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Wade" <wwegner23NOEMAILhotmail.com> wrote in message news:OySXXXZrFHA.3424@.TK2MSFTNGP14.phx.gbl...
> Thanks, I already found it:
> use [master]
> select name, DATABASEPROPERTY(name, N'IsReadOnly') from
> master.dbo.sysdatabases
> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
> news:uvylHUZrFHA.2008@.TK2MSFTNGP10.phx.gbl...
>> You continue to do diff and/or log restores while the database is in
>> standby. You stop the restores when it is recovered.
>> The information you seek is in the master.dbo.sysdatabases table,
>> specifically the status column. Look up sysdatabases in BOL for what the
>> status bit flags mean.
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>> "Wade" <wwegner23NOEMAILhotmail.com> wrote in message
>> news:OI8mAJZrFHA.248@.TK2MSFTNGP14.phx.gbl...
>> Another question ... is there a query I can use to determine if the
>> database is in standby/read-only mode? This way I can turn off my diff
>> and trans restores.
>> Thanks!
>> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
>> news:eOaFmBZrFHA.1984@.tk2msftngp13.phx.gbl...
>> That is all you need. Note that once it is recovered you cannot apply
>> subsequent transaction log or differential backups.
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>> "Wade" <wwegner23NOEMAILhotmail.com> wrote in message
>> news:e9PjMuYrFHA.2072@.TK2MSFTNGP14.phx.gbl...
>> Hi all,
>> I have a database that is restored with the following command:
>> RESTORE DATABASE [<databasename>] FROM DISK=>> '\\<path>\<databasename>_diff.bak' WITH MOVE '<databasename>_Data' TO
>> 'C:\<databasename>_Data.mdf', MOVE '<databasename>_Log' TO
>> 'C:\<databasename>_Log.ldf', STANDBY = 'C:\<databasename>_standby.rdo'
>> So, the database is in standy/read-only mode. Now, I want to make sure
>> that I can change it over so that it's not longer in standy/read-only
>> mode. Is the following synatx/script correct?
>> RESTORE DATABASE [<databasename>] WITH RECOVERY
>> Is that enough? It seems to work, but I'm not sure if I'm missing an
>> important element.
>> Thanks!
>> Wade
>>
>>
>>
>>
>
Subscribe to:
Posts (Atom)