database problem: ran

Showing posts with label ran. Show all posts

Friday, March 23, 2012

Restoring SQL Server Data

hi. I just ran a dts that overwrote my data in my sql server 2000 database. Is it possible to still recover my old data?if you are running in full backup mode and you just ran DML statements, backup the transaction log of the database and restore the whole backup (full + evtl. differential + transaction log) with the option point -in-time recovery, stopping the restore until a specific time. if you don′t have the full backup model or no full backup, then there is no chance.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

I agree with the above answer, but if you have not been taking backups you will not be able to recover the database.

sql

Monday, March 12, 2012

restoring master database - single user mode

I am trying to restore a backup of a master database from one server to a
new server.
I stopped the SQL Server services, and ran "sqlservr.exe -c -m" at a command
prompt to start it in single user mode. It finished with "SQL Global
counter collection task is created." It appears to stop at this point -
there is no prompt.
While in single-user mode, I open Query Analyzer and attempt to log in both
with Windows Authentication and with SQL Server authentication, using the sa
login.
In both cases, I get an error "Login failed for user '(login)'. Reason:
Server is in single user mode. Only one administrator can connect at this
time."
Thanks for any suggestions.
-BillThere might be an EM connection that is already open.
"bill" <belgie@.datamti.com> wrote in message
news:OvtzHq4tEHA.2124@.TK2MSFTNGP11.phx.gbl...
> I am trying to restore a backup of a master database from one server to a
> new server.
> I stopped the SQL Server services, and ran "sqlservr.exe -c -m" at a
command
> prompt to start it in single user mode. It finished with "SQL Global
> counter collection task is created." It appears to stop at this point -
> there is no prompt.
> While in single-user mode, I open Query Analyzer and attempt to log in
both
> with Windows Authentication and with SQL Server authentication, using the
sa
> login.
> In both cases, I get an error "Login failed for user '(login)'. Reason:
> Server is in single user mode. Only one administrator can connect at this
> time."
> Thanks for any suggestions.
> -Bill
>|||Good thought, but that's not it.
Thanks!
"Bhanu" <SQLDBA1999@.yahoo.com> wrote in message
news:epspEa5tEHA.2692@.TK2MSFTNGP10.phx.gbl...
> There might be an EM connection that is already open.
> "bill" <belgie@.datamti.com> wrote in message
> news:OvtzHq4tEHA.2124@.TK2MSFTNGP11.phx.gbl...
> > I am trying to restore a backup of a master database from one server to
a
> > new server.
> >
> > I stopped the SQL Server services, and ran "sqlservr.exe -c -m" at a
> command
> > prompt to start it in single user mode. It finished with "SQL Global
> > counter collection task is created." It appears to stop at this point -
> > there is no prompt.
> >
> > While in single-user mode, I open Query Analyzer and attempt to log in
> both
> > with Windows Authentication and with SQL Server authentication, using
the
> sa
> > login.
> >
> > In both cases, I get an error "Login failed for user '(login)'. Reason:
> > Server is in single user mode. Only one administrator can connect at
this
> > time."
> >
> > Thanks for any suggestions.
> > -Bill
> >
> >
>|||Agent? Other services or users? You need to find who is connected...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"bill" <belgie@.datamti.com> wrote in message news:ug%23zrT6tEHA.1228@.TK2MSFTNGP10.phx.gbl...
> Good thought, but that's not it.
> Thanks!
> "Bhanu" <SQLDBA1999@.yahoo.com> wrote in message
> news:epspEa5tEHA.2692@.TK2MSFTNGP10.phx.gbl...
> > There might be an EM connection that is already open.
> >
> > "bill" <belgie@.datamti.com> wrote in message
> > news:OvtzHq4tEHA.2124@.TK2MSFTNGP11.phx.gbl...
> > > I am trying to restore a backup of a master database from one server to
> a
> > > new server.
> > >
> > > I stopped the SQL Server services, and ran "sqlservr.exe -c -m" at a
> > command
> > > prompt to start it in single user mode. It finished with "SQL Global
> > > counter collection task is created." It appears to stop at this point -
> > > there is no prompt.
> > >
> > > While in single-user mode, I open Query Analyzer and attempt to log in
> > both
> > > with Windows Authentication and with SQL Server authentication, using
> the
> > sa
> > > login.
> > >
> > > In both cases, I get an error "Login failed for user '(login)'. Reason:
> > > Server is in single user mode. Only one administrator can connect at
> this
> > > time."
> > >
> > > Thanks for any suggestions.
> > > -Bill
> > >
> > >
> >
> >
>|||How can I do this? I can't connect to SQL Server to see the connections.
Thanks
Bill
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Oj%23tuBCuEHA.2956@.TK2MSFTNGP12.phx.gbl...
> Agent? Other services or users? You need to find who is connected...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "bill" <belgie@.datamti.com> wrote in message
news:ug%23zrT6tEHA.1228@.TK2MSFTNGP10.phx.gbl...
> > Good thought, but that's not it.
> >
> > Thanks!
> >
> > "Bhanu" <SQLDBA1999@.yahoo.com> wrote in message
> > news:epspEa5tEHA.2692@.TK2MSFTNGP10.phx.gbl...
> > > There might be an EM connection that is already open.
> > >
> > > "bill" <belgie@.datamti.com> wrote in message
> > > news:OvtzHq4tEHA.2124@.TK2MSFTNGP11.phx.gbl...
> > > > I am trying to restore a backup of a master database from one server
to
> > a
> > > > new server.
> > > >
> > > > I stopped the SQL Server services, and ran "sqlservr.exe -c -m" at a
> > > command
> > > > prompt to start it in single user mode. It finished with "SQL
Global
> > > > counter collection task is created." It appears to stop at this
point -
> > > > there is no prompt.
> > > >
> > > > While in single-user mode, I open Query Analyzer and attempt to log
in
> > > both
> > > > with Windows Authentication and with SQL Server authentication,
using
> > the
> > > sa
> > > > login.
> > > >
> > > > In both cases, I get an error "Login failed for user '(login)'.
Reason:
> > > > Server is in single user mode. Only one administrator can connect at
> > this
> > > > time."
> > > >
> > > > Thanks for any suggestions.
> > > > -Bill
> > > >
> > > >
> > >
> > >
> >
> >
>|||Start stopping SQL server Agent, and other possible services that might use SQL server. You might
need to get physical access to the machine, or terminal services, so you can work locally on the
machine stopping services and perhaps even disabling network access (which isn't doable over
terminal services, of course).
Another thing you can try is to login using OSQL.EXE instead of Query Analyzer. QA *might* try to
open a connection for Object Browser and that *might* get connected before your query window.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"bill" <belgie@.datamti.com> wrote in message news:emBg4NEuEHA.348@.tk2msftngp13.phx.gbl...
> How can I do this? I can't connect to SQL Server to see the connections.
> Thanks
> Bill
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:Oj%23tuBCuEHA.2956@.TK2MSFTNGP12.phx.gbl...
>> Agent? Other services or users? You need to find who is connected...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "bill" <belgie@.datamti.com> wrote in message
> news:ug%23zrT6tEHA.1228@.TK2MSFTNGP10.phx.gbl...
>> > Good thought, but that's not it.
>> >
>> > Thanks!
>> >
>> > "Bhanu" <SQLDBA1999@.yahoo.com> wrote in message
>> > news:epspEa5tEHA.2692@.TK2MSFTNGP10.phx.gbl...
>> > > There might be an EM connection that is already open.
>> > >
>> > > "bill" <belgie@.datamti.com> wrote in message
>> > > news:OvtzHq4tEHA.2124@.TK2MSFTNGP11.phx.gbl...
>> > > > I am trying to restore a backup of a master database from one server
> to
>> > a
>> > > > new server.
>> > > >
>> > > > I stopped the SQL Server services, and ran "sqlservr.exe -c -m" at a
>> > > command
>> > > > prompt to start it in single user mode. It finished with "SQL
> Global
>> > > > counter collection task is created." It appears to stop at this
> point -
>> > > > there is no prompt.
>> > > >
>> > > > While in single-user mode, I open Query Analyzer and attempt to log
> in
>> > > both
>> > > > with Windows Authentication and with SQL Server authentication,
> using
>> > the
>> > > sa
>> > > > login.
>> > > >
>> > > > In both cases, I get an error "Login failed for user '(login)'.
> Reason:
>> > > > Server is in single user mode. Only one administrator can connect at
>> > this
>> > > > time."
>> > > >
>> > > > Thanks for any suggestions.
>> > > > -Bill
>> > > >
>> > > >
>> > >
>> > >
>> >
>> >
>>
>

