recovery db from files *.mdf and *.ldf

Hi
My server machine had problems, and i had that reinstall,=20
and I only can recover the files *.mdf, and *.ldf.
I tried to recover the one db with the command:
EXEC sp_attach_db @.dbname =3D'viaticos',=20
@.filename1 =3D 'F:\Microsoft SQL=20
Server\MSSQL\Data\Viaticos_Data.mdf',=20
@.filename2 =3D 'F:\Microsoft SQL=20
Server\MSSQL\Data\Viaticos_log.ldf'
and=20
EXEC sp_attach_single_file_db 'viaticos', @.physname=20
=3D 'F:\Microsoft SQL
Server\MSSQL\Data\Viaticos_Data.MDF'
But, the last commnad can create the db, and generated the=20
follow error;=20
Error de activaci=F3n de dispositivo. El nombre de archivo=20
f=EDsico 'f:\Microsoft
SQL Server\MSSQL\data\Viaticos_Log.LDF' puede ser=20
incorrecto.
Se cre=F3 un nuevo archivo de registro llamado 'F:\Microsoft=20
SQL
Server\MSSQL\Data\viaticos_log.LDF'.
With this option I can see the store procedure and views,=20
but i don't see the data and definitions of tables, and
generated this error:
SQL-DMO(ODBC SQLState:08S01
Error 0: this server has been disconected. You must=20
reconnected to perform this operation
Error 823.
and=20
Error 22528[SQL-DMO] This server connect has been=20
unexpected broken and cannot be reconected.
How I do for recover the data and structure of tables?
than's.
Do you not have proper backups?
You could *try* sp_attach_single_file_db against just the MDF file, but if
SQL Server crashed and it wasn't detached correctly, I'm not very
optimistic.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Ivan Alexander Laverde G" <anonymous@.discussions.microsoft.com> wrote in
message news:fe6101c43ea3$ee330950$a501280a@.phx.gbl...
Hi
My server machine had problems, and i had that reinstall,
and I only can recover the files *.mdf, and *.ldf.
I tried to recover the one db with the command:
EXEC sp_attach_db @.dbname ='viaticos',
@.filename1 = 'F:\Microsoft SQL
Server\MSSQL\Data\Viaticos_Data.mdf',
@.filename2 = 'F:\Microsoft SQL
Server\MSSQL\Data\Viaticos_log.ldf'
and
EXEC sp_attach_single_file_db 'viaticos', @.physname
= 'F:\Microsoft SQL
Server\MSSQL\Data\Viaticos_Data.MDF'
But, the last commnad can create the db, and generated the
follow error;
Error de activacin de dispositivo. El nombre de archivo
fsico 'f:\Microsoft
SQL Server\MSSQL\data\Viaticos_Log.LDF' puede ser
incorrecto.
Se cre un nuevo archivo de registro llamado 'F:\Microsoft
SQL
Server\MSSQL\Data\viaticos_log.LDF'.
With this option I can see the store procedure and views,
but i don't see the data and definitions of tables, and
generated this error:
SQL-DMO(ODBC SQLState:08S01
Error 0: this server has been disconected. You must
reconnected to perform this operation
Error 823.
and
Error 22528[SQL-DMO] This server connect has been
unexpected broken and cannot be reconected.
How I do for recover the data and structure of tables?
than's.
|||Hi ,
Try the below steps, it might work if you are lucky.
1. Create a new database with the same name
2. Mention the file names (MDF and LDF ) same as old but in different
3. After you create the new database, stop SQL Server
4. Copy the old database files (MDF and LDF) over the top of the new ones.
5 SQL Server will recognize the database.
6. Database will started in suspect status
7. Update the database status to emergency mode (use below script in Query
Analyzer)
Sp_configure "allow updates", 1
go
Reconfigure with override
GO
Update sysdatabases set status = 32768 where name = "BadDbName"
go
Sp_configure "allow updates", 0
go
Reconfigure with override
GO
8. Now the database will be open for read only (with out using Transaction
log)
9. Create a new database and use DTS to move the data and objects from old
database to new database
Note:
After step-7 try DBCC Checkdb with repair options (See books online) and see
if it can be repaired.
Thanks
Hari
MCDBA
"Ivan Alexander Laverde G" <anonymous@.discussions.microsoft.com> wrote in
message news:fe6101c43ea3$ee330950$a501280a@.phx.gbl...
Hi
My server machine had problems, and i had that reinstall,
and I only can recover the files *.mdf, and *.ldf.
I tried to recover the one db with the command:
EXEC sp_attach_db @.dbname ='viaticos',
@.filename1 = 'F:\Microsoft SQL
Server\MSSQL\Data\Viaticos_Data.mdf',
@.filename2 = 'F:\Microsoft SQL
Server\MSSQL\Data\Viaticos_log.ldf'
and
EXEC sp_attach_single_file_db 'viaticos', @.physname
= 'F:\Microsoft SQL
Server\MSSQL\Data\Viaticos_Data.MDF'
But, the last commnad can create the db, and generated the
follow error;
Error de activacin de dispositivo. El nombre de archivo
fsico 'f:\Microsoft
SQL Server\MSSQL\data\Viaticos_Log.LDF' puede ser
incorrecto.
Se cre un nuevo archivo de registro llamado 'F:\Microsoft
SQL
Server\MSSQL\Data\viaticos_log.LDF'.
With this option I can see the store procedure and views,
but i don't see the data and definitions of tables, and
generated this error:
SQL-DMO(ODBC SQLState:08S01
Error 0: this server has been disconected. You must
reconnected to perform this operation
Error 823.
and
Error 22528[SQL-DMO] This server connect has been
unexpected broken and cannot be reconected.
How I do for recover the data and structure of tables?
than's.

recovery db from files *.mdf and *.ldf

Hi
My server machine had problems, and i had that reinstall,=20
and I only can recover the files *.mdf, and *.ldf.
I tried to recover the one db with the command:
EXEC sp_attach_db @.dbname =3D'viaticos',=20
@.filename1 =3D 'F:\Microsoft SQL=20
Server\MSSQL\Data\Viaticos_Data.mdf',=20
@.filename2 =3D 'F:\Microsoft SQL=20
Server\MSSQL\Data\Viaticos_log.ldf'
and=20
EXEC sp_attach_single_file_db 'viaticos', @.physname=20
=3D 'F:\Microsoft SQL
Server\MSSQL\Data\Viaticos_Data.MDF'
But, the last commnad can create the db, and generated the=20
follow error;=20
Error de activaci=F3n de dispositivo. El nombre de archivo=20
f=EDsico 'f:\Microsoft
SQL Server\MSSQL\data\Viaticos_Log.LDF' puede ser=20
incorrecto.
Se cre=F3 un nuevo archivo de registro llamado 'F:\Microsoft=20
SQL
Server\MSSQL\Data\viaticos_log.LDF'.
With this option I can see the store procedure and views,=20
but i don't see the data and definitions of tables, and
generated this error:
SQL-DMO(ODBC SQLState:08S01
Error 0: this server has been disconected. You must=20
reconnected to perform this operation
Error 823.
and=20
Error 22528[SQL-DMO] This server connect has been=20
unexpected broken and cannot be reconected.
How I do for recover the data and structure of tables?
than's.Do you not have proper backups?
You could *try* sp_attach_single_file_db against just the MDF file, but if
SQL Server crashed and it wasn't detached correctly, I'm not very
optimistic.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Ivan Alexander Laverde G" <anonymous@.discussions.microsoft.com> wrote in
message news:fe6101c43ea3$ee330950$a501280a@.phx.gbl...
Hi
My server machine had problems, and i had that reinstall,
and I only can recover the files *.mdf, and *.ldf.
I tried to recover the one db with the command:
EXEC sp_attach_db @.dbname ='viaticos',
@.filename1 = 'F:\Microsoft SQL
Server\MSSQL\Data\Viaticos_Data.mdf',
@.filename2 = 'F:\Microsoft SQL
Server\MSSQL\Data\Viaticos_log.ldf'
and
EXEC sp_attach_single_file_db 'viaticos', @.physname
= 'F:\Microsoft SQL
Server\MSSQL\Data\Viaticos_Data.MDF'
But, the last commnad can create the db, and generated the
follow error;
Error de activacin de dispositivo. El nombre de archivo
fsico 'f:\Microsoft
SQL Server\MSSQL\data\Viaticos_Log.LDF' puede ser
incorrecto.
Se cre un nuevo archivo de registro llamado 'F:\Microsoft
SQL
Server\MSSQL\Data\viaticos_log.LDF'.
With this option I can see the store procedure and views,
but i don't see the data and definitions of tables, and
generated this error:
SQL-DMO(ODBC SQLState:08S01
Error 0: this server has been disconected. You must
reconnected to perform this operation
Error 823.
and
Error 22528[SQL-DMO] This server connect has been
unexpected broken and cannot be reconected.
How I do for recover the data and structure of tables?
than's.|||Hi ,
Try the below steps, it might work if you are lucky.
1. Create a new database with the same name
2. Mention the file names (MDF and LDF ) same as old but in different
3. After you create the new database, stop SQL Server
4. Copy the old database files (MDF and LDF) over the top of the new ones.
5 SQL Server will recognize the database.
6. Database will started in suspect status
7. Update the database status to emergency mode (use below script in Query
Analyzer)
Sp_configure "allow updates", 1
go
Reconfigure with override
GO
Update sysdatabases set status = 32768 where name = "BadDbName"
go
Sp_configure "allow updates", 0
go
Reconfigure with override
GO
8. Now the database will be open for read only (with out using Transaction
log)
9. Create a new database and use DTS to move the data and objects from old
database to new database
Note:
After step-7 try DBCC Checkdb with repair options (See books online) and see
if it can be repaired.
Thanks
Hari
MCDBA
"Ivan Alexander Laverde G" <anonymous@.discussions.microsoft.com> wrote in
message news:fe6101c43ea3$ee330950$a501280a@.phx.gbl...
Hi
My server machine had problems, and i had that reinstall,
and I only can recover the files *.mdf, and *.ldf.
I tried to recover the one db with the command:
EXEC sp_attach_db @.dbname ='viaticos',
@.filename1 = 'F:\Microsoft SQL
Server\MSSQL\Data\Viaticos_Data.mdf',
@.filename2 = 'F:\Microsoft SQL
Server\MSSQL\Data\Viaticos_log.ldf'
and
EXEC sp_attach_single_file_db 'viaticos', @.physname
= 'F:\Microsoft SQL
Server\MSSQL\Data\Viaticos_Data.MDF'
But, the last commnad can create the db, and generated the
follow error;
Error de activacin de dispositivo. El nombre de archivo
fsico 'f:\Microsoft
SQL Server\MSSQL\data\Viaticos_Log.LDF' puede ser
incorrecto.
Se cre un nuevo archivo de registro llamado 'F:\Microsoft
SQL
Server\MSSQL\Data\viaticos_log.LDF'.
With this option I can see the store procedure and views,
but i don't see the data and definitions of tables, and
generated this error:
SQL-DMO(ODBC SQLState:08S01
Error 0: this server has been disconected. You must
reconnected to perform this operation
Error 823.
and
Error 22528[SQL-DMO] This server connect has been
unexpected broken and cannot be reconected.
How I do for recover the data and structure of tables?
than's.

recovery db from files *.mdf and *.ldf

Hi
My server machine had problems, and i had that reinstall, and I only can recover the files *.mdf, and *.ldf.
I tried to recover the one db with the command:
EXEC sp_attach_db @.dbname =3D'viaticos', @.filename1 =3D 'F:\Microsoft SQL Server\MSSQL\Data\Viaticos_Data.mdf', @.filename2 =3D 'F:\Microsoft SQL Server\MSSQL\Data\Viaticos_log.ldf'
and
EXEC sp_attach_single_file_db 'viaticos', @.physname =3D 'F:\Microsoft SQL
Server\MSSQL\Data\Viaticos_Data.MDF'
But, the last commnad can create the db, and generated the follow error;
Error de activaci=F3n de dispositivo. El nombre de archivo f=EDsico 'f:\Microsoft
SQL Server\MSSQL\data\Viaticos_Log.LDF' puede ser incorrecto.
Se cre=F3 un nuevo archivo de registro llamado 'F:\Microsoft SQL
Server\MSSQL\Data\viaticos_log.LDF'.
With this option I can see the store procedure and views, but i don't see the data and definitions of tables, and
generated this error:
SQL-DMO(ODBC SQLState:08S01
Error 0: this server has been disconected. You must reconnected to perform this operation
Error 823.
and
Error 22528[SQL-DMO] This server connect has been unexpected broken and cannot be reconected.
How I do for recover the data and structure of tables?
than's.Do you not have proper backups?
You could *try* sp_attach_single_file_db against just the MDF file, but if
SQL Server crashed and it wasn't detached correctly, I'm not very
optimistic.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Ivan Alexander Laverde G" <anonymous@.discussions.microsoft.com> wrote in
message news:fe6101c43ea3$ee330950$a501280a@.phx.gbl...
Hi
My server machine had problems, and i had that reinstall,
and I only can recover the files *.mdf, and *.ldf.
I tried to recover the one db with the command:
EXEC sp_attach_db @.dbname ='viaticos',
@.filename1 = 'F:\Microsoft SQL
Server\MSSQL\Data\Viaticos_Data.mdf',
@.filename2 = 'F:\Microsoft SQL
Server\MSSQL\Data\Viaticos_log.ldf'
and
EXEC sp_attach_single_file_db 'viaticos', @.physname
= 'F:\Microsoft SQL
Server\MSSQL\Data\Viaticos_Data.MDF'
But, the last commnad can create the db, and generated the
follow error;
Error de activación de dispositivo. El nombre de archivo
físico 'f:\Microsoft
SQL Server\MSSQL\data\Viaticos_Log.LDF' puede ser
incorrecto.
Se creó un nuevo archivo de registro llamado 'F:\Microsoft
SQL
Server\MSSQL\Data\viaticos_log.LDF'.
With this option I can see the store procedure and views,
but i don't see the data and definitions of tables, and
generated this error:
SQL-DMO(ODBC SQLState:08S01
Error 0: this server has been disconected. You must
reconnected to perform this operation
Error 823.
and
Error 22528[SQL-DMO] This server connect has been
unexpected broken and cannot be reconected.
How I do for recover the data and structure of tables?
than's.|||Hi ,
Try the below steps, it might work if you are lucky.
1. Create a new database with the same name
2. Mention the file names (MDF and LDF ) same as old but in different
3. After you create the new database, stop SQL Server
4. Copy the old database files (MDF and LDF) over the top of the new ones.
5 SQL Server will recognize the database.
6. Database will started in suspect status
7. Update the database status to emergency mode (use below script in Query
Analyzer)
Sp_configure "allow updates", 1
go
Reconfigure with override
GO
Update sysdatabases set status = 32768 where name = "BadDbName"
go
Sp_configure "allow updates", 0
go
Reconfigure with override
GO
8. Now the database will be open for read only (with out using Transaction
log)
9. Create a new database and use DTS to move the data and objects from old
database to new database
Note:
After step-7 try DBCC Checkdb with repair options (See books online) and see
if it can be repaired.
Thanks
Hari
MCDBA
"Ivan Alexander Laverde G" <anonymous@.discussions.microsoft.com> wrote in
message news:fe6101c43ea3$ee330950$a501280a@.phx.gbl...
Hi
My server machine had problems, and i had that reinstall,
and I only can recover the files *.mdf, and *.ldf.
I tried to recover the one db with the command:
EXEC sp_attach_db @.dbname ='viaticos',
@.filename1 = 'F:\Microsoft SQL
Server\MSSQL\Data\Viaticos_Data.mdf',
@.filename2 = 'F:\Microsoft SQL
Server\MSSQL\Data\Viaticos_log.ldf'
and
EXEC sp_attach_single_file_db 'viaticos', @.physname
= 'F:\Microsoft SQL
Server\MSSQL\Data\Viaticos_Data.MDF'
But, the last commnad can create the db, and generated the
follow error;
Error de activación de dispositivo. El nombre de archivo
físico 'f:\Microsoft
SQL Server\MSSQL\data\Viaticos_Log.LDF' puede ser
incorrecto.
Se creó un nuevo archivo de registro llamado 'F:\Microsoft
SQL
Server\MSSQL\Data\viaticos_log.LDF'.
With this option I can see the store procedure and views,
but i don't see the data and definitions of tables, and
generated this error:
SQL-DMO(ODBC SQLState:08S01
Error 0: this server has been disconected. You must
reconnected to perform this operation
Error 823.
and
Error 22528[SQL-DMO] This server connect has been
unexpected broken and cannot be reconected.
How I do for recover the data and structure of tables?
than's.sql

Recovery data from file mdf e log

from PC is uninstalled SQLSERVER2000 and related files mdf and log without to
execute backup database. With a tool of recovery file deleted *.mdf and
*.log, we have recoverd files. How to do you to recovery the database from
mdf e log file?
If you are lucky, you can attach them using sp_attach_db. This might not work, though as you didn't
detach the database first. If it doesn't work, open a case with MS Support and see if they can
assist.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"marisa" <marisa@.discussions.microsoft.com> wrote in message
news:E8CFE7A0-1C04-4324-A258-3EAB1BC2ADD5@.microsoft.com...
> from PC is uninstalled SQLSERVER2000 and related files mdf and log without to
> execute backup database. With a tool of recovery file deleted *.mdf and
> *.log, we have recoverd files. How to do you to recovery the database from
> mdf e log file?

Recovery data from file mdf e log

from PC is uninstalled SQLSERVER2000 and related files mdf and log without t
o
execute backup database. With a tool of recovery file deleted *.mdf and
*.log, we have recoverd files. How to do you to recovery the database from
mdf e log file?If you are lucky, you can attach them using sp_attach_db. This might not wor
k, though as you didn't
detach the database first. If it doesn't work, open a case with MS Support a
nd see if they can
assist.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"marisa" <marisa@.discussions.microsoft.com> wrote in message
news:E8CFE7A0-1C04-4324-A258-3EAB1BC2ADD5@.microsoft.com...
> from PC is uninstalled SQLSERVER2000 and related files mdf and log without
to
> execute backup database. With a tool of recovery file deleted *.mdf and
> *.log, we have recoverd files. How to do you to recovery the database from
> mdf e log file?

Recovery data from file mdf e log

from PC is uninstalled SQLSERVER2000 and related files mdf and log without to
execute backup database. With a tool of recovery file deleted *.mdf and
*.log, we have recoverd files. How to do you to recovery the database from
mdf e log file?If you are lucky, you can attach them using sp_attach_db. This might not work, though as you didn't
detach the database first. If it doesn't work, open a case with MS Support and see if they can
assist.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"marisa" <marisa@.discussions.microsoft.com> wrote in message
news:E8CFE7A0-1C04-4324-A258-3EAB1BC2ADD5@.microsoft.com...
> from PC is uninstalled SQLSERVER2000 and related files mdf and log without to
> execute backup database. With a tool of recovery file deleted *.mdf and
> *.log, we have recoverd files. How to do you to recovery the database from
> mdf e log file?

recovery a SQL Server db from mdf/Ldf files

hi to all,

I'm newbie with Sql Server,

I've a serious problem. After a crash of one of my disk I loose all the data about the SQL Server db but I've the mdf and ldf files because were placed on another hard disk different from the SQL Serve Installation disk. I reinstalled the Sql Server (in a new path, a new hard disk letter) but I don't know how to rebuild the db starting from the mdf/ldf files. Is it possible?

Please help me, I'm in a ugly Situation.

If you can access the mdf/ldf files, simply doing an attach will do the trick ... sometimes|||

can you teach me how to attach the files? please help me, I need a solution for this problem

thanks a lot

Claudio

|||

What version are you using...SQL Server 2000 or 2005?

|||...I'm using SQL Server 2000 developer + sp3|||Open Books Online. Do a search for Attach. It will have instructions and sample code.

Recovering using the log file

I have a situation where the server and data files were located on the C:
drive, and the log files on the F: drive.
The C: drive crashed, total loss. But I still have full backups for the
databases and also their log files.
Is there a way of recovering my DBs to the point of failure using the
backups and log files?
Everything I see in the BOL refer to use a log backup. I don't have log
backups, just the log files themselves.
ThanksIf the database is in full recovery mode, and no-one has truncated the log,
you can do:
BACKUP LOG dbname TO ... WITH NO_TRUNCATE
If the database is in simple recovery mode, then SQL Server will truncate
the log at each checkpoint. This means that the information you need is no
longer available in the transaction log files.
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Dallara" <someone@.microsoft.com> wrote in message
news:OPpd5co%23DHA.3232@.TK2MSFTNGP10.phx.gbl...
> I have a situation where the server and data files were located on the C:
> drive, and the log files on the F: drive.
> The C: drive crashed, total loss. But I still have full backups for the
> databases and also their log files.
> Is there a way of recovering my DBs to the point of failure using the
> backups and log files?
> Everything I see in the BOL refer to use a log backup. I don't have log
> backups, just the log files themselves.
> Thanks
>|||They were in full recovery.
Thanks for yr help, but the point you missed here is that the DBs are gone,
the server is gone as well.
So "BACKUP LOG dbname TO ... WITH NO_TRUNCATE" doesn't work as "dbname"
doesn't exist.
I've set up a new server, and I want to recreate the databases using the
full backups and log files that I have.
I know how to recover the DBs to the point of their full backup. I'm
wondering if there's a way of recover them to the point of failure using
their log files.
>"Tibor Karaszi" wrote in message
>If the database is in full recovery mode, and no-one has truncated the log,
>you can do:
>BACKUP LOG dbname TO ... WITH NO_TRUNCATE
>If the database is in simple recovery mode, then SQL Server will truncate
>the log at each checkpoint. This means that the information you need is no
>longer available in the transaction log files.
> Tibor Karaszi, SQL Server MVP
>
C:|||OK, so the SQL Server install in itself is toast...
The concept to save this is to have your new installation with a working SQL
Server, create a database with the same name and same file layout as the old
one. Stop that SQL Server, delete the mdf, ndf and ldf files. Then copy over
the ldf file from the broken install. Start SQL Server. Database is now
suspect (the mdf and ndf files are missing). You now do
BACKUP LOG dbname TO DISK = 'C:\...' WITH NO_TRUNCATE
Then restore the db backup and that log backups.
Above assumes that you indeed have all log records available in the ldf
file, since the db backup was taken.
There's a KB on the subject, I don't have the number, but it shouldn't be
hard to find with a little bit of searching...
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Dallara" <someone@.microsoft.com> wrote in message
news:OcVlVay%23DHA.1212@.TK2MSFTNGP12.phx.gbl...
> They were in full recovery.
> Thanks for yr help, but the point you missed here is that the DBs are
gone,
> the server is gone as well.
> So "BACKUP LOG dbname TO ... WITH NO_TRUNCATE" doesn't work as "dbname"
> doesn't exist.
> I've set up a new server, and I want to recreate the databases using the
> full backups and log files that I have.
> I know how to recover the DBs to the point of their full backup. I'm
> wondering if there's a way of recover them to the point of failure using
> their log files.
>
>
log,
no
> C:
the
log
>

Recovering using the log file

I have a situation where the server and data files were located on the C:
drive, and the log files on the F: drive.
The C: drive crashed, total loss. But I still have full backups for the
databases and also their log files.
Is there a way of recovering my DBs to the point of failure using the
backups and log files?
Everything I see in the BOL refer to use a log backup. I don't have log
backups, just the log files themselves.
ThanksIf the database is in full recovery mode, and no-one has truncated the log,
you can do:
BACKUP LOG dbname TO ... WITH NO_TRUNCATE
If the database is in simple recovery mode, then SQL Server will truncate
the log at each checkpoint. This means that the information you need is no
longer available in the transaction log files.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Dallara" <someone@.microsoft.com> wrote in message
news:OPpd5co%23DHA.3232@.TK2MSFTNGP10.phx.gbl...
> I have a situation where the server and data files were located on the C:
> drive, and the log files on the F: drive.
> The C: drive crashed, total loss. But I still have full backups for the
> databases and also their log files.
> Is there a way of recovering my DBs to the point of failure using the
> backups and log files?
> Everything I see in the BOL refer to use a log backup. I don't have log
> backups, just the log files themselves.
> Thanks
>|||They were in full recovery.
Thanks for yr help, but the point you missed here is that the DBs are gone,
the server is gone as well.
So "BACKUP LOG dbname TO ... WITH NO_TRUNCATE" doesn't work as "dbname"
doesn't exist.
I've set up a new server, and I want to recreate the databases using the
full backups and log files that I have.
I know how to recover the DBs to the point of their full backup. I'm
wondering if there's a way of recover them to the point of failure using
their log files.
>"Tibor Karaszi" wrote in message
>If the database is in full recovery mode, and no-one has truncated the log,
>you can do:
>BACKUP LOG dbname TO ... WITH NO_TRUNCATE
>If the database is in simple recovery mode, then SQL Server will truncate
>the log at each checkpoint. This means that the information you need is no
>longer available in the transaction log files.
> Tibor Karaszi, SQL Server MVP
>
>> "Dallara" wrote in message
> > I have a situation where the server and data files were located on the
C:
> > drive, and the log files on the F: drive.
> > The C: drive crashed, total loss. But I still have full backups for the
> > databases and also their log files.
> >
> > Is there a way of recovering my DBs to the point of failure using the
> > backups and log files?
> > Everything I see in the BOL refer to use a log backup. I don't have log
> > backups, just the log files themselves.
> >
> > Thanks|||OK, so the SQL Server install in itself is toast...
The concept to save this is to have your new installation with a working SQL
Server, create a database with the same name and same file layout as the old
one. Stop that SQL Server, delete the mdf, ndf and ldf files. Then copy over
the ldf file from the broken install. Start SQL Server. Database is now
suspect (the mdf and ndf files are missing). You now do
BACKUP LOG dbname TO DISK = 'C:\...' WITH NO_TRUNCATE
Then restore the db backup and that log backups.
Above assumes that you indeed have all log records available in the ldf
file, since the db backup was taken.
There's a KB on the subject, I don't have the number, but it shouldn't be
hard to find with a little bit of searching...
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Dallara" <someone@.microsoft.com> wrote in message
news:OcVlVay%23DHA.1212@.TK2MSFTNGP12.phx.gbl...
> They were in full recovery.
> Thanks for yr help, but the point you missed here is that the DBs are
gone,
> the server is gone as well.
> So "BACKUP LOG dbname TO ... WITH NO_TRUNCATE" doesn't work as "dbname"
> doesn't exist.
> I've set up a new server, and I want to recreate the databases using the
> full backups and log files that I have.
> I know how to recover the DBs to the point of their full backup. I'm
> wondering if there's a way of recover them to the point of failure using
> their log files.
>
> >"Tibor Karaszi" wrote in message
> >
> >If the database is in full recovery mode, and no-one has truncated the
log,
> >you can do:
> >BACKUP LOG dbname TO ... WITH NO_TRUNCATE
> >
> >If the database is in simple recovery mode, then SQL Server will truncate
> >the log at each checkpoint. This means that the information you need is
no
> >longer available in the transaction log files.
> >
> > Tibor Karaszi, SQL Server MVP
> >
> >
> >> "Dallara" wrote in message
> >>
> > > I have a situation where the server and data files were located on the
> C:
> > > drive, and the log files on the F: drive.
> > > The C: drive crashed, total loss. But I still have full backups for
the
> > > databases and also their log files.
> > >
> > > Is there a way of recovering my DBs to the point of failure using the
> > > backups and log files?
> > > Everything I see in the BOL refer to use a log backup. I don't have
log
> > > backups, just the log files themselves.
> > >
> > > Thanks
>

Recovering Reports

I had an unfortunate situation where a downed server resulted in a significant amount of lost work (the rdl files of several reports I had been working on).

I had previously uploaded them to the reporting services server and had hoped that I could recover them from there - but I can't find them anywhere.

Is there a mechnaism for recovering rdl files (or at least the sql behind them?) from the reporting services server?It's very easy. Open the SQL Managment Studio. Connect to Reporting Services. Then right click on each report do you want to export and select the option script, script to file.
That's all.
I hope it helps you.
VectorR3|||Excellent - thank you...sql

Recovering Reports

I had an unfortunate situation where a downed server resulted in a
significant amount of lost work (the rdl files of several reports I had been
working on).
I had previously uploaded them to the reporting services server and had
hoped that I could recover them from there - but I can't find them anywhere.
Is there a mechnaism for recovering rdl files (or at least the sql behind
them?) from the reporting services server?I beleive it is stored in an image field named Content in the Catalog table
of your reporting server database
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Kwebster" <Kwebster@.discussions.microsoft.com> wrote in message
news:4DBD3671-64E8-4159-8887-D26B59C18D89@.microsoft.com...
> I had an unfortunate situation where a downed server resulted in a
> significant amount of lost work (the rdl files of several reports I had
been
> working on).
> I had previously uploaded them to the reporting services server and had
> hoped that I could recover them from there - but I can't find them
anywhere.
> Is there a mechnaism for recovering rdl files (or at least the sql behind
> them?) from the reporting services server?|||Are the reports still showing up when you go to the report manager site? If
so, you can go to the properties -> general page of each report and click on
the edit link under report definition and save as.
"Kwebster" wrote:
> I had an unfortunate situation where a downed server resulted in a
> significant amount of lost work (the rdl files of several reports I had been
> working on).
> I had previously uploaded them to the reporting services server and had
> hoped that I could recover them from there - but I can't find them anywhere.
> Is there a mechnaism for recovering rdl files (or at least the sql behind
> them?) from the reporting services server?|||Hello Daivd. A thousand thank you's - worked perfectly.
"David Siebert" wrote:
> Are the reports still showing up when you go to the report manager site? If
> so, you can go to the properties -> general page of each report and click on
> the edit link under report definition and save as.
> "Kwebster" wrote:
> > I had an unfortunate situation where a downed server resulted in a
> > significant amount of lost work (the rdl files of several reports I had been
> > working on).
> >
> > I had previously uploaded them to the reporting services server and had
> > hoped that I could recover them from there - but I can't find them anywhere.
> >
> > Is there a mechnaism for recovering rdl files (or at least the sql behind
> > them?) from the reporting services server?|||Hrllo Wayne
Thank you - learn something new everyday!
"Wayne Snyder" wrote:
> I beleive it is stored in an image field named Content in the Catalog table
> of your reporting server database
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "Kwebster" <Kwebster@.discussions.microsoft.com> wrote in message
> news:4DBD3671-64E8-4159-8887-D26B59C18D89@.microsoft.com...
> > I had an unfortunate situation where a downed server resulted in a
> > significant amount of lost work (the rdl files of several reports I had
> been
> > working on).
> >
> > I had previously uploaded them to the reporting services server and had
> > hoped that I could recover them from there - but I can't find them
> anywhere.
> >
> > Is there a mechnaism for recovering rdl files (or at least the sql behind
> > them?) from the reporting services server?
>
>

recovering multi-log db without logs

We have a problem.
A database was set to use 2 log files, and one of them somehow grew to a bloated 32GB, with a 400MB database! The recovery model was set to full, which will not be used again.
Now, whenever we start SQL Server a system process places an exclusive lock on the database is question and never lets go of it. It doesn't seem to be doing anything but it never lets go, so nothing can be done with the database.

We suspect the massive log file has something to do with the problem.
We want to somehow remove the log files, but can't do a damn thing because of the lock.

We have tried renaming the log files, then detaching the database and reattaching, but sp_attach_db and sp_attach_single_file_db won't recreate log files on databases that use more than one.

We have tried creating a "dummy", empty database with the same name and log files, then moving in the original MDF, but it error out saying we can't use log files from another database.

Does anyone have any suggestions?
No, we don't have backups of any of the log files, only the mdf. This is going to be corrected, but we need to get this thing going!sp_attach_single_file_db should cope with it if you have managed the dettach succesfully.
It should recreate a single log file for you.

Having a backup of the mdf sounds like you are just backing up the file. This might work if you close down sql server or dettach the database first but better to just backup the database.

What is the status of the database when it has the exclusive lock on it@. recovering?|||Thanks for the reply. the attach_single_file_db doesn't work either with a multi-logfile database, if you don't have the log files available. We did correct the problem with a call to Microsoft.

Thanks for the suggestion.|||What did MicroSoft say?|||Originally posted by nigelrivett
What did MicroSoft say?

I guess that under the circumstances they suggested using
sp_attach_single_file_db. But use if anyone is planning on using this command have a look at BOL first.

BTW using full recovery mode is ok as long as you create a job that backs up the transaction log on a regular basis. One can easily set this up in enterprise manager -> sql server agent -> right click on datbase maintence plans -> new etc etc you can even tell it to write over trans backups older than a set period of time say 24 hours.

Making a really good maintaince plain withing sql server is not brain surgery it just takes a little effort.

Recovering from hard disk crash

SQL 7.0/NT 4.0
The C drive has crashed. The MDF and LDF files are on
Drive D.
Is there any path to recovery?
Thanks,
Don
Hi, Don! As long as the db files are not corrupt, you can try using the sp_attach_db after you reinstall. See BOL for details. HTH. =)
|||Prerequisite:
Copy all the MDF and LDF files (User databases and System databases) to a safe directory,
Installation:
Since the C drive is crashed, you have to Install the Windows NT 4.0 with the same service pack.
After that install SQL 7.0 with same directory structure and the same service pack as before.
Now follow the below steps:-
1. Stop the SQL Server
2. Move the current MDF and LDF files for system databases (Created on the new installation) to a new folder
3. Copy back all the MDF and LDF files to the same old folders ( Files copied in prerequisite step)
4. Start the SQL server and check all the databases are accessible.
The above step will work fine if you have the system files safe.
If the above steps fail then you will have to restore from good backup files.
-- Don wrote: --
SQL 7.0/NT 4.0
The C drive has crashed. The MDF and LDF files are on
Drive D.
Is there any path to recovery?
Thanks,
Don

Recovering from hard disk crash

SQL 7.0/NT 4.0
The C drive has crashed. The MDF and LDF files are on
Drive D.
Is there any path to recovery?
Thanks,
DonHi, Don! As long as the db files are not corrupt, you can try using the sp_a
ttach_db after you reinstall. See BOL for details. HTH. =)|||Prerequisite:
Copy all the MDF and LDF files (User databases and System databases) to a sa
fe directory,
Installation:
Since the C drive is crashed, you have to Install the Windows NT 4.0 with th
e same service pack.
After that install SQL 7.0 with same directory structure and the same servic
e pack as before.
Now follow the below steps:-
1. Stop the SQL Server
2. Move the current MDF and LDF files for system databases (Created on the n
ew installation) to a new folder
3. Copy back all the MDF and LDF files to the same old folders ( Files copie
d in prerequisite step)
4. Start the SQL server and check all the databases are accessible.
The above step will work fine if you have the system files safe.
If the above steps fail then you will have to restore from good backup files
.
-- Don wrote: --
SQL 7.0/NT 4.0
The C drive has crashed. The MDF and LDF files are on
Drive D.
Is there any path to recovery?
Thanks,
Don

Recovering from hard disk crash

SQL 7.0/NT 4.0
The C drive has crashed. The MDF and LDF files are on
Drive D.
Is there any path to recovery?
Thanks,
DonHi, Don! As long as the db files are not corrupt, you can try using the sp_attach_db after you reinstall. See BOL for details. HTH. =)|||Prerequisite
Copy all the MDF and LDF files (User databases and System databases) to a safe directory
Installation
Since the C drive is crashed, you have to Install the Windows NT 4.0 with the same service pack.
After that install SQL 7.0 with same directory structure and the same service pack as before
Now follow the below steps:
1. Stop the SQL Server
2. Move the current MDF and LDF files for system databases (Created on the new installation) to a new folde
3. Copy back all the MDF and LDF files to the same old folders ( Files copied in prerequisite step
4. Start the SQL server and check all the databases are accessible
The above step will work fine if you have the system files safe
If the above steps fail then you will have to restore from good backup files
-- Don wrote: --
SQL 7.0/NT 4.
The C drive has crashed. The MDF and LDF files are on
Drive D
Is there any path to recovery
Thanks
Do

Recovering from a lost log file

I'm trying to make sure that we're following the "best practices" with SQL Server and I have some questions about log files.

One general DBA rule is that data files must be on a different device than log files. This way, you can lose either disk and still recover all committed transactions. If you lose the data disk, you restore from backup and then rollforward all of the transactions that are in the log file. If you lose the log disk, you roll back uncommitted transactions and then create a new log file.

This scenerio doesn't seem to be supported by SQL Server. It looks like SQL Sever can deal with a failed data disk but, it can't handled a failed log disk. Is that correct? The documentation says that the log file holds both before image data and after image data so, if the log disk drops dead, SQL server can't roll back uncommitted transactions so you're left with a corrupt (or and least suspect) database. You can restore from backup but, you can't roll forward since you've lost the log file.

SQL Server seems to be a really good database with the exception of this gigantic flaw. I must be missing something, what is it?

Thanks,

John Vottero

John,

I think your explanation of why you separate log and data is where the problem lies. The transaction log in SQL Server is the first place tranaction data is written, and it's only written to the data files when a checkpoint occurs.

The reason you want to keep the log and data separate is because log file access is mostly writes, and you want to put the log on a mirrored drive, optimized for sequential write access, and put the data files on a RAID drive, because you'll mostly be reading from the data files. This provides the best performance of your database.

|||

Yes, there are lots of good reasons for putting logs and data on different disks but, whatever the configuration, I want to make sure that we don't lose a days worth of transactions if we lose a disk, any disk. Can that be done with SQL Server?

|||

Standard practice is to put your log file on a RAID 10 device - hardware mirroring.

Your guidelines seem to be based on loosing only 1 disk, so having a hardware mirror fits the requirement. In the event a physical disk is lost the mirror picks up and moves on.

If you loose the data drive and part of the mirror you're still fine, just restore the last full backup and the tail of the log.

If you need more assurance than that you can ship the log to another server.

Should you need still more security than log shipping you can use database mirroring in sql 2005 sp1.

In the end your business reasons are going to dictate what kind of fault tolerance you will need, and what kind of down time you can tolerate. That will drive your decision toward "best practices".

|||

John,

On our production servers I have a database maintenance plan which backs up the full database once a day in the early morning, and performs transaction log backups every hour. (One server has significant enough activity that I back up the transaction log every 15 minutes.)

These backups are done to disk files on a separate drive from either the data or the log files. Each night the backup files are then backed up to tape.

Since my data and log files are on our SAN, the backup files are sent to local drives on each server, so at no time will a single point of failure cause the loss of more than an hour's work, or in the case of the one server, more than 15 minutes work.

You might consider a similar backup strategy.

|||

All of our logical disks will be RAID10 so it's unlikely that we will lose anything but, I want to make sure that all the bases are covered. It looks like we'll be doing log shipping or frequent log backups.

Thanks for all the advice!

Recovering DB from deleted files

Hi All,
Using sql 2000 on windows 200 server
I am trying to recover a SQL 2000 DB from crashed.
Now I only have the .LDF file of the DB
is there any way i can use to recover the db from the >LDF file
Thanx,
mateHi,
No, you cant recover a database with just the LDF file. If you have the MDF
file then you can try attaching the database using
SP_ATTACH_DB procedure.
In this scenario, the only way to recover the database is to RESTORE the
database from last successful full datbase backup
and apply the subsequent transaction logs.
Thanks
Hari
MCDBA
"zoro24" <zoro24@.hotmail.com> wrote in message
news:OZiWis2DEHA.240@.tk2msftngp13.phx.gbl...
>
> Hi All,
> Using sql 2000 on windows 200 server
> I am trying to recover a SQL 2000 DB from crashed.
> Now I only have the .LDF file of the DB
> is there any way i can use to recover the db from the >LDF file
>
> Thanx,
> mate|||If you only have the LDF file, you have a problem. Are you certain you don't
have the mdf file as well? Or a backup?
The ldf file only contains the modifications performed in the database, and
if you never did a database backup, the file is emptied automatically
regularly. There exists log readers (see the link page on my web site), but
there's a high risk that the information is no longer there.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"zoro24" <zoro24@.hotmail.com> wrote in message
news:OZiWis2DEHA.240@.tk2msftngp13.phx.gbl...
>
> Hi All,
> Using sql 2000 on windows 200 server
> I am trying to recover a SQL 2000 DB from crashed.
> Now I only have the .LDF file of the DB
> is there any way i can use to recover the db from the >LDF file
>
> Thanx,
> mate

Recover the MSSQL Server 2000 from DLT Tape

Dear all,
I would like to ask if I can recover those databases from tape if I just
backup all the folders under the "C:\Program Files" ? I have export those
databases to files...
As my server's HDD is fail and I only get the DLT Tape back.
Please give me some advises!
Thanks all!if you have valid backup of c:\program files\ microsoft sql server\...\data
folder and your data files are in this folder you can attach the mdf and ldf
files to another servers.
Look for sp_attach_db in BOL
not sure what you meant with "I have export those databases to files..."
"Kevin" <Kevin@.discussions.microsoft.com> wrote in message
news:D8A6D646-6778-4DA1-AA50-7D50383E1B09@.microsoft.com...
> Dear all,
> I would like to ask if I can recover those databases from tape if I just
> backup all the folders under the "C:\Program Files" ? I have export those
> databases to files...
> As my server's HDD is fail and I only get the DLT Tape back.
> Please give me some advises!
> Thanks all!|||Thx for your reply!
actually, it's my typing mistake...
it should be "i haven't exported those database as a file"
"ME" wrote:
> if you have valid backup of c:\program files\ microsoft sql server\...\data
> folder and your data files are in this folder you can attach the mdf and ldf
> files to another servers.
> Look for sp_attach_db in BOL
> not sure what you meant with "I have export those databases to files..."
>
> "Kevin" <Kevin@.discussions.microsoft.com> wrote in message
> news:D8A6D646-6778-4DA1-AA50-7D50383E1B09@.microsoft.com...
> > Dear all,
> >
> > I would like to ask if I can recover those databases from tape if I just
> > backup all the folders under the "C:\Program Files" ? I have export those
> > databases to files...
> >
> > As my server's HDD is fail and I only get the DLT Tape back.
> >
> > Please give me some advises!
> >
> > Thanks all!
>
>|||so you were able to attach the mdf and ldf files?
"Kevin" <Kevin@.discussions.microsoft.com> wrote in message
news:FF8B6AC6-7F80-4D83-B14C-2A21235E56D8@.microsoft.com...
> Thx for your reply!
> actually, it's my typing mistake...
> it should be "i haven't exported those database as a file"
> "ME" wrote:
>> if you have valid backup of c:\program files\ microsoft sql
>> server\...\data
>> folder and your data files are in this folder you can attach the mdf and
>> ldf
>> files to another servers.
>> Look for sp_attach_db in BOL
>> not sure what you meant with "I have export those databases to files..."
>>
>> "Kevin" <Kevin@.discussions.microsoft.com> wrote in message
>> news:D8A6D646-6778-4DA1-AA50-7D50383E1B09@.microsoft.com...
>> > Dear all,
>> >
>> > I would like to ask if I can recover those databases from tape if I
>> > just
>> > backup all the folders under the "C:\Program Files" ? I have export
>> > those
>> > databases to files...
>> >
>> > As my server's HDD is fail and I only get the DLT Tape back.
>> >
>> > Please give me some advises!
>> >
>> > Thanks all!
>>

Recover the MSSQL Server 2000 from DLT Tape

Dear all,
I would like to ask if I can recover those databases from tape if I just
backup all the folders under the "C:\Program Files" ? I have export those
databases to files...
As my server's HDD is fail and I only get the DLT Tape back.
Please give me some advises!
Thanks all!if you have valid backup of c:\program files\ microsoft sql server\...\data
folder and your data files are in this folder you can attach the mdf and ldf
files to another servers.
Look for sp_attach_db in BOL
not sure what you meant with "I have export those databases to files..."
"Kevin" <Kevin@.discussions.microsoft.com> wrote in message
news:D8A6D646-6778-4DA1-AA50-7D50383E1B09@.microsoft.com...
> Dear all,
> I would like to ask if I can recover those databases from tape if I just
> backup all the folders under the "C:\Program Files" ? I have export those
> databases to files...
> As my server's HDD is fail and I only get the DLT Tape back.
> Please give me some advises!
> Thanks all!|||Thx for your reply!
actually, it's my typing mistake...
it should be "i haven't exported those database as a file"
"ME" wrote:

> if you have valid backup of c:\program files\ microsoft sql server\...\da
ta
> folder and your data files are in this folder you can attach the mdf and l
df
> files to another servers.
> Look for sp_attach_db in BOL
> not sure what you meant with "I have export those databases to files..."
>
> "Kevin" <Kevin@.discussions.microsoft.com> wrote in message
> news:D8A6D646-6778-4DA1-AA50-7D50383E1B09@.microsoft.com...
>
>|||so you were able to attach the mdf and ldf files?
"Kevin" <Kevin@.discussions.microsoft.com> wrote in message
news:FF8B6AC6-7F80-4D83-B14C-2A21235E56D8@.microsoft.com...[vbcol=seagreen]
> Thx for your reply!
> actually, it's my typing mistake...
> it should be "i haven't exported those database as a file"
> "ME" wrote:
>

Recover the MSSQL Server 2000 from DLT Tape

Dear all,
I would like to ask if I can recover those databases from tape if I just
backup all the folders under the "C:\Program Files" ? I have export those
databases to files...
As my server's HDD is fail and I only get the DLT Tape back.
Please give me some advises!
Thanks all!
if you have valid backup of c:\program files\ microsoft sql server\...\data
folder and your data files are in this folder you can attach the mdf and ldf
files to another servers.
Look for sp_attach_db in BOL
not sure what you meant with "I have export those databases to files..."
"Kevin" <Kevin@.discussions.microsoft.com> wrote in message
news:D8A6D646-6778-4DA1-AA50-7D50383E1B09@.microsoft.com...
> Dear all,
> I would like to ask if I can recover those databases from tape if I just
> backup all the folders under the "C:\Program Files" ? I have export those
> databases to files...
> As my server's HDD is fail and I only get the DLT Tape back.
> Please give me some advises!
> Thanks all!
|||Thx for your reply!
actually, it's my typing mistake...
it should be "i haven't exported those database as a file"
"ME" wrote:

> if you have valid backup of c:\program files\ microsoft sql server\...\data
> folder and your data files are in this folder you can attach the mdf and ldf
> files to another servers.
> Look for sp_attach_db in BOL
> not sure what you meant with "I have export those databases to files..."
>
> "Kevin" <Kevin@.discussions.microsoft.com> wrote in message
> news:D8A6D646-6778-4DA1-AA50-7D50383E1B09@.microsoft.com...
>
>
|||so you were able to attach the mdf and ldf files?
"Kevin" <Kevin@.discussions.microsoft.com> wrote in message
news:FF8B6AC6-7F80-4D83-B14C-2A21235E56D8@.microsoft.com...[vbcol=seagreen]
> Thx for your reply!
> actually, it's my typing mistake...
> it should be "i haven't exported those database as a file"
> "ME" wrote: