I got called in on Monday because people couldn't get into
our SQL Server application. It's SQL Server 2000. After
talking to someone (I'm new to the application) I found
that the 'Single user' option for the database in
Enterprise Manager had been checked. Now I can't think
that anyone would have gone up to the server between
Friday and Monday (I was in at the weekend too, and didn't
touch the server). So I'm wondering how it might have
suddenly become checked and ruin my day off... :-)
TimA maintenance plan with the "repair minor problems" option set will do this.
"Tim Gowen" <tim.gowen@.rafmuseum.org> wrote in message
news:152b01c46f2c$30f715c0$a301280a@.phx.gbl...
> I got called in on Monday because people couldn't get into
> our SQL Server application. It's SQL Server 2000. After
> talking to someone (I'm new to the application) I found
> that the 'Single user' option for the database in
> Enterprise Manager had been checked. Now I can't think
> that anyone would have gone up to the server between
> Friday and Monday (I was in at the weekend too, and didn't
> touch the server). So I'm wondering how it might have
> suddenly become checked and ruin my day off... :-)
> Tim
Showing posts with label application. Show all posts
Showing posts with label application. Show all posts
Wednesday, March 28, 2012
Restrict Access Suddenly Active
I got called in on Monday because people couldn't get into
our SQL Server application. It's SQL Server 2000. After
talking to someone (I'm new to the application) I found
that the 'Single user' option for the database in
Enterprise Manager had been checked. Now I can't think
that anyone would have gone up to the server between
Friday and Monday (I was in at the weekend too, and didn't
touch the server). So I'm wondering how it might have
suddenly become checked and ruin my day off... :-)
Tim
A maintenance plan with the "repair minor problems" option set will do this.
"Tim Gowen" <tim.gowen@.rafmuseum.org> wrote in message
news:152b01c46f2c$30f715c0$a301280a@.phx.gbl...
> I got called in on Monday because people couldn't get into
> our SQL Server application. It's SQL Server 2000. After
> talking to someone (I'm new to the application) I found
> that the 'Single user' option for the database in
> Enterprise Manager had been checked. Now I can't think
> that anyone would have gone up to the server between
> Friday and Monday (I was in at the weekend too, and didn't
> touch the server). So I'm wondering how it might have
> suddenly become checked and ruin my day off... :-)
> Tim
our SQL Server application. It's SQL Server 2000. After
talking to someone (I'm new to the application) I found
that the 'Single user' option for the database in
Enterprise Manager had been checked. Now I can't think
that anyone would have gone up to the server between
Friday and Monday (I was in at the weekend too, and didn't
touch the server). So I'm wondering how it might have
suddenly become checked and ruin my day off... :-)
Tim
A maintenance plan with the "repair minor problems" option set will do this.
"Tim Gowen" <tim.gowen@.rafmuseum.org> wrote in message
news:152b01c46f2c$30f715c0$a301280a@.phx.gbl...
> I got called in on Monday because people couldn't get into
> our SQL Server application. It's SQL Server 2000. After
> talking to someone (I'm new to the application) I found
> that the 'Single user' option for the database in
> Enterprise Manager had been checked. Now I can't think
> that anyone would have gone up to the server between
> Friday and Monday (I was in at the weekend too, and didn't
> touch the server). So I'm wondering how it might have
> suddenly become checked and ruin my day off... :-)
> Tim
Monday, March 26, 2012
Restoring state of database after running a Unit test
Sorry if this is a newbie question.
My application sends data (via TCP/IP sockets) to a 3rd party
application which dispenses the data to various tables in a SQL Server
2005 database based on its own business logic.
I am in the process of writing Unit tests for my application that
mimics the sending of this data. However on test Teardown I would like
to delete this test data so that next time I run the tests, the
database is in a known state.
I can think of a few options, none of which sound ideal to me:
1. Run the profiler on the database and note all the tables it touches
and delete the those rows in the reverse order in which it was
inserted. This is less than ideal because insertion is happening via
stored complex procedures and it would be an arduous task going
through the code of all the stored procs and coming up with a list
such the referential constraints are not violated on deletion.
2. Backup and Restore the database.This is quite a time consuming
operation and would be a huge bottleneck to do that everytime a test
suite is run.
Unfortunately rolling back is not an option for me because the
transaction was commited by that different third party application.
Is there some other mechanism by which I can tell SQL Server: "Note
the state of the database now, and essentially rollback to it when I
tell you so (in test teardown)"?
Thanks.Hi
Your best option is to restore a backup, you only need one known state
backup and you can restore consistently (use a script rather than the GUI as
this can be automated using test tools). If you don't want to do that have
copies of the MDF and LDF files and detach/copy/re-attach instead.
Unless you are testing performance and require a large amount of data in the
database, then using a smaller amount will back restoring it quicker.
John
"sprash" <sprash25@.gmail.com> wrote in message
news:9bc7966a-638e-4ff3-9b4e-6e5972c32ec7@.u10g2000prn.googlegroups.com...
> Sorry if this is a newbie question.
> My application sends data (via TCP/IP sockets) to a 3rd party
> application which dispenses the data to various tables in a SQL Server
> 2005 database based on its own business logic.
> I am in the process of writing Unit tests for my application that
> mimics the sending of this data. However on test Teardown I would like
> to delete this test data so that next time I run the tests, the
> database is in a known state.
> I can think of a few options, none of which sound ideal to me:
> 1. Run the profiler on the database and note all the tables it touches
> and delete the those rows in the reverse order in which it was
> inserted. This is less than ideal because insertion is happening via
> stored complex procedures and it would be an arduous task going
> through the code of all the stored procs and coming up with a list
> such the referential constraints are not violated on deletion.
> 2. Backup and Restore the database.This is quite a time consuming
> operation and would be a huge bottleneck to do that everytime a test
> suite is run.
> Unfortunately rolling back is not an option for me because the
> transaction was commited by that different third party application.
> Is there some other mechanism by which I can tell SQL Server: "Note
> the state of the database now, and essentially rollback to it when I
> tell you so (in test teardown)"?
> Thanks.
My application sends data (via TCP/IP sockets) to a 3rd party
application which dispenses the data to various tables in a SQL Server
2005 database based on its own business logic.
I am in the process of writing Unit tests for my application that
mimics the sending of this data. However on test Teardown I would like
to delete this test data so that next time I run the tests, the
database is in a known state.
I can think of a few options, none of which sound ideal to me:
1. Run the profiler on the database and note all the tables it touches
and delete the those rows in the reverse order in which it was
inserted. This is less than ideal because insertion is happening via
stored complex procedures and it would be an arduous task going
through the code of all the stored procs and coming up with a list
such the referential constraints are not violated on deletion.
2. Backup and Restore the database.This is quite a time consuming
operation and would be a huge bottleneck to do that everytime a test
suite is run.
Unfortunately rolling back is not an option for me because the
transaction was commited by that different third party application.
Is there some other mechanism by which I can tell SQL Server: "Note
the state of the database now, and essentially rollback to it when I
tell you so (in test teardown)"?
Thanks.Hi
Your best option is to restore a backup, you only need one known state
backup and you can restore consistently (use a script rather than the GUI as
this can be automated using test tools). If you don't want to do that have
copies of the MDF and LDF files and detach/copy/re-attach instead.
Unless you are testing performance and require a large amount of data in the
database, then using a smaller amount will back restoring it quicker.
John
"sprash" <sprash25@.gmail.com> wrote in message
news:9bc7966a-638e-4ff3-9b4e-6e5972c32ec7@.u10g2000prn.googlegroups.com...
> Sorry if this is a newbie question.
> My application sends data (via TCP/IP sockets) to a 3rd party
> application which dispenses the data to various tables in a SQL Server
> 2005 database based on its own business logic.
> I am in the process of writing Unit tests for my application that
> mimics the sending of this data. However on test Teardown I would like
> to delete this test data so that next time I run the tests, the
> database is in a known state.
> I can think of a few options, none of which sound ideal to me:
> 1. Run the profiler on the database and note all the tables it touches
> and delete the those rows in the reverse order in which it was
> inserted. This is less than ideal because insertion is happening via
> stored complex procedures and it would be an arduous task going
> through the code of all the stored procs and coming up with a list
> such the referential constraints are not violated on deletion.
> 2. Backup and Restore the database.This is quite a time consuming
> operation and would be a huge bottleneck to do that everytime a test
> suite is run.
> Unfortunately rolling back is not an option for me because the
> transaction was commited by that different third party application.
> Is there some other mechanism by which I can tell SQL Server: "Note
> the state of the database now, and essentially rollback to it when I
> tell you so (in test teardown)"?
> Thanks.
Restoring state of database after running a Unit test
Sorry if this is a newbie question.
My application sends data (via TCP/IP sockets) to a 3rd party
application which dispenses the data to various tables in a SQL Server
2005 database based on its own business logic.
I am in the process of writing Unit tests for my application that
mimics the sending of this data. However on test Teardown I would like
to delete this test data so that next time I run the tests, the
database is in a known state.
I can think of a few options, none of which sound ideal to me:
1. Run the profiler on the database and note all the tables it touches
and delete the those rows in the reverse order in which it was
inserted. This is less than ideal because insertion is happening via
stored complex procedures and it would be an arduous task going
through the code of all the stored procs and coming up with a list
such the referential constraints are not violated on deletion.
2. Backup and Restore the database.This is quite a time consuming
operation and would be a huge bottleneck to do that everytime a test
suite is run.
Unfortunately rolling back is not an option for me because the
transaction was commited by that different third party application.
Is there some other mechanism by which I can tell SQL Server: "Note
the state of the database now, and essentially rollback to it when I
tell you so (in test teardown)"?
Thanks.
Hi
Your best option is to restore a backup, you only need one known state
backup and you can restore consistently (use a script rather than the GUI as
this can be automated using test tools). If you don't want to do that have
copies of the MDF and LDF files and detach/copy/re-attach instead.
Unless you are testing performance and require a large amount of data in the
database, then using a smaller amount will back restoring it quicker.
John
"sprash" <sprash25@.gmail.com> wrote in message
news:9bc7966a-638e-4ff3-9b4e-6e5972c32ec7@.u10g2000prn.googlegroups.com...
> Sorry if this is a newbie question.
> My application sends data (via TCP/IP sockets) to a 3rd party
> application which dispenses the data to various tables in a SQL Server
> 2005 database based on its own business logic.
> I am in the process of writing Unit tests for my application that
> mimics the sending of this data. However on test Teardown I would like
> to delete this test data so that next time I run the tests, the
> database is in a known state.
> I can think of a few options, none of which sound ideal to me:
> 1. Run the profiler on the database and note all the tables it touches
> and delete the those rows in the reverse order in which it was
> inserted. This is less than ideal because insertion is happening via
> stored complex procedures and it would be an arduous task going
> through the code of all the stored procs and coming up with a list
> such the referential constraints are not violated on deletion.
> 2. Backup and Restore the database.This is quite a time consuming
> operation and would be a huge bottleneck to do that everytime a test
> suite is run.
> Unfortunately rolling back is not an option for me because the
> transaction was commited by that different third party application.
> Is there some other mechanism by which I can tell SQL Server: "Note
> the state of the database now, and essentially rollback to it when I
> tell you so (in test teardown)"?
> Thanks.
My application sends data (via TCP/IP sockets) to a 3rd party
application which dispenses the data to various tables in a SQL Server
2005 database based on its own business logic.
I am in the process of writing Unit tests for my application that
mimics the sending of this data. However on test Teardown I would like
to delete this test data so that next time I run the tests, the
database is in a known state.
I can think of a few options, none of which sound ideal to me:
1. Run the profiler on the database and note all the tables it touches
and delete the those rows in the reverse order in which it was
inserted. This is less than ideal because insertion is happening via
stored complex procedures and it would be an arduous task going
through the code of all the stored procs and coming up with a list
such the referential constraints are not violated on deletion.
2. Backup and Restore the database.This is quite a time consuming
operation and would be a huge bottleneck to do that everytime a test
suite is run.
Unfortunately rolling back is not an option for me because the
transaction was commited by that different third party application.
Is there some other mechanism by which I can tell SQL Server: "Note
the state of the database now, and essentially rollback to it when I
tell you so (in test teardown)"?
Thanks.
Hi
Your best option is to restore a backup, you only need one known state
backup and you can restore consistently (use a script rather than the GUI as
this can be automated using test tools). If you don't want to do that have
copies of the MDF and LDF files and detach/copy/re-attach instead.
Unless you are testing performance and require a large amount of data in the
database, then using a smaller amount will back restoring it quicker.
John
"sprash" <sprash25@.gmail.com> wrote in message
news:9bc7966a-638e-4ff3-9b4e-6e5972c32ec7@.u10g2000prn.googlegroups.com...
> Sorry if this is a newbie question.
> My application sends data (via TCP/IP sockets) to a 3rd party
> application which dispenses the data to various tables in a SQL Server
> 2005 database based on its own business logic.
> I am in the process of writing Unit tests for my application that
> mimics the sending of this data. However on test Teardown I would like
> to delete this test data so that next time I run the tests, the
> database is in a known state.
> I can think of a few options, none of which sound ideal to me:
> 1. Run the profiler on the database and note all the tables it touches
> and delete the those rows in the reverse order in which it was
> inserted. This is less than ideal because insertion is happening via
> stored complex procedures and it would be an arduous task going
> through the code of all the stored procs and coming up with a list
> such the referential constraints are not violated on deletion.
> 2. Backup and Restore the database.This is quite a time consuming
> operation and would be a huge bottleneck to do that everytime a test
> suite is run.
> Unfortunately rolling back is not an option for me because the
> transaction was commited by that different third party application.
> Is there some other mechanism by which I can tell SQL Server: "Note
> the state of the database now, and essentially rollback to it when I
> tell you so (in test teardown)"?
> Thanks.
Tuesday, March 20, 2012
Restoring MSDE Databases
I have uninstalled and re-installed an application that uses MSDE on one of
our workstations. Before uninstalling the application I copied the .mdf and
..ldf files to a new location. I now need to restore the databases that are
associated with this application using the .mdf and .ldf files I saved. I
also need to verify the initial configuration (data and log files
names/paths)
I initially tried to detach the database, copy the backup files to the
MSSQL\Data folder and then reattach the database. This worked except that
the database came back in a "read-only" mode. I then opened the properties
of the database and tried to uncheck the "Read-only" option in the
properties. When I tried to save the new settings I received a message that
stated "Device activation error".
Can anyone tell me how I can:
1) Verify the configuration of the database that was installed by the
application I installed.
2) Rebuild this database using the .mdf and .ldf files?
Thanks for any input.
Nancy
Hi Nancy,
Sounds like you're basically on the right track. Is there any chance the
files were copied in from a CD or something and actually are read-only?
If you attach read-only mdf & ldf files, the database comes up in a
read-only mode.
HTH,
Greg Low [MVP]
MSDE Manager SQL Tools
www.whitebearconsulting.com
"Nancy Kafer" <nkafer@.homesteaderslife.com> wrote in message
news:OBnN12BgEHA.3428@.TK2MSFTNGP11.phx.gbl...
> I have uninstalled and re-installed an application that uses MSDE on one
of
> our workstations. Before uninstalling the application I copied the .mdf
and
> .ldf files to a new location. I now need to restore the databases that are
> associated with this application using the .mdf and .ldf files I saved. I
> also need to verify the initial configuration (data and log files
> names/paths)
> I initially tried to detach the database, copy the backup files to the
> MSSQL\Data folder and then reattach the database. This worked except that
> the database came back in a "read-only" mode. I then opened the properties
> of the database and tried to uncheck the "Read-only" option in the
> properties. When I tried to save the new settings I received a message
that
> stated "Device activation error".
> Can anyone tell me how I can:
> 1) Verify the configuration of the database that was installed by the
> application I installed.
> 2) Rebuild this database using the .mdf and .ldf files?
> Thanks for any input.
> Nancy
>
|||Hi Greg,
Thanks for the solution. That worked perfectly!!!!
Nancy
"Greg Low [MVP]" <greglow@.lowell.com.au> wrote in message
news:OHp7LaFgEHA.384@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Hi Nancy,
> Sounds like you're basically on the right track. Is there any chance the
> files were copied in from a CD or something and actually are read-only?
> If you attach read-only mdf & ldf files, the database comes up in a
> read-only mode.
> HTH,
> --
> Greg Low [MVP]
> MSDE Manager SQL Tools
> www.whitebearconsulting.com
> "Nancy Kafer" <nkafer@.homesteaderslife.com> wrote in message
> news:OBnN12BgEHA.3428@.TK2MSFTNGP11.phx.gbl...
> of
> and
are[vbcol=seagreen]
I[vbcol=seagreen]
that[vbcol=seagreen]
properties
> that
>
our workstations. Before uninstalling the application I copied the .mdf and
..ldf files to a new location. I now need to restore the databases that are
associated with this application using the .mdf and .ldf files I saved. I
also need to verify the initial configuration (data and log files
names/paths)
I initially tried to detach the database, copy the backup files to the
MSSQL\Data folder and then reattach the database. This worked except that
the database came back in a "read-only" mode. I then opened the properties
of the database and tried to uncheck the "Read-only" option in the
properties. When I tried to save the new settings I received a message that
stated "Device activation error".
Can anyone tell me how I can:
1) Verify the configuration of the database that was installed by the
application I installed.
2) Rebuild this database using the .mdf and .ldf files?
Thanks for any input.
Nancy
Hi Nancy,
Sounds like you're basically on the right track. Is there any chance the
files were copied in from a CD or something and actually are read-only?
If you attach read-only mdf & ldf files, the database comes up in a
read-only mode.
HTH,
Greg Low [MVP]
MSDE Manager SQL Tools
www.whitebearconsulting.com
"Nancy Kafer" <nkafer@.homesteaderslife.com> wrote in message
news:OBnN12BgEHA.3428@.TK2MSFTNGP11.phx.gbl...
> I have uninstalled and re-installed an application that uses MSDE on one
of
> our workstations. Before uninstalling the application I copied the .mdf
and
> .ldf files to a new location. I now need to restore the databases that are
> associated with this application using the .mdf and .ldf files I saved. I
> also need to verify the initial configuration (data and log files
> names/paths)
> I initially tried to detach the database, copy the backup files to the
> MSSQL\Data folder and then reattach the database. This worked except that
> the database came back in a "read-only" mode. I then opened the properties
> of the database and tried to uncheck the "Read-only" option in the
> properties. When I tried to save the new settings I received a message
that
> stated "Device activation error".
> Can anyone tell me how I can:
> 1) Verify the configuration of the database that was installed by the
> application I installed.
> 2) Rebuild this database using the .mdf and .ldf files?
> Thanks for any input.
> Nancy
>
|||Hi Greg,
Thanks for the solution. That worked perfectly!!!!
Nancy
"Greg Low [MVP]" <greglow@.lowell.com.au> wrote in message
news:OHp7LaFgEHA.384@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Hi Nancy,
> Sounds like you're basically on the right track. Is there any chance the
> files were copied in from a CD or something and actually are read-only?
> If you attach read-only mdf & ldf files, the database comes up in a
> read-only mode.
> HTH,
> --
> Greg Low [MVP]
> MSDE Manager SQL Tools
> www.whitebearconsulting.com
> "Nancy Kafer" <nkafer@.homesteaderslife.com> wrote in message
> news:OBnN12BgEHA.3428@.TK2MSFTNGP11.phx.gbl...
> of
> and
are[vbcol=seagreen]
I[vbcol=seagreen]
that[vbcol=seagreen]
properties
> that
>
Restoring Master to get SQL login back
Hi Everyone, I'm hoping someone can help me.
We've had a SQL login deleted (special application one) and I understand the best way I can get it back, is to restore the Master database. Is this true ? All the manuals are working on the fact that Master has become corrupt & that it should be rebuilt before restoring. Ours is not corrupt, so am I ok to skip rebuilding it first ?
CheersI wouldn't do that in production environment, because you lose definition of all logins, linked servers, dbs etc. created after the backup you pick to restore. What about restoring master db on test server and using DTS Transfer Logins Task or script out the login and run the script on your server? mojza|||As to rebuild, you don't need to do that. Just restore master from backup over existing master. Look up How to restore the master database (Transact-SQL) topic in Books Online. mojza|||If you know the particulars of the login (name and password), it is much easier to re-add the login, rather than restore all of master. Assuming the user has been dropped as well (which Enterprise Manager cheerfully does for you), you would actually not get any benefit from restoring master, as all of the user's permissions in the application database would still be lost. Restoring the user's permissions is what is going to bog you down much more.
If the user was not deleted, then you can run sp_change_users_login to restore the link between the database user and the (new) login.
We've had a SQL login deleted (special application one) and I understand the best way I can get it back, is to restore the Master database. Is this true ? All the manuals are working on the fact that Master has become corrupt & that it should be rebuilt before restoring. Ours is not corrupt, so am I ok to skip rebuilding it first ?
CheersI wouldn't do that in production environment, because you lose definition of all logins, linked servers, dbs etc. created after the backup you pick to restore. What about restoring master db on test server and using DTS Transfer Logins Task or script out the login and run the script on your server? mojza|||As to rebuild, you don't need to do that. Just restore master from backup over existing master. Look up How to restore the master database (Transact-SQL) topic in Books Online. mojza|||If you know the particulars of the login (name and password), it is much easier to re-add the login, rather than restore all of master. Assuming the user has been dropped as well (which Enterprise Manager cheerfully does for you), you would actually not get any benefit from restoring master, as all of the user's permissions in the application database would still be lost. Restoring the user's permissions is what is going to bog you down much more.
If the user was not deleted, then you can run sp_change_users_login to restore the link between the database user and the (new) login.
Friday, March 9, 2012
Restoring just the Data?
Hi All,
We have a database on our server in the office, which I have installed to test an application in delevelopment. I've got it all working and tested OK.
I've continued working on it at home. I've added some fields to one of the tables. The database in office has a data in which I whish to transfer to my test database - with the new fields added.
I have tried the backup and restore procedure, which works great, but it overwrites the entire database, I just want the data from all tables in one procedure each way, one to backup and one to restore.
I hope that makes sense.
Thanks
Wayne
You will need to employ some form of scripting, perhaps these resources will help point you in the right direction.
DDL -Script Database to File
http://www.wardyit.com/blog/blog/archive/2006/07/21/133.aspx
http://www.sqlteam.com/publish/scriptio
http://www.aspfaq.com/etiquette.asp?id=5006
http://www.codeproject.com/dotnet/ScriptDatabase.asp
http://www.nigelrivett.net/DMO/DMOScriptAllDatabases.html
http://rac4sql.net/qalite_main.asp
Restoring from Backup and Fulltext Catelog
Dear Friends,
When deploying the application I send a back up of the data base to be
restored on clients place.
there is a table in the database which a has FullText Index associated with
it. However since it is in a different machince the client server cannot
recognise the Full-Text catelog.
I tried to drop / edit/ rebulid the Full Text index.
While editing and rebuilding it shows that the FullText catelog is not
found.
I am unable to drop it as it says that it contains indexes.
Command used was : EXEC sp_fulltext_catalog 'LLBPSEPGDataCatelog', 'drop'
What should be the usuall way in this scenario when restoring the back of
database in different server?
Please give us some hint on this..
Regards
Sathian
Review the notes in
http://support.microsoft.com/default...b;en-us;240867
I find the best way to fix this sort of a problem is to update
[database_name].dbo.sysfulltextcatalogs to reflect a valid path, and then
rebuild the catalog.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Sathian" <sathian.t@.in.bosch.com> wrote in message
news:d2bhiv$i11$1@.ns2.fe.internet.bosch.com...
> Dear Friends,
> When deploying the application I send a back up of the data base to be
> restored on clients place.
> there is a table in the database which a has FullText Index associated
with
> it. However since it is in a different machince the client server cannot
> recognise the Full-Text catelog.
> I tried to drop / edit/ rebulid the Full Text index.
> While editing and rebuilding it shows that the FullText catelog is not
> found.
> I am unable to drop it as it says that it contains indexes.
> Command used was : EXEC sp_fulltext_catalog 'LLBPSEPGDataCatelog', 'drop'
> What should be the usuall way in this scenario when restoring the back of
> database in different server?
> Please give us some hint on this..
> Regards
> Sathian
>
When deploying the application I send a back up of the data base to be
restored on clients place.
there is a table in the database which a has FullText Index associated with
it. However since it is in a different machince the client server cannot
recognise the Full-Text catelog.
I tried to drop / edit/ rebulid the Full Text index.
While editing and rebuilding it shows that the FullText catelog is not
found.
I am unable to drop it as it says that it contains indexes.
Command used was : EXEC sp_fulltext_catalog 'LLBPSEPGDataCatelog', 'drop'
What should be the usuall way in this scenario when restoring the back of
database in different server?
Please give us some hint on this..
Regards
Sathian
Review the notes in
http://support.microsoft.com/default...b;en-us;240867
I find the best way to fix this sort of a problem is to update
[database_name].dbo.sysfulltextcatalogs to reflect a valid path, and then
rebuild the catalog.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Sathian" <sathian.t@.in.bosch.com> wrote in message
news:d2bhiv$i11$1@.ns2.fe.internet.bosch.com...
> Dear Friends,
> When deploying the application I send a back up of the data base to be
> restored on clients place.
> there is a table in the database which a has FullText Index associated
with
> it. However since it is in a different machince the client server cannot
> recognise the Full-Text catelog.
> I tried to drop / edit/ rebulid the Full Text index.
> While editing and rebuilding it shows that the FullText catelog is not
> found.
> I am unable to drop it as it says that it contains indexes.
> Command used was : EXEC sp_fulltext_catalog 'LLBPSEPGDataCatelog', 'drop'
> What should be the usuall way in this scenario when restoring the back of
> database in different server?
> Please give us some hint on this..
> Regards
> Sathian
>
Wednesday, March 7, 2012
Restoring from Backup and Fulltext Catelog
Dear Friends,
When deploying the application I send a back up of the data base to be
restored on clients place.
there is a table in the database which a has FullText Index associated with
it. However since it is in a different machince the client server cannot
recognise the Full-Text catelog.
I tried to drop / edit/ rebulid the Full Text index.
While editing and rebuilding it shows that the FullText catelog is not
found.
I am unable to drop it as it says that it contains indexes.
Command used was : EXEC sp_fulltext_catalog 'LLBPSEPGDataCatelog', 'drop'
What should be the usuall way in this scenario when restoring the back of
database in different server?
Please give us some hint on this..
Regards
SathianReview the notes in
http://support.microsoft.com/defaul...kb;en-us;240867
I find the best way to fix this sort of a problem is to update
[database_name].dbo.sysfulltextcatalogs to reflect a valid path, and then
rebuild the catalog.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Sathian" <sathian.t@.in.bosch.com> wrote in message
news:d2bhiv$i11$1@.ns2.fe.internet.bosch.com...
> Dear Friends,
> When deploying the application I send a back up of the data base to be
> restored on clients place.
> there is a table in the database which a has FullText Index associated
with
> it. However since it is in a different machince the client server cannot
> recognise the Full-Text catelog.
> I tried to drop / edit/ rebulid the Full Text index.
> While editing and rebuilding it shows that the FullText catelog is not
> found.
> I am unable to drop it as it says that it contains indexes.
> Command used was : EXEC sp_fulltext_catalog 'LLBPSEPGDataCatelog', 'drop'
> What should be the usuall way in this scenario when restoring the back of
> database in different server?
> Please give us some hint on this..
> Regards
> Sathian
>
When deploying the application I send a back up of the data base to be
restored on clients place.
there is a table in the database which a has FullText Index associated with
it. However since it is in a different machince the client server cannot
recognise the Full-Text catelog.
I tried to drop / edit/ rebulid the Full Text index.
While editing and rebuilding it shows that the FullText catelog is not
found.
I am unable to drop it as it says that it contains indexes.
Command used was : EXEC sp_fulltext_catalog 'LLBPSEPGDataCatelog', 'drop'
What should be the usuall way in this scenario when restoring the back of
database in different server?
Please give us some hint on this..
Regards
SathianReview the notes in
http://support.microsoft.com/defaul...kb;en-us;240867
I find the best way to fix this sort of a problem is to update
[database_name].dbo.sysfulltextcatalogs to reflect a valid path, and then
rebuild the catalog.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Sathian" <sathian.t@.in.bosch.com> wrote in message
news:d2bhiv$i11$1@.ns2.fe.internet.bosch.com...
> Dear Friends,
> When deploying the application I send a back up of the data base to be
> restored on clients place.
> there is a table in the database which a has FullText Index associated
with
> it. However since it is in a different machince the client server cannot
> recognise the Full-Text catelog.
> I tried to drop / edit/ rebulid the Full Text index.
> While editing and rebuilding it shows that the FullText catelog is not
> found.
> I am unable to drop it as it says that it contains indexes.
> Command used was : EXEC sp_fulltext_catalog 'LLBPSEPGDataCatelog', 'drop'
> What should be the usuall way in this scenario when restoring the back of
> database in different server?
> Please give us some hint on this..
> Regards
> Sathian
>
Tuesday, February 21, 2012
Restoring database problem
i have developed an application where in one form i placed two buttons, backup and restore. when i want to take backup from database to which i currenly login, i can .
But when i want to restore database that bakup to database to which i currently login, i get an exception.
So then add following method
myConnection.Close()
RestoreBackup();
even though it is not restoring.
the resore backup method is following
public void RestoreBackup()
{
// If there was a SQL connection created
if (srvSql == null)
{
Connect();
}
if (srvSql != null)
{
openBackupDialog.InitialDirectory = "./Backup";
openBackupDialog.DefaultExt = "bak";
// If the user has chosen the file from which he wants the database to be restored
if (openBackupDialog.ShowDialog() == DialogResult.OK)
{
// Create a new database restore operation
Restore rstDatabase = new Restore();
// Set the restore type to a database restore
rstDatabase.Action = RestoreActionType.Database;
// Set the database that we want to perform the restore on
rstDatabase.Database = "test";
// Set the backup device from which we want to restore, to a file
BackupDeviceItem bkpDevice = new BackupDeviceItem(openBackupDialog.FileName, DeviceType.File);
// Add the backup device to the restore type
rstDatabase.Devices.Add(bkpDevice);
// If the database already exists, replace it
rstDatabase.ReplaceDatabase = true;
rstDatabase.Complete += new Microsoft.SqlServer.Management.Common.ServerMessageEventHandler(rstDatabase_Complete);
// Perform the restore
try
{
rstDatabase.SqlRestore(srvSql);
}
catch (FailedOperationException ex)
{
//Log.WriteLine("");
//Log.WriteLine("Error: (BackupManager.RestoreBackup)");
//Log.Write(ex);
MessageBox.Show("the Database which which u want to restore is in use kindly close connection first");
throw new Exception("Error while restoring backup.", ex);
}
}
}
else
{
throw new Exception("Could not connect to database.");
}
}
YOu will either have to drop all existing connections to the database first. Current Connections won′t be dropped automatically, see my post on the blog for this issue concerning DROP DATABASE:
http://sqlserver2005.de/SQLServer2005/MyBlog/tabid/56/EntryID/9/Default.aspx
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||Two things you must remember when attempting to restore: 1) you must have sysadmin or dbcreator privileges, and 2) you cannot be connected to the database you're attempting to restore. Set your current database to 'master' before issuing the restore command.
|||How to drop all connections and
How to master,
Thanks
|||Hi,drop all connections is listed in the blog. The default database can be tweaked by either changing the default database of the user, or using it directly within the connection string -> Initial Catalog=master
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de|||
Thank you sir, you really helped me, my problem solved by following steps
public void RestoreBackup()
{
Global.con.Close(); // connection of my database for normal transaction
string databaseName = "test"; //database which i want to restore from test.bak
if (srvSql == null) // Server
{
Connect1(); //Opening a new connection (conForMasterDatabase) to master database
}
srvSql.Databases["master"].ExecuteNonQuery(string.Format("ALTER DATABASE {0} SET SINGLE_USER with ROLLBACK IMMEDIATE", databaseName));
..................
//// restore database codes
..............................
Global.conForMasterDatabase.Close();
Global.con.Open(); // reopen connection for normal use
///Global is my class
}
Now please tell me how to restore and backup from remote location
Subscribe to:
Posts (Atom)