Friday, March 9, 2012

Restoring from non-truncated transaction log

We have a database which unfortunately someone has restored from a backup
which was 2+ months old after he ran a script against the database that had
negative effects. So, now there is a gap in the data from Nov23 â' Jan 31.
What I am wondering is would it be possible to restore the missing data in
the database with the transaction log? We were not backing up the
transaction log; so, it was not truncated. We have detached the database and
the transaction log and reattached them on another server.
For some reason every time I explain this in person, people miss something.
So, here are the basic facts:
MS SQL server 2000 database restored from backup
2 months of missing data
in tact(not truncated) transaction log
a script was run against the database before it was restored from backup on
Jan 31
database structure is identical.
Is this savable data, or should we just give up. I am not a DBA. He quit
about 1.5 months ago; so please be specific about what to do as I am not yet
extreamly familiar with SQL server 2000. By the way we have been working on
this since the 31st, and all we know for sure is that SQL doesn't like it
when you do something like this. Thanks in advance for any guidance either
way.> We have a database which unfortunately someone has restored from a backup
> which was 2+ months old after he ran a script against the database that had
> negative effects.
That restore overwrites everything inside the database. Including the entries in the transaction
log. This of a restore like applying a VM Ware image on a machine.
> What I am wondering is would it be possible to restore the missing data in
> the database with the transaction log?
Are you saying that you saved something, somehow (backup, detach) before that restore was performed?
If not, I fail to see anyway to get anything back. Unelss you have some type of backup which is more
recent that the 2 month old backup.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Support" <Support@.discussions.microsoft.com> wrote in message
news:A8AE3D87-6975-444D-BD77-23407F529DEE@.microsoft.com...
> We have a database which unfortunately someone has restored from a backup
> which was 2+ months old after he ran a script against the database that had
> negative effects. So, now there is a gap in the data from Nov23 - Jan 31.
> What I am wondering is would it be possible to restore the missing data in
> the database with the transaction log? We were not backing up the
> transaction log; so, it was not truncated. We have detached the database and
> the transaction log and reattached them on another server.
> For some reason every time I explain this in person, people miss something.
> So, here are the basic facts:
> MS SQL server 2000 database restored from backup
> 2 months of missing data
> in tact(not truncated) transaction log
> a script was run against the database before it was restored from backup on
> Jan 31
> database structure is identical.
> Is this savable data, or should we just give up. I am not a DBA. He quit
> about 1.5 months ago; so please be specific about what to do as I am not yet
> extreamly familiar with SQL server 2000. By the way we have been working on
> this since the 31st, and all we know for sure is that SQL doesn't like it
> when you do something like this. Thanks in advance for any guidance either
> way.|||> That restore overwrites everything inside the database. Including the
entries in the transaction
> log. This of a restore like applying a VM Ware image on a machine.
Like I said I am not very experienced with SQL. Please forgive my ignorance
in this matter. We were not backing up the Transaction log, just the
database. When the restore was done it wasn't restoring the transaction log.
Does it remove the entries from the transaction log when you restore the
database form a backup even if it doesn't restore the transaction log? I
would think that would be the opposite of what the transaction log was
supposed to do.
"Tibor Karaszi" wrote:
> > We have a database which unfortunately someone has restored from a backup
> > which was 2+ months old after he ran a script against the database that had
> > negative effects.
> That restore overwrites everything inside the database. Including the entries in the transaction
> log. This of a restore like applying a VM Ware image on a machine.
>
> > What I am wondering is would it be possible to restore the missing data in
> > the database with the transaction log?
> Are you saying that you saved something, somehow (backup, detach) before that restore was performed?
> If not, I fail to see anyway to get anything back. Unelss you have some type of backup which is more
> recent that the 2 month old backup.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Support" <Support@.discussions.microsoft.com> wrote in message
> news:A8AE3D87-6975-444D-BD77-23407F529DEE@.microsoft.com...
> > We have a database which unfortunately someone has restored from a backup
> > which was 2+ months old after he ran a script against the database that had
> > negative effects. So, now there is a gap in the data from Nov23 - Jan 31.
> > What I am wondering is would it be possible to restore the missing data in
> > the database with the transaction log? We were not backing up the
> > transaction log; so, it was not truncated. We have detached the database and
> > the transaction log and reattached them on another server.
> >
> > For some reason every time I explain this in person, people miss something.
> > So, here are the basic facts:
> >
> > MS SQL server 2000 database restored from backup
> > 2 months of missing data
> > in tact(not truncated) transaction log
> > a script was run against the database before it was restored from backup on
> > Jan 31
> > database structure is identical.
> >
> > Is this savable data, or should we just give up. I am not a DBA. He quit
> > about 1.5 months ago; so please be specific about what to do as I am not yet
> > extreamly familiar with SQL server 2000. By the way we have been working on
> > this since the 31st, and all we know for sure is that SQL doesn't like it
> > when you do something like this. Thanks in advance for any guidance either
> > way.
>
>|||Hi,
In my opinion, if
1- the DB was in Full recovery mode
and
2- you have have the complete T-log since Nov
then it will work.
But I would be surprised if you meet both requirement
Chris
"Support" <Support@.discussions.microsoft.com> a écrit dans le message de
news:A8AE3D87-6975-444D-BD77-23407F529DEE@.microsoft.com...
> We have a database which unfortunately someone has restored from a backup
> which was 2+ months old after he ran a script against the database that
had
> negative effects. So, now there is a gap in the data from Nov23 - Jan 31.
> What I am wondering is would it be possible to restore the missing data in
> the database with the transaction log? We were not backing up the
> transaction log; so, it was not truncated. We have detached the database
and
> the transaction log and reattached them on another server.
> For some reason every time I explain this in person, people miss
something.
> So, here are the basic facts:
> MS SQL server 2000 database restored from backup
> 2 months of missing data
> in tact(not truncated) transaction log
> a script was run against the database before it was restored from backup
on
> Jan 31
> database structure is identical.
> Is this savable data, or should we just give up. I am not a DBA. He quit
> about 1.5 months ago; so please be specific about what to do as I am not
yet
> extreamly familiar with SQL server 2000. By the way we have been working
on
> this since the 31st, and all we know for sure is that SQL doesn't like it
> when you do something like this. Thanks in advance for any guidance
either
> way.|||>> 1- the DB was in Full recovery mode
Sorry about this. Like I said, I am not very experienced with SQL. I had
NOT been backing up the transaction log, so I would assume the transaction
log would still have all of that data in it. Does it remove data from the
transaction log when you restore a database but not the transaction log? As
far as the "Full recovery mode" goes, do you mean backup the entire database
every time. We do backup the entire database as opposed to just the changes,
but not the transaction logs. Is this what you are referring to, or is it a
setting on the restore?
"Chris" wrote:
> Hi,
> In my opinion, if
> 1- the DB was in Full recovery mode
> and
> 2- you have have the complete T-log since Nov
> then it will work.
> But I would be surprised if you meet both requirement
> Chris
>
> "Support" <Support@.discussions.microsoft.com> a Ã©crit dans le message de
> news:A8AE3D87-6975-444D-BD77-23407F529DEE@.microsoft.com...
> > We have a database which unfortunately someone has restored from a backup
> > which was 2+ months old after he ran a script against the database that
> had
> > negative effects. So, now there is a gap in the data from Nov23 - Jan 31.
> > What I am wondering is would it be possible to restore the missing data in
> > the database with the transaction log? We were not backing up the
> > transaction log; so, it was not truncated. We have detached the database
> and
> > the transaction log and reattached them on another server.
> >
> > For some reason every time I explain this in person, people miss
> something.
> > So, here are the basic facts:
> >
> > MS SQL server 2000 database restored from backup
> > 2 months of missing data
> > in tact(not truncated) transaction log
> > a script was run against the database before it was restored from backup
> on
> > Jan 31
> > database structure is identical.
> >
> > Is this savable data, or should we just give up. I am not a DBA. He quit
> > about 1.5 months ago; so please be specific about what to do as I am not
> yet
> > extreamly familiar with SQL server 2000. By the way we have been working
> on
> > this since the 31st, and all we know for sure is that SQL doesn't like it
> > when you do something like this. Thanks in advance for any guidance
> either
> > way.
>
>|||Hi - I suggest you read the Books Online topics on recovery models
('Selecting a Recovery Model' is a good start) as that will help you
understand the issues and alternatives you have depending on what recovery
model you're using.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"2 Struggling Admins" <2StrugglingAdmins@.discussions.microsoft.com> wrote in
message news:BABCE850-09CC-4766-A370-71360F7C9C8C@.microsoft.com...
> >> 1- the DB was in Full recovery mode
> Sorry about this. Like I said, I am not very experienced with SQL. I had
> NOT been backing up the transaction log, so I would assume the transaction
> log would still have all of that data in it. Does it remove data from the
> transaction log when you restore a database but not the transaction log?
As
> far as the "Full recovery mode" goes, do you mean backup the entire
database
> every time. We do backup the entire database as opposed to just the
changes,
> but not the transaction logs. Is this what you are referring to, or is it
a
> setting on the restore?
> "Chris" wrote:
> > Hi,
> >
> > In my opinion, if
> > 1- the DB was in Full recovery mode
> > and
> > 2- you have have the complete T-log since Nov
> >
> > then it will work.
> >
> > But I would be surprised if you meet both requirement
> > Chris
> >
> >
> > "Support" <Support@.discussions.microsoft.com> a écrit dans le message de
> > news:A8AE3D87-6975-444D-BD77-23407F529DEE@.microsoft.com...
> > > We have a database which unfortunately someone has restored from a
backup
> > > which was 2+ months old after he ran a script against the database
that
> > had
> > > negative effects. So, now there is a gap in the data from Nov23 - Jan
31.
> > > What I am wondering is would it be possible to restore the missing
data in
> > > the database with the transaction log? We were not backing up the
> > > transaction log; so, it was not truncated. We have detached the
database
> > and
> > > the transaction log and reattached them on another server.
> > >
> > > For some reason every time I explain this in person, people miss
> > something.
> > > So, here are the basic facts:
> > >
> > > MS SQL server 2000 database restored from backup
> > > 2 months of missing data
> > > in tact(not truncated) transaction log
> > > a script was run against the database before it was restored from
backup
> > on
> > > Jan 31
> > > database structure is identical.
> > >
> > > Is this savable data, or should we just give up. I am not a DBA. He
quit
> > > about 1.5 months ago; so please be specific about what to do as I am
not
> > yet
> > > extreamly familiar with SQL server 2000. By the way we have been
working
> > on
> > > this since the 31st, and all we know for sure is that SQL doesn't like
it
> > > when you do something like this. Thanks in advance for any guidance
> > either
> > > way.
> >
> >
> >|||The transaction log is there to support transactional consistency (rollbacks and recovery) as well
as supporting "incremental" backup (backup of the transaction log).
A database backup contains both data pages as well as the necessary log records (from the
transaction log file(s)). This is so that SQL Server when you restore can apply the data pages and
perform recovery (roll -forward and -back). When your restore a database backup, the contents of the
ldf file of the current database will be overwritten. I agree with Paul R regarding reading up on
the suggested section on Books Online. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"2 Struggling Admins" <2StrugglingAdmins@.discussions.microsoft.com> wrote in message
news:12970127-DB33-4D7B-ADE7-7136BD17E4F8@.microsoft.com...
>> That restore overwrites everything inside the database. Including the
> entries in the transaction
>> log. This of a restore like applying a VM Ware image on a machine.
> Like I said I am not very experienced with SQL. Please forgive my ignorance
> in this matter. We were not backing up the Transaction log, just the
> database. When the restore was done it wasn't restoring the transaction log.
> Does it remove the entries from the transaction log when you restore the
> database form a backup even if it doesn't restore the transaction log? I
> would think that would be the opposite of what the transaction log was
> supposed to do.
>
> "Tibor Karaszi" wrote:
>> > We have a database which unfortunately someone has restored from a backup
>> > which was 2+ months old after he ran a script against the database that had
>> > negative effects.
>> That restore overwrites everything inside the database. Including the entries in the transaction
>> log. This of a restore like applying a VM Ware image on a machine.
>>
>> > What I am wondering is would it be possible to restore the missing data in
>> > the database with the transaction log?
>> Are you saying that you saved something, somehow (backup, detach) before that restore was
>> performed?
>> If not, I fail to see anyway to get anything back. Unelss you have some type of backup which is
>> more
>> recent that the 2 month old backup.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> http://www.sqlug.se/
>>
>> "Support" <Support@.discussions.microsoft.com> wrote in message
>> news:A8AE3D87-6975-444D-BD77-23407F529DEE@.microsoft.com...
>> > We have a database which unfortunately someone has restored from a backup
>> > which was 2+ months old after he ran a script against the database that had
>> > negative effects. So, now there is a gap in the data from Nov23 - Jan 31.
>> > What I am wondering is would it be possible to restore the missing data in
>> > the database with the transaction log? We were not backing up the
>> > transaction log; so, it was not truncated. We have detached the database and
>> > the transaction log and reattached them on another server.
>> >
>> > For some reason every time I explain this in person, people miss something.
>> > So, here are the basic facts:
>> >
>> > MS SQL server 2000 database restored from backup
>> > 2 months of missing data
>> > in tact(not truncated) transaction log
>> > a script was run against the database before it was restored from backup on
>> > Jan 31
>> > database structure is identical.
>> >
>> > Is this savable data, or should we just give up. I am not a DBA. He quit
>> > about 1.5 months ago; so please be specific about what to do as I am not yet
>> > extreamly familiar with SQL server 2000. By the way we have been working on
>> > this since the 31st, and all we know for sure is that SQL doesn't like it
>> > when you do something like this. Thanks in advance for any guidance either
>> > way.
>>

Friday, March 23, 2012

Restoring SQL Server Data

Monday, March 12, 2012

restoring master database - single user mode

restoring master database - single user mode

restoring master database - single user mode

Friday, March 9, 2012

Restoring from non-truncated transaction log

Restoring from non-truncated transaction log

Restoring from non-truncated transaction log

database problem

Blog Archive

About Me