How to recover transaction log from failed database ?.

Hi,
If data and log devices are stored on separate disks and
the disk with the data device crashes, is it then possible to backup (or
in some other was get) the transaction log from the log device so it can
be applied to the new database ?.
After the crash will database will be marked as "suspect" and it's not
possible to use the "backup log" command.
Let's say that one total backup was taken the previous night and the
last transaction log was taken 2 hours before the disk crash.
Best regards
Kjell Gunnarsson, Stockholm, Sweden.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
From BOL (Topic: How to Restore to the point of Failure)
How to restore to the point of failure (Transact-SQL)
To restore to the point of failure
1.. Execute the BACKUP LOG statement using the NO_TRUNCATE clause to back
up the currently active transaction log.
2.. Execute the RESTORE DATABASE statement using the NORECOVERY clause to
restore the database backup.
3.. Execute the RESTORE LOG statement using the NORECOVERY clause to apply
each transaction log backup.
4.. Execute the RESTORE LOG statement using the RECOVERY clause to apply
the transaction log backup created in Step 1.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Kjell Gunnarsson" <kjell.gunnarsson@.sungard.com> wrote in message
news:us4YvPcvEHA.3376@.TK2MSFTNGP12.phx.gbl...
> Hi,
> If data and log devices are stored on separate disks and
> the disk with the data device crashes, is it then possible to backup (or
> in some other was get) the transaction log from the log device so it can
> be applied to the new database ?.
> After the crash will database will be marked as "suspect" and it's not
> possible to use the "backup log" command.
> Let's say that one total backup was taken the previous night and the
> last transaction log was taken 2 hours before the disk crash.
> Best regards
> Kjell Gunnarsson, Stockholm, Sweden.
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||This scenario is what the NO_TRUNCATE option for the log backup command is for.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Kjell Gunnarsson" <kjell.gunnarsson@.sungard.com> wrote in message
news:us4YvPcvEHA.3376@.TK2MSFTNGP12.phx.gbl...
> Hi,
> If data and log devices are stored on separate disks and
> the disk with the data device crashes, is it then possible to backup (or
> in some other was get) the transaction log from the log device so it can
> be applied to the new database ?.
> After the crash will database will be marked as "suspect" and it's not
> possible to use the "backup log" command.
> Let's say that one total backup was taken the previous night and the
> last transaction log was taken 2 hours before the disk crash.
> Best regards
> Kjell Gunnarsson, Stockholm, Sweden.
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

How to recover transaction log from failed database ?.

Hi,
If data and log devices are stored on separate disks and
the disk with the data device crashes, is it then possible to backup (or
in some other was get) the transaction log from the log device so it can
be applied to the new database ?.
After the crash will database will be marked as "suspect" and it's not
possible to use the "backup log" command.
Let's say that one total backup was taken the previous night and the
last transaction log was taken 2 hours before the disk crash.
Best regards
Kjell Gunnarsson, Stockholm, Sweden.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!From BOL (Topic: How to Restore to the point of Failure)
How to restore to the point of failure (Transact-SQL)
To restore to the point of failure
1.. Execute the BACKUP LOG statement using the NO_TRUNCATE clause to back
up the currently active transaction log.
2.. Execute the RESTORE DATABASE statement using the NORECOVERY clause to
restore the database backup.
3.. Execute the RESTORE LOG statement using the NORECOVERY clause to apply
each transaction log backup.
4.. Execute the RESTORE LOG statement using the RECOVERY clause to apply
the transaction log backup created in Step 1.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Kjell Gunnarsson" <kjell.gunnarsson@.sungard.com> wrote in message
news:us4YvPcvEHA.3376@.TK2MSFTNGP12.phx.gbl...
> Hi,
> If data and log devices are stored on separate disks and
> the disk with the data device crashes, is it then possible to backup (or
> in some other was get) the transaction log from the log device so it can
> be applied to the new database ?.
> After the crash will database will be marked as "suspect" and it's not
> possible to use the "backup log" command.
> Let's say that one total backup was taken the previous night and the
> last transaction log was taken 2 hours before the disk crash.
> Best regards
> Kjell Gunnarsson, Stockholm, Sweden.
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!|||This scenario is what the NO_TRUNCATE option for the log backup command is f
or.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Kjell Gunnarsson" <kjell.gunnarsson@.sungard.com> wrote in message
news:us4YvPcvEHA.3376@.TK2MSFTNGP12.phx.gbl...
> Hi,
> If data and log devices are stored on separate disks and
> the disk with the data device crashes, is it then possible to backup (or
> in some other was get) the transaction log from the log device so it can
> be applied to the new database ?.
> After the crash will database will be marked as "suspect" and it's not
> possible to use the "backup log" command.
> Let's say that one total backup was taken the previous night and the
> last transaction log was taken 2 hours before the disk crash.
> Best regards
> Kjell Gunnarsson, Stockholm, Sweden.
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

How to recover transaction log from failed database ?.

