Attach db w/no log file

I had a client run out of disk space because their transaction log grew too
large. They accidentally deleted the tran log (didn't go to Recycle bin
because of size), and their last good backup was from 3 weeks ago. If I try
to attach the database either through Enterprise Manager or QA it tells me
the physical file name for the ldf is incorrect. Is there a way to get
around this error? Can I restore the old backup to a different name and use
its ldf file? Where is the info stored that tells the system what log file
is associated with the database? Can I modify that somehow? Thanks!Look up sp_attach_single_file_db in BOL
"Roger" <Roger@.discussions.microsoft.com> wrote in message
news:FC936F6C-7280-4DD7-933B-08F7D6A112ED@.microsoft.com...
>I had a client run out of disk space because their transaction log grew too
> large. They accidentally deleted the tran log (didn't go to Recycle bin
> because of size), and their last good backup was from 3 weeks ago. If I
> try
> to attach the database either through Enterprise Manager or QA it tells me
> the physical file name for the ldf is incorrect. Is there a way to get
> around this error? Can I restore the old backup to a different name and
> use
> its ldf file? Where is the info stored that tells the system what log
> file
> is associated with the database? Can I modify that somehow? Thanks!|||Thanks for the reply. I just tried this and it is still telling me that the
ldf file 'may be incorrect.' Can I reattach using the old log file? Or
would that potentially mess up my database? Thanks.
"Michael C#" wrote:
> Look up sp_attach_single_file_db in BOL
> "Roger" <Roger@.discussions.microsoft.com> wrote in message
> news:FC936F6C-7280-4DD7-933B-08F7D6A112ED@.microsoft.com...
> >I had a client run out of disk space because their transaction log grew too
> > large. They accidentally deleted the tran log (didn't go to Recycle bin
> > because of size), and their last good backup was from 3 weeks ago. If I
> > try
> > to attach the database either through Enterprise Manager or QA it tells me
> > the physical file name for the ldf is incorrect. Is there a way to get
> > around this error? Can I restore the old backup to a different name and
> > use
> > its ldf file? Where is the info stored that tells the system what log
> > file
> > is associated with the database? Can I modify that somehow? Thanks!
>
>|||You can't add an unmacthing transaction log file to a database. The purpose of a transaction log is
amongst other things to support recovery. SQL Server does a check at startup that the lsn number for
the log records matches with what to expect for the database. If restore isn't a viable option, I
suggest you let MS Support help you. They might have a way to get the database back, in some shape.
And, make sure you give access to the system to people who might do these operations as well as have
a good backup strategy in the future. But I guess you know this by now. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Roger" <Roger@.discussions.microsoft.com> wrote in message
news:ADE099E2-854A-426E-9A54-64CB430CE5CD@.microsoft.com...
> Thanks for the reply. I just tried this and it is still telling me that the
> ldf file 'may be incorrect.' Can I reattach using the old log file? Or
> would that potentially mess up my database? Thanks.
> "Michael C#" wrote:
>> Look up sp_attach_single_file_db in BOL
>> "Roger" <Roger@.discussions.microsoft.com> wrote in message
>> news:FC936F6C-7280-4DD7-933B-08F7D6A112ED@.microsoft.com...
>> >I had a client run out of disk space because their transaction log grew too
>> > large. They accidentally deleted the tran log (didn't go to Recycle bin
>> > because of size), and their last good backup was from 3 weeks ago. If I
>> > try
>> > to attach the database either through Enterprise Manager or QA it tells me
>> > the physical file name for the ldf is incorrect. Is there a way to get
>> > around this error? Can I restore the old backup to a different name and
>> > use
>> > its ldf file? Where is the info stored that tells the system what log
>> > file
>> > is associated with the database? Can I modify that somehow? Thanks!
>>|||Check and see if it actually attached. If I recall correctly,
sp_attach_single_file_db will give a warning message when it doesn't find
the log file, but it should still attach successfully.
"Roger" <Roger@.discussions.microsoft.com> wrote in message
news:ADE099E2-854A-426E-9A54-64CB430CE5CD@.microsoft.com...
> Thanks for the reply. I just tried this and it is still telling me that
> the
> ldf file 'may be incorrect.' Can I reattach using the old log file? Or
> would that potentially mess up my database? Thanks.
> "Michael C#" wrote:
>> Look up sp_attach_single_file_db in BOL
>> "Roger" <Roger@.discussions.microsoft.com> wrote in message
>> news:FC936F6C-7280-4DD7-933B-08F7D6A112ED@.microsoft.com...
>> >I had a client run out of disk space because their transaction log grew
>> >too
>> > large. They accidentally deleted the tran log (didn't go to Recycle
>> > bin
>> > because of size), and their last good backup was from 3 weeks ago. If
>> > I
>> > try
>> > to attach the database either through Enterprise Manager or QA it tells
>> > me
>> > the physical file name for the ldf is incorrect. Is there a way to get
>> > around this error? Can I restore the old backup to a different name
>> > and
>> > use
>> > its ldf file? Where is the info stored that tells the system what log
>> > file
>> > is associated with the database? Can I modify that somehow? Thanks!
>>|||I double-checked and it is not attached. A few other users on a different
forum mentioned the same thing. Someone else mentioned an Emergency Mode and
a log re buid resolution they received from MS. Hopefully this will help.
Thank you for your responses.
"Michael C#" wrote:
> Check and see if it actually attached. If I recall correctly,
> sp_attach_single_file_db will give a warning message when it doesn't find
> the log file, but it should still attach successfully.
>
> "Roger" <Roger@.discussions.microsoft.com> wrote in message
> news:ADE099E2-854A-426E-9A54-64CB430CE5CD@.microsoft.com...
> > Thanks for the reply. I just tried this and it is still telling me that
> > the
> > ldf file 'may be incorrect.' Can I reattach using the old log file? Or
> > would that potentially mess up my database? Thanks.
> >
> > "Michael C#" wrote:
> >
> >> Look up sp_attach_single_file_db in BOL
> >>
> >> "Roger" <Roger@.discussions.microsoft.com> wrote in message
> >> news:FC936F6C-7280-4DD7-933B-08F7D6A112ED@.microsoft.com...
> >> >I had a client run out of disk space because their transaction log grew
> >> >too
> >> > large. They accidentally deleted the tran log (didn't go to Recycle
> >> > bin
> >> > because of size), and their last good backup was from 3 weeks ago. If
> >> > I
> >> > try
> >> > to attach the database either through Enterprise Manager or QA it tells
> >> > me
> >> > the physical file name for the ldf is incorrect. Is there a way to get
> >> > around this error? Can I restore the old backup to a different name
> >> > and
> >> > use
> >> > its ldf file? Where is the info stored that tells the system what log
> >> > file
> >> > is associated with the database? Can I modify that somehow? Thanks!
> >>
> >>
> >>
>
>|||One more thing. Make sure the MDF file is not marked READ-ONLY by the
operating system (for instance, if you burned it on a CD, and copied it
back). If it is, make it read/write and try again. Hope you find a
resolution quickly.
"Roger" <Roger@.discussions.microsoft.com> wrote in message
news:00FC1F66-801D-4BC6-8C72-3884BBDDB250@.microsoft.com...
>I double-checked and it is not attached. A few other users on a different
> forum mentioned the same thing. Someone else mentioned an Emergency Mode
> and
> a log re buid resolution they received from MS. Hopefully this will help.
> Thank you for your responses.
> "Michael C#" wrote:
>> Check and see if it actually attached. If I recall correctly,
>> sp_attach_single_file_db will give a warning message when it doesn't find
>> the log file, but it should still attach successfully.
>>
>> "Roger" <Roger@.discussions.microsoft.com> wrote in message
>> news:ADE099E2-854A-426E-9A54-64CB430CE5CD@.microsoft.com...
>> > Thanks for the reply. I just tried this and it is still telling me
>> > that
>> > the
>> > ldf file 'may be incorrect.' Can I reattach using the old log file?
>> > Or
>> > would that potentially mess up my database? Thanks.
>> >
>> > "Michael C#" wrote:
>> >
>> >> Look up sp_attach_single_file_db in BOL
>> >>
>> >> "Roger" <Roger@.discussions.microsoft.com> wrote in message
>> >> news:FC936F6C-7280-4DD7-933B-08F7D6A112ED@.microsoft.com...
>> >> >I had a client run out of disk space because their transaction log
>> >> >grew
>> >> >too
>> >> > large. They accidentally deleted the tran log (didn't go to Recycle
>> >> > bin
>> >> > because of size), and their last good backup was from 3 weeks ago.
>> >> > If
>> >> > I
>> >> > try
>> >> > to attach the database either through Enterprise Manager or QA it
>> >> > tells
>> >> > me
>> >> > the physical file name for the ldf is incorrect. Is there a way to
>> >> > get
>> >> > around this error? Can I restore the old backup to a different name
>> >> > and
>> >> > use
>> >> > its ldf file? Where is the info stored that tells the system what
>> >> > log
>> >> > file
>> >> > is associated with the database? Can I modify that somehow?
>> >> > Thanks!
>> >>
>> >>
>> >>
>>

