I try to access a remote SP.
Server1 has a working DTS-service running
Server2 cannot start the DTS due to the following error:
"An error 1060 - (the specified service does not exist as an installes service) occured while performing this service operation on the MSDTC service".
Ok, I got it, the service isn't running (since I get this mess. when I'm tryin to start the service).
Can't find any service in the opsys services. Tried to reinstall the MSSQL but the MSDTC was/is allready installed.
Please help!
By the way, I'm running MSSQL 2000.
thanksIs the dtc service started ?|||Originally posted by rnealejr
Is the dtc service started ?
Nope, and that's the problem. I get the errormessage when I try to start the service in enterprise manager.
I can't find any corresponding service in the opsys services. It seems like the service isn't installed...but it is??!!
Showing posts with label due. Show all posts
Showing posts with label due. Show all posts
Sunday, March 25, 2012
Based on backup and recovery
hello,
i have taken a backup at 3pm ,
some data was entered in an xx table at 5pm,
which was lost due to some reasons at 530pm,
now if i want to get back the data what i should do..
as we have a concept of time based recovery in oracle
what is the method used here to get my data back
i have taken my second backup at 7pm
its an imm. requirement for me
just help me out...
thnx & regards
pavanSimply use FULL recovery model to the database which benefits in - No work is lost due to a lost or damaged data file. Can recover to an arbitrary point in time (for example, prior to application or user error).
i have taken a backup at 3pm ,
some data was entered in an xx table at 5pm,
which was lost due to some reasons at 530pm,
now if i want to get back the data what i should do..
as we have a concept of time based recovery in oracle
what is the method used here to get my data back
i have taken my second backup at 7pm
its an imm. requirement for me
just help me out...
thnx & regards
pavanSimply use FULL recovery model to the database which benefits in - No work is lost due to a lost or damaged data file. Can recover to an arbitrary point in time (for example, prior to application or user error).
Monday, March 19, 2012
bad performance due to drivers?
Do you know if there are any performance issues using a sql server 3.70 driver on an application server versus using a 2000 version driver? The DB is on a server with SQL Server 2000 EE. We have four application servers and 2 have 2000 drivers (and window
s SP4) and the other 2 have 3.70 drivers (and windows SP3) and the ones with the 3.70 drivers keep getting timeouts. CPU and memory usage doesn't seem very high - does anyone know what else to monitor to see what could be affecting performance? Thanks.
One way to analyze the performance issues in your 7.0 environment would be
to enable SQL Server profiler and capture the execution plan, show plan
statistics, Started and completed events for RPC, SQL and Batch. We can
compare the results to SQL 2000.
Other factors (other than drivers) that could contribute to the
differences in the performance in the two environments are;
- SQL Server 2000 vs 7.0
- Data differences in the two environments
- Application differences
- Maybe network related issues
If you require assistance in analyzing the profiler data please contact
Microsoft Support.
HTH
Agnes Panosian
SQL Server Support
|||Here are some references that may also help
http://msdn.microsoft.com/library/de...us/dnsql7/html
/msdn_sql7perftune.asp
KB articles
INF: Job to Monitor SQL Server 7.0 Performance and Activity ID: 286191
HOW TO: Troubleshoot Application Performance with SQL Server ID: 224587
INF: How to Monitor SQL Server 7.0 Blocking ID: 251004.
s SP4) and the other 2 have 3.70 drivers (and windows SP3) and the ones with the 3.70 drivers keep getting timeouts. CPU and memory usage doesn't seem very high - does anyone know what else to monitor to see what could be affecting performance? Thanks.
One way to analyze the performance issues in your 7.0 environment would be
to enable SQL Server profiler and capture the execution plan, show plan
statistics, Started and completed events for RPC, SQL and Batch. We can
compare the results to SQL 2000.
Other factors (other than drivers) that could contribute to the
differences in the performance in the two environments are;
- SQL Server 2000 vs 7.0
- Data differences in the two environments
- Application differences
- Maybe network related issues
If you require assistance in analyzing the profiler data please contact
Microsoft Support.
HTH
Agnes Panosian
SQL Server Support
|||Here are some references that may also help
http://msdn.microsoft.com/library/de...us/dnsql7/html
/msdn_sql7perftune.asp
KB articles
INF: Job to Monitor SQL Server 7.0 Performance and Activity ID: 286191
HOW TO: Troubleshoot Application Performance with SQL Server ID: 224587
INF: How to Monitor SQL Server 7.0 Blocking ID: 251004.
bad performance due to drivers?
Do you know if there are any performance issues using a sql server 3.70 driver on an application server versus using a 2000 version driver? The DB is on a server with SQL Server 2000 EE. We have four application servers and 2 have 2000 drivers (and windows SP4) and the other 2 have 3.70 drivers (and windows SP3) and the ones with the 3.70 drivers keep getting timeouts. CPU and memory usage doesn't seem very high - does anyone know what else to monitor to see what could be affecting performance? Thanks.One way to analyze the performance issues in your 7.0 environment would be
to enable SQL Server profiler and capture the execution plan, show plan
statistics, Started and completed events for RPC, SQL and Batch. We can
compare the results to SQL 2000.
Other factors (other than drivers) that could contribute to the
differences in the performance in the two environments are;
- SQL Server 2000 vs 7.0
- Data differences in the two environments
- Application differences
- Maybe network related issues
If you require assistance in analyzing the profiler data please contact
Microsoft Support.
HTH
Agnes Panosian
SQL Server Support|||Here are some references that may also help
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql7/html
/msdn_sql7perftune.asp
KB articles
INF: Job to Monitor SQL Server 7.0 Performance and Activity ID: 286191
HOW TO: Troubleshoot Application Performance with SQL Server ID: 224587
INF: How to Monitor SQL Server 7.0 Blocking ID: 251004.
to enable SQL Server profiler and capture the execution plan, show plan
statistics, Started and completed events for RPC, SQL and Batch. We can
compare the results to SQL 2000.
Other factors (other than drivers) that could contribute to the
differences in the performance in the two environments are;
- SQL Server 2000 vs 7.0
- Data differences in the two environments
- Application differences
- Maybe network related issues
If you require assistance in analyzing the profiler data please contact
Microsoft Support.
HTH
Agnes Panosian
SQL Server Support|||Here are some references that may also help
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql7/html
/msdn_sql7perftune.asp
KB articles
INF: Job to Monitor SQL Server 7.0 Performance and Activity ID: 286191
HOW TO: Troubleshoot Application Performance with SQL Server ID: 224587
INF: How to Monitor SQL Server 7.0 Blocking ID: 251004.
bad performance due to drivers?
Do you know if there are any performance issues using a sql server 3.70 driv
er on an application server versus using a 2000 version driver? The DB is on
a server with SQL Server 2000 EE. We have four application servers and 2 ha
ve 2000 drivers (and window
s SP4) and the other 2 have 3.70 drivers (and windows SP3) and the ones with
the 3.70 drivers keep getting timeouts. CPU and memory usage doesn't seem v
ery high - does anyone know what else to monitor to see what could be affect
ing performance? Thanks.One way to analyze the performance issues in your 7.0 environment would be
to enable SQL Server profiler and capture the execution plan, show plan
statistics, Started and completed events for RPC, SQL and Batch. We can
compare the results to SQL 2000.
Other factors (other than drivers) that could contribute to the
differences in the performance in the two environments are;
- SQL Server 2000 vs 7.0
- Data differences in the two environments
- Application differences
- Maybe network related issues
If you require assistance in analyzing the profiler data please contact
Microsoft Support.
HTH
Agnes Panosian
SQL Server Support|||Here are some references that may also help
http://msdn.microsoft.com/library/d...-us/dnsql7/html
/msdn_sql7perftune.asp
KB articles
INF: Job to Monitor SQL Server 7.0 Performance and Activity ID: 286191
HOW TO: Troubleshoot Application Performance with SQL Server ID: 224587
INF: How to Monitor SQL Server 7.0 Blocking ID: 251004.
er on an application server versus using a 2000 version driver? The DB is on
a server with SQL Server 2000 EE. We have four application servers and 2 ha
ve 2000 drivers (and window
s SP4) and the other 2 have 3.70 drivers (and windows SP3) and the ones with
the 3.70 drivers keep getting timeouts. CPU and memory usage doesn't seem v
ery high - does anyone know what else to monitor to see what could be affect
ing performance? Thanks.One way to analyze the performance issues in your 7.0 environment would be
to enable SQL Server profiler and capture the execution plan, show plan
statistics, Started and completed events for RPC, SQL and Batch. We can
compare the results to SQL 2000.
Other factors (other than drivers) that could contribute to the
differences in the performance in the two environments are;
- SQL Server 2000 vs 7.0
- Data differences in the two environments
- Application differences
- Maybe network related issues
If you require assistance in analyzing the profiler data please contact
Microsoft Support.
HTH
Agnes Panosian
SQL Server Support|||Here are some references that may also help
http://msdn.microsoft.com/library/d...-us/dnsql7/html
/msdn_sql7perftune.asp
KB articles
INF: Job to Monitor SQL Server 7.0 Performance and Activity ID: 286191
HOW TO: Troubleshoot Application Performance with SQL Server ID: 224587
INF: How to Monitor SQL Server 7.0 Blocking ID: 251004.
Sunday, February 12, 2012
Backup transaction logs when the database is damaged.
Hi,
Due to a disk failure, I lost my .mdf file of a database. Of course I have a
backup, of the database and transactionlogs from last night, but I want to
restore untill the time of failure. So I tried to follow the procedure
explained in : how to backup transaction logs when the database is damaged.
But it wont work because I can't bring my database online. The only thing I
can do is restore my backup from last night but all the work that was
performed during the day is lost.
Can anyone help?
Thanks
FelixFirst, you have to be able to bring the SQL server itself online. If you
can't do that, you can't get any further. If the server is online but the
user database is missing the .mdf file, you can use the BACKUP LOG command
with the NO_TRUNCATE option. This will extract the "tail" of the log and
allow you to recover the missing transactions. This command works even with
a suspect or damaged database, provided the log file is undamaged.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Felix" <Felix@.discussions.microsoft.com> wrote in message
news:9EB0EEF3-948D-4B44-A509-762FAF60E90E@.microsoft.com...
> Hi,
> Due to a disk failure, I lost my .mdf file of a database. Of course I have
> a
> backup, of the database and transactionlogs from last night, but I want to
> restore untill the time of failure. So I tried to follow the procedure
> explained in : how to backup transaction logs when the database is
> damaged.
> But it wont work because I can't bring my database online. The only thing
> I
> can do is restore my backup from last night but all the work that was
> performed during the day is lost.
> Can anyone help?
> Thanks
> Felix|||Hi Geoff,
Thanks for the very fast reply. The situation is the following:
My SQL server is online, I only lost the .mdf file of one user database.
The user database is offline (of course .mdf file is lost).
I use: 'backup log user_database to disk '...file...' with NO_TRUNCATE' to
try and save the last portion of the log file but:
Server: MSG 942, Level 14, State3, Line 1
Datebase 'user_database' cannot be opened because it is offline.
How to proceed now?
Thanks in advance
Felix
"Geoff N. Hiten" wrote:
> First, you have to be able to bring the SQL server itself online. If you
> can't do that, you can't get any further. If the server is online but the
> user database is missing the .mdf file, you can use the BACKUP LOG command
> with the NO_TRUNCATE option. This will extract the "tail" of the log and
> allow you to recover the missing transactions. This command works even with
> a suspect or damaged database, provided the log file is undamaged.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Felix" <Felix@.discussions.microsoft.com> wrote in message
> news:9EB0EEF3-948D-4B44-A509-762FAF60E90E@.microsoft.com...
> > Hi,
> > Due to a disk failure, I lost my .mdf file of a database. Of course I have
> > a
> > backup, of the database and transactionlogs from last night, but I want to
> > restore untill the time of failure. So I tried to follow the procedure
> > explained in : how to backup transaction logs when the database is
> > damaged.
> > But it wont work because I can't bring my database online. The only thing
> > I
> > can do is restore my backup from last night but all the work that was
> > performed during the day is lost.
> > Can anyone help?
> >
> > Thanks
> >
> > Felix
>
>|||Offline and Suspect are two very different things in SQL Server. Offline is
a SQL controlled database state. Suspect is a reaction to an external
event. Try ALTER DATABASE user_database SET ONLINE and then try the backup
log command.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Felix" <Felix@.discussions.microsoft.com> wrote in message
news:93749B04-F531-4C2E-9395-F7F2ED4E2EB4@.microsoft.com...
> Hi Geoff,
> Thanks for the very fast reply. The situation is the following:
> My SQL server is online, I only lost the .mdf file of one user database.
> The user database is offline (of course .mdf file is lost).
> I use: 'backup log user_database to disk '...file...' with NO_TRUNCATE'
> to
> try and save the last portion of the log file but:
> Server: MSG 942, Level 14, State3, Line 1
> Datebase 'user_database' cannot be opened because it is offline.
> How to proceed now?
> Thanks in advance
> Felix
>
> "Geoff N. Hiten" wrote:
>> First, you have to be able to bring the SQL server itself online. If you
>> can't do that, you can't get any further. If the server is online but
>> the
>> user database is missing the .mdf file, you can use the BACKUP LOG
>> command
>> with the NO_TRUNCATE option. This will extract the "tail" of the log and
>> allow you to recover the missing transactions. This command works even
>> with
>> a suspect or damaged database, provided the log file is undamaged.
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>> "Felix" <Felix@.discussions.microsoft.com> wrote in message
>> news:9EB0EEF3-948D-4B44-A509-762FAF60E90E@.microsoft.com...
>> > Hi,
>> > Due to a disk failure, I lost my .mdf file of a database. Of course I
>> > have
>> > a
>> > backup, of the database and transactionlogs from last night, but I want
>> > to
>> > restore untill the time of failure. So I tried to follow the procedure
>> > explained in : how to backup transaction logs when the database is
>> > damaged.
>> > But it wont work because I can't bring my database online. The only
>> > thing
>> > I
>> > can do is restore my backup from last night but all the work that was
>> > performed during the day is lost.
>> > Can anyone help?
>> >
>> > Thanks
>> >
>> > Felix
>>|||> First, you have to be able to bring the SQL server itself online. If you can't do that, you can't
> get any further.
Some nit-picking, if you don't mind ;-)
You can get further. Copy the ldf file to a healthy SQL Server. Create a database with same file
names as the original database. Stop that SQL Server. Delete the newly created database files. Copy
the ldf file from the crashed machine to the place of the newly created database ldf file. Start
that SQL Server. That database is now suspect. Do the backup using NO_TRUNCATE.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:OFu1KrbyFHA.2448@.TK2MSFTNGP10.phx.gbl...
> First, you have to be able to bring the SQL server itself online. If you can't do that, you can't
> get any further. If the server is online but the user database is missing the .mdf file, you can
> use the BACKUP LOG command with the NO_TRUNCATE option. This will extract the "tail" of the log
> and allow you to recover the missing transactions. This command works even with a suspect or
> damaged database, provided the log file is undamaged.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Felix" <Felix@.discussions.microsoft.com> wrote in message
> news:9EB0EEF3-948D-4B44-A509-762FAF60E90E@.microsoft.com...
>> Hi,
>> Due to a disk failure, I lost my .mdf file of a database. Of course I have a
>> backup, of the database and transactionlogs from last night, but I want to
>> restore untill the time of failure. So I tried to follow the procedure
>> explained in : how to backup transaction logs when the database is damaged.
>> But it wont work because I can't bring my database online. The only thing I
>> can do is restore my backup from last night but all the work that was
>> performed during the day is lost.
>> Can anyone help?
>> Thanks
>> Felix
>|||Good point.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uWKGSccyFHA.916@.TK2MSFTNGP10.phx.gbl...
>> First, you have to be able to bring the SQL server itself online. If you
>> can't do that, you can't get any further.
> Some nit-picking, if you don't mind ;-)
> You can get further. Copy the ldf file to a healthy SQL Server. Create a
> database with same file names as the original database. Stop that SQL
> Server. Delete the newly created database files. Copy the ldf file from
> the crashed machine to the place of the newly created database ldf file.
> Start that SQL Server. That database is now suspect. Do the backup using
> NO_TRUNCATE.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
> news:OFu1KrbyFHA.2448@.TK2MSFTNGP10.phx.gbl...
>> First, you have to be able to bring the SQL server itself online. If you
>> can't do that, you can't get any further. If the server is online but
>> the user database is missing the .mdf file, you can use the BACKUP LOG
>> command with the NO_TRUNCATE option. This will extract the "tail" of the
>> log and allow you to recover the missing transactions. This command
>> works even with a suspect or damaged database, provided the log file is
>> undamaged.
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>> "Felix" <Felix@.discussions.microsoft.com> wrote in message
>> news:9EB0EEF3-948D-4B44-A509-762FAF60E90E@.microsoft.com...
>> Hi,
>> Due to a disk failure, I lost my .mdf file of a database. Of course I
>> have a
>> backup, of the database and transactionlogs from last night, but I want
>> to
>> restore untill the time of failure. So I tried to follow the procedure
>> explained in : how to backup transaction logs when the database is
>> damaged.
>> But it wont work because I can't bring my database online. The only
>> thing I
>> can do is restore my backup from last night but all the work that was
>> performed during the day is lost.
>> Can anyone help?
>> Thanks
>> Felix
>>
>|||Woooo...Tibor does that work? Kinda cool if it does ;-)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uWKGSccyFHA.916@.TK2MSFTNGP10.phx.gbl...
>> First, you have to be able to bring the SQL server itself online. If you
>> can't do that, you can't get any further.
> Some nit-picking, if you don't mind ;-)
> You can get further. Copy the ldf file to a healthy SQL Server. Create a
> database with same file names as the original database. Stop that SQL
> Server. Delete the newly created database files. Copy the ldf file from
> the crashed machine to the place of the newly created database ldf file.
> Start that SQL Server. That database is now suspect. Do the backup using
> NO_TRUNCATE.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
> news:OFu1KrbyFHA.2448@.TK2MSFTNGP10.phx.gbl...
>> First, you have to be able to bring the SQL server itself online. If you
>> can't do that, you can't get any further. If the server is online but
>> the user database is missing the .mdf file, you can use the BACKUP LOG
>> command with the NO_TRUNCATE option. This will extract the "tail" of the
>> log and allow you to recover the missing transactions. This command
>> works even with a suspect or damaged database, provided the log file is
>> undamaged.
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>> "Felix" <Felix@.discussions.microsoft.com> wrote in message
>> news:9EB0EEF3-948D-4B44-A509-762FAF60E90E@.microsoft.com...
>> Hi,
>> Due to a disk failure, I lost my .mdf file of a database. Of course I
>> have a
>> backup, of the database and transactionlogs from last night, but I want
>> to
>> restore untill the time of failure. So I tried to follow the procedure
>> explained in : how to backup transaction logs when the database is
>> damaged.
>> But it wont work because I can't bring my database online. The only
>> thing I
>> can do is restore my backup from last night but all the work that was
>> performed during the day is lost.
>> Can anyone help?
>> Thanks
>> Felix
>>
>|||> Woooo...Tibor does that work? Kinda cool if it does ;-)
Yep, sure is. I've done this several times, when customers had suspect databases, stopped SQL
Server, copied the ldf file "for safety", started SQL Server, dropped the database since "it is
suspect anyhow...". See http://support.microsoft.com/default.aspx?scid=kb;en-us;253817. The KB
states rebuilding master on the same machine, but that is no different from doing the same operation
on some other machine. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:eywlorcyFHA.2076@.TK2MSFTNGP14.phx.gbl...
> Woooo...Tibor does that work? Kinda cool if it does ;-)
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:uWKGSccyFHA.916@.TK2MSFTNGP10.phx.gbl...
>> First, you have to be able to bring the SQL server itself online. If you can't do that, you
>> can't get any further.
>> Some nit-picking, if you don't mind ;-)
>> You can get further. Copy the ldf file to a healthy SQL Server. Create a database with same file
>> names as the original database. Stop that SQL Server. Delete the newly created database files.
>> Copy the ldf file from the crashed machine to the place of the newly created database ldf file.
>> Start that SQL Server. That database is now suspect. Do the backup using NO_TRUNCATE.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
>> news:OFu1KrbyFHA.2448@.TK2MSFTNGP10.phx.gbl...
>> First, you have to be able to bring the SQL server itself online. If you can't do that, you
>> can't get any further. If the server is online but the user database is missing the .mdf file,
>> you can use the BACKUP LOG command with the NO_TRUNCATE option. This will extract the "tail" of
>> the log and allow you to recover the missing transactions. This command works even with a
>> suspect or damaged database, provided the log file is undamaged.
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>> "Felix" <Felix@.discussions.microsoft.com> wrote in message
>> news:9EB0EEF3-948D-4B44-A509-762FAF60E90E@.microsoft.com...
>> Hi,
>> Due to a disk failure, I lost my .mdf file of a database. Of course I have a
>> backup, of the database and transactionlogs from last night, but I want to
>> restore untill the time of failure. So I tried to follow the procedure
>> explained in : how to backup transaction logs when the database is damaged.
>> But it wont work because I can't bring my database online. The only thing I
>> can do is restore my backup from last night but all the work that was
>> performed during the day is lost.
>> Can anyone help?
>> Thanks
>> Felix
>>
>|||Ok...cudos to you...you da man ;-)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OczvUNdyFHA.3588@.tk2msftngp13.phx.gbl...
>> Woooo...Tibor does that work? Kinda cool if it does ;-)
> Yep, sure is. I've done this several times, when customers had suspect
> databases, stopped SQL Server, copied the ldf file "for safety", started
> SQL Server, dropped the database since "it is suspect anyhow...". See
> http://support.microsoft.com/default.aspx?scid=kb;en-us;253817. The KB
> states rebuilding master on the same machine, but that is no different
> from doing the same operation on some other machine. :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:eywlorcyFHA.2076@.TK2MSFTNGP14.phx.gbl...
>> Woooo...Tibor does that work? Kinda cool if it does ;-)
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:uWKGSccyFHA.916@.TK2MSFTNGP10.phx.gbl...
>> First, you have to be able to bring the SQL server itself online. If
>> you can't do that, you can't get any further.
>> Some nit-picking, if you don't mind ;-)
>> You can get further. Copy the ldf file to a healthy SQL Server. Create a
>> database with same file names as the original database. Stop that SQL
>> Server. Delete the newly created database files. Copy the ldf file from
>> the crashed machine to the place of the newly created database ldf file.
>> Start that SQL Server. That database is now suspect. Do the backup using
>> NO_TRUNCATE.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
>> news:OFu1KrbyFHA.2448@.TK2MSFTNGP10.phx.gbl...
>> First, you have to be able to bring the SQL server itself online. If
>> you can't do that, you can't get any further. If the server is online
>> but the user database is missing the .mdf file, you can use the BACKUP
>> LOG command with the NO_TRUNCATE option. This will extract the "tail"
>> of the log and allow you to recover the missing transactions. This
>> command works even with a suspect or damaged database, provided the log
>> file is undamaged.
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>> "Felix" <Felix@.discussions.microsoft.com> wrote in message
>> news:9EB0EEF3-948D-4B44-A509-762FAF60E90E@.microsoft.com...
>> Hi,
>> Due to a disk failure, I lost my .mdf file of a database. Of course I
>> have a
>> backup, of the database and transactionlogs from last night, but I
>> want to
>> restore untill the time of failure. So I tried to follow the procedure
>> explained in : how to backup transaction logs when the database is
>> damaged.
>> But it wont work because I can't bring my database online. The only
>> thing I
>> can do is restore my backup from last night but all the work that was
>> performed during the day is lost.
>> Can anyone help?
>> Thanks
>> Felix
>>
>>
>|||Hi,
I have to thank you all, I succeeded in restoring the userdatabase to the
point of failure. My problem was that I always brought de database offline.
How it worked:
I stopped and started the SQL server, the database was left in status
suspect, not offline.
In this status, I was able to execute : backup log user_db to disk = 'file'
with INIT, NO_TRUNCATE.
Then I restored the backup from last night but with the NORECOVERY clause.
Then I executed restore log user_db from disk = 'file' with RECOVERY
And my database was back online containing everything until the point of
failure.
Thank you very much.
Hope this helps other people too.
"Jerry Spivey" wrote:
> Ok...cudos to you...you da man ;-)
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:OczvUNdyFHA.3588@.tk2msftngp13.phx.gbl...
> >> Woooo...Tibor does that work? Kinda cool if it does ;-)
> >
> > Yep, sure is. I've done this several times, when customers had suspect
> > databases, stopped SQL Server, copied the ldf file "for safety", started
> > SQL Server, dropped the database since "it is suspect anyhow...". See
> > http://support.microsoft.com/default.aspx?scid=kb;en-us;253817. The KB
> > states rebuilding master on the same machine, but that is no different
> > from doing the same operation on some other machine. :-)
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> > Blog: http://solidqualitylearning.com/blogs/tibor/
> >
> >
> > "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> > news:eywlorcyFHA.2076@.TK2MSFTNGP14.phx.gbl...
> >> Woooo...Tibor does that work? Kinda cool if it does ;-)
> >>
> >> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> >> in message news:uWKGSccyFHA.916@.TK2MSFTNGP10.phx.gbl...
> >> First, you have to be able to bring the SQL server itself online. If
> >> you can't do that, you can't get any further.
> >>
> >> Some nit-picking, if you don't mind ;-)
> >>
> >> You can get further. Copy the ldf file to a healthy SQL Server. Create a
> >> database with same file names as the original database. Stop that SQL
> >> Server. Delete the newly created database files. Copy the ldf file from
> >> the crashed machine to the place of the newly created database ldf file.
> >> Start that SQL Server. That database is now suspect. Do the backup using
> >> NO_TRUNCATE.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> Blog: http://solidqualitylearning.com/blogs/tibor/
> >>
> >>
> >> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
> >> news:OFu1KrbyFHA.2448@.TK2MSFTNGP10.phx.gbl...
> >> First, you have to be able to bring the SQL server itself online. If
> >> you can't do that, you can't get any further. If the server is online
> >> but the user database is missing the .mdf file, you can use the BACKUP
> >> LOG command with the NO_TRUNCATE option. This will extract the "tail"
> >> of the log and allow you to recover the missing transactions. This
> >> command works even with a suspect or damaged database, provided the log
> >> file is undamaged.
> >>
> >> --
> >> Geoff N. Hiten
> >> Senior Database Administrator
> >> Microsoft SQL Server MVP
> >>
> >> "Felix" <Felix@.discussions.microsoft.com> wrote in message
> >> news:9EB0EEF3-948D-4B44-A509-762FAF60E90E@.microsoft.com...
> >> Hi,
> >> Due to a disk failure, I lost my .mdf file of a database. Of course I
> >> have a
> >> backup, of the database and transactionlogs from last night, but I
> >> want to
> >> restore untill the time of failure. So I tried to follow the procedure
> >> explained in : how to backup transaction logs when the database is
> >> damaged.
> >> But it wont work because I can't bring my database online. The only
> >> thing I
> >> can do is restore my backup from last night but all the work that was
> >> performed during the day is lost.
> >> Can anyone help?
> >>
> >> Thanks
> >>
> >> Felix
> >>
> >>
> >>
> >>
> >>
> >
>
>
Due to a disk failure, I lost my .mdf file of a database. Of course I have a
backup, of the database and transactionlogs from last night, but I want to
restore untill the time of failure. So I tried to follow the procedure
explained in : how to backup transaction logs when the database is damaged.
But it wont work because I can't bring my database online. The only thing I
can do is restore my backup from last night but all the work that was
performed during the day is lost.
Can anyone help?
Thanks
FelixFirst, you have to be able to bring the SQL server itself online. If you
can't do that, you can't get any further. If the server is online but the
user database is missing the .mdf file, you can use the BACKUP LOG command
with the NO_TRUNCATE option. This will extract the "tail" of the log and
allow you to recover the missing transactions. This command works even with
a suspect or damaged database, provided the log file is undamaged.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Felix" <Felix@.discussions.microsoft.com> wrote in message
news:9EB0EEF3-948D-4B44-A509-762FAF60E90E@.microsoft.com...
> Hi,
> Due to a disk failure, I lost my .mdf file of a database. Of course I have
> a
> backup, of the database and transactionlogs from last night, but I want to
> restore untill the time of failure. So I tried to follow the procedure
> explained in : how to backup transaction logs when the database is
> damaged.
> But it wont work because I can't bring my database online. The only thing
> I
> can do is restore my backup from last night but all the work that was
> performed during the day is lost.
> Can anyone help?
> Thanks
> Felix|||Hi Geoff,
Thanks for the very fast reply. The situation is the following:
My SQL server is online, I only lost the .mdf file of one user database.
The user database is offline (of course .mdf file is lost).
I use: 'backup log user_database to disk '...file...' with NO_TRUNCATE' to
try and save the last portion of the log file but:
Server: MSG 942, Level 14, State3, Line 1
Datebase 'user_database' cannot be opened because it is offline.
How to proceed now?
Thanks in advance
Felix
"Geoff N. Hiten" wrote:
> First, you have to be able to bring the SQL server itself online. If you
> can't do that, you can't get any further. If the server is online but the
> user database is missing the .mdf file, you can use the BACKUP LOG command
> with the NO_TRUNCATE option. This will extract the "tail" of the log and
> allow you to recover the missing transactions. This command works even with
> a suspect or damaged database, provided the log file is undamaged.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Felix" <Felix@.discussions.microsoft.com> wrote in message
> news:9EB0EEF3-948D-4B44-A509-762FAF60E90E@.microsoft.com...
> > Hi,
> > Due to a disk failure, I lost my .mdf file of a database. Of course I have
> > a
> > backup, of the database and transactionlogs from last night, but I want to
> > restore untill the time of failure. So I tried to follow the procedure
> > explained in : how to backup transaction logs when the database is
> > damaged.
> > But it wont work because I can't bring my database online. The only thing
> > I
> > can do is restore my backup from last night but all the work that was
> > performed during the day is lost.
> > Can anyone help?
> >
> > Thanks
> >
> > Felix
>
>|||Offline and Suspect are two very different things in SQL Server. Offline is
a SQL controlled database state. Suspect is a reaction to an external
event. Try ALTER DATABASE user_database SET ONLINE and then try the backup
log command.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Felix" <Felix@.discussions.microsoft.com> wrote in message
news:93749B04-F531-4C2E-9395-F7F2ED4E2EB4@.microsoft.com...
> Hi Geoff,
> Thanks for the very fast reply. The situation is the following:
> My SQL server is online, I only lost the .mdf file of one user database.
> The user database is offline (of course .mdf file is lost).
> I use: 'backup log user_database to disk '...file...' with NO_TRUNCATE'
> to
> try and save the last portion of the log file but:
> Server: MSG 942, Level 14, State3, Line 1
> Datebase 'user_database' cannot be opened because it is offline.
> How to proceed now?
> Thanks in advance
> Felix
>
> "Geoff N. Hiten" wrote:
>> First, you have to be able to bring the SQL server itself online. If you
>> can't do that, you can't get any further. If the server is online but
>> the
>> user database is missing the .mdf file, you can use the BACKUP LOG
>> command
>> with the NO_TRUNCATE option. This will extract the "tail" of the log and
>> allow you to recover the missing transactions. This command works even
>> with
>> a suspect or damaged database, provided the log file is undamaged.
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>> "Felix" <Felix@.discussions.microsoft.com> wrote in message
>> news:9EB0EEF3-948D-4B44-A509-762FAF60E90E@.microsoft.com...
>> > Hi,
>> > Due to a disk failure, I lost my .mdf file of a database. Of course I
>> > have
>> > a
>> > backup, of the database and transactionlogs from last night, but I want
>> > to
>> > restore untill the time of failure. So I tried to follow the procedure
>> > explained in : how to backup transaction logs when the database is
>> > damaged.
>> > But it wont work because I can't bring my database online. The only
>> > thing
>> > I
>> > can do is restore my backup from last night but all the work that was
>> > performed during the day is lost.
>> > Can anyone help?
>> >
>> > Thanks
>> >
>> > Felix
>>|||> First, you have to be able to bring the SQL server itself online. If you can't do that, you can't
> get any further.
Some nit-picking, if you don't mind ;-)
You can get further. Copy the ldf file to a healthy SQL Server. Create a database with same file
names as the original database. Stop that SQL Server. Delete the newly created database files. Copy
the ldf file from the crashed machine to the place of the newly created database ldf file. Start
that SQL Server. That database is now suspect. Do the backup using NO_TRUNCATE.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:OFu1KrbyFHA.2448@.TK2MSFTNGP10.phx.gbl...
> First, you have to be able to bring the SQL server itself online. If you can't do that, you can't
> get any further. If the server is online but the user database is missing the .mdf file, you can
> use the BACKUP LOG command with the NO_TRUNCATE option. This will extract the "tail" of the log
> and allow you to recover the missing transactions. This command works even with a suspect or
> damaged database, provided the log file is undamaged.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Felix" <Felix@.discussions.microsoft.com> wrote in message
> news:9EB0EEF3-948D-4B44-A509-762FAF60E90E@.microsoft.com...
>> Hi,
>> Due to a disk failure, I lost my .mdf file of a database. Of course I have a
>> backup, of the database and transactionlogs from last night, but I want to
>> restore untill the time of failure. So I tried to follow the procedure
>> explained in : how to backup transaction logs when the database is damaged.
>> But it wont work because I can't bring my database online. The only thing I
>> can do is restore my backup from last night but all the work that was
>> performed during the day is lost.
>> Can anyone help?
>> Thanks
>> Felix
>|||Good point.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uWKGSccyFHA.916@.TK2MSFTNGP10.phx.gbl...
>> First, you have to be able to bring the SQL server itself online. If you
>> can't do that, you can't get any further.
> Some nit-picking, if you don't mind ;-)
> You can get further. Copy the ldf file to a healthy SQL Server. Create a
> database with same file names as the original database. Stop that SQL
> Server. Delete the newly created database files. Copy the ldf file from
> the crashed machine to the place of the newly created database ldf file.
> Start that SQL Server. That database is now suspect. Do the backup using
> NO_TRUNCATE.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
> news:OFu1KrbyFHA.2448@.TK2MSFTNGP10.phx.gbl...
>> First, you have to be able to bring the SQL server itself online. If you
>> can't do that, you can't get any further. If the server is online but
>> the user database is missing the .mdf file, you can use the BACKUP LOG
>> command with the NO_TRUNCATE option. This will extract the "tail" of the
>> log and allow you to recover the missing transactions. This command
>> works even with a suspect or damaged database, provided the log file is
>> undamaged.
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>> "Felix" <Felix@.discussions.microsoft.com> wrote in message
>> news:9EB0EEF3-948D-4B44-A509-762FAF60E90E@.microsoft.com...
>> Hi,
>> Due to a disk failure, I lost my .mdf file of a database. Of course I
>> have a
>> backup, of the database and transactionlogs from last night, but I want
>> to
>> restore untill the time of failure. So I tried to follow the procedure
>> explained in : how to backup transaction logs when the database is
>> damaged.
>> But it wont work because I can't bring my database online. The only
>> thing I
>> can do is restore my backup from last night but all the work that was
>> performed during the day is lost.
>> Can anyone help?
>> Thanks
>> Felix
>>
>|||Woooo...Tibor does that work? Kinda cool if it does ;-)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uWKGSccyFHA.916@.TK2MSFTNGP10.phx.gbl...
>> First, you have to be able to bring the SQL server itself online. If you
>> can't do that, you can't get any further.
> Some nit-picking, if you don't mind ;-)
> You can get further. Copy the ldf file to a healthy SQL Server. Create a
> database with same file names as the original database. Stop that SQL
> Server. Delete the newly created database files. Copy the ldf file from
> the crashed machine to the place of the newly created database ldf file.
> Start that SQL Server. That database is now suspect. Do the backup using
> NO_TRUNCATE.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
> news:OFu1KrbyFHA.2448@.TK2MSFTNGP10.phx.gbl...
>> First, you have to be able to bring the SQL server itself online. If you
>> can't do that, you can't get any further. If the server is online but
>> the user database is missing the .mdf file, you can use the BACKUP LOG
>> command with the NO_TRUNCATE option. This will extract the "tail" of the
>> log and allow you to recover the missing transactions. This command
>> works even with a suspect or damaged database, provided the log file is
>> undamaged.
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>> "Felix" <Felix@.discussions.microsoft.com> wrote in message
>> news:9EB0EEF3-948D-4B44-A509-762FAF60E90E@.microsoft.com...
>> Hi,
>> Due to a disk failure, I lost my .mdf file of a database. Of course I
>> have a
>> backup, of the database and transactionlogs from last night, but I want
>> to
>> restore untill the time of failure. So I tried to follow the procedure
>> explained in : how to backup transaction logs when the database is
>> damaged.
>> But it wont work because I can't bring my database online. The only
>> thing I
>> can do is restore my backup from last night but all the work that was
>> performed during the day is lost.
>> Can anyone help?
>> Thanks
>> Felix
>>
>|||> Woooo...Tibor does that work? Kinda cool if it does ;-)
Yep, sure is. I've done this several times, when customers had suspect databases, stopped SQL
Server, copied the ldf file "for safety", started SQL Server, dropped the database since "it is
suspect anyhow...". See http://support.microsoft.com/default.aspx?scid=kb;en-us;253817. The KB
states rebuilding master on the same machine, but that is no different from doing the same operation
on some other machine. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:eywlorcyFHA.2076@.TK2MSFTNGP14.phx.gbl...
> Woooo...Tibor does that work? Kinda cool if it does ;-)
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:uWKGSccyFHA.916@.TK2MSFTNGP10.phx.gbl...
>> First, you have to be able to bring the SQL server itself online. If you can't do that, you
>> can't get any further.
>> Some nit-picking, if you don't mind ;-)
>> You can get further. Copy the ldf file to a healthy SQL Server. Create a database with same file
>> names as the original database. Stop that SQL Server. Delete the newly created database files.
>> Copy the ldf file from the crashed machine to the place of the newly created database ldf file.
>> Start that SQL Server. That database is now suspect. Do the backup using NO_TRUNCATE.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
>> news:OFu1KrbyFHA.2448@.TK2MSFTNGP10.phx.gbl...
>> First, you have to be able to bring the SQL server itself online. If you can't do that, you
>> can't get any further. If the server is online but the user database is missing the .mdf file,
>> you can use the BACKUP LOG command with the NO_TRUNCATE option. This will extract the "tail" of
>> the log and allow you to recover the missing transactions. This command works even with a
>> suspect or damaged database, provided the log file is undamaged.
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>> "Felix" <Felix@.discussions.microsoft.com> wrote in message
>> news:9EB0EEF3-948D-4B44-A509-762FAF60E90E@.microsoft.com...
>> Hi,
>> Due to a disk failure, I lost my .mdf file of a database. Of course I have a
>> backup, of the database and transactionlogs from last night, but I want to
>> restore untill the time of failure. So I tried to follow the procedure
>> explained in : how to backup transaction logs when the database is damaged.
>> But it wont work because I can't bring my database online. The only thing I
>> can do is restore my backup from last night but all the work that was
>> performed during the day is lost.
>> Can anyone help?
>> Thanks
>> Felix
>>
>|||Ok...cudos to you...you da man ;-)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OczvUNdyFHA.3588@.tk2msftngp13.phx.gbl...
>> Woooo...Tibor does that work? Kinda cool if it does ;-)
> Yep, sure is. I've done this several times, when customers had suspect
> databases, stopped SQL Server, copied the ldf file "for safety", started
> SQL Server, dropped the database since "it is suspect anyhow...". See
> http://support.microsoft.com/default.aspx?scid=kb;en-us;253817. The KB
> states rebuilding master on the same machine, but that is no different
> from doing the same operation on some other machine. :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:eywlorcyFHA.2076@.TK2MSFTNGP14.phx.gbl...
>> Woooo...Tibor does that work? Kinda cool if it does ;-)
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:uWKGSccyFHA.916@.TK2MSFTNGP10.phx.gbl...
>> First, you have to be able to bring the SQL server itself online. If
>> you can't do that, you can't get any further.
>> Some nit-picking, if you don't mind ;-)
>> You can get further. Copy the ldf file to a healthy SQL Server. Create a
>> database with same file names as the original database. Stop that SQL
>> Server. Delete the newly created database files. Copy the ldf file from
>> the crashed machine to the place of the newly created database ldf file.
>> Start that SQL Server. That database is now suspect. Do the backup using
>> NO_TRUNCATE.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
>> news:OFu1KrbyFHA.2448@.TK2MSFTNGP10.phx.gbl...
>> First, you have to be able to bring the SQL server itself online. If
>> you can't do that, you can't get any further. If the server is online
>> but the user database is missing the .mdf file, you can use the BACKUP
>> LOG command with the NO_TRUNCATE option. This will extract the "tail"
>> of the log and allow you to recover the missing transactions. This
>> command works even with a suspect or damaged database, provided the log
>> file is undamaged.
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>> "Felix" <Felix@.discussions.microsoft.com> wrote in message
>> news:9EB0EEF3-948D-4B44-A509-762FAF60E90E@.microsoft.com...
>> Hi,
>> Due to a disk failure, I lost my .mdf file of a database. Of course I
>> have a
>> backup, of the database and transactionlogs from last night, but I
>> want to
>> restore untill the time of failure. So I tried to follow the procedure
>> explained in : how to backup transaction logs when the database is
>> damaged.
>> But it wont work because I can't bring my database online. The only
>> thing I
>> can do is restore my backup from last night but all the work that was
>> performed during the day is lost.
>> Can anyone help?
>> Thanks
>> Felix
>>
>>
>|||Hi,
I have to thank you all, I succeeded in restoring the userdatabase to the
point of failure. My problem was that I always brought de database offline.
How it worked:
I stopped and started the SQL server, the database was left in status
suspect, not offline.
In this status, I was able to execute : backup log user_db to disk = 'file'
with INIT, NO_TRUNCATE.
Then I restored the backup from last night but with the NORECOVERY clause.
Then I executed restore log user_db from disk = 'file' with RECOVERY
And my database was back online containing everything until the point of
failure.
Thank you very much.
Hope this helps other people too.
"Jerry Spivey" wrote:
> Ok...cudos to you...you da man ;-)
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:OczvUNdyFHA.3588@.tk2msftngp13.phx.gbl...
> >> Woooo...Tibor does that work? Kinda cool if it does ;-)
> >
> > Yep, sure is. I've done this several times, when customers had suspect
> > databases, stopped SQL Server, copied the ldf file "for safety", started
> > SQL Server, dropped the database since "it is suspect anyhow...". See
> > http://support.microsoft.com/default.aspx?scid=kb;en-us;253817. The KB
> > states rebuilding master on the same machine, but that is no different
> > from doing the same operation on some other machine. :-)
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> > Blog: http://solidqualitylearning.com/blogs/tibor/
> >
> >
> > "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> > news:eywlorcyFHA.2076@.TK2MSFTNGP14.phx.gbl...
> >> Woooo...Tibor does that work? Kinda cool if it does ;-)
> >>
> >> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> >> in message news:uWKGSccyFHA.916@.TK2MSFTNGP10.phx.gbl...
> >> First, you have to be able to bring the SQL server itself online. If
> >> you can't do that, you can't get any further.
> >>
> >> Some nit-picking, if you don't mind ;-)
> >>
> >> You can get further. Copy the ldf file to a healthy SQL Server. Create a
> >> database with same file names as the original database. Stop that SQL
> >> Server. Delete the newly created database files. Copy the ldf file from
> >> the crashed machine to the place of the newly created database ldf file.
> >> Start that SQL Server. That database is now suspect. Do the backup using
> >> NO_TRUNCATE.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> Blog: http://solidqualitylearning.com/blogs/tibor/
> >>
> >>
> >> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
> >> news:OFu1KrbyFHA.2448@.TK2MSFTNGP10.phx.gbl...
> >> First, you have to be able to bring the SQL server itself online. If
> >> you can't do that, you can't get any further. If the server is online
> >> but the user database is missing the .mdf file, you can use the BACKUP
> >> LOG command with the NO_TRUNCATE option. This will extract the "tail"
> >> of the log and allow you to recover the missing transactions. This
> >> command works even with a suspect or damaged database, provided the log
> >> file is undamaged.
> >>
> >> --
> >> Geoff N. Hiten
> >> Senior Database Administrator
> >> Microsoft SQL Server MVP
> >>
> >> "Felix" <Felix@.discussions.microsoft.com> wrote in message
> >> news:9EB0EEF3-948D-4B44-A509-762FAF60E90E@.microsoft.com...
> >> Hi,
> >> Due to a disk failure, I lost my .mdf file of a database. Of course I
> >> have a
> >> backup, of the database and transactionlogs from last night, but I
> >> want to
> >> restore untill the time of failure. So I tried to follow the procedure
> >> explained in : how to backup transaction logs when the database is
> >> damaged.
> >> But it wont work because I can't bring my database online. The only
> >> thing I
> >> can do is restore my backup from last night but all the work that was
> >> performed during the day is lost.
> >> Can anyone help?
> >>
> >> Thanks
> >>
> >> Felix
> >>
> >>
> >>
> >>
> >>
> >
>
>
Backup transaction logs when the database is damaged.
Hi,
Due to a disk failure, I lost my .mdf file of a database. Of course I have a
backup, of the database and transactionlogs from last night, but I want to
restore untill the time of failure. So I tried to follow the procedure
explained in : how to backup transaction logs when the database is damaged.
But it wont work because I can't bring my database online. The only thing I
can do is restore my backup from last night but all the work that was
performed during the day is lost.
Can anyone help?
Thanks
Felix
First, you have to be able to bring the SQL server itself online. If you
can't do that, you can't get any further. If the server is online but the
user database is missing the .mdf file, you can use the BACKUP LOG command
with the NO_TRUNCATE option. This will extract the "tail" of the log and
allow you to recover the missing transactions. This command works even with
a suspect or damaged database, provided the log file is undamaged.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Felix" <Felix@.discussions.microsoft.com> wrote in message
news:9EB0EEF3-948D-4B44-A509-762FAF60E90E@.microsoft.com...
> Hi,
> Due to a disk failure, I lost my .mdf file of a database. Of course I have
> a
> backup, of the database and transactionlogs from last night, but I want to
> restore untill the time of failure. So I tried to follow the procedure
> explained in : how to backup transaction logs when the database is
> damaged.
> But it wont work because I can't bring my database online. The only thing
> I
> can do is restore my backup from last night but all the work that was
> performed during the day is lost.
> Can anyone help?
> Thanks
> Felix
|||Hi Geoff,
Thanks for the very fast reply. The situation is the following:
My SQL server is online, I only lost the .mdf file of one user database.
The user database is offline (of course .mdf file is lost).
I use: 'backup log user_database to disk '...file...' with NO_TRUNCATE' to
try and save the last portion of the log file but:
Server: MSG 942, Level 14, State3, Line 1
Datebase 'user_database' cannot be opened because it is offline.
How to proceed now?
Thanks in advance
Felix
"Geoff N. Hiten" wrote:
> First, you have to be able to bring the SQL server itself online. If you
> can't do that, you can't get any further. If the server is online but the
> user database is missing the .mdf file, you can use the BACKUP LOG command
> with the NO_TRUNCATE option. This will extract the "tail" of the log and
> allow you to recover the missing transactions. This command works even with
> a suspect or damaged database, provided the log file is undamaged.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Felix" <Felix@.discussions.microsoft.com> wrote in message
> news:9EB0EEF3-948D-4B44-A509-762FAF60E90E@.microsoft.com...
>
>
|||Offline and Suspect are two very different things in SQL Server. Offline is
a SQL controlled database state. Suspect is a reaction to an external
event. Try ALTER DATABASE user_database SET ONLINE and then try the backup
log command.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Felix" <Felix@.discussions.microsoft.com> wrote in message
news:93749B04-F531-4C2E-9395-F7F2ED4E2EB4@.microsoft.com...[vbcol=seagreen]
> Hi Geoff,
> Thanks for the very fast reply. The situation is the following:
> My SQL server is online, I only lost the .mdf file of one user database.
> The user database is offline (of course .mdf file is lost).
> I use: 'backup log user_database to disk '...file...' with NO_TRUNCATE'
> to
> try and save the last portion of the log file but:
> Server: MSG 942, Level 14, State3, Line 1
> Datebase 'user_database' cannot be opened because it is offline.
> How to proceed now?
> Thanks in advance
> Felix
>
> "Geoff N. Hiten" wrote:
|||> First, you have to be able to bring the SQL server itself online. If you can't do that, you can't
> get any further.
Some nit-picking, if you don't mind ;-)
You can get further. Copy the ldf file to a healthy SQL Server. Create a database with same file
names as the original database. Stop that SQL Server. Delete the newly created database files. Copy
the ldf file from the crashed machine to the place of the newly created database ldf file. Start
that SQL Server. That database is now suspect. Do the backup using NO_TRUNCATE.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:OFu1KrbyFHA.2448@.TK2MSFTNGP10.phx.gbl...
> First, you have to be able to bring the SQL server itself online. If you can't do that, you can't
> get any further. If the server is online but the user database is missing the .mdf file, you can
> use the BACKUP LOG command with the NO_TRUNCATE option. This will extract the "tail" of the log
> and allow you to recover the missing transactions. This command works even with a suspect or
> damaged database, provided the log file is undamaged.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Felix" <Felix@.discussions.microsoft.com> wrote in message
> news:9EB0EEF3-948D-4B44-A509-762FAF60E90E@.microsoft.com...
>
|||Good point.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uWKGSccyFHA.916@.TK2MSFTNGP10.phx.gbl...
> Some nit-picking, if you don't mind ;-)
> You can get further. Copy the ldf file to a healthy SQL Server. Create a
> database with same file names as the original database. Stop that SQL
> Server. Delete the newly created database files. Copy the ldf file from
> the crashed machine to the place of the newly created database ldf file.
> Start that SQL Server. That database is now suspect. Do the backup using
> NO_TRUNCATE.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
> news:OFu1KrbyFHA.2448@.TK2MSFTNGP10.phx.gbl...
>
|||Woooo...Tibor does that work? Kinda cool if it does ;-)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uWKGSccyFHA.916@.TK2MSFTNGP10.phx.gbl...
> Some nit-picking, if you don't mind ;-)
> You can get further. Copy the ldf file to a healthy SQL Server. Create a
> database with same file names as the original database. Stop that SQL
> Server. Delete the newly created database files. Copy the ldf file from
> the crashed machine to the place of the newly created database ldf file.
> Start that SQL Server. That database is now suspect. Do the backup using
> NO_TRUNCATE.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
> news:OFu1KrbyFHA.2448@.TK2MSFTNGP10.phx.gbl...
>
|||> Woooo...Tibor does that work? Kinda cool if it does ;-)
Yep, sure is. I've done this several times, when customers had suspect databases, stopped SQL
Server, copied the ldf file "for safety", started SQL Server, dropped the database since "it is
suspect anyhow...". See http://support.microsoft.com/default...;en-us;253817. The KB
states rebuilding master on the same machine, but that is no different from doing the same operation
on some other machine. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:eywlorcyFHA.2076@.TK2MSFTNGP14.phx.gbl...
> Woooo...Tibor does that work? Kinda cool if it does ;-)
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:uWKGSccyFHA.916@.TK2MSFTNGP10.phx.gbl...
>
|||Ok...cudos to you...you da man ;-)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OczvUNdyFHA.3588@.tk2msftngp13.phx.gbl...
> Yep, sure is. I've done this several times, when customers had suspect
> databases, stopped SQL Server, copied the ldf file "for safety", started
> SQL Server, dropped the database since "it is suspect anyhow...". See
> http://support.microsoft.com/default...;en-us;253817. The KB
> states rebuilding master on the same machine, but that is no different
> from doing the same operation on some other machine. :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:eywlorcyFHA.2076@.TK2MSFTNGP14.phx.gbl...
>
|||Hi,
I have to thank you all, I succeeded in restoring the userdatabase to the
point of failure. My problem was that I always brought de database offline.
How it worked:
I stopped and started the SQL server, the database was left in status
suspect, not offline.
In this status, I was able to execute : backup log user_db to disk = 'file'
with INIT, NO_TRUNCATE.
Then I restored the backup from last night but with the NORECOVERY clause.
Then I executed restore log user_db from disk = 'file' with RECOVERY
And my database was back online containing everything until the point of
failure.
Thank you very much.
Hope this helps other people too.
"Jerry Spivey" wrote:
> Ok...cudos to you...you da man ;-)
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:OczvUNdyFHA.3588@.tk2msftngp13.phx.gbl...
>
>
Due to a disk failure, I lost my .mdf file of a database. Of course I have a
backup, of the database and transactionlogs from last night, but I want to
restore untill the time of failure. So I tried to follow the procedure
explained in : how to backup transaction logs when the database is damaged.
But it wont work because I can't bring my database online. The only thing I
can do is restore my backup from last night but all the work that was
performed during the day is lost.
Can anyone help?
Thanks
Felix
First, you have to be able to bring the SQL server itself online. If you
can't do that, you can't get any further. If the server is online but the
user database is missing the .mdf file, you can use the BACKUP LOG command
with the NO_TRUNCATE option. This will extract the "tail" of the log and
allow you to recover the missing transactions. This command works even with
a suspect or damaged database, provided the log file is undamaged.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Felix" <Felix@.discussions.microsoft.com> wrote in message
news:9EB0EEF3-948D-4B44-A509-762FAF60E90E@.microsoft.com...
> Hi,
> Due to a disk failure, I lost my .mdf file of a database. Of course I have
> a
> backup, of the database and transactionlogs from last night, but I want to
> restore untill the time of failure. So I tried to follow the procedure
> explained in : how to backup transaction logs when the database is
> damaged.
> But it wont work because I can't bring my database online. The only thing
> I
> can do is restore my backup from last night but all the work that was
> performed during the day is lost.
> Can anyone help?
> Thanks
> Felix
|||Hi Geoff,
Thanks for the very fast reply. The situation is the following:
My SQL server is online, I only lost the .mdf file of one user database.
The user database is offline (of course .mdf file is lost).
I use: 'backup log user_database to disk '...file...' with NO_TRUNCATE' to
try and save the last portion of the log file but:
Server: MSG 942, Level 14, State3, Line 1
Datebase 'user_database' cannot be opened because it is offline.
How to proceed now?
Thanks in advance
Felix
"Geoff N. Hiten" wrote:
> First, you have to be able to bring the SQL server itself online. If you
> can't do that, you can't get any further. If the server is online but the
> user database is missing the .mdf file, you can use the BACKUP LOG command
> with the NO_TRUNCATE option. This will extract the "tail" of the log and
> allow you to recover the missing transactions. This command works even with
> a suspect or damaged database, provided the log file is undamaged.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Felix" <Felix@.discussions.microsoft.com> wrote in message
> news:9EB0EEF3-948D-4B44-A509-762FAF60E90E@.microsoft.com...
>
>
|||Offline and Suspect are two very different things in SQL Server. Offline is
a SQL controlled database state. Suspect is a reaction to an external
event. Try ALTER DATABASE user_database SET ONLINE and then try the backup
log command.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Felix" <Felix@.discussions.microsoft.com> wrote in message
news:93749B04-F531-4C2E-9395-F7F2ED4E2EB4@.microsoft.com...[vbcol=seagreen]
> Hi Geoff,
> Thanks for the very fast reply. The situation is the following:
> My SQL server is online, I only lost the .mdf file of one user database.
> The user database is offline (of course .mdf file is lost).
> I use: 'backup log user_database to disk '...file...' with NO_TRUNCATE'
> to
> try and save the last portion of the log file but:
> Server: MSG 942, Level 14, State3, Line 1
> Datebase 'user_database' cannot be opened because it is offline.
> How to proceed now?
> Thanks in advance
> Felix
>
> "Geoff N. Hiten" wrote:
|||> First, you have to be able to bring the SQL server itself online. If you can't do that, you can't
> get any further.
Some nit-picking, if you don't mind ;-)
You can get further. Copy the ldf file to a healthy SQL Server. Create a database with same file
names as the original database. Stop that SQL Server. Delete the newly created database files. Copy
the ldf file from the crashed machine to the place of the newly created database ldf file. Start
that SQL Server. That database is now suspect. Do the backup using NO_TRUNCATE.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:OFu1KrbyFHA.2448@.TK2MSFTNGP10.phx.gbl...
> First, you have to be able to bring the SQL server itself online. If you can't do that, you can't
> get any further. If the server is online but the user database is missing the .mdf file, you can
> use the BACKUP LOG command with the NO_TRUNCATE option. This will extract the "tail" of the log
> and allow you to recover the missing transactions. This command works even with a suspect or
> damaged database, provided the log file is undamaged.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Felix" <Felix@.discussions.microsoft.com> wrote in message
> news:9EB0EEF3-948D-4B44-A509-762FAF60E90E@.microsoft.com...
>
|||Good point.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uWKGSccyFHA.916@.TK2MSFTNGP10.phx.gbl...
> Some nit-picking, if you don't mind ;-)
> You can get further. Copy the ldf file to a healthy SQL Server. Create a
> database with same file names as the original database. Stop that SQL
> Server. Delete the newly created database files. Copy the ldf file from
> the crashed machine to the place of the newly created database ldf file.
> Start that SQL Server. That database is now suspect. Do the backup using
> NO_TRUNCATE.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
> news:OFu1KrbyFHA.2448@.TK2MSFTNGP10.phx.gbl...
>
|||Woooo...Tibor does that work? Kinda cool if it does ;-)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uWKGSccyFHA.916@.TK2MSFTNGP10.phx.gbl...
> Some nit-picking, if you don't mind ;-)
> You can get further. Copy the ldf file to a healthy SQL Server. Create a
> database with same file names as the original database. Stop that SQL
> Server. Delete the newly created database files. Copy the ldf file from
> the crashed machine to the place of the newly created database ldf file.
> Start that SQL Server. That database is now suspect. Do the backup using
> NO_TRUNCATE.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
> news:OFu1KrbyFHA.2448@.TK2MSFTNGP10.phx.gbl...
>
|||> Woooo...Tibor does that work? Kinda cool if it does ;-)
Yep, sure is. I've done this several times, when customers had suspect databases, stopped SQL
Server, copied the ldf file "for safety", started SQL Server, dropped the database since "it is
suspect anyhow...". See http://support.microsoft.com/default...;en-us;253817. The KB
states rebuilding master on the same machine, but that is no different from doing the same operation
on some other machine. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:eywlorcyFHA.2076@.TK2MSFTNGP14.phx.gbl...
> Woooo...Tibor does that work? Kinda cool if it does ;-)
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:uWKGSccyFHA.916@.TK2MSFTNGP10.phx.gbl...
>
|||Ok...cudos to you...you da man ;-)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OczvUNdyFHA.3588@.tk2msftngp13.phx.gbl...
> Yep, sure is. I've done this several times, when customers had suspect
> databases, stopped SQL Server, copied the ldf file "for safety", started
> SQL Server, dropped the database since "it is suspect anyhow...". See
> http://support.microsoft.com/default...;en-us;253817. The KB
> states rebuilding master on the same machine, but that is no different
> from doing the same operation on some other machine. :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:eywlorcyFHA.2076@.TK2MSFTNGP14.phx.gbl...
>
|||Hi,
I have to thank you all, I succeeded in restoring the userdatabase to the
point of failure. My problem was that I always brought de database offline.
How it worked:
I stopped and started the SQL server, the database was left in status
suspect, not offline.
In this status, I was able to execute : backup log user_db to disk = 'file'
with INIT, NO_TRUNCATE.
Then I restored the backup from last night but with the NORECOVERY clause.
Then I executed restore log user_db from disk = 'file' with RECOVERY
And my database was back online containing everything until the point of
failure.
Thank you very much.
Hope this helps other people too.
"Jerry Spivey" wrote:
> Ok...cudos to you...you da man ;-)
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:OczvUNdyFHA.3588@.tk2msftngp13.phx.gbl...
>
>
Backup transaction logs when the database is damaged.
Hi,
Due to a disk failure, I lost my .mdf file of a database. Of course I have a
backup, of the database and transactionlogs from last night, but I want to
restore untill the time of failure. So I tried to follow the procedure
explained in : how to backup transaction logs when the database is damaged.
But it wont work because I can't bring my database online. The only thing I
can do is restore my backup from last night but all the work that was
performed during the day is lost.
Can anyone help?
Thanks
FelixFirst, you have to be able to bring the SQL server itself online. If you
can't do that, you can't get any further. If the server is online but the
user database is missing the .mdf file, you can use the BACKUP LOG command
with the NO_TRUNCATE option. This will extract the "tail" of the log and
allow you to recover the missing transactions. This command works even with
a suspect or damaged database, provided the log file is undamaged.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Felix" <Felix@.discussions.microsoft.com> wrote in message
news:9EB0EEF3-948D-4B44-A509-762FAF60E90E@.microsoft.com...
> Hi,
> Due to a disk failure, I lost my .mdf file of a database. Of course I have
> a
> backup, of the database and transactionlogs from last night, but I want to
> restore untill the time of failure. So I tried to follow the procedure
> explained in : how to backup transaction logs when the database is
> damaged.
> But it wont work because I can't bring my database online. The only thing
> I
> can do is restore my backup from last night but all the work that was
> performed during the day is lost.
> Can anyone help?
> Thanks
> Felix|||Hi Geoff,
Thanks for the very fast reply. The situation is the following:
My SQL server is online, I only lost the .mdf file of one user database.
The user database is offline (of course .mdf file is lost).
I use: 'backup log user_database to disk '...file...' with NO_TRUNCATE' to
try and save the last portion of the log file but:
Server: MSG 942, Level 14, State3, Line 1
Datebase 'user_database' cannot be opened because it is offline.
How to proceed now?
Thanks in advance
Felix
"Geoff N. Hiten" wrote:
> First, you have to be able to bring the SQL server itself online. If you
> can't do that, you can't get any further. If the server is online but the
> user database is missing the .mdf file, you can use the BACKUP LOG command
> with the NO_TRUNCATE option. This will extract the "tail" of the log and
> allow you to recover the missing transactions. This command works even wi
th
> a suspect or damaged database, provided the log file is undamaged.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Felix" <Felix@.discussions.microsoft.com> wrote in message
> news:9EB0EEF3-948D-4B44-A509-762FAF60E90E@.microsoft.com...
>
>|||Offline and Suspect are two very different things in SQL Server. Offline is
a SQL controlled database state. Suspect is a reaction to an external
event. Try ALTER DATABASE user_database SET ONLINE and then try the backup
log command.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Felix" <Felix@.discussions.microsoft.com> wrote in message
news:93749B04-F531-4C2E-9395-F7F2ED4E2EB4@.microsoft.com...[vbcol=seagreen]
> Hi Geoff,
> Thanks for the very fast reply. The situation is the following:
> My SQL server is online, I only lost the .mdf file of one user database.
> The user database is offline (of course .mdf file is lost).
> I use: 'backup log user_database to disk '...file...' with NO_TRUNCATE'
> to
> try and save the last portion of the log file but:
> Server: MSG 942, Level 14, State3, Line 1
> Datebase 'user_database' cannot be opened because it is offline.
> How to proceed now?
> Thanks in advance
> Felix
>
> "Geoff N. Hiten" wrote:
>|||> First, you have to be able to bring the SQL server itself online. If you can't do that, y
ou can't
> get any further.
Some nit-picking, if you don't mind ;-)
You can get further. Copy the ldf file to a healthy SQL Server. Create a dat
abase with same file
names as the original database. Stop that SQL Server. Delete the newly creat
ed database files. Copy
the ldf file from the crashed machine to the place of the newly created data
base ldf file. Start
that SQL Server. That database is now suspect. Do the backup using NO_TRUNCA
TE.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:OFu1KrbyFHA.2448@.TK2MSFTNGP10.phx.gbl...
> First, you have to be able to bring the SQL server itself online. If you
can't do that, you can't
> get any further. If the server is online but the user database is missing
the .mdf file, you can
> use the BACKUP LOG command with the NO_TRUNCATE option. This will extract
the "tail" of the log
> and allow you to recover the missing transactions. This command works eve
n with a suspect or
> damaged database, provided the log file is undamaged.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Felix" <Felix@.discussions.microsoft.com> wrote in message
> news:9EB0EEF3-948D-4B44-A509-762FAF60E90E@.microsoft.com...
>|||Good point.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uWKGSccyFHA.916@.TK2MSFTNGP10.phx.gbl...
> Some nit-picking, if you don't mind ;-)
> You can get further. Copy the ldf file to a healthy SQL Server. Create a
> database with same file names as the original database. Stop that SQL
> Server. Delete the newly created database files. Copy the ldf file from
> the crashed machine to the place of the newly created database ldf file.
> Start that SQL Server. That database is now suspect. Do the backup using
> NO_TRUNCATE.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
> news:OFu1KrbyFHA.2448@.TK2MSFTNGP10.phx.gbl...
>|||Woooo...Tibor does that work? Kinda cool if it does ;-)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uWKGSccyFHA.916@.TK2MSFTNGP10.phx.gbl...
> Some nit-picking, if you don't mind ;-)
> You can get further. Copy the ldf file to a healthy SQL Server. Create a
> database with same file names as the original database. Stop that SQL
> Server. Delete the newly created database files. Copy the ldf file from
> the crashed machine to the place of the newly created database ldf file.
> Start that SQL Server. That database is now suspect. Do the backup using
> NO_TRUNCATE.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
> news:OFu1KrbyFHA.2448@.TK2MSFTNGP10.phx.gbl...
>|||> Woooo...Tibor does that work? Kinda cool if it does ;-)
Yep, sure is. I've done this several times, when customers had suspect datab
ases, stopped SQL
Server, copied the ldf file "for safety", started SQL Server, dropped the da
tabase since "it is
suspect anyhow...". See http://support.microsoft.com/defaul...25
3817. The KB
states rebuilding master on the same machine, but that is no different from
doing the same operation
on some other machine. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:eywlorcyFHA.2076@.TK2MSFTNGP14.phx.gbl...
> Woooo...Tibor does that work? Kinda cool if it does ;-)
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:uWKGSccyFHA.916@.TK2MSFTNGP10.phx.gbl...
>|||Ok...cudos to you...you da man ;-)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OczvUNdyFHA.3588@.tk2msftngp13.phx.gbl...
> Yep, sure is. I've done this several times, when customers had suspect
> databases, stopped SQL Server, copied the ldf file "for safety", started
> SQL Server, dropped the database since "it is suspect anyhow...". See
> http://support.microsoft.com/defaul...b;en-us;253817. The KB
> states rebuilding master on the same machine, but that is no different
> from doing the same operation on some other machine. :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:eywlorcyFHA.2076@.TK2MSFTNGP14.phx.gbl...
>|||Hi,
I have to thank you all, I succeeded in restoring the userdatabase to the
point of failure. My problem was that I always brought de database offline.
How it worked:
I stopped and started the SQL server, the database was left in status
suspect, not offline.
In this status, I was able to execute : backup log user_db to disk = 'file'
with INIT, NO_TRUNCATE.
Then I restored the backup from last night but with the NORECOVERY clause.
Then I executed restore log user_db from disk = 'file' with RECOVERY
And my database was back online containing everything until the point of
failure.
Thank you very much.
Hope this helps other people too.
"Jerry Spivey" wrote:
> Ok...cudos to you...you da man ;-)
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:OczvUNdyFHA.3588@.tk2msftngp13.phx.gbl...
>
>
Due to a disk failure, I lost my .mdf file of a database. Of course I have a
backup, of the database and transactionlogs from last night, but I want to
restore untill the time of failure. So I tried to follow the procedure
explained in : how to backup transaction logs when the database is damaged.
But it wont work because I can't bring my database online. The only thing I
can do is restore my backup from last night but all the work that was
performed during the day is lost.
Can anyone help?
Thanks
FelixFirst, you have to be able to bring the SQL server itself online. If you
can't do that, you can't get any further. If the server is online but the
user database is missing the .mdf file, you can use the BACKUP LOG command
with the NO_TRUNCATE option. This will extract the "tail" of the log and
allow you to recover the missing transactions. This command works even with
a suspect or damaged database, provided the log file is undamaged.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Felix" <Felix@.discussions.microsoft.com> wrote in message
news:9EB0EEF3-948D-4B44-A509-762FAF60E90E@.microsoft.com...
> Hi,
> Due to a disk failure, I lost my .mdf file of a database. Of course I have
> a
> backup, of the database and transactionlogs from last night, but I want to
> restore untill the time of failure. So I tried to follow the procedure
> explained in : how to backup transaction logs when the database is
> damaged.
> But it wont work because I can't bring my database online. The only thing
> I
> can do is restore my backup from last night but all the work that was
> performed during the day is lost.
> Can anyone help?
> Thanks
> Felix|||Hi Geoff,
Thanks for the very fast reply. The situation is the following:
My SQL server is online, I only lost the .mdf file of one user database.
The user database is offline (of course .mdf file is lost).
I use: 'backup log user_database to disk '...file...' with NO_TRUNCATE' to
try and save the last portion of the log file but:
Server: MSG 942, Level 14, State3, Line 1
Datebase 'user_database' cannot be opened because it is offline.
How to proceed now?
Thanks in advance
Felix
"Geoff N. Hiten" wrote:
> First, you have to be able to bring the SQL server itself online. If you
> can't do that, you can't get any further. If the server is online but the
> user database is missing the .mdf file, you can use the BACKUP LOG command
> with the NO_TRUNCATE option. This will extract the "tail" of the log and
> allow you to recover the missing transactions. This command works even wi
th
> a suspect or damaged database, provided the log file is undamaged.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Felix" <Felix@.discussions.microsoft.com> wrote in message
> news:9EB0EEF3-948D-4B44-A509-762FAF60E90E@.microsoft.com...
>
>|||Offline and Suspect are two very different things in SQL Server. Offline is
a SQL controlled database state. Suspect is a reaction to an external
event. Try ALTER DATABASE user_database SET ONLINE and then try the backup
log command.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Felix" <Felix@.discussions.microsoft.com> wrote in message
news:93749B04-F531-4C2E-9395-F7F2ED4E2EB4@.microsoft.com...[vbcol=seagreen]
> Hi Geoff,
> Thanks for the very fast reply. The situation is the following:
> My SQL server is online, I only lost the .mdf file of one user database.
> The user database is offline (of course .mdf file is lost).
> I use: 'backup log user_database to disk '...file...' with NO_TRUNCATE'
> to
> try and save the last portion of the log file but:
> Server: MSG 942, Level 14, State3, Line 1
> Datebase 'user_database' cannot be opened because it is offline.
> How to proceed now?
> Thanks in advance
> Felix
>
> "Geoff N. Hiten" wrote:
>|||> First, you have to be able to bring the SQL server itself online. If you can't do that, y
ou can't
> get any further.
Some nit-picking, if you don't mind ;-)
You can get further. Copy the ldf file to a healthy SQL Server. Create a dat
abase with same file
names as the original database. Stop that SQL Server. Delete the newly creat
ed database files. Copy
the ldf file from the crashed machine to the place of the newly created data
base ldf file. Start
that SQL Server. That database is now suspect. Do the backup using NO_TRUNCA
TE.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:OFu1KrbyFHA.2448@.TK2MSFTNGP10.phx.gbl...
> First, you have to be able to bring the SQL server itself online. If you
can't do that, you can't
> get any further. If the server is online but the user database is missing
the .mdf file, you can
> use the BACKUP LOG command with the NO_TRUNCATE option. This will extract
the "tail" of the log
> and allow you to recover the missing transactions. This command works eve
n with a suspect or
> damaged database, provided the log file is undamaged.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Felix" <Felix@.discussions.microsoft.com> wrote in message
> news:9EB0EEF3-948D-4B44-A509-762FAF60E90E@.microsoft.com...
>|||Good point.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uWKGSccyFHA.916@.TK2MSFTNGP10.phx.gbl...
> Some nit-picking, if you don't mind ;-)
> You can get further. Copy the ldf file to a healthy SQL Server. Create a
> database with same file names as the original database. Stop that SQL
> Server. Delete the newly created database files. Copy the ldf file from
> the crashed machine to the place of the newly created database ldf file.
> Start that SQL Server. That database is now suspect. Do the backup using
> NO_TRUNCATE.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
> news:OFu1KrbyFHA.2448@.TK2MSFTNGP10.phx.gbl...
>|||Woooo...Tibor does that work? Kinda cool if it does ;-)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uWKGSccyFHA.916@.TK2MSFTNGP10.phx.gbl...
> Some nit-picking, if you don't mind ;-)
> You can get further. Copy the ldf file to a healthy SQL Server. Create a
> database with same file names as the original database. Stop that SQL
> Server. Delete the newly created database files. Copy the ldf file from
> the crashed machine to the place of the newly created database ldf file.
> Start that SQL Server. That database is now suspect. Do the backup using
> NO_TRUNCATE.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
> news:OFu1KrbyFHA.2448@.TK2MSFTNGP10.phx.gbl...
>|||> Woooo...Tibor does that work? Kinda cool if it does ;-)
Yep, sure is. I've done this several times, when customers had suspect datab
ases, stopped SQL
Server, copied the ldf file "for safety", started SQL Server, dropped the da
tabase since "it is
suspect anyhow...". See http://support.microsoft.com/defaul...25
3817. The KB
states rebuilding master on the same machine, but that is no different from
doing the same operation
on some other machine. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:eywlorcyFHA.2076@.TK2MSFTNGP14.phx.gbl...
> Woooo...Tibor does that work? Kinda cool if it does ;-)
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:uWKGSccyFHA.916@.TK2MSFTNGP10.phx.gbl...
>|||Ok...cudos to you...you da man ;-)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OczvUNdyFHA.3588@.tk2msftngp13.phx.gbl...
> Yep, sure is. I've done this several times, when customers had suspect
> databases, stopped SQL Server, copied the ldf file "for safety", started
> SQL Server, dropped the database since "it is suspect anyhow...". See
> http://support.microsoft.com/defaul...b;en-us;253817. The KB
> states rebuilding master on the same machine, but that is no different
> from doing the same operation on some other machine. :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:eywlorcyFHA.2076@.TK2MSFTNGP14.phx.gbl...
>|||Hi,
I have to thank you all, I succeeded in restoring the userdatabase to the
point of failure. My problem was that I always brought de database offline.
How it worked:
I stopped and started the SQL server, the database was left in status
suspect, not offline.
In this status, I was able to execute : backup log user_db to disk = 'file'
with INIT, NO_TRUNCATE.
Then I restored the backup from last night but with the NORECOVERY clause.
Then I executed restore log user_db from disk = 'file' with RECOVERY
And my database was back online containing everything until the point of
failure.
Thank you very much.
Hope this helps other people too.
"Jerry Spivey" wrote:
> Ok...cudos to you...you da man ;-)
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:OczvUNdyFHA.3588@.tk2msftngp13.phx.gbl...
>
>
Subscribe to:
Posts (Atom)