Hi,
If data and log devices are stored on separate disks and
the disk with the data device crashes, is it then possible to backup (or
in some other was get) the transaction log from the log device so it can
be applied to the new database ?.
After the crash will database will be marked as "suspect" and it's not
possible to use the "backup log" command.
Let's say that one total backup was taken the previous night and the
last transaction log was taken 2 hours before the disk crash.
Best regards
Kjell Gunnarsson, Stockholm, Sweden.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!From BOL (Topic: How to Restore to the point of Failure)
How to restore to the point of failure (Transact-SQL)
To restore to the point of failure
1.. Execute the BACKUP LOG statement using the NO_TRUNCATE clause to back
up the currently active transaction log.
2.. Execute the RESTORE DATABASE statement using the NORECOVERY clause to
restore the database backup.
3.. Execute the RESTORE LOG statement using the NORECOVERY clause to apply
each transaction log backup.
4.. Execute the RESTORE LOG statement using the RECOVERY clause to apply
the transaction log backup created in Step 1.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Kjell Gunnarsson" <kjell.gunnarsson@.sungard.com> wrote in message
news:us4YvPcvEHA.3376@.TK2MSFTNGP12.phx.gbl...
> Hi,
> If data and log devices are stored on separate disks and
> the disk with the data device crashes, is it then possible to backup (or
> in some other was get) the transaction log from the log device so it can
> be applied to the new database ?.
> After the crash will database will be marked as "suspect" and it's not
> possible to use the "backup log" command.
> Let's say that one total backup was taken the previous night and the
> last transaction log was taken 2 hours before the disk crash.
> Best regards
> Kjell Gunnarsson, Stockholm, Sweden.
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||This scenario is what the NO_TRUNCATE option for the log backup command is for.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Kjell Gunnarsson" <kjell.gunnarsson@.sungard.com> wrote in message
news:us4YvPcvEHA.3376@.TK2MSFTNGP12.phx.gbl...
> Hi,
> If data and log devices are stored on separate disks and
> the disk with the data device crashes, is it then possible to backup (or
> in some other was get) the transaction log from the log device so it can
> be applied to the new database ?.
> After the crash will database will be marked as "suspect" and it's not
> possible to use the "backup log" command.
> Let's say that one total backup was taken the previous night and the
> last transaction log was taken 2 hours before the disk crash.
> Best regards
> Kjell Gunnarsson, Stockholm, Sweden.
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

How to recover the primary sevrer in a cluster?

Hello, everyone:
The two SQL Server 2k (SP4) are running on W2K(SP4) in clustered A/A mode. The seconary server took over successfuly as the primary server crached. How to recover the primary sevrer?
Thanks a lot.Few articles to help:

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/failclus.mspx and http://www.microsoft.com/resources/documentation/sql/2000/all/reskit/en-us/part4/c1261.mspx

HTH

How to recover SQL Server database from suspect status

One of the database in our SQL Server 2000 environment is in the suspect status. We need to bring it back to the normal status.

The problem occurred because the disk on which the data file and log file for this database were placed ran out of space.

Pls note other databases in the same server are working fine.
Later on more space was made available on this disk. We tried the following options but with no success.

1. Reset the status of database and restarted the SQL Server. After restarting the SQL Server, the database once again was showing the suspect status.
2. Used the same data and log file in another SQL Server and attached with the database in this another SQL Server.
3. Tried dbcc chkdb with repair_allow_data_loss.

Since the database is in suspect status, we are neither able to export the data nor able to back up the database.

Please suggest some options to recover the database from the suspect status. Also it would be great if we can get the commands, scripts to find if the data/log file is corrupt and a way to correct it (even with data loss is fine).

Did you first run a checkdb? What were the errors from the checkdb?

What errors are in the SQL Server error log? Do you have backups available to restore from? That's a better option if the checkdb reported allow_data_loss was the repair level required.

-Sue

|||

Thanks for the response. The problem is resolved. We brought the database in emergency mode and then in single user mode. Later we were able to recover the database using the checkdb utility, but with some loss of data.

How to recover sql server database data

Hi all. A few days ago i installed mcafee total protection 2007 and that disabled my internet connection totally!! I couldn't fix it so i had to uninstall it. After uninstalling mcafee total protection 2007 still i couldn't join Internet!!

So i got mad and went and made repair installation of windows xp pro. After repair installation i couldn't activate it since Internet connection was still not working!! So i went and installed another copy of xp pro on different folder and named it WINDOWS (old one was WINDOWS2).Thanks god now i could join Internet but all my shortcuts and menu items are not visible.

Unfortunately I can't even start sql server 2000 either!! could any one tell me what should i do in order to get access to all database tables and data that i had in sql server and make a back up. I didn't install any new sql server yet. I be happy if an expert tell me what should i do. The installation folder of old windows(WINDOWS2) is still available along with sql server installation folder.Thank and looking forward for reply.As this question is really more of a Windows / Microsoft SQL Server question than a true SQL language question, I'm going to move the thread to the Microsoft SQL Server forum so it will get more attention.

-PatP|||what I presume, when you install a new copy of Windows leaving behind the old copy will not copy all your applications onto new winodws software. You need to install all your applications and sql server as well because when you install the new copy of windows xp, this will create a new registry file which may not have your old windows application registry details...
I hope this will you.

How to recover SQL database from a removed laptop hard drive?

Dear group:

I have removed my hard drive from my laptop (which is now toast) and
have managed to recover nearly all the data from it by installing the
drive into my desktop. I was hoping to reboot the dektop to see if I
could load the operating system on the laptop's hard drive so I could
do a manual backup of the SQL database on it. This does not work.

Does anyone know of a way to recover my SQL database and all its tables
given the circumstances above?

TIA
ISZNevermind... I found the answer. I was able to successfully use the
follwing transact...