Attach db w/no log file

I had a client run out of disk space because their transaction log grew too
large. They accidentally deleted the tran log (didn't go to Recycle bin
because of size), and their last good backup was from 3 weeks ago. If I try
to attach the database either through Enterprise Manager or QA it tells me
the physical file name for the ldf is incorrect. Is there a way to get
around this error? Can I restore the old backup to a different name and use
its ldf file? Where is the info stored that tells the system what log file
is associated with the database? Can I modify that somehow? Thanks!Look up sp_attach_single_file_db in BOL
"Roger" <Roger@.discussions.microsoft.com> wrote in message
news:FC936F6C-7280-4DD7-933B-08F7D6A112ED@.microsoft.com...
>I had a client run out of disk space because their transaction log grew too
> large. They accidentally deleted the tran log (didn't go to Recycle bin
> because of size), and their last good backup was from 3 weeks ago. If I
> try
> to attach the database either through Enterprise Manager or QA it tells me
> the physical file name for the ldf is incorrect. Is there a way to get
> around this error? Can I restore the old backup to a different name and
> use
> its ldf file? Where is the info stored that tells the system what log
> file
> is associated with the database? Can I modify that somehow? Thanks!|||Thanks for the reply. I just tried this and it is still telling me that the
ldf file 'may be incorrect.' Can I reattach using the old log file? Or
would that potentially mess up my database? Thanks.
"Michael C#" wrote:

> Look up sp_attach_single_file_db in BOL
> "Roger" <Roger@.discussions.microsoft.com> wrote in message
> news:FC936F6C-7280-4DD7-933B-08F7D6A112ED@.microsoft.com...
>
>|||You can't add an unmacthing transaction log file to a database. The purpose
of a transaction log is
amongst other things to support recovery. SQL Server does a check at startup
that the lsn number for
the log records matches with what to expect for the database. If restore isn
't a viable option, I
suggest you let MS Support help you. They might have a way to get the databa
se back, in some shape.
And, make sure you give access to the system to people who might do these op
erations as well as have
a good backup strategy in the future. But I guess you know this by now. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Roger" <Roger@.discussions.microsoft.com> wrote in message
news:ADE099E2-854A-426E-9A54-64CB430CE5CD@.microsoft.com...[vbcol=seagreen]
> Thanks for the reply. I just tried this and it is still telling me that t
he
> ldf file 'may be incorrect.' Can I reattach using the old log file? Or
> would that potentially mess up my database? Thanks.
> "Michael C#" wrote:
>|||Check and see if it actually attached. If I recall correctly,
sp_attach_single_file_db will give a warning message when it doesn't find
the log file, but it should still attach successfully.
"Roger" <Roger@.discussions.microsoft.com> wrote in message
news:ADE099E2-854A-426E-9A54-64CB430CE5CD@.microsoft.com...[vbcol=seagreen]
> Thanks for the reply. I just tried this and it is still telling me that
> the
> ldf file 'may be incorrect.' Can I reattach using the old log file? Or
> would that potentially mess up my database? Thanks.
> "Michael C#" wrote:
>|||I double-checked and it is not attached. A few other users on a different
forum mentioned the same thing. Someone else mentioned an Emergency Mode an
d
a log re buid resolution they received from MS. Hopefully this will help.
Thank you for your responses.
"Michael C#" wrote:

