Data file missing

Last night one of my SQL Servers stopped and restarted for no reason. When
it came back up all of the files (.mdf) on one of my volumes were missing
(as were the directories/folders).
We are currently running SQL 2000 sp3a Enterprise Edition, Win 2000 on a
XIOTech Magnitude SAN.
Has this happened to any of you? Do you have any ideas on what may have
caused this? I am currently restoring a 100gig database and feeling rather
scared about the IO subsystem.
Mike
It seems strange.Looks like you have been attacked by virus.
"Mike Johnson" <mj@.microsoft.com> wrote in message
news:OdqRhz61EHA.3576@.TK2MSFTNGP12.phx.gbl...
> Last night one of my SQL Servers stopped and restarted for no reason.
When
> it came back up all of the files (.mdf) on one of my volumes were missing
> (as were the directories/folders).
> We are currently running SQL 2000 sp3a Enterprise Edition, Win 2000 on a
> XIOTech Magnitude SAN.
> Has this happened to any of you? Do you have any ideas on what may have
> caused this? I am currently restoring a 100gig database and feeling
rather
> scared about the IO subsystem.
>
|||I disagree. Xiotech is notorious for spurious losses of data. We use to
have that in our data center but they proved to be too unreliable. We now
use EMC Symmetrix systems and reserve a few remaining Xiotech systems for
our developement environments.
Sincerely,
Anthony Thomas

"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%233YG$461EHA.3468@.TK2MSFTNGP14.phx.gbl...
Mike
It seems strange.Looks like you have been attacked by virus.
"Mike Johnson" <mj@.microsoft.com> wrote in message
news:OdqRhz61EHA.3576@.TK2MSFTNGP12.phx.gbl...
> Last night one of my SQL Servers stopped and restarted for no reason.
When
> it came back up all of the files (.mdf) on one of my volumes were missing
> (as were the directories/folders).
> We are currently running SQL 2000 sp3a Enterprise Edition, Win 2000 on a
> XIOTech Magnitude SAN.
> Has this happened to any of you? Do you have any ideas on what may have
> caused this? I am currently restoring a 100gig database and feeling
rather
> scared about the IO subsystem.
>

Data file missing

Last night one of my SQL Servers stopped and restarted for no reason. When
it came back up all of the files (.mdf) on one of my volumes were missing
(as were the directories/folders).
We are currently running SQL 2000 sp3a Enterprise Edition, Win 2000 on a
XIOTech Magnitude SAN.
Has this happened to any of you? Do you have any ideas on what may have
caused this? I am currently restoring a 100gig database and feeling rather
scared about the IO subsystem.Mike
It seems strange.Looks like you have been attacked by virus.
"Mike Johnson" <mj@.microsoft.com> wrote in message
news:OdqRhz61EHA.3576@.TK2MSFTNGP12.phx.gbl...
> Last night one of my SQL Servers stopped and restarted for no reason.
When
> it came back up all of the files (.mdf) on one of my volumes were missing
> (as were the directories/folders).
> We are currently running SQL 2000 sp3a Enterprise Edition, Win 2000 on a
> XIOTech Magnitude SAN.
> Has this happened to any of you? Do you have any ideas on what may have
> caused this? I am currently restoring a 100gig database and feeling
rather
> scared about the IO subsystem.
>|||I disagree. Xiotech is notorious for spurious losses of data. We use to
have that in our data center but they proved to be too unreliable. We now
use EMC Symmetrix systems and reserve a few remaining Xiotech systems for
our developement environments.
Sincerely,
Anthony Thomas
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%233YG$461EHA.3468@.TK2MSFTNGP14.phx.gbl...
Mike
It seems strange.Looks like you have been attacked by virus.
"Mike Johnson" <mj@.microsoft.com> wrote in message
news:OdqRhz61EHA.3576@.TK2MSFTNGP12.phx.gbl...
> Last night one of my SQL Servers stopped and restarted for no reason.
When
> it came back up all of the files (.mdf) on one of my volumes were missing
> (as were the directories/folders).
> We are currently running SQL 2000 sp3a Enterprise Edition, Win 2000 on a
> XIOTech Magnitude SAN.
> Has this happened to any of you? Do you have any ideas on what may have
> caused this? I am currently restoring a 100gig database and feeling
rather
> scared about the IO subsystem.
>