EXEC sp_attach_db @.dbname = N'****',
@.filename1 = N'g:\Program Files\Microsoft SQL
Server\MSSQL$****\Data\****_DB_Data.MDF',
@.filename2 = N'g:\Program Files\Microsoft SQL
Server\MSSQL$****\Data\****_DB_Log.LDF'|||Nevermind... I found the answer. I was able to successfully use the
follwing transact...

EXEC sp_attach_db @.dbname = N'****',
@.filename1 = N'g:\Program Files\Microsoft SQL
Server\MSSQL$****\Data\****_DB_Data.MDF',
@.filename2 = N'g:\Program Files\Microsoft SQL
Server\MSSQL$****\Data\****_DB_Log.LDF'|||Nevermind... I found the answer. I was able to successfully use the
follwing transact...

EXEC sp_attach_db @.dbname = N'****',
@.filename1 = N'g:\Program Files\Microsoft SQL
Server\MSSQL$****\Data\****_DB_Data.MDF',
@.filename2 = N'g:\Program Files\Microsoft SQL
Server\MSSQL$****\Data\****_DB_Log.LDF'|||Restore from a backup. You do have backups of your laptop of course...?
:-)
--
David Portas
SQL Server MVP
--|||<q2face@.hotmail.com> wrote in message
news:1108162896.401884.268390@.l41g2000cwc.googlegr oups.com...
> Dear group:
> I have removed my hard drive from my laptop (which is now toast) and
> have managed to recover nearly all the data from it by installing the
> drive into my desktop. I was hoping to reboot the dektop to see if I
> could load the operating system on the laptop's hard drive so I could
> do a manual backup of the SQL database on it. This does not work.
> Does anyone know of a way to recover my SQL database and all its tables
> given the circumstances above?
> TIA
> ISZ

If you can recover the .mdf and .ldf files from the laptop hard drive, you
could try attaching the database with sp_attach_db (or
sp_attach_single_file_db if you only have the .mdf file). But this may not
work at all, and your best option is, as David said, to restore from a
backup of the database.

Simon

How to recover Master database table data

Hi all, I am working with SQL Server2000 and I have done a horrible thing here.
We here have an script that delete all data from all user tables of a database, and I run it in the master DATABASE.
As we don't made backups of this database, now somethings of the database aren't working.

Here is the script:

declare @.table_name sysname
declare @.alter_table_statement varchar(256)
declare @.delete_statement varchar(256)

-- definindo o cursor...
declare table_name_cursor cursor local fast_forward for
select
name
from
sysobjects
where
xtype = 'U'
and
name <> 'dtproperties'

-- desligando os vínculos...
open table_name_cursor
fetch next from table_name_cursor into @.table_name
select @.alter_table_statement = 'alter table ' + ltrim(rtrim(@.table_name)) + ' nocheck constraint all'
exec(@.alter_table_statement)
while @.@.Fetch_Status = 0
begin
fetch next from table_name_cursor into @.table_name
select @.alter_table_statement = 'alter table ' + ltrim(rtrim(@.table_name)) + ' nocheck constraint all'
exec(@.alter_table_statement)
end
close table_name_cursor

open table_name_cursor
fetch next from table_name_cursor into @.table_name
select @.delete_statement = 'delete from ' + ltrim(rtrim(@.table_name))
exec(@.delete_statement)
while @.@.Fetch_Status = 0
begin
fetch next from table_name_cursor into @.table_name
select @.delete_statement = 'delete from ' + ltrim(rtrim(@.table_name))
exec(@.delete_statement)
end
close table_name_cursor

open table_name_cursor
fetch next from table_name_cursor into @.table_name
select @.alter_table_statement = 'alter table ' + ltrim(rtrim(@.table_name)) + ' check constraint all'
exec(@.alter_table_statement)
while @.@.Fetch_Status = 0
begin
fetch next from table_name_cursor into @.table_name
select @.alter_table_statement = 'alter table ' + ltrim(rtrim(@.table_name)) + ' check constraint all'
exec(@.alter_table_statement)
end
close table_name_cursor

deallocate table_name_cursor

I have tried to restore master table with the restore function, but it doesn't work. When I try to do this I received a message informing that it can't copy the data because one file was in use. The server was in a single user mode.

Is there anyway to recover the data that I have lost?

Help me understand this.

At the beginning, you say that you don't have backups of the master database.

At the end you say that you tried to restore the master database, but had problems.

You should be able to restore the master database if you have a backup of it, and this would be the best method to use.

Can you clarify, and also let us know what method (T-SQL commands etc.) you used to attempt the restore?

|||Kevin don't have any backup of this database, I tried to rebuild it, not restore it, I used the wrong word for it.|||

There isn't any easy answer to this. If you have already rebuilt master then you should have SQL Server up and running with a clean master. Realize that by rebuilding the master database all your jobs and maintenance plans in MSDB have been lost, unless you have a backup of msdb or scripts to recreate the jobs.

The datafiles for your user databases should still be on the disk, you will need to attach those using sp_attach_db.

Once you have the databases attached you still won't have the logins. You will need to recreate the logins, then remap those new logins using the procedure outlined in http://support.microsoft.com/kb/274188/en-us

|||Ok thanks... one more explanation
In fact my real problem isn't on the databases and user logins, becaus this I doesn't lost. I lost the access to table metadata (via jdbc driver) and the permission to create diagrams. Probably this can be done by reconfiguring the server, but I doesn't know how to do it.

How to recover master database