> Check and see if it actually attached. If I recall correctly,
> sp_attach_single_file_db will give a warning message when it doesn't find
> the log file, but it should still attach successfully.
>
> "Roger" <Roger@.discussions.microsoft.com> wrote in message
> news:ADE099E2-854A-426E-9A54-64CB430CE5CD@.microsoft.com...
>
>|||One more thing. Make sure the MDF file is not marked READ-ONLY by the
operating system (for instance, if you burned it on a CD, and copied it
back). If it is, make it read/write and try again. Hope you find a
resolution quickly.
"Roger" <Roger@.discussions.microsoft.com> wrote in message
news:00FC1F66-801D-4BC6-8C72-3884BBDDB250@.microsoft.com...[vbcol=seagreen]
>I double-checked and it is not attached. A few other users on a different
> forum mentioned the same thing. Someone else mentioned an Emergency Mode
> and
> a log re buid resolution they received from MS. Hopefully this will help.
> Thank you for your responses.
> "Michael C#" wrote:
>

Attach db to sql 2005 express edition

My dog ate my sql2000 installation disk! After a recent xp installation I remembered that.

Now I'm in a hurry to get up and running with SQL again. I've installed SQL server 2005 express, but I can't attach any databases, or really do anything for that matter. I have a copy of SQL Server 2005 enterprise edition but that won't install on my development machine, Xp Sp2. Which way should I go, install SQL 2005 enterprise on a Win 2003 server or can I download something to administer the express version?

As always thanks for any and all replies!

/Eskil

You can download the SQL Server 2005 Management Studiohttps://www.microsoft.com/downloads/details.aspx?familyid=C243A5AE-4BD1-4E3D-94B8-5A0F62BF7796&displaylang=en)

Attach a DB that was never Detached