Data file missing

Last night one of my SQL Servers stopped and restarted for no reason. When
it came back up all of the files (.mdf) on one of my volumes were missing
(as were the directories/folders).
We are currently running SQL 2000 sp3a Enterprise Edition, Win 2000 on a
XIOTech Magnitude SAN.
Has this happened to any of you? Do you have any ideas on what may have
caused this? I am currently restoring a 100gig database and feeling rather
scared about the IO subsystem.Mike
It seems strange.Looks like you have been attacked by virus.
"Mike Johnson" <mj@.microsoft.com> wrote in message
news:OdqRhz61EHA.3576@.TK2MSFTNGP12.phx.gbl...
> Last night one of my SQL Servers stopped and restarted for no reason.
When
> it came back up all of the files (.mdf) on one of my volumes were missing
> (as were the directories/folders).
> We are currently running SQL 2000 sp3a Enterprise Edition, Win 2000 on a
> XIOTech Magnitude SAN.
> Has this happened to any of you? Do you have any ideas on what may have
> caused this? I am currently restoring a 100gig database and feeling
rather
> scared about the IO subsystem.
>|||I disagree. Xiotech is notorious for spurious losses of data. We use to
have that in our data center but they proved to be too unreliable. We now
use EMC Symmetrix systems and reserve a few remaining Xiotech systems for
our developement environments.
Sincerely,
Anthony Thomas
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%233YG$461EHA.3468@.TK2MSFTNGP14.phx.gbl...
Mike
It seems strange.Looks like you have been attacked by virus.
"Mike Johnson" <mj@.microsoft.com> wrote in message
news:OdqRhz61EHA.3576@.TK2MSFTNGP12.phx.gbl...
> Last night one of my SQL Servers stopped and restarted for no reason.
When
> it came back up all of the files (.mdf) on one of my volumes were missing
> (as were the directories/folders).
> We are currently running SQL 2000 sp3a Enterprise Edition, Win 2000 on a
> XIOTech Magnitude SAN.
> Has this happened to any of you? Do you have any ideas on what may have
> caused this? I am currently restoring a 100gig database and feeling
rather
> scared about the IO subsystem.
>

Data File Growth During Re-Index