How Do I do to recover information from master database
to master database?Lorena
if you mean copy between master in one installation and master in =
another you could use DTS, linked servers or a variety of other options, =
but it all depneds what you are trying to recover from where and to =
where, can you post some more details?
Mike John
"Lorena" <anonymous@.discussions.microsoft.com> wrote in message =
news:d71701c40de3$4f81cb20$a601280a@.phx.gbl...
> How Do I do to recover information from master database
> to master database?
>|||My database master was destroided, but I have a full
backup of master database, but i can't recover it, show
me the next message. RESTORE DATABASE must be used in
single user mode when trying to restore the master
database
I need recover information about the aplicactions users.
Thanks
Lorena

>--Original Message--
>Lorena
>if you mean copy between master in one installation and
master in another you could use DTS, linked servers or a
variety of other options, but it all depneds what you are
trying to recover from where and to where, can you post
some more details?
>Mike John
>"Lorena" <anonymous@.discussions.microsoft.com> wrote in
message news:d71701c40de3$4f81cb20$a601280a@.phx.gbl...
>.
>|||If you search for below in Books Online, you find instructions on how to
start SQL Server in single user mode:
"restore master"
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Lorena" <anonymous@.discussions.microsoft.com> wrote in message
news:bb8d01c40de8$f50d2670$a001280a@.phx.gbl...
> My database master was destroided, but I have a full
> backup of master database, but i can't recover it, show
> me the next message. RESTORE DATABASE must be used in
> single user mode when trying to restore the master
> database
> I need recover information about the aplicactions users.
> Thanks
> Lorena
>
>
> master in another you could use DTS, linked servers or a
> variety of other options, but it all depneds what you are
> trying to recover from where and to where, can you post
> some more details?
> message news:d71701c40de3$4f81cb20$a601280a@.phx.gbl...|||Hi,
Steps:
1. Stop MSSQL server Service from Control panel
2. Execute sqlservr.exe -c -m from command prompt
3. Restore the Master database backup using RESTORE DATABASE
4. Once all the activies are over Press COntrol break in key board to stop
MS Sqlserver
5. Restart SQL server from COntrol panel services as normal.
Consider the below after master restore
a. If the Master backup is old, then any database users previously
associated with logins that need to be re-created .
b. If any user databases were created after Master db was backed up, those
databases will not be available.Use SP_Attach_db to
attach those databases to avoid restore time. If attach gives issues
then try restoring from backup.
Thanks
Hari
MCDBA
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ezAvumeDEHA.2932@.tk2msftngp13.phx.gbl...
> If you search for below in Books Online, you find instructions on how to
> start SQL Server in single user mode:
> "restore master"
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Lorena" <anonymous@.discussions.microsoft.com> wrote in message
> news:bb8d01c40de8$f50d2670$a001280a@.phx.gbl...
>

How to recover if just .ldf is lost?

If a disk containing the log file is lost, but the main database files are
OK,
what do you have to do to fix it?
All data before the last checkpoint should be in there.Hi
You may be lucky an sp_attach_single_file_db may work!
A data recovery firm may be able to retrieve the log files.
If you have log backups then you may be able to recover to a certain
point with them,
John|||Try Johns suggestion first. If that does not work have a look here:
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
Restoring a .mdf
--
Andrew J. Kelly SQL MVP
"Sandy Tipper" <sandy@.removethis.atuc.net> wrote in message
news:BQQSd.1174$MJ.6200@.newscontent-01.sprint.ca...
> If a disk containing the log file is lost, but the main database files are
> OK,
> what do you have to do to fix it?
> All data before the last checkpoint should be in there.
>|||You can attach the database from Enterprise manager.
You can just selet the primary mdf file and it will warn you that the .ldf
file will be recreated.
"Andrew J. Kelly" wrote:
> Try Johns suggestion first. If that does not work have a look here:
> http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
> Restoring a .mdf
> --
> Andrew J. Kelly SQL MVP
>
> "Sandy Tipper" <sandy@.removethis.atuc.net> wrote in message
> news:BQQSd.1174$MJ.6200@.newscontent-01.sprint.ca...
> > If a disk containing the log file is lost, but the main database files are
> > OK,
> > what do you have to do to fix it?
> > All data before the last checkpoint should be in there.
> >
>
>|||There are a number of conditions that has to be met in order for SQL server to be able to just
"create a log file". IF these are not met, attach will not work and you are in for a support call,
restore or similar.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in message
news:0601E3D1-1CF2-4F85-95C7-E41788576320@.microsoft.com...
> You can attach the database from Enterprise manager.
> You can just selet the primary mdf file and it will warn you that the .ldf
> file will be recreated.
> "Andrew J. Kelly" wrote:
>> Try Johns suggestion first. If that does not work have a look here:
>> http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
>> Restoring a .mdf
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Sandy Tipper" <sandy@.removethis.atuc.net> wrote in message
>> news:BQQSd.1174$MJ.6200@.newscontent-01.sprint.ca...
>> > If a disk containing the log file is lost, but the main database files are
>> > OK,
>> > what do you have to do to fix it?
>> > All data before the last checkpoint should be in there.
>> >
>>

How to recover if just .ldf is lost?

If a disk containing the log file is lost, but the main database files are
OK,
what do you have to do to fix it?
All data before the last checkpoint should be in there.
Hi
You may be lucky an sp_attach_single_file_db may work!
A data recovery firm may be able to retrieve the log files.
If you have log backups then you may be able to recover to a certain
point with them,
John
|||Try Johns suggestion first. If that does not work have a look here:
http://www.sqlservercentral.com/scri...p?scriptid=599
Restoring a .mdf
Andrew J. Kelly SQL MVP
"Sandy Tipper" <sandy@.removethis.atuc.net> wrote in message
news:BQQSd.1174$MJ.6200@.newscontent-01.sprint.ca...
> If a disk containing the log file is lost, but the main database files are
> OK,
> what do you have to do to fix it?
> All data before the last checkpoint should be in there.
>
|||You can attach the database from Enterprise manager.
You can just selet the primary mdf file and it will warn you that the .ldf
file will be recreated.
"Andrew J. Kelly" wrote:

> Try Johns suggestion first. If that does not work have a look here:
> http://www.sqlservercentral.com/scri...p?scriptid=599
> Restoring a .mdf
> --
> Andrew J. Kelly SQL MVP
>
> "Sandy Tipper" <sandy@.removethis.atuc.net> wrote in message
> news:BQQSd.1174$MJ.6200@.newscontent-01.sprint.ca...
>
>
|||There are a number of conditions that has to be met in order for SQL server to be able to just
"create a log file". IF these are not met, attach will not work and you are in for a support call,
restore or similar.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in message
news:0601E3D1-1CF2-4F85-95C7-E41788576320@.microsoft.com...[vbcol=seagreen]
> You can attach the database from Enterprise manager.
> You can just selet the primary mdf file and it will warn you that the .ldf
> file will be recreated.
> "Andrew J. Kelly" wrote:

How to recover if just .ldf is lost?

If a disk containing the log file is lost, but the main database files are
OK,
what do you have to do to fix it?
All data before the last checkpoint should be in there.Hi
You may be lucky an sp_attach_single_file_db may work!
A data recovery firm may be able to retrieve the log files.
If you have log backups then you may be able to recover to a certain
point with them,
John|||Try Johns suggestion first. If that does not work have a look here:
http://www.sqlservercentral.com/scr...sp?scriptid=599
Restoring a .mdf
Andrew J. Kelly SQL MVP
"Sandy Tipper" <sandy@.removethis.atuc.net> wrote in message
news:BQQSd.1174$MJ.6200@.newscontent-01.sprint.ca...
> If a disk containing the log file is lost, but the main database files are
> OK,
> what do you have to do to fix it?
> All data before the last checkpoint should be in there.
>|||You can attach the database from Enterprise manager.
You can just selet the primary mdf file and it will warn you that the .ldf
file will be recreated.
"Andrew J. Kelly" wrote:

> Try Johns suggestion first. If that does not work have a look here:
> http://www.sqlservercentral.com/scr...sp?scriptid=599
> Restoring a .mdf
> --
> Andrew J. Kelly SQL MVP
>
> "Sandy Tipper" <sandy@.removethis.atuc.net> wrote in message
> news:BQQSd.1174$MJ.6200@.newscontent-01.sprint.ca...
>
>|||There are a number of conditions that has to be met in order for SQL server
to be able to just
"create a log file". IF these are not met, attach will not work and you are
in for a support call,
restore or similar.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in me
ssage
news:0601E3D1-1CF2-4F85-95C7-E41788576320@.microsoft.com...[vbcol=seagreen]
> You can attach the database from Enterprise manager.
> You can just selet the primary mdf file and it will warn you that the .ldf
> file will be recreated.
> "Andrew J. Kelly" wrote:
>

How to Recover from: OS on drive C, SQL Server installation & mdf's on X, ldf's on Y

Our main sql server has failed, and our failover procedure has failed
as well (the backup server couldn't handle the temporary strain).
Our OS was installed on one drive (drive C)
Our SQL Server installation is on drive X, along with the MDF files for
the databases.
The LDF files are on drive Y.
Drive C is fried, drive X & Y are fine. We are recreating drive C's OS
installation, but how do we handle the actual server installation being
on drive X? Do we need to run the SQL Server installation over again?
If so, are there special considerations in this case?<ebeiler@.fandr.com> wrote in message
news:1141394015.039948.245470@.j33g2000cwa.googlegroups.com...
> Our main sql server has failed, and our failover procedure has failed
> as well (the backup server couldn't handle the temporary strain).
> Our OS was installed on one drive (drive C)
> Our SQL Server installation is on drive X, along with the MDF files for
> the databases.
> The LDF files are on drive Y.
>
> Drive C is fried, drive X & Y are fine. We are recreating drive C's OS
> installation, but how do we handle the actual server installation being
> on drive X? Do we need to run the SQL Server installation over again?
> If so, are there special considerations in this case?
>
You can install SQL Server normally. The program files will go on the new
drive C.
Then simply attach (or restore) your databases on drive X.
sp_attach_db
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ae-az_52oy.asp
How to move databases between computers that are running SQL Server
http://www.support.microsoft.com/?id=314546
David|||Thanks for your help.

How to Recover from: OS on drive C, SQL Server installation & mdf's on X, ldf's on Y

Our main sql server has failed, and our failover procedure has failed
as well (the backup server couldn't handle the temporary strain).
Our OS was installed on one drive (drive C)
Our SQL Server installation is on drive X, along with the MDF files for
the databases.
The LDF files are on drive Y.
Drive C is fried, drive X & Y are fine. We are recreating drive C's OS
installation, but how do we handle the actual server installation being
on drive X? Do we need to run the SQL Server installation over again?
If so, are there special considerations in this case?
<ebeiler@.fandr.com> wrote in message
news:1141394015.039948.245470@.j33g2000cwa.googlegr oups.com...
> Our main sql server has failed, and our failover procedure has failed
> as well (the backup server couldn't handle the temporary strain).
> Our OS was installed on one drive (drive C)
> Our SQL Server installation is on drive X, along with the MDF files for
> the databases.
> The LDF files are on drive Y.
>
> Drive C is fried, drive X & Y are fine. We are recreating drive C's OS
> installation, but how do we handle the actual server installation being
> on drive X? Do we need to run the SQL Server installation over again?
> If so, are there special considerations in this case?
>
You can install SQL Server normally. The program files will go on the new
drive C.
Then simply attach (or restore) your databases on drive X.
sp_attach_db
http://msdn.microsoft.com/library/de...ae-az_52oy.asp
How to move databases between computers that are running SQL Server
http://www.support.microsoft.com/?id=314546
David
|||Thanks for your help.

how to recover from suspect mode distribution database

Put the database into emergency mode and attempt to bcp the data out.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Kuba Guszkiewicz [cs]" <k.gluszkiewicz@.citysoftware.com.pl> wrote in
message news:%23Qw%23$mvZHHA.1388@.TK2MSFTNGP05.phx.gbl...
> Hi ng,
> during this night big (3GB) log file in my distribution database was
> corrupted,
> now my database is in suspect mode,
> is the any solution to recover database from this mode? for ex.: use bcp
> or something?
> i need this replication working...
> if anyone knows the solve please answer me,
> i will be very gratefull for step by step instructions
> thanks in advance
> kg
>
Try this, script out all publications and subscriptions. Then drop the
database, and recreate it.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Kuba Guszkiewicz [cs]" <k.gluszkiewicz@.citysoftware.com.pl> wrote in
message news:u3Sha4wZHHA.5020@.TK2MSFTNGP05.phx.gbl...
> Hilary,
> thanks, but what's next?
> i have to run bcp on each table or exist any bcp instuction which can i
> out all data?
> could you give me next step?
> i have to delete database?
> please help
> thanks
> --
> pozdrawiam
> Kuba Guszkiewicz
> k.gluszkiewicz@.citysoftware.com.pl
> tel. 509-650-351
> (22) 711-26-45
> (22) 711-26-46
> fax (22) 711-26-47
> Uytkownik "Hilary Cotter" <hilary.cotter@.gmail.com> napisa w wiadomoci
> news:O8T2vywZHHA.2432@.TK2MSFTNGP03.phx.gbl...
>
|||Ok, script out all your publications and subscriptions.
try to remove replication, by disabling it.
If this fails try the following:
sp_dropdistpublisher
if this doesn't work try it with sp_dropdistpublisher @.no_checks=1 and then
if this fails sp_dropdistpublisher @.no_checks=1 , @.ignore_distributor=1
Then
sp_dropdistributor
again if this fails try it with @.no_checks and then @.ignore_distributor
Then
sp_dropdistributiondb
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Kuba Guszkiewicz [cs]" <k.gluszkiewicz@.citysoftware.com.pl> wrote in
message news:eitJ0fzZHHA.4136@.TK2MSFTNGP04.phx.gbl...
> thanks for any advice, but i need more detailed advice,
> Hilary could you help?
> Uytkownik "Hilary Cotter" <hilary.cotter@.gmail.com> napisa w wiadomoci
> news:%23Pgb$UxZHHA.3968@.TK2MSFTNGP06.phx.gbl...
>

How to recover from mdf file (SQL Server 2000)

Hi,

My database corrupted because when I was running an update query, there is a power failure. After the computer booted, I cannot open the database anymore, it just not responding. Then I stop the sql server service, and tried to rename the .mdf and .ldf. After that it worked normally, but I need the data from the corrupted mdf file, I tried to attach the database but it just hanged. I even tried to attach without the .ldf file but it didn't work either, so I concluded that the problem is with the mdf file.

Is there any way to recover my data ?

Thanks in advance

Regards,

Edwin

Can you rename the mdf,ldf files to their original names and attach them to your SQL Server? (with all SQL Services running)

If that works, try using

DBCC CHECKDB ('DatabaseName' /*,REPAIR_REBUILD*/)
WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITY

To see what went wrong

A 2nd choice is to restore the mdf,ldf files from a recent backup (if backup exists)

|||

Hi,

We'd tried that but we got no luck. Attaching the file in it's original name didn't work, the computer just hanged, we suspected that the .mdf file corrupted

Unfortunately, we don't have any backup.

Thanks for reply

|||

Can you try this trick:

Create a new 'dummy' database that has the same name as the old database, say 'TEST'

So now you have 2 files: test.mdf and test.ldf files

Stop SQL Server services and delete these files

Copy and rename your corrupted mdf,ldf files in their place

Restart the services and see what error message you get when SQL tries to read from the corrupted files that are now attached to the TEST db.

Then you can start the debugging 'process' based on error number

Cheers

|||Try this undocumented stuff provided by Kevin [MS].

==========
1. Back up the .mdf/.ndf files at first!!!

2. Change the database context to Master and allow updates to system tables:

Use Master
Go
sp_configure 'allow updates', 1
reconfigure with override
Go

3. Set the database in Emergency (bypass recovery) mode:

select * from sysdatabases where name = '<db_name>'
-- note the value of the status column for later use in # 6
begin tran
update sysdatabases set status = 32768 where name = '<db_name>'
-- Verify one row is updated before committing
commit tran

4. Stop and restart SQL server.

5. Call DBCC REBUILD_LOG command to rebuild a "blank" log file based on the
suspected db.
The syntax for DBCC REBUILD_LOG is as follows:

DBCC rebuild_log('<db_name>','<log_filename>')

where <db_name> is the name of the database and <log_filename> is
the physical path to the new log file, not a logical file name. If you
do not
specify the full path, the new log is created in the Windows NT system
root
directory (by default, this is the Winnt\System32 directory).

6. Set the database in single-user mode and run DBCC CHECKDB to validate
physical consistency:

sp_dboption '<db_name>', 'single user', 'true'
DBCC checkdb('<db_name>')
Go
begin tran
update sysdatabases set status = <prior value> where name = '<db_name>'
-- verify one row is updated before committing
commit tran
Go

7. Turn off the updates to system tables by using:

sp_configure 'allow updates', 0
reconfigure with override
Go
============|||

OK.

First off, I don't think I've posted those steps. I've probably posted similar for use in DIRE circumstances (like this one) where there is no backup, and data loss is acceptable.

The procedure above is primarily used for cases where you have only the MDF file and no log.

Attaching the database should not hang the system. It could make it busy for awhile, but not totally hang.

How long did you let the system go before giving up and canceling?

Please look in both the Windows Event Log and in the SQL errorlog files and post any related errors here.

You need to get the database attached to an instance in order to do anything with it. Your best bet is to put the files back in their original locations and just let it run its course. You might try putting the database in emergency mode (using the steps above) before putting the files back in place. Then the database wouldn't run recovery when the instance started up.

You could then run DBCC CHECKDB , and presuming that there are serious problems, you can then re-run the CHECKDB with REPAIR_ALLOW_DATA_LOSS, taking into consideration that the command means what it says: data will be lost.

How to recover from dropped tempdb?

I am testing sql server 2005 in different disaster situations happened under 2000. I was reading a lot about no update on system tables, so:

sp_detach_db 'tempdb'

In sql 2000 i could issue something like the following command to recover:

use master

sp_configure 'allow updates', 1

reconfigure with override

go

insert sys.sysdatabases (name, dbid, sid, mode, status, status2, crdate, reserved, category, cmptlevel, filename, version)

values ('tempdb', 2, 0x01, 0, 8, 1090520064, '2007-01-27 13:03:10.873', '1900-01-01 00:00:00.000', 0, '90', 'D:\beep\mssql\temp\tempdb.mdf', 611)

go

sp_configure 'allow updates', 0

reconfigure with override

go

As tempdb is hardcoded db id 2, I do not have a chance to recover without a master backup.

Of course, this situation can be recovered using a master backup, but it is not always available.

Thank you for your comments.

How is it that you are detaching tempdb?

1> sp_detach_db tempdb
2> go
Msg 7940, Level 16, State 1:
System databases master, model, msdb, and tempdb cannot be detached.
1>

|||

Thanks for the fast response.

Yes, it is the normal behavior. But imagine the following situation:

The end-user tries to move the system databases to another disk. He reads the information on your site (http://support.microsoft.com/kb/224071/) and starts the server with /T3608. He executes the procedure step by step, moves msdb and model, forgets to read the remaining part and happy to move tempdb the same way.

This is the point, when you are getting involved. How to proceed?

|||Well, that script won'y work with SQL Server 2005. You can not allowed to make direct updates to system tables. You can't drop or detach a system database, so the only way of having an issue with tempdb is that it either becomes damaged while SQL Server is running in which case the SQL Server will go offline and fixing the issue is a matter of restarting SQL Server or you have an issue while starting up SQL Server in which case the SQL Server won't start and you would then need to go through the process documented in BOL for recreating tempdb.|||I have asked that the article be updated to remove SQL 2005 from the section that does the moves via detach. ALTER DATABASE should be used to change the file paths in these cases and there are detailed instructions in books online.|||

Thank you for your comment.

You _can_ detach the tempdb, if you start SQL Server 2005 with the /T3608 parameter [I have tried before posting the question]. From the point tempdb detached it cannot be re-attached, nor can be re-created using the way you mention, as the row with hardcoded database id "2" does not exists in the sysdatabases table.

|||

Thank you.

For closing the issue can we say, that the only way to recover from this situation is attaching a clean master db and re-attach the databases and re-create all global settings (logins, endpoints, etc.) stored in master?

|||

If you have a backup of master, you could restore that after putting in the clean master. Master is usually small enough that it should not be too big a burden to do full backups on a regular basis.

And if you had truly followed the article, the first step is

?

Make a current backup of all databases, especially master, from their current location.

which should allow you to recover.

How to recover from dropped tempdb?

I am testing sql server 2005 in different disaster situations happened under 2000. I was reading a lot about no update on system tables, so:

sp_detach_db 'tempdb'

In sql 2000 i could issue something like the following command to recover:

use master

sp_configure 'allow updates', 1

reconfigure with override

go

insert sys.sysdatabases (name, dbid, sid, mode, status, status2, crdate, reserved, category, cmptlevel, filename, version)

values ('tempdb', 2, 0x01, 0, 8, 1090520064, '2007-01-27 13:03:10.873', '1900-01-01 00:00:00.000', 0, '90', 'D:\beep\mssql\temp\tempdb.mdf', 611)

go

sp_configure 'allow updates', 0

reconfigure with override

go

As tempdb is hardcoded db id 2, I do not have a chance to recover without a master backup.

Of course, this situation can be recovered using a master backup, but it is not always available.

Thank you for your comments.

How is it that you are detaching tempdb?

1> sp_detach_db tempdb
2> go
Msg 7940, Level 16, State 1:
System databases master, model, msdb, and tempdb cannot be detached.
1>

|||

Thanks for the fast response.

Yes, it is the normal behavior. But imagine the following situation:

The end-user tries to move the system databases to another disk. He reads the information on your site (http://support.microsoft.com/kb/224071/) and starts the server with /T3608. He executes the procedure step by step, moves msdb and model, forgets to read the remaining part and happy to move tempdb the same way.

This is the point, when you are getting involved. How to proceed?

|||Well, that script won'y work with SQL Server 2005. You can not allowed to make direct updates to system tables. You can't drop or detach a system database, so the only way of having an issue with tempdb is that it either becomes damaged while SQL Server is running in which case the SQL Server will go offline and fixing the issue is a matter of restarting SQL Server or you have an issue while starting up SQL Server in which case the SQL Server won't start and you would then need to go through the process documented in BOL for recreating tempdb.|||I have asked that the article be updated to remove SQL 2005 from the section that does the moves via detach. ALTER DATABASE should be used to change the file paths in these cases and there are detailed instructions in books online.|||

Thank you for your comment.

You _can_ detach the tempdb, if you start SQL Server 2005 with the /T3608 parameter [I have tried before posting the question]. From the point tempdb detached it cannot be re-attached, nor can be re-created using the way you mention, as the row with hardcoded database id "2" does not exists in the sysdatabases table.

|||

Thank you.

For closing the issue can we say, that the only way to recover from this situation is attaching a clean master db and re-attach the databases and re-create all global settings (logins, endpoints, etc.) stored in master?

|||

If you have a backup of master, you could restore that after putting in the clean master. Master is usually small enough that it should not be too big a burden to do full backups on a regular basis.

And if you had truly followed the article, the first step is

?

Make a current backup of all databases, especially master, from their current location.

which should allow you to recover.

how to recover deleted reports

I accidentally deleted few reports from reporting service management site.
how can i recover them? Thank you.Unless you have a backup you can't (of reporting services database). You
would need to redeploy these reports.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"showjohnathan" <showjohnathan@.discussions.microsoft.com> wrote in message
news:07A380C6-5AF2-430E-A04D-F4A13B5249C5@.microsoft.com...
>I accidentally deleted few reports from reporting service management site.
> how can i recover them? Thank you.|||thanks
"Bruce L-C [MVP]" wrote:
> Unless you have a backup you can't (of reporting services database). You
> would need to redeploy these reports.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "showjohnathan" <showjohnathan@.discussions.microsoft.com> wrote in message
> news:07A380C6-5AF2-430E-A04D-F4A13B5249C5@.microsoft.com...
> >I accidentally deleted few reports from reporting service management site.
> > how can i recover them? Thank you.
>
>

How to recover deleted data from master database

Hi all,
I made a horrible thing this week. I have an script that delete all data from all user tables, and I run it in the master database.
After this I couldn't access the metadata from the tables of my databases using a JDBC connection.
My script runs over all sysobject that are different from dtproperties.
I don't have a back up of master table. I have tried to copy the data from another master database (from another machine) but I did not work. I tried to copy from a xls, but it did'nt work too. I try do recover the master database (as it is explained in the online books) but it did not work either.

Someone know how can I recover this data?Here is the script:

declare @.table_name sysname
declare @.alter_table_statement varchar(256)
declare @.delete_statement varchar(256)

declare table_name_cursor cursor local fast_forward for
select
name
from
sysobjects
where
xtype = 'U'
and
name <> 'dtproperties'

open table_name_cursor
fetch next from table_name_cursor into @.table_name
select @.alter_table_statement = 'alter table ' + ltrim(rtrim(@.table_name)) + ' nocheck constraint all'
exec(@.alter_table_statement)
while @.@.Fetch_Status = 0
begin
fetch next from table_name_cursor into @.table_name
select @.alter_table_statement = 'alter table ' + ltrim(rtrim(@.table_name)) + ' nocheck constraint all'
exec(@.alter_table_statement)
end
close table_name_cursor

open table_name_cursor
fetch next from table_name_cursor into @.table_name
select @.delete_statement = 'delete from ' + ltrim(rtrim(@.table_name))
exec(@.delete_statement)
while @.@.Fetch_Status = 0
begin
fetch next from table_name_cursor into @.table_name
select @.delete_statement = 'delete from ' + ltrim(rtrim(@.table_name))
exec(@.delete_statement)
end
close table_name_cursor

-- ligando os vnculos...
open table_name_cursor
fetch next from table_name_cursor into @.table_name
select @.alter_table_statement = 'alter table ' + ltrim(rtrim(@.table_name)) + ' check constraint all'
exec(@.alter_table_statement)
while @.@.Fetch_Status = 0
begin
fetch next from table_name_cursor into @.table_name
select @.alter_table_statement = 'alter table ' + ltrim(rtrim(@.table_name)) + ' check constraint all'
exec(@.alter_table_statement)
end
close table_name_cursor

deallocate table_name_cursor

And here is the names of the tables of master database that this script had deleted all the containing data:
spt_monitor
spt_values
spt_fallback_db
spt_fallback_dev
spt_fallback_usg
spt_provider_types
spt_datatype_info_ext
MSreplication_options
spt_datatype_info
spt_server_info
spt_server_info