I'm in the process of moving our database server to new hardware, but when I
try to use the Copy Database Wizard it finds to concurrent users and won't
allow for the copy. How can I find out who is on and then disconnect them?exec sp_who2 to find them
KILL spid to close them
You only need to kill the spid's where the spid is greater than 50
http://sqlservercode.blogspot.com/|||EXEC sp_who for discovering who is connected
KILL spid for killing a connection
Both are documented in Books On Line
--
Med Bouchenafa
"Cwhitmore" <Cwhitmore@.discussions.microsoft.com> wrote in message
news:2D491056-49C8-4061-BE1E-5E1F6F77D722@.microsoft.com...
> I'm in the process of moving our database server to new hardware, but when
> I
> try to use the Copy Database Wizard it finds to concurrent users and won't
> allow for the copy. How can I find out who is on and then disconnect them?
>|||Another method for forcibly terminate database connections is:
ALTER DATABASE MyDatabase
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
and then back to normal:
ALTER DATABASE MyDatabase
SET MULTI_USER
See the Books Online for more information.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Cwhitmore" <Cwhitmore@.discussions.microsoft.com> wrote in message
news:2D491056-49C8-4061-BE1E-5E1F6F77D722@.microsoft.com...
> I'm in the process of moving our database server to new hardware, but when
> I
> try to use the Copy Database Wizard it finds to concurrent users and won't
> allow for the copy. How can I find out who is on and then disconnect them?
>|||I find that stopping and starting the SQL services usually does the
trick. Nice and easy too.|||Thats a hard one if you have more than one userdatabase one your
server, because ALL serverusers will be wiped out of the system, so
better altering the database like DAN stated would be the best (it is
also my perferable solution, for a hard kick-off)
HTH, jens Suessmeyer.
Showing posts with label hardware. Show all posts
Showing posts with label hardware. Show all posts
Monday, March 26, 2012
How to release user connections?
I'm in the process of moving our database server to new hardware, but when I
try to use the Copy Database Wizard it finds to concurrent users and won't
allow for the copy. How can I find out who is on and then disconnect them?
exec sp_who2 to find them
KILL spid to close them
You only need to kill the spid's where the spid is greater than 50
http://sqlservercode.blogspot.com/
|||EXEC sp_who for discovering who is connected
KILL spid for killing a connection
Both are documented in Books On Line
Med Bouchenafa
"Cwhitmore" <Cwhitmore@.discussions.microsoft.com> wrote in message
news:2D491056-49C8-4061-BE1E-5E1F6F77D722@.microsoft.com...
> I'm in the process of moving our database server to new hardware, but when
> I
> try to use the Copy Database Wizard it finds to concurrent users and won't
> allow for the copy. How can I find out who is on and then disconnect them?
>
|||Another method for forcibly terminate database connections is:
ALTER DATABASE MyDatabase
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
and then back to normal:
ALTER DATABASE MyDatabase
SET MULTI_USER
See the Books Online for more information.
Hope this helps.
Dan Guzman
SQL Server MVP
"Cwhitmore" <Cwhitmore@.discussions.microsoft.com> wrote in message
news:2D491056-49C8-4061-BE1E-5E1F6F77D722@.microsoft.com...
> I'm in the process of moving our database server to new hardware, but when
> I
> try to use the Copy Database Wizard it finds to concurrent users and won't
> allow for the copy. How can I find out who is on and then disconnect them?
>
|||I find that stopping and starting the SQL services usually does the
trick. Nice and easy too.
|||Thats a hard one if you have more than one userdatabase one your
server, because ALL serverusers will be wiped out of the system, so
better altering the database like DAN stated would be the best (it is
also my perferable solution, for a hard kick-off)
HTH, jens Suessmeyer.
try to use the Copy Database Wizard it finds to concurrent users and won't
allow for the copy. How can I find out who is on and then disconnect them?
exec sp_who2 to find them
KILL spid to close them
You only need to kill the spid's where the spid is greater than 50
http://sqlservercode.blogspot.com/
|||EXEC sp_who for discovering who is connected
KILL spid for killing a connection
Both are documented in Books On Line
Med Bouchenafa
"Cwhitmore" <Cwhitmore@.discussions.microsoft.com> wrote in message
news:2D491056-49C8-4061-BE1E-5E1F6F77D722@.microsoft.com...
> I'm in the process of moving our database server to new hardware, but when
> I
> try to use the Copy Database Wizard it finds to concurrent users and won't
> allow for the copy. How can I find out who is on and then disconnect them?
>
|||Another method for forcibly terminate database connections is:
ALTER DATABASE MyDatabase
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
and then back to normal:
ALTER DATABASE MyDatabase
SET MULTI_USER
See the Books Online for more information.
Hope this helps.
Dan Guzman
SQL Server MVP
"Cwhitmore" <Cwhitmore@.discussions.microsoft.com> wrote in message
news:2D491056-49C8-4061-BE1E-5E1F6F77D722@.microsoft.com...
> I'm in the process of moving our database server to new hardware, but when
> I
> try to use the Copy Database Wizard it finds to concurrent users and won't
> allow for the copy. How can I find out who is on and then disconnect them?
>
|||I find that stopping and starting the SQL services usually does the
trick. Nice and easy too.
|||Thats a hard one if you have more than one userdatabase one your
server, because ALL serverusers will be wiped out of the system, so
better altering the database like DAN stated would be the best (it is
also my perferable solution, for a hard kick-off)
HTH, jens Suessmeyer.
How to release user connections?
I'm in the process of moving our database server to new hardware, but when I
try to use the Copy Database Wizard it finds to concurrent users and won't
allow for the copy. How can I find out who is on and then disconnect them?exec sp_who2 to find them
KILL spid to close them
You only need to kill the spid's where the spid is greater than 50
http://sqlservercode.blogspot.com/|||EXEC sp_who for discovering who is connected
KILL spid for killing a connection
Both are documented in Books On Line
Med Bouchenafa
"Cwhitmore" <Cwhitmore@.discussions.microsoft.com> wrote in message
news:2D491056-49C8-4061-BE1E-5E1F6F77D722@.microsoft.com...
> I'm in the process of moving our database server to new hardware, but when
> I
> try to use the Copy Database Wizard it finds to concurrent users and won't
> allow for the copy. How can I find out who is on and then disconnect them?
>|||Another method for forcibly terminate database connections is:
ALTER DATABASE MyDatabase
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
and then back to normal:
ALTER DATABASE MyDatabase
SET MULTI_USER
See the Books Online for more information.
Hope this helps.
Dan Guzman
SQL Server MVP
"Cwhitmore" <Cwhitmore@.discussions.microsoft.com> wrote in message
news:2D491056-49C8-4061-BE1E-5E1F6F77D722@.microsoft.com...
> I'm in the process of moving our database server to new hardware, but when
> I
> try to use the Copy Database Wizard it finds to concurrent users and won't
> allow for the copy. How can I find out who is on and then disconnect them?
>|||I find that stopping and starting the SQL services usually does the
trick. Nice and easy too.|||Thats a hard one if you have more than one userdatabase one your
server, because ALL serverusers will be wiped out of the system, so
better altering the database like DAN stated would be the best (it is
also my perferable solution, for a hard kick-off)
HTH, jens Suessmeyer.
try to use the Copy Database Wizard it finds to concurrent users and won't
allow for the copy. How can I find out who is on and then disconnect them?exec sp_who2 to find them
KILL spid to close them
You only need to kill the spid's where the spid is greater than 50
http://sqlservercode.blogspot.com/|||EXEC sp_who for discovering who is connected
KILL spid for killing a connection
Both are documented in Books On Line
Med Bouchenafa
"Cwhitmore" <Cwhitmore@.discussions.microsoft.com> wrote in message
news:2D491056-49C8-4061-BE1E-5E1F6F77D722@.microsoft.com...
> I'm in the process of moving our database server to new hardware, but when
> I
> try to use the Copy Database Wizard it finds to concurrent users and won't
> allow for the copy. How can I find out who is on and then disconnect them?
>|||Another method for forcibly terminate database connections is:
ALTER DATABASE MyDatabase
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
and then back to normal:
ALTER DATABASE MyDatabase
SET MULTI_USER
See the Books Online for more information.
Hope this helps.
Dan Guzman
SQL Server MVP
"Cwhitmore" <Cwhitmore@.discussions.microsoft.com> wrote in message
news:2D491056-49C8-4061-BE1E-5E1F6F77D722@.microsoft.com...
> I'm in the process of moving our database server to new hardware, but when
> I
> try to use the Copy Database Wizard it finds to concurrent users and won't
> allow for the copy. How can I find out who is on and then disconnect them?
>|||I find that stopping and starting the SQL services usually does the
trick. Nice and easy too.|||Thats a hard one if you have more than one userdatabase one your
server, because ALL serverusers will be wiped out of the system, so
better altering the database like DAN stated would be the best (it is
also my perferable solution, for a hard kick-off)
HTH, jens Suessmeyer.
Monday, March 12, 2012
HOw to recover a database from the .LDF file.
Hi,
Due to a hardware problem I lost my mydatabase.MDF file, but my
mydatabase.LDF file is still OK because it is on another partition. I can
restore the database, but (here comes Murphy) the tape containing the
transaction logs backups is corrupt too. Since my last full backup I made
transaction log backups with the NO_TRUNCATE option, so everything should be
in the mydatabase.LDF file. After restoring the database in no_recover mode,
how can I apply the transactions that are still in mydatabase.LDF file
(NO_TRUNCATE)?
Any help is welcome.
Thanks
Felix> Since my last full backup I made
> transaction log backups with the NO_TRUNCATE option, so everything should be
> in the mydatabase.LDF file.
I'm afraid not. The name of that option is misleading. See
http://www.karaszi.com/SQLServer/info_restore_no_truncate.asp
Your best bet is probably to use a log reader tool and see if you can salvage anything from the
existing log backup. But, as per the article, the information in the prior log backups is most
probably lost. You might want to open a case with MS Support and see if they have anything 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/
"Felix" <Felix@.discussions.microsoft.com> wrote in message
news:D686735A-BF91-40EA-BBD3-16ED67B13E3B@.microsoft.com...
> Hi,
> Due to a hardware problem I lost my mydatabase.MDF file, but my
> mydatabase.LDF file is still OK because it is on another partition. I can
> restore the database, but (here comes Murphy) the tape containing the
> transaction logs backups is corrupt too. Since my last full backup I made
> transaction log backups with the NO_TRUNCATE option, so everything should be
> in the mydatabase.LDF file. After restoring the database in no_recover mode,
> how can I apply the transactions that are still in mydatabase.LDF file
> (NO_TRUNCATE)?
> Any help is welcome.
> Thanks
> Felix|||Hi Tibor,
Thanks for your answer, this helped us a lot.
Lucky enough, this was not a production database, but a test we were
performing.
Indead, the NO_TRUNCATE is misleading.
This means also that you are only able to restore till the time of failure
if you are able to backup the still existing logs first, else you can only
restore untill the latest log backup. This means that you should backup as
often as possible for critical DB's!
Kind regards
Felix
"Tibor Karaszi" wrote:
> > Since my last full backup I made
> > transaction log backups with the NO_TRUNCATE option, so everything should be
> > in the mydatabase.LDF file.
> I'm afraid not. The name of that option is misleading. See
> http://www.karaszi.com/SQLServer/info_restore_no_truncate.asp
> Your best bet is probably to use a log reader tool and see if you can salvage anything from the
> existing log backup. But, as per the article, the information in the prior log backups is most
> probably lost. You might want to open a case with MS Support and see if they have anything 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/
>
> "Felix" <Felix@.discussions.microsoft.com> wrote in message
> news:D686735A-BF91-40EA-BBD3-16ED67B13E3B@.microsoft.com...
> > Hi,
> > Due to a hardware problem I lost my mydatabase.MDF file, but my
> > mydatabase.LDF file is still OK because it is on another partition. I can
> > restore the database, but (here comes Murphy) the tape containing the
> > transaction logs backups is corrupt too. Since my last full backup I made
> > transaction log backups with the NO_TRUNCATE option, so everything should be
> > in the mydatabase.LDF file. After restoring the database in no_recover mode,
> > how can I apply the transactions that are still in mydatabase.LDF file
> > (NO_TRUNCATE)?
> >
> > Any help is welcome.
> >
> > Thanks
> >
> > Felix
>
Due to a hardware problem I lost my mydatabase.MDF file, but my
mydatabase.LDF file is still OK because it is on another partition. I can
restore the database, but (here comes Murphy) the tape containing the
transaction logs backups is corrupt too. Since my last full backup I made
transaction log backups with the NO_TRUNCATE option, so everything should be
in the mydatabase.LDF file. After restoring the database in no_recover mode,
how can I apply the transactions that are still in mydatabase.LDF file
(NO_TRUNCATE)?
Any help is welcome.
Thanks
Felix> Since my last full backup I made
> transaction log backups with the NO_TRUNCATE option, so everything should be
> in the mydatabase.LDF file.
I'm afraid not. The name of that option is misleading. See
http://www.karaszi.com/SQLServer/info_restore_no_truncate.asp
Your best bet is probably to use a log reader tool and see if you can salvage anything from the
existing log backup. But, as per the article, the information in the prior log backups is most
probably lost. You might want to open a case with MS Support and see if they have anything 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/
"Felix" <Felix@.discussions.microsoft.com> wrote in message
news:D686735A-BF91-40EA-BBD3-16ED67B13E3B@.microsoft.com...
> Hi,
> Due to a hardware problem I lost my mydatabase.MDF file, but my
> mydatabase.LDF file is still OK because it is on another partition. I can
> restore the database, but (here comes Murphy) the tape containing the
> transaction logs backups is corrupt too. Since my last full backup I made
> transaction log backups with the NO_TRUNCATE option, so everything should be
> in the mydatabase.LDF file. After restoring the database in no_recover mode,
> how can I apply the transactions that are still in mydatabase.LDF file
> (NO_TRUNCATE)?
> Any help is welcome.
> Thanks
> Felix|||Hi Tibor,
Thanks for your answer, this helped us a lot.
Lucky enough, this was not a production database, but a test we were
performing.
Indead, the NO_TRUNCATE is misleading.
This means also that you are only able to restore till the time of failure
if you are able to backup the still existing logs first, else you can only
restore untill the latest log backup. This means that you should backup as
often as possible for critical DB's!
Kind regards
Felix
"Tibor Karaszi" wrote:
> > Since my last full backup I made
> > transaction log backups with the NO_TRUNCATE option, so everything should be
> > in the mydatabase.LDF file.
> I'm afraid not. The name of that option is misleading. See
> http://www.karaszi.com/SQLServer/info_restore_no_truncate.asp
> Your best bet is probably to use a log reader tool and see if you can salvage anything from the
> existing log backup. But, as per the article, the information in the prior log backups is most
> probably lost. You might want to open a case with MS Support and see if they have anything 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/
>
> "Felix" <Felix@.discussions.microsoft.com> wrote in message
> news:D686735A-BF91-40EA-BBD3-16ED67B13E3B@.microsoft.com...
> > Hi,
> > Due to a hardware problem I lost my mydatabase.MDF file, but my
> > mydatabase.LDF file is still OK because it is on another partition. I can
> > restore the database, but (here comes Murphy) the tape containing the
> > transaction logs backups is corrupt too. Since my last full backup I made
> > transaction log backups with the NO_TRUNCATE option, so everything should be
> > in the mydatabase.LDF file. After restoring the database in no_recover mode,
> > how can I apply the transactions that are still in mydatabase.LDF file
> > (NO_TRUNCATE)?
> >
> > Any help is welcome.
> >
> > Thanks
> >
> > Felix
>
HOw to recover a database from the .LDF file.
Hi,
Due to a hardware problem I lost my mydatabase.MDF file, but my
mydatabase.LDF file is still OK because it is on another partition. I can
restore the database, but (here comes Murphy) the tape containing the
transaction logs backups is corrupt too. Since my last full backup I made
transaction log backups with the NO_TRUNCATE option, so everything should be
in the mydatabase.LDF file. After restoring the database in no_recover mode,
how can I apply the transactions that are still in mydatabase.LDF file
(NO_TRUNCATE)?
Any help is welcome.
Thanks
Felix
> Since my last full backup I made
> transaction log backups with the NO_TRUNCATE option, so everything should be
> in the mydatabase.LDF file.
I'm afraid not. The name of that option is misleading. See
http://www.karaszi.com/SQLServer/inf...o_truncate.asp
Your best bet is probably to use a log reader tool and see if you can salvage anything from the
existing log backup. But, as per the article, the information in the prior log backups is most
probably lost. You might want to open a case with MS Support and see if they have anything 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/
"Felix" <Felix@.discussions.microsoft.com> wrote in message
news:D686735A-BF91-40EA-BBD3-16ED67B13E3B@.microsoft.com...
> Hi,
> Due to a hardware problem I lost my mydatabase.MDF file, but my
> mydatabase.LDF file is still OK because it is on another partition. I can
> restore the database, but (here comes Murphy) the tape containing the
> transaction logs backups is corrupt too. Since my last full backup I made
> transaction log backups with the NO_TRUNCATE option, so everything should be
> in the mydatabase.LDF file. After restoring the database in no_recover mode,
> how can I apply the transactions that are still in mydatabase.LDF file
> (NO_TRUNCATE)?
> Any help is welcome.
> Thanks
> Felix
|||Hi Tibor,
Thanks for your answer, this helped us a lot.
Lucky enough, this was not a production database, but a test we were
performing.
Indead, the NO_TRUNCATE is misleading.
This means also that you are only able to restore till the time of failure
if you are able to backup the still existing logs first, else you can only
restore untill the latest log backup. This means that you should backup as
often as possible for critical DB's!
Kind regards
Felix
"Tibor Karaszi" wrote:
> I'm afraid not. The name of that option is misleading. See
> http://www.karaszi.com/SQLServer/inf...o_truncate.asp
> Your best bet is probably to use a log reader tool and see if you can salvage anything from the
> existing log backup. But, as per the article, the information in the prior log backups is most
> probably lost. You might want to open a case with MS Support and see if they have anything 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/
>
> "Felix" <Felix@.discussions.microsoft.com> wrote in message
> news:D686735A-BF91-40EA-BBD3-16ED67B13E3B@.microsoft.com...
>
Due to a hardware problem I lost my mydatabase.MDF file, but my
mydatabase.LDF file is still OK because it is on another partition. I can
restore the database, but (here comes Murphy) the tape containing the
transaction logs backups is corrupt too. Since my last full backup I made
transaction log backups with the NO_TRUNCATE option, so everything should be
in the mydatabase.LDF file. After restoring the database in no_recover mode,
how can I apply the transactions that are still in mydatabase.LDF file
(NO_TRUNCATE)?
Any help is welcome.
Thanks
Felix
> Since my last full backup I made
> transaction log backups with the NO_TRUNCATE option, so everything should be
> in the mydatabase.LDF file.
I'm afraid not. The name of that option is misleading. See
http://www.karaszi.com/SQLServer/inf...o_truncate.asp
Your best bet is probably to use a log reader tool and see if you can salvage anything from the
existing log backup. But, as per the article, the information in the prior log backups is most
probably lost. You might want to open a case with MS Support and see if they have anything 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/
"Felix" <Felix@.discussions.microsoft.com> wrote in message
news:D686735A-BF91-40EA-BBD3-16ED67B13E3B@.microsoft.com...
> Hi,
> Due to a hardware problem I lost my mydatabase.MDF file, but my
> mydatabase.LDF file is still OK because it is on another partition. I can
> restore the database, but (here comes Murphy) the tape containing the
> transaction logs backups is corrupt too. Since my last full backup I made
> transaction log backups with the NO_TRUNCATE option, so everything should be
> in the mydatabase.LDF file. After restoring the database in no_recover mode,
> how can I apply the transactions that are still in mydatabase.LDF file
> (NO_TRUNCATE)?
> Any help is welcome.
> Thanks
> Felix
|||Hi Tibor,
Thanks for your answer, this helped us a lot.
Lucky enough, this was not a production database, but a test we were
performing.
Indead, the NO_TRUNCATE is misleading.
This means also that you are only able to restore till the time of failure
if you are able to backup the still existing logs first, else you can only
restore untill the latest log backup. This means that you should backup as
often as possible for critical DB's!
Kind regards
Felix
"Tibor Karaszi" wrote:
> I'm afraid not. The name of that option is misleading. See
> http://www.karaszi.com/SQLServer/inf...o_truncate.asp
> Your best bet is probably to use a log reader tool and see if you can salvage anything from the
> existing log backup. But, as per the article, the information in the prior log backups is most
> probably lost. You might want to open a case with MS Support and see if they have anything 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/
>
> "Felix" <Felix@.discussions.microsoft.com> wrote in message
> news:D686735A-BF91-40EA-BBD3-16ED67B13E3B@.microsoft.com...
>
HOw to recover a database from the .LDF file.
Hi,
Due to a hardware problem I lost my mydatabase.MDF file, but my
mydatabase.LDF file is still OK because it is on another partition. I can
restore the database, but (here comes Murphy) the tape containing the
transaction logs backups is corrupt too. Since my last full backup I made
transaction log backups with the NO_TRUNCATE option, so everything should be
in the mydatabase.LDF file. After restoring the database in no_recover mode,
how can I apply the transactions that are still in mydatabase.LDF file
(NO_TRUNCATE)?
Any help is welcome.
Thanks
Felix> Since my last full backup I made
> transaction log backups with the NO_TRUNCATE option, so everything should
be
> in the mydatabase.LDF file.
I'm afraid not. The name of that option is misleading. See
http://www.karaszi.com/SQLServer/in...no_truncate.asp
Your best bet is probably to use a log reader tool and see if you can salvag
e anything from the
existing log backup. But, as per the article, the information in the prior l
og backups is most
probably lost. You might want to open a case with MS Support and see if they
have anything 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/
"Felix" <Felix@.discussions.microsoft.com> wrote in message
news:D686735A-BF91-40EA-BBD3-16ED67B13E3B@.microsoft.com...
> Hi,
> Due to a hardware problem I lost my mydatabase.MDF file, but my
> mydatabase.LDF file is still OK because it is on another partition. I can
> restore the database, but (here comes Murphy) the tape containing the
> transaction logs backups is corrupt too. Since my last full backup I made
> transaction log backups with the NO_TRUNCATE option, so everything should
be
> in the mydatabase.LDF file. After restoring the database in no_recover mod
e,
> how can I apply the transactions that are still in mydatabase.LDF file
> (NO_TRUNCATE)?
> Any help is welcome.
> Thanks
> Felix|||Hi Tibor,
Thanks for your answer, this helped us a lot.
Lucky enough, this was not a production database, but a test we were
performing.
Indead, the NO_TRUNCATE is misleading.
This means also that you are only able to restore till the time of failure
if you are able to backup the still existing logs first, else you can only
restore untill the latest log backup. This means that you should backup as
often as possible for critical DB's!
Kind regards
Felix
"Tibor Karaszi" wrote:
> I'm afraid not. The name of that option is misleading. See
> http://www.karaszi.com/SQLServer/in...no_truncate.asp
> Your best bet is probably to use a log reader tool and see if you can salv
age anything from the
> existing log backup. But, as per the article, the information in the prior
log backups is most
> probably lost. You might want to open a case with MS Support and see if th
ey have anything 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/
>
> "Felix" <Felix@.discussions.microsoft.com> wrote in message
> news:D686735A-BF91-40EA-BBD3-16ED67B13E3B@.microsoft.com...
>
Due to a hardware problem I lost my mydatabase.MDF file, but my
mydatabase.LDF file is still OK because it is on another partition. I can
restore the database, but (here comes Murphy) the tape containing the
transaction logs backups is corrupt too. Since my last full backup I made
transaction log backups with the NO_TRUNCATE option, so everything should be
in the mydatabase.LDF file. After restoring the database in no_recover mode,
how can I apply the transactions that are still in mydatabase.LDF file
(NO_TRUNCATE)?
Any help is welcome.
Thanks
Felix> Since my last full backup I made
> transaction log backups with the NO_TRUNCATE option, so everything should
be
> in the mydatabase.LDF file.
I'm afraid not. The name of that option is misleading. See
http://www.karaszi.com/SQLServer/in...no_truncate.asp
Your best bet is probably to use a log reader tool and see if you can salvag
e anything from the
existing log backup. But, as per the article, the information in the prior l
og backups is most
probably lost. You might want to open a case with MS Support and see if they
have anything 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/
"Felix" <Felix@.discussions.microsoft.com> wrote in message
news:D686735A-BF91-40EA-BBD3-16ED67B13E3B@.microsoft.com...
> Hi,
> Due to a hardware problem I lost my mydatabase.MDF file, but my
> mydatabase.LDF file is still OK because it is on another partition. I can
> restore the database, but (here comes Murphy) the tape containing the
> transaction logs backups is corrupt too. Since my last full backup I made
> transaction log backups with the NO_TRUNCATE option, so everything should
be
> in the mydatabase.LDF file. After restoring the database in no_recover mod
e,
> how can I apply the transactions that are still in mydatabase.LDF file
> (NO_TRUNCATE)?
> Any help is welcome.
> Thanks
> Felix|||Hi Tibor,
Thanks for your answer, this helped us a lot.
Lucky enough, this was not a production database, but a test we were
performing.
Indead, the NO_TRUNCATE is misleading.
This means also that you are only able to restore till the time of failure
if you are able to backup the still existing logs first, else you can only
restore untill the latest log backup. This means that you should backup as
often as possible for critical DB's!
Kind regards
Felix
"Tibor Karaszi" wrote:
> I'm afraid not. The name of that option is misleading. See
> http://www.karaszi.com/SQLServer/in...no_truncate.asp
> Your best bet is probably to use a log reader tool and see if you can salv
age anything from the
> existing log backup. But, as per the article, the information in the prior
log backups is most
> probably lost. You might want to open a case with MS Support and see if th
ey have anything 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/
>
> "Felix" <Felix@.discussions.microsoft.com> wrote in message
> news:D686735A-BF91-40EA-BBD3-16ED67B13E3B@.microsoft.com...
>
How to Reboot a Publication server ?
We have to add some hardware and I was wondering if someone could provide a stepwise tip to do it .
It is a 3 Server Environment Pub/Dist/Sub
All three running under SQL2K
Here is what I am planning.
a. Uncheck the Enable in Distributed Agent for each replication
b. Wait for all replications to complete
c. Shut down Publisher ? Or do I have to do something else before that.
Please explain a little .
ThanksStop Distribution Agent, make CHECKPOINT on Publisher and shut it down. You also could just shut down publisher but it is not a good practise.|||Thanks . I guess I don't have to worry about Log reader agents ! They will die once publisher is out ?
It is a 3 Server Environment Pub/Dist/Sub
All three running under SQL2K
Here is what I am planning.
a. Uncheck the Enable in Distributed Agent for each replication
b. Wait for all replications to complete
c. Shut down Publisher ? Or do I have to do something else before that.
Please explain a little .
ThanksStop Distribution Agent, make CHECKPOINT on Publisher and shut it down. You also could just shut down publisher but it is not a good practise.|||Thanks . I guess I don't have to worry about Log reader agents ! They will die once publisher is out ?
Subscribe to:
Posts (Atom)