Hi All
I have a 100GB SQL Server database and when I run a Database Re-Index the
data file (mdf) increases in size from 100GB to 190GB.
Is this normal and if not how do I go about resolving the issue as once the
re-index is finished there is ~90GB of free space in the database.
Thanks
- David T
- David TDavid
1) SQL Server 2005
ALTER INDEX..... there us ONLINE option too see BOL for details
2) SQL Server 2000
Don't run this command on all tables. Identify heavy fragmented tables and
then run DBCC
"David" <David@.discussions.microsoft.com> wrote in message
news:E00AC669-B2C3-4324-B082-4844771F9B93@.microsoft.com...
> Hi All
> I have a 100GB SQL Server database and when I run a Database Re-Index the
> data file (mdf) increases in size from 100GB to 190GB.
> Is this normal and if not how do I go about resolving the issue as once
> the
> re-index is finished there is ~90GB of free space in the database.
> Thanks
> - David T
> - David T|||Also if 2005 check the SORT_IN_TEMPDB option of Alter Index. Sort operations
are performed in tempdb, which does cause tempdb to grow so consider this
also.
--
Adam J Warne, MCDBA
"David" wrote:
> Hi All
> I have a 100GB SQL Server database and when I run a Database Re-Index the
> data file (mdf) increases in size from 100GB to 190GB.
> Is this normal and if not how do I go about resolving the issue as once the
> re-index is finished there is ~90GB of free space in the database.
> Thanks
> - David T
> - David T|||Having lots of free space is good for a db so that when you perform
operations such as these there is enough room to do it's job properly. When
you reindex it creates a new copy of all the indexes before dropping and
renaming the existing ones. So you need room for this to happen. Twice the
space is usually not required although it won't hurt anything. I suspect
your autogrow is set to a high percentage. Most dbs don't need all indexes
rebuilt each time so you may want to consider only rebuilding ones that are
fairly fragmented. See DBCC SHOWCONTIG in BooksOnLine for a sample script to
defrag based on %.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"David" <David@.discussions.microsoft.com> wrote in message
news:E00AC669-B2C3-4324-B082-4844771F9B93@.microsoft.com...
> Hi All
> I have a 100GB SQL Server database and when I run a Database Re-Index the
> data file (mdf) increases in size from 100GB to 190GB.
> Is this normal and if not how do I go about resolving the issue as once
> the
> re-index is finished there is ~90GB of free space in the database.
> Thanks
> - David T
> - David T|||It is important to understand what these commands do, in order to understand the ramifications of
running the commands. You don't mention what version of SQL Server you have, so I'll assume 2005 in
the commands I mention below. The same principle holds for 2000, but the commands names are
different.
ALTER INDEX ... REBUILD:
This will create a new index internally, and *after that has been done* the old one is removed. So
if you have one large table in the database, consuming say close to 90 GB and that table has a
clustered index, then what you see is expected.
As suggested:
Don't reindex if you don't need to. Base you reindexing on fragmentation level and whether you
actually see enough performance improvements from the reindex.
Also, REORGANIZE instead of REBUILD does something different and does not require as much working
space in the database.
Check out http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx for more
information (as well as Books Online of course).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"David" <David@.discussions.microsoft.com> wrote in message
news:E00AC669-B2C3-4324-B082-4844771F9B93@.microsoft.com...
> Hi All
> I have a 100GB SQL Server database and when I run a Database Re-Index the
> data file (mdf) increases in size from 100GB to 190GB.
> Is this normal and if not how do I go about resolving the issue as once the
> re-index is finished there is ~90GB of free space in the database.
> Thanks
> - David T
> - David T

Data between two sql registrations

Hi how can i set a query to retrieve data from 2 registrations ?
i am using the default sql server registration and an mdf database that i
connected. The data are distributed to the two databases.
Can anyone help me please ?Makis
Do the databases reside on the same Server?
If they do ,please run
INSERT INTO DataBase1.dbo.Table (....)
SELECT ... FROM DataBase2.dbo.Table2
"makis" <sera@.mind.gr> wrote in message
news:O54VPFaIGHA.2036@.TK2MSFTNGP14.phx.gbl...
> Hi how can i set a query to retrieve data from 2 registrations ?
> i am using the default sql server registration and an mdf database that i
> connected. The data are distributed to the two databases.
> Can anyone help me please ?
>|||Are those two SQL Server instances? Then use sp_addlinked server to create a
link between the instances, then by using properly qualified identifiers
(look up "identifiers" in Books Online) you can access data in either of the
databases (tables, etc.).
ML
http://milambda.blogspot.com/|||If "registrations" are meant with different servers, you have to
use/configure a linked server. Then you can extend the syntax from uri
by the following:
INSERT INTO Servername1.DataBase1.dbo.Table (....)
SELECT ... FROM Servername2.DataBase2.dbo.Table2
(four part notation)
If one server is local you sure can leave one Servername{0} out.
HTH, Jens Suessmeyer.|||Yep , I was by this statement
>The data are distributed to the two databases.
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1138189932.137989.39870@.g44g2000cwa.googlegroups.com...
> If "registrations" are meant with different servers, you have to
> use/configure a linked server. Then you can extend the syntax from uri
> by the following:
> INSERT INTO Servername1.DataBase1.dbo.Table (....)
> SELECT ... FROM Servername2.DataBase2.dbo.Table2
> (four part notation)
> If one server is local you sure can leave one Servername{0} out.
> HTH, Jens Suessmeyer.
>|||the databases are in one server.
I meant that the data are spread accross the two databases.
I believe your query will help me, thanks for your help. Will post again if
any problems.