Sorry if this is the wrong place to ask this. I have a file "restored.mdf",
that was simply copied from the disk by backup software, i need the data
inside that file. It is 12GB. the file was backed up from
E:\SQLData\MSSQL\Data\ABC.mdf, and is now in
E:\SQLData\MSSQL\Data\restore\restored.mdf.
Is there any way to attach this database to any SQL Server 2000 Server ? If
so, i can then just copy the data from one table to another. I tried
EXEC sp_attach_single_file_db @.dbname = N'restored',
@.physname = N'E:\SQLdata\MSSQL\Data\restore\restored.mdf'
But when i run this i get:
Server: Msg 5105, Level 16, State 4, Line 1
Device activation error. The physical file name
'e:\SQLdata\MSSQL\data\ABC.ldf' may be incorrect.
Server: Msg 1813, Level 16, State 1, Line 1
Could not open new database 'restore_AVC'. CREATE DATABASE is aborted.
Device activation error. The physical file name
'e:\SQLdata\MSSQL\data\ABC.ldf' may be incorrect.
It looks like it is looking for the original logfile, i guess because this
database was never actually detached.
Attaching databases that were not detached properly is unreliable, and does
not always work.
If there were open transactions, you will need to use sp_attach_db instead
(as these transactions will need to be rolled forward when the database is
attached). You will therefore need access to the log.
John.
hogwart wrote:
> Sorry if this is the wrong place to ask this. I have a file
> "restored.mdf", that was simply copied from the disk by backup
> software, i need the data inside that file. It is 12GB. the file
> was backed up from E:\SQLData\MSSQL\Data\ABC.mdf, and is now in
> E:\SQLData\MSSQL\Data\restore\restored.mdf.
> Is there any way to attach this database to any SQL Server 2000
> Server ? If so, i can then just copy the data from one table to
> another. I tried
> EXEC sp_attach_single_file_db @.dbname = N'restored',
> @.physname = N'E:\SQLdata\MSSQL\Data\restore\restored.mdf'
> But when i run this i get:
> Server: Msg 5105, Level 16, State 4, Line 1
> Device activation error. The physical file name
> 'e:\SQLdata\MSSQL\data\ABC.ldf' may be incorrect.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'restore_AVC'. CREATE DATABASE is aborted.
> Device activation error. The physical file name
> 'e:\SQLdata\MSSQL\data\ABC.ldf' may be incorrect.
> It looks like it is looking for the original logfile, i guess because
> this database was never actually detached.
|||Give the suggestion here a try (no guarantees). Make a backup of your files
first though.
http://tinyurl.com/3s4b6
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Use MiniSQLBackup Lite, free!
"hogwart" <hogwart@.dannyland.com> wrote in message
news:%230tcOwzlEHA.3156@.TK2MSFTNGP12.phx.gbl...
> Sorry if this is the wrong place to ask this. I have a file
"restored.mdf",
> that was simply copied from the disk by backup software, i need the data
> inside that file. It is 12GB. the file was backed up from
> E:\SQLData\MSSQL\Data\ABC.mdf, and is now in
> E:\SQLData\MSSQL\Data\restore\restored.mdf.
> Is there any way to attach this database to any SQL Server 2000 Server ?
If
> so, i can then just copy the data from one table to another. I tried
> EXEC sp_attach_single_file_db @.dbname = N'restored',
> @.physname = N'E:\SQLdata\MSSQL\Data\restore\restored.mdf'
> But when i run this i get:
> Server: Msg 5105, Level 16, State 4, Line 1
> Device activation error. The physical file name
> 'e:\SQLdata\MSSQL\data\ABC.ldf' may be incorrect.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'restore_AVC'. CREATE DATABASE is aborted.
> Device activation error. The physical file name
> 'e:\SQLdata\MSSQL\data\ABC.ldf' may be incorrect.
> It looks like it is looking for the original logfile, i guess because this
> database was never actually detached.
>

Attach a DB that was never Detached