Data base growth control

Please anybody can explain how to reduce MDF LDF files size
or tuneup the MS SQL Server

Quote:

Originally Posted by Ashutosh Keskar

Please anybody can explain how to reduce MDF LDF files size
or tuneup the MS SQL Server

I'm not absolutely sure what you're after but you can change the size and growth properties of both files in Enterprise Manager: right click on the database -> Properties -> Data files and Transaction Log tab.|||Hi Ashutosh Keskar and welcome to TSDN MSSQL Forum,

You can also take a look at the maintenance plan - Enterprise manager -> Server -> Management -> Database Maintenance and see if you are running a job to reorg the data and index pages( may be part of your backup), there is also an option to free unused space.

I cannot guarantee it will help (there is a small chance it could make performance worse) but it is worth a look.

Regards Purple

Data and log sits on different disk drive.

Hi,
Can I create a database with the .mdf (data) and .ldf (log) on 2 different disk drive? Is there any benefit of doing it in term of performance? How about backup and recovery?
Thanks.
Hi,
That will be a very good approach to reduce the Disk I/o and increase the
performance. I recommend you to create multiple file groups
to keep the data and Index as well.
Backup and Recovery will of same sort as normal. If you have File groups you
will be able to backup and restore file group wise.
Have a look into this article (Read the Disk I/o Part).
http://msdn.microsoft.com/library/de...us/dnsql7/html
/msdn_sql7perftune.asp
Thanks
Hari
MCDBA
"Alida" <Alida@.discussions.microsoft.com> wrote in message
news:773FA6D4-CA83-46EE-BB23-2ACC8851C84E@.microsoft.com...
> Hi,
> Can I create a database with the .mdf (data) and .ldf (log) on 2 different
disk drive? Is there any benefit of doing it in term of performance? How
about backup and recovery?
> Thanks.
>
|||In addition to Hari's response... IF you need up-to-the-minute recovery OR
better performance, you should always keep the log and data on separate
physical drives ( not just a different partition on the same drive.)
This separates serial IO ( log io) from random IO (Data rows) which allows
for better performance. The log should be mirrored...
Here are the data placement rules.:
1. Keep log away from data
2. Keep data away from master, model
3. Mirror the log
As Hari says, there is no difference in regard to how backup is done..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Alida" <Alida@.discussions.microsoft.com> wrote in message
news:773FA6D4-CA83-46EE-BB23-2ACC8851C84E@.microsoft.com...
> Hi,
> Can I create a database with the .mdf (data) and .ldf (log) on 2 different
disk drive? Is there any benefit of doing it in term of performance? How
about backup and recovery?
> Thanks.
>

Data and log sits on different disk drive.

Hi,
Can I create a database with the .mdf (data) and .ldf (log) on 2 different disk drive? Is there any benefit of doing it in term of performance? How about backup and recovery?
Thanks.Hi,
That will be a very good approach to reduce the Disk I/o and increase the
performance. I recommend you to create multiple file groups
to keep the data and Index as well.
Backup and Recovery will of same sort as normal. If you have File groups you
will be able to backup and restore file group wise.
Have a look into this article (Read the Disk I/o Part).
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql7/html
/msdn_sql7perftune.asp
Thanks
Hari
MCDBA
"Alida" <Alida@.discussions.microsoft.com> wrote in message
news:773FA6D4-CA83-46EE-BB23-2ACC8851C84E@.microsoft.com...
> Hi,
> Can I create a database with the .mdf (data) and .ldf (log) on 2 different
disk drive? Is there any benefit of doing it in term of performance? How
about backup and recovery?
> Thanks.
>|||In addition to Hari's response... IF you need up-to-the-minute recovery OR
better performance, you should always keep the log and data on separate
physical drives ( not just a different partition on the same drive.)
This separates serial IO ( log io) from random IO (Data rows) which allows
for better performance. The log should be mirrored...
Here are the data placement rules.:
1. Keep log away from data
2. Keep data away from master, model
3. Mirror the log
As Hari says, there is no difference in regard to how backup is done..
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Alida" <Alida@.discussions.microsoft.com> wrote in message
news:773FA6D4-CA83-46EE-BB23-2ACC8851C84E@.microsoft.com...
> Hi,
> Can I create a database with the .mdf (data) and .ldf (log) on 2 different
disk drive? Is there any benefit of doing it in term of performance? How
about backup and recovery?
> Thanks.
>

Data and log sits on different disk drive.

Hi,
Can I create a database with the .mdf (data) and .ldf (log) on 2 different d
isk drive? Is there any benefit of doing it in term of performance? How abou
t backup and recovery?
Thanks.Hi,
That will be a very good approach to reduce the Disk I/o and increase the
performance. I recommend you to create multiple file groups
to keep the data and Index as well.
Backup and Recovery will of same sort as normal. If you have File groups you
will be able to backup and restore file group wise.
Have a look into this article (Read the Disk I/o Part).
http://msdn.microsoft.com/library/d...-us/dnsql7/html
/msdn_sql7perftune.asp
Thanks
Hari
MCDBA
"Alida" <Alida@.discussions.microsoft.com> wrote in message
news:773FA6D4-CA83-46EE-BB23-2ACC8851C84E@.microsoft.com...
> Hi,
> Can I create a database with the .mdf (data) and .ldf (log) on 2 different
disk drive? Is there any benefit of doing it in term of performance? How
about backup and recovery?
> Thanks.
>|||In addition to Hari's response... IF you need up-to-the-minute recovery OR
better performance, you should always keep the log and data on separate
physical drives ( not just a different partition on the same drive.)
This separates serial IO ( log io) from random IO (Data rows) which allows
for better performance. The log should be mirrored...
Here are the data placement rules.:
1. Keep log away from data
2. Keep data away from master, model
3. Mirror the log
As Hari says, there is no difference in regard to how backup is done..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Alida" <Alida@.discussions.microsoft.com> wrote in message
news:773FA6D4-CA83-46EE-BB23-2ACC8851C84E@.microsoft.com...
> Hi,
> Can I create a database with the .mdf (data) and .ldf (log) on 2 different
disk drive? Is there any benefit of doing it in term of performance? How
about backup and recovery?
> Thanks.
>

data and log files won't copy

I copied a database using backup and restore from Sql server 2000 to Sql server 2005. I noticed the data(mdf) and log(ldf) files did not copy. when I try to do a copy and past it gives me an error: cannot copy lof: it is being used by another person or program. Close all programs and try again. Does this mean I have to stop both servers to copy between them? Please help.

Hi,

you have to detach your database form your sql server 2000, and then attach them to the sql server 2005.

...

Detaching a database requires exclusive access to the database

USE master;
ALTER DATABASE dbname
SET SINGLE_USER;
GO

exec sp_detach_db @.dbname='dbname'

...

plz read BOL for more information: Detaching and Attaching Databases

|||

If you are restoring from an actual backup file (which usually has a .bak extension), you would not need to copy the data and log files. The restore process creates new ones from the backup file.

There are two easy ways to go from 2000 on one DB Server to 2005 on another DB Server. One is to backup the SQL Server 2000 database to a .bak file, then copy the .bak file to the new server and use SQL Server 2005 to do the restore (on the new server). This converts the database to 2005 format.

The second way is to detach the database in SQL Server 2000 and then copy the data and log files to the new server and then use SQL Server 2005 to attach them. This also converts the database to 2005 format. Either way, once you have restored or attached a SQL Server 2000 database in SQL Server 2005, the database cannot go back to SQL Server 2000 format!

Finally, whichever method you use, you should change the compatibility level to 90 and run sp_UpdateStats on the upgraded database.

|||Thank you for all your help. This worked out fine.|||

Hi,

First step :Take database offline or stop the sql server 2000 service.

After that you copy the .mdf and .ldf files.

withRegards

S.kuraliniyan

SystemEngineer

Data and log file naming conventions

I have several user databases across several servers.
Some of the physical data files are stored as databasename.mdf,
databasename_data.mdf...
Some of the physical log files are stored as databasename.ldf,
databasename_log.ldf...
Does any one has any naming convention suggestion on this?
Currently SQL2K. Planning to move SQL2005 when it is available.
Thanks,
HarryUse whatever convention is logical and meets your needs; the main thing
is to be consistent.
Personally, I tend to stick with the GUI default <dbname>_data.mdf &
<dbname>_log.ldf for everything I can (using default values for things,
if they're sensible & reasonable, tends to avoid the pain of having to
memorise different conventions). For secondary data files I tend to use
<dbname>n_data.ndf where n is just a simple incrementing integer (1, 2,
3...), eg. mydb_Data.mdf, mydb_Log.ldf, mydb1_Data.ndf, mydb2_Data.ndf, etc.
CREATE DATABASE <dbname>, with no extra parameters, will create the
database with <dbname>.mdf & <dbname>_log.ldf but I've always thought
that to be inconsistent (with "_log" but not "_data") so I've always
gone with the recommendation of the GUI (with the _data & _log postfixes).
*mike hodgson*
blog: http://sqlnerd.blogspot.com
HarrySmith wrote:

>I have several user databases across several servers.
>Some of the physical data files are stored as databasename.mdf,
>databasename_data.mdf...
>Some of the physical log files are stored as databasename.ldf,
>databasename_log.ldf...
>Does any one has any naming convention suggestion on this?
>Currently SQL2K. Planning to move SQL2005 when it is available.
>Thanks,
>Harry
>
>

Data and log file naming conventions

I have several user databases across several servers.
Some of the physical data files are stored as databasename.mdf,
databasename_data.mdf...
Some of the physical log files are stored as databasename.ldf,
databasename_log.ldf...
Does any one has any naming convention suggestion on this?
Currently SQL2K. Planning to move SQL2005 when it is available.
Thanks,
Harry
Use whatever convention is logical and meets your needs; the main thing
is to be consistent.
Personally, I tend to stick with the GUI default <dbname>_data.mdf &
<dbname>_log.ldf for everything I can (using default values for things,
if they're sensible & reasonable, tends to avoid the pain of having to
memorise different conventions). For secondary data files I tend to use
<dbname>n_data.ndf where n is just a simple incrementing integer (1, 2,
3...), eg. mydb_Data.mdf, mydb_Log.ldf, mydb1_Data.ndf, mydb2_Data.ndf, etc.
CREATE DATABASE <dbname>, with no extra parameters, will create the
database with <dbname>.mdf & <dbname>_log.ldf but I've always thought
that to be inconsistent (with "_log" but not "_data") so I've always
gone with the recommendation of the GUI (with the _data & _log postfixes).
*mike hodgson*
blog: http://sqlnerd.blogspot.com
HarrySmith wrote:

>I have several user databases across several servers.
>Some of the physical data files are stored as databasename.mdf,
>databasename_data.mdf...
>Some of the physical log files are stored as databasename.ldf,
>databasename_log.ldf...
>Does any one has any naming convention suggestion on this?
>Currently SQL2K. Planning to move SQL2005 when it is available.
>Thanks,
>Harry
>
>

Data and log file naming conventions