Sorry if this is the wrong place to ask this. I have a file "restored.mdf",
that was simply copied from the disk by backup software, i need the data
inside that file. It is 12GB. the file was backed up from
E:\SQLData\MSSQL\Data\ABC.mdf, and is now in
E:\SQLData\MSSQL\Data\restore\restored.mdf.
Is there any way to attach this database to any SQL Server 2000 Server ? If
so, i can then just copy the data from one table to another. I tried
EXEC sp_attach_single_file_db @.dbname = N'restored',
@.physname = N'E:\SQLdata\MSSQL\Data\restore\restored.mdf'
But when i run this i get:
Server: Msg 5105, Level 16, State 4, Line 1
Device activation error. The physical file name
'e:\SQLdata\MSSQL\data\ABC.ldf' may be incorrect.
Server: Msg 1813, Level 16, State 1, Line 1
Could not open new database 'restore_AVC'. CREATE DATABASE is aborted.
Device activation error. The physical file name
'e:\SQLdata\MSSQL\data\ABC.ldf' may be incorrect.
It looks like it is looking for the original logfile, i guess because this
database was never actually detached.Attaching databases that were not detached properly is unreliable, and does
not always work.
If there were open transactions, you will need to use sp_attach_db instead
(as these transactions will need to be rolled forward when the database is
attached). You will therefore need access to the log.
John.
hogwart wrote:
> Sorry if this is the wrong place to ask this. I have a file
> "restored.mdf", that was simply copied from the disk by backup
> software, i need the data inside that file. It is 12GB. the file
> was backed up from E:\SQLData\MSSQL\Data\ABC.mdf, and is now in
> E:\SQLData\MSSQL\Data\restore\restored.mdf.
> Is there any way to attach this database to any SQL Server 2000
> Server ? If so, i can then just copy the data from one table to
> another. I tried
> EXEC sp_attach_single_file_db @.dbname = N'restored',
> @.physname = N'E:\SQLdata\MSSQL\Data\restore\restored.mdf'
> But when i run this i get:
> Server: Msg 5105, Level 16, State 4, Line 1
> Device activation error. The physical file name
> 'e:\SQLdata\MSSQL\data\ABC.ldf' may be incorrect.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'restore_AVC'. CREATE DATABASE is aborted.
> Device activation error. The physical file name
> 'e:\SQLdata\MSSQL\data\ABC.ldf' may be incorrect.
> It looks like it is looking for the original logfile, i guess because
> this database was never actually detached.|||Give the suggestion here a try (no guarantees). Make a backup of your files
first though.
http://tinyurl.com/3s4b6
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Use MiniSQLBackup Lite, free!
"hogwart" <hogwart@.dannyland.com> wrote in message
news:%230tcOwzlEHA.3156@.TK2MSFTNGP12.phx.gbl...
> Sorry if this is the wrong place to ask this. I have a file
"restored.mdf",
> that was simply copied from the disk by backup software, i need the data
> inside that file. It is 12GB. the file was backed up from
> E:\SQLData\MSSQL\Data\ABC.mdf, and is now in
> E:\SQLData\MSSQL\Data\restore\restored.mdf.
> Is there any way to attach this database to any SQL Server 2000 Server ?
If
> so, i can then just copy the data from one table to another. I tried
> EXEC sp_attach_single_file_db @.dbname = N'restored',
> @.physname = N'E:\SQLdata\MSSQL\Data\restore\restored.mdf'
> But when i run this i get:
> Server: Msg 5105, Level 16, State 4, Line 1
> Device activation error. The physical file name
> 'e:\SQLdata\MSSQL\data\ABC.ldf' may be incorrect.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'restore_AVC'. CREATE DATABASE is aborted.
> Device activation error. The physical file name
> 'e:\SQLdata\MSSQL\data\ABC.ldf' may be incorrect.
> It looks like it is looking for the original logfile, i guess because this
> database was never actually detached.
>

Attach a DB that was never Detached

Sorry if this is the wrong place to ask this. I have a file "restored.mdf",
that was simply copied from the disk by backup software, i need the data
inside that file. It is 12GB. the file was backed up from
E:\SQLData\MSSQL\Data\ABC.mdf, and is now in
E:\SQLData\MSSQL\Data\restore\restored.mdf.
Is there any way to attach this database to any SQL Server 2000 Server ? If
so, i can then just copy the data from one table to another. I tried
EXEC sp_attach_single_file_db @.dbname = N'restored',
@.physname = N'E:\SQLdata\MSSQL\Data\restore\restored.mdf'
But when i run this i get:
Server: Msg 5105, Level 16, State 4, Line 1
Device activation error. The physical file name
'e:\SQLdata\MSSQL\data\ABC.ldf' may be incorrect.
Server: Msg 1813, Level 16, State 1, Line 1
Could not open new database 'restore_AVC'. CREATE DATABASE is aborted.
Device activation error. The physical file name
'e:\SQLdata\MSSQL\data\ABC.ldf' may be incorrect.
It looks like it is looking for the original logfile, i guess because this
database was never actually detached.
Attaching databases that were not detached properly is unreliable, and does
not always work.
If there were open transactions, you will need to use sp_attach_db instead
(as these transactions will need to be rolled forward when the database is
attached). You will therefore need access to the log.
John.
hogwart wrote:
> Sorry if this is the wrong place to ask this. I have a file
> "restored.mdf", that was simply copied from the disk by backup
> software, i need the data inside that file. It is 12GB. the file
> was backed up from E:\SQLData\MSSQL\Data\ABC.mdf, and is now in
> E:\SQLData\MSSQL\Data\restore\restored.mdf.
> Is there any way to attach this database to any SQL Server 2000
> Server ? If so, i can then just copy the data from one table to
> another. I tried
> EXEC sp_attach_single_file_db @.dbname = N'restored',
> @.physname = N'E:\SQLdata\MSSQL\Data\restore\restored.mdf'
> But when i run this i get:
> Server: Msg 5105, Level 16, State 4, Line 1
> Device activation error. The physical file name
> 'e:\SQLdata\MSSQL\data\ABC.ldf' may be incorrect.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'restore_AVC'. CREATE DATABASE is aborted.
> Device activation error. The physical file name
> 'e:\SQLdata\MSSQL\data\ABC.ldf' may be incorrect.
> It looks like it is looking for the original logfile, i guess because
> this database was never actually detached.
|||Give the suggestion here a try (no guarantees). Make a backup of your files
first though.
http://tinyurl.com/3s4b6
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Use MiniSQLBackup Lite, free!
"hogwart" <hogwart@.dannyland.com> wrote in message
news:%230tcOwzlEHA.3156@.TK2MSFTNGP12.phx.gbl...
> Sorry if this is the wrong place to ask this. I have a file
"restored.mdf",
> that was simply copied from the disk by backup software, i need the data
> inside that file. It is 12GB. the file was backed up from
> E:\SQLData\MSSQL\Data\ABC.mdf, and is now in
> E:\SQLData\MSSQL\Data\restore\restored.mdf.
> Is there any way to attach this database to any SQL Server 2000 Server ?
If
> so, i can then just copy the data from one table to another. I tried
> EXEC sp_attach_single_file_db @.dbname = N'restored',
> @.physname = N'E:\SQLdata\MSSQL\Data\restore\restored.mdf'
> But when i run this i get:
> Server: Msg 5105, Level 16, State 4, Line 1
> Device activation error. The physical file name
> 'e:\SQLdata\MSSQL\data\ABC.ldf' may be incorrect.
> Server: Msg 1813, Level 16, State 1, Line 1
> Could not open new database 'restore_AVC'. CREATE DATABASE is aborted.
> Device activation error. The physical file name
> 'e:\SQLdata\MSSQL\data\ABC.ldf' may be incorrect.
> It looks like it is looking for the original logfile, i guess because this
> database was never actually detached.
>
sql