I have several user databases across several servers.
Some of the physical data files are stored as databasename.mdf,
databasename_data.mdf...
Some of the physical log files are stored as databasename.ldf,
databasename_log.ldf...
Does any one has any naming convention suggestion on this?
Currently SQL2K. Planning to move SQL2005 when it is available.
Thanks,
HarryThis is a multi-part message in MIME format.
--020803060805070804090702
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Use whatever convention is logical and meets your needs; the main thing
is to be consistent.
Personally, I tend to stick with the GUI default <dbname>_data.mdf &
<dbname>_log.ldf for everything I can (using default values for things,
if they're sensible & reasonable, tends to avoid the pain of having to
memorise different conventions). For secondary data files I tend to use
<dbname>n_data.ndf where n is just a simple incrementing integer (1, 2,
3...), eg. mydb_Data.mdf, mydb_Log.ldf, mydb1_Data.ndf, mydb2_Data.ndf, etc.
CREATE DATABASE <dbname>, with no extra parameters, will create the
database with <dbname>.mdf & <dbname>_log.ldf but I've always thought
that to be inconsistent (with "_log" but not "_data") so I've always
gone with the recommendation of the GUI (with the _data & _log postfixes).
--
*mike hodgson*
blog: http://sqlnerd.blogspot.com
HarrySmith wrote:
>I have several user databases across several servers.
>Some of the physical data files are stored as databasename.mdf,
>databasename_data.mdf...
>Some of the physical log files are stored as databasename.ldf,
>databasename_log.ldf...
>Does any one has any naming convention suggestion on this?
>Currently SQL2K. Planning to move SQL2005 when it is available.
>Thanks,
>Harry
>
>
--020803060805070804090702
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>Use whatever convention is logical and meets your needs; the main
thing is to be consistent.<br>
<br>
Personally, I tend to stick with the GUI default
<dbname>_data.mdf & <dbname>_log.ldf for everything I
can (using default values for things, if they're sensible &
reasonable, tends to avoid the pain of having to memorise different
conventions). For secondary data files I tend to use
<dbname>n_data.ndf where n is just a simple incrementing integer
(1, 2, 3...), eg. mydb_Data.mdf, mydb_Log.ldf, mydb1_Data.ndf,
mydb2_Data.ndf, etc.<br>
<br>
CREATE DATABASE <dbname>, with no extra parameters, will create
the database with <dbname>.mdf & <dbname>_log.ldf but
I've always thought that to be inconsistent (with "_log" but not
"_data") so I've always gone with the recommendation of the GUI (with
the _data & _log postfixes).<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2">blog:</font><font face="Tahoma" size="2"> <a
href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
HarrySmith wrote:
<blockquote cite="midONj5bFAtFHA.3604@.tk2msftngp13.phx.gbl" type="cite">
<pre wrap="">I have several user databases across several servers.
Some of the physical data files are stored as databasename.mdf,
databasename_data.mdf...
Some of the physical log files are stored as databasename.ldf,
databasename_log.ldf...
Does any one has any naming convention suggestion on this?
Currently SQL2K. Planning to move SQL2005 when it is available.
Thanks,
Harry
</pre>
</blockquote>
</body>
</html>
--020803060805070804090702--

Damaged database