Attaach 2000 data file from network drive

Hi
How can I attach mdf file without copying it to local disk.
I dont have enough space on the server which I have to attach db. But there
is enough space on an other server in the network.
Thanks
BanuYou will need to set this trace flag to enable
dbcc traceon(1807)
Be aware that this isn't entirley supported by Microsoft and any network
delays can lead to corruption. Also performance will suffer...
HTH. Ryan
"Banu_tr" <abuslu@.hotmail.com> wrote in message
news:D8AB0981-FBDF-479F-B09E-D5B1A5289D60@.microsoft.com...
> Hi
> How can I attach mdf file without copying it to local disk.
> I dont have enough space on the server which I have to attach db. But
> there
> is enough space on an other server in the network.
> Thanks
> Banu|||Dear Ryan,
I execute the dbcc traceon(1807) . But still couln't see the mapped network
drive when I try to attach the mdf file.
What else should I do?
"Ryan" wrote:

> You will need to set this trace flag to enable
> dbcc traceon(1807)
> Be aware that this isn't entirley supported by Microsoft and any network
> delays can lead to corruption. Also performance will suffer...
> --
> HTH. Ryan
> "Banu_tr" <abuslu@.hotmail.com> wrote in message
> news:D8AB0981-FBDF-479F-B09E-D5B1A5289D60@.microsoft.com...
>
>|||Banu_tr wrote:
> Dear Ryan,
> I execute the dbcc traceon(1807) . But still couln't see the mapped networ
k
> drive when I try to attach the mdf file.
> What else should I do?
> "Ryan" wrote:
Make sure you refer to the network path with the UNC name rather than a
drive letter. Like:
EXEC sp_attach_db 'JUNK',
'\\SERVER\share\MSSQL\data\JUNK.mdf',
'\\SERVER\share\MSSQL\data\JUNK_log.LDF'
or:
EXEC sp_attach_db 'JUNK',
'\\SERVER\C$\MSSQL\data\JUNK.mdf',
'\\SERVER\C$\MSSQL\data\JUNK_log.LDF'
IMO running a database from a network drive is a near suicidal thing to
do if you care about the integrity or availability of your data. Do
this for a READ-ONLY or SINGLE USER database only. Make sure you have a
current backup BEFORE you attempt it. Don't expect decent performance
or reliability.
Read:
http://support.microsoft.com/defaul...kb;en-us;304261
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Dear David,
I still got the message "Device Activation Error"
any advice?
"David Portas" wrote:

> Banu_tr wrote:
> Make sure you refer to the network path with the UNC name rather than a
> drive letter. Like:
> EXEC sp_attach_db 'JUNK',
> '\\SERVER\share\MSSQL\data\JUNK.mdf',
> '\\SERVER\share\MSSQL\data\JUNK_log.LDF'
> or:
> EXEC sp_attach_db 'JUNK',
> '\\SERVER\C$\MSSQL\data\JUNK.mdf',
> '\\SERVER\C$\MSSQL\data\JUNK_log.LDF'
> IMO running a database from a network drive is a near suicidal thing to
> do if you care about the integrity or availability of your data. Do
> this for a READ-ONLY or SINGLE USER database only. Make sure you have a
> current backup BEFORE you attempt it. Don't expect decent performance
> or reliability.
> Read:
> http://support.microsoft.com/defaul...kb;en-us;304261
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Banu_tr wrote:
> Dear David,
> I still got the message "Device Activation Error"
> any advice?
>
Are you sure the server has access to the share you are trying to use?
Same advice as before: don't do it. Not for production use anyway. Tell
your boss, customer or business owner that they need to purchase some
more storage.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Banu_tr wrote:
> Dear David,
> I still got the message "Device Activation Error"
> any advice?
>
You need to check that the account that the SQL Server service runs
under can access the share where your data files are located. If SQL is
running under the service account then you need to change it to run as
a domain login, give that login the necessary permissions, then stop
and restart the SQL server service. Having done that, it should work
but as already indicated this is not a fully supported option and never
having tried it I don't know under what circumstances it might not work
at all.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Assertion and 823 errors!

Hi,
We have been getting a lot of 823 errors lately, ran
checkdb and it always comes out with 0 errors.
We ran a detailed disk and hardware diagnostic and could
not come up with any issues.
However, today, we got a failed assertion error :
SQL Server Assertion: File:
<R:\sql\ntdbms\storeng\drs\include\record.inl>, line=1447
Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <= MAXDATAROW'.
I isolated this error to an application that is mostly UI
driven (with respect to performing Unions and joins),
since i read in MSKB that this may also be caused by any
subquery with a distinct clause or anything that returns a
row bigger than 8060KB.
I have 3 queries that run, and i checked the execution
plans, the tables are all properly indexed and they seem
to look fine.
we have EMC hardware , sql 2000 on Advanced server
(8.00.818)
Not sure what the next steps should be, Appreciate your
guidance and suggestions.
Thanks,Sounds like a case for MS Support to me (assuming you are up on service pack and have searched KB).
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"mark" <anonymous@.discussions.microsoft.com> wrote in message
news:02f701c3b449$acd3a670$a101280a@.phx.gbl...
> Hi,
> We have been getting a lot of 823 errors lately, ran
> checkdb and it always comes out with 0 errors.
> We ran a detailed disk and hardware diagnostic and could
> not come up with any issues.
> However, today, we got a failed assertion error :
> SQL Server Assertion: File:
> <R:\sql\ntdbms\storeng\drs\include\record.inl>, line=1447
> Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <=> MAXDATAROW'.
> I isolated this error to an application that is mostly UI
> driven (with respect to performing Unions and joins),
> since i read in MSKB that this may also be caused by any
> subquery with a distinct clause or anything that returns a
> row bigger than 8060KB.
> I have 3 queries that run, and i checked the execution
> plans, the tables are all properly indexed and they seem
> to look fine.
> we have EMC hardware , sql 2000 on Advanced server
> (8.00.818)
> Not sure what the next steps should be, Appreciate your
> guidance and suggestions.
> Thanks,
>