I in a real catastrophic scenario in a SQL2000
installation:
Due to a hardware failure:
I HAVE A SINGLE MDF, NOTHING ELSE .
The database originally was formed with this single mdf
and a single ldf, and its recovery model was simple.
I tried sp_attach_single_file_db as documented, but it
returned errors.
I tried to out smart the engine and create a database
with same name, and BORROW its log, but when attaching
with my lonely mdf, the engine returned a logical error
massage telling me that these two files doesn't belong .
Please can anyone help ?If that didn't work then you're in a bit of trouble but you can try this
**Make sure you have a copy of your MDF first and a backup of master**
Replace the filenames with your filename !!!
1) Make sure you have a copy of PowerDVD301_2_Data.MDF
2) Create a new database called fake (default file locations)
3) Stop SQL Service
4) Delete the fake_Data.MDF and copy PowerDVD301_2_Data.MDF
to where fake_Data.MDF used to be and rename the file to fake_Data.MDF
5) Start SQL Service
6) Database fake will appear as suspect in EM
7) Open Query Analyser and in master database run the following :
sp_configure 'allow updates',1
go
reconfigure with override
go
update sysdatabases set
status=-32768 where dbid=DB_ID('fake')
go
sp_configure 'allow updates',0
go
reconfigure with override
go
This will put the database in emergency recovery mode
8) Stop SQL Service
9) Delete the fake_Log.LDF file
10) Restart SQL Service
11) In QA run the following (with correct path for log)
dbcc rebuild_log('fake','h:\fake_log.ldf')
go
dbcc checkdb('fake') -- to check for errors
go
12) Now we need to rename the files, run the following (make sure
there are no connections to it) in Query Analyser
(At this stage you can actually access the database so you could use
DTS or bcp to move the data to another database .)
use master
go
sp_helpdb 'fake'
go
/* Make a note of the names of the files , you will need them
in the next bit of the script to replace datafilename and
logfilename - it might be that they have the right names */
sp_renamedb 'fake','PowerDVD301'
go
alter database PowerDVD301
MODIFY FILE(NAME='datafilename', NEWNAME = 'PowerDVD301_Data')
go
alter database PowerDVD301
MODIFY FILE(NAME='logfilename', NEWNAME = 'PowerDVD301_Log')
go
dbcc checkdb('PowerDVD301')
go
sp_dboption 'PowerDVD301','dbo use only','false'
go
use PowerDVD301
go
sp_updatestats
go
13) You should now have a working database. However the log file
will be small so it will be worth increasing its size
Unfortunately your files will be called fake_Data.MDF and
fake_Log.LDF but you can get round this by detaching the
database properly and then renaming the files and reattaching
it
14) Run the following in QA
sp_detach_db PowerDVD301
--now rename the files then reattach
sp_attach_db 'PowerDVD301','h:\dvd.mdf','h:\DVD.ldf'
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"ATS967" <anonymous@.discussions.microsoft.com> wrote in message
news:098d01c3a06f$83c18e20$a301280a@.phx.gbl...
I in a real catastrophic scenario in a SQL2000
installation:
Due to a hardware failure:
I HAVE A SINGLE MDF, NOTHING ELSE .
The database originally was formed with this single mdf
and a single ldf, and its recovery model was simple.
I tried sp_attach_single_file_db as documented, but it
returned errors.
I tried to out smart the engine and create a database
with same name, and BORROW its log, but when attaching
with my lonely mdf, the engine returned a logical error
massage telling me that these two files doesn't belong .
Please can anyone help ?

damaged data file

Hi, I have been given a damaged SQL 7 .mdf file of which rhere is no backup.
I have tried attaching but get a device activation error with a reference to
the original log file (which no longer exists).
Is there anything I can try to recover the file for these people?
God bless,
Stuart HawkinsI've never used it, but it is the only one I know of. Short of that, I think
your only option is to open a case with MS Support.
http://www.officerecovery.com/mssql/
(I presume you've already tried sp_attach_single_file_db.)
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Stuart Hawkins" <stuart@.sanctified.ltd.uk> wrote in message
news:%239LX7I7pDHA.1488@.TK2MSFTNGP12.phx.gbl...
> Hi, I have been given a damaged SQL 7 .mdf file of which rhere is no
backup.
> I have tried attaching but get a device activation error with a reference
to
> the original log file (which no longer exists).
> Is there anything I can try to recover the file for these people?
> God bless,
> Stuart Hawkins
>|||Worked great thank you. (I had tried sp_attach_single_file_db!)
God bless,
Stuart Hawkins
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:eMjUXk7pDHA.2772@.TK2MSFTNGP12.phx.gbl...
> I've never used it, but it is the only one I know of. Short of that, I
think
> your only option is to open a case with MS Support.
> http://www.officerecovery.com/mssql/
> (I presume you've already tried sp_attach_single_file_db.)
>
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Stuart Hawkins" <stuart@.sanctified.ltd.uk> wrote in message
> news:%239LX7I7pDHA.1488@.TK2MSFTNGP12.phx.gbl...
> > Hi, I have been given a damaged SQL 7 .mdf file of which rhere is no
> backup.
> > I have tried attaching but get a device activation error with a
reference
> to
> > the original log file (which no longer exists).
> >
> > Is there anything I can try to recover the file for these people?
> >
> > God bless,
> > Stuart Hawkins
> >
> >
>