Big Problem Sql server 2000 database crash

I have a small sql server 2000 database that has never been backed up. The
drive where the main database file resides failed on the server but the
drive where the transaction logs is still good.
Can I recover the database from just the transaction logs? And can you
point me to some documentation on how to do this?
Thank you
JoelYou can't restore any transaction logs without first restoring a full
backup.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Joel" <nowhere@.nowhere.com> wrote in message
news:%23gyxCqhxGHA.1248@.TK2MSFTNGP02.phx.gbl...
I have a small sql server 2000 database that has never been backed up. The
drive where the main database file resides failed on the server but the
drive where the transaction logs is still good.
Can I recover the database from just the transaction logs? And can you
point me to some documentation on how to do this?
Thank you
Joel|||If I remember correctly, you can't even back up the transaction log
without first backing up the database.
Roy Harvey
Beacon Falls, CT
On Tue, 22 Aug 2006 15:47:31 -0400, "Tom Moreau"
<tom@.dont.spam.me.cips.ca> wrote:
>You can't restore any transaction logs without first restoring a full
>backup.
>--
> Tom
>----
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Toronto, ON Canada|||Roy Harvey wrote:
> If I remember correctly, you can't even back up the transaction log
> without first backing up the database.
>
And for the OP, the moral of this story is...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I know it might be stretching it a bit, but can I recreate a database and
then somehow use the log file to repopulate the database file?
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OSHYaPixGHA.2260@.TK2MSFTNGP03.phx.gbl...
> You can't restore any transaction logs without first restoring a full
> backup.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Joel" <nowhere@.nowhere.com> wrote in message
> news:%23gyxCqhxGHA.1248@.TK2MSFTNGP02.phx.gbl...
> I have a small sql server 2000 database that has never been backed up.
> The
> drive where the main database file resides failed on the server but the
> drive where the transaction logs is still good.
> Can I recover the database from just the transaction logs? And can you
> point me to some documentation on how to do this?
> Thank you
> Joel
>|||The moral is even if it is a semi "test" database back it up.
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:44EB6274.9080507@.realsqlguy.com...
> Roy Harvey wrote:
>> If I remember correctly, you can't even back up the transaction log
>> without first backing up the database.
> And for the OP, the moral of this story is...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Joel wrote:
> The moral is even if it is a semi "test" database back it up.
>
Bingo!!
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Nope, that's not possible.
Bob
"Joel" wrote:
> I know it might be stretching it a bit, but can I recreate a database and
> then somehow use the log file to repopulate the database file?
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:OSHYaPixGHA.2260@.TK2MSFTNGP03.phx.gbl...
> > You can't restore any transaction logs without first restoring a full
> > backup.
> >
> > --
> > Tom
> >
> > ----
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > SQL Server MVP
> > Toronto, ON Canada
> > .
> > "Joel" <nowhere@.nowhere.com> wrote in message
> > news:%23gyxCqhxGHA.1248@.TK2MSFTNGP02.phx.gbl...
> > I have a small sql server 2000 database that has never been backed up.
> > The
> > drive where the main database file resides failed on the server but the
> > drive where the transaction logs is still good.
> >
> > Can I recover the database from just the transaction logs? And can you
> > point me to some documentation on how to do this?
> >
> > Thank you
> >
> > Joel
> >
> >
>
>

Best way to move datafile from C drive to D

I obviously did not search the archives on the right terms so what is
the easiest and fastest way to move a 3G database from a nearly full C
drive to the nearly empty D drive that should have been used.

I could back it up, drop it, recreate it using the D drive, and restore
it but it seems like there should be a way to just move the datafile
and use if from the new location.

I am thinking that detatch/attach is the best method, but I would like
confirmation or suggestions on how to proceed or things to be aware of
when using this method.

-- Mark D Powell --the safest method is to do a backup and restore; however, you can
detach, move, and reattach (usually) with no problems. Of coruse,
before doing any operation such as this, you should take a backup first
(which is why the first method is recommended).

Stu

Mark D Powell wrote:

Quote:

Originally Posted by

I obviously did not search the archives on the right terms so what is
the easiest and fastest way to move a 3G database from a nearly full C
drive to the nearly empty D drive that should have been used.
>
I could back it up, drop it, recreate it using the D drive, and restore
it but it seems like there should be a way to just move the datafile
and use if from the new location.
>
I am thinking that detatch/attach is the best method, but I would like
confirmation or suggestions on how to proceed or things to be aware of
when using this method.
>
-- Mark D Powell --

|||Stu (stuart.ainsworth@.gmail.com) writes:

Quote:

Originally Posted by

the safest method is to do a backup and restore; however, you can
detach, move, and reattach (usually) with no problems. Of coruse,
before doing any operation such as this, you should take a backup first
(which is why the first method is recommended).

The advantage with detach/attach is that you don't have to wait for
the new data file to be initiated. For a 3GB database that's maybe 5-10
minutes on SQL 2000. SQL 2005 has some quick initiation under some circum-
stances.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog wrote:

Quote:

Originally Posted by

Stu (stuart.ainsworth@.gmail.com) writes:

Quote:

Originally Posted by

the safest method is to do a backup and restore; however, you can
detach, move, and reattach (usually) with no problems. Of coruse,
before doing any operation such as this, you should take a backup first
(which is why the first method is recommended).

>
The advantage with detach/attach is that you don't have to wait for
the new data file to be initiated. For a 3GB database that's maybe 5-10
minutes on SQL 2000. SQL 2005 has some quick initiation under some circum-
stances.
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

OK, the plan is

1- that the customer is going to try to lock the end-users out of the
application
2- I will backup the database
3- I will detatch it
4- I will copy the files to the new location
5- I will attach the db
6- I will test accessing some data
7- the customer will open the application

In the event of serious problems I will drop the database, recreate it,
restore using the backup over the new version, and fix the user access.

Sounds easy enough.
-- Mark D Powell --|||Mark D Powell (Mark.Powell@.eds.com) writes:

Quote:

Originally Posted by

In the event of serious problems I will drop the database, recreate it,
restore using the backup over the new version, and fix the user access.

You don't have to create the database prior to restoring it. RESTORE will
do that for you.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog wrote:

Quote:

Originally Posted by

Mark D Powell (Mark.Powell@.eds.com) writes:

Quote:

Originally Posted by

In the event of serious problems I will drop the database, recreate it,
restore using the backup over the new version, and fix the user access.

>
You don't have to create the database prior to restoring it. RESTORE will
do that for you.
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

I know a restore can recreate a database but since I want to change the
location of the datafiles will it in this case allow me to change them
on the screen where it shows the datafile names? I have changed them
when I overlaid a production database with a test (upgrade) version
that ran on the same server but I changed them to existing files for
the existing producion database that I wanted to overlay.

I should take a few minutes and try this on the test server.

Thanks -- Mark --|||You want to use the WITH MOVE option; check the BOL. However, detach,
move, and attach should work fine.

HTH,
Stu

Mark D Powell wrote:

Quote:

Originally Posted by

Erland Sommarskog wrote:

Quote:

Originally Posted by

Mark D Powell (Mark.Powell@.eds.com) writes:

Quote:

Originally Posted by

In the event of serious problems I will drop the database, recreate it,
restore using the backup over the new version, and fix the user access.

You don't have to create the database prior to restoring it. RESTORE will
do that for you.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

>
I know a restore can recreate a database but since I want to change the
location of the datafiles will it in this case allow me to change them
on the screen where it shows the datafile names? I have changed them
when I overlaid a production database with a test (upgrade) version
that ran on the same server but I changed them to existing files for
the existing producion database that I wanted to overlay.
>
I should take a few minutes and try this on the test server.
>
Thanks -- Mark --

|||Mark D Powell (Mark.Powell@.eds.com) writes:

Quote:

Originally Posted by

I know a restore can recreate a database but since I want to change the
location of the datafiles will it in this case allow me to change them
on the screen where it shows the datafile names?

Don't know what the screen permits, and I don't want to engage in guessing
what it does.

The syntax is:

RESTORE DATABASE db FROM FILE = 'yourdump.bak',
WITH MOVE 'logicalfilename1' TO 'newlocation.mdf',
MOVE 'logicalfilename2' TO 'newlocation.ldf',
REPLACE

The logical file names talked about here, are those you see when you dp
sp_helpdb in the first column in the second result set. You can also
retrieve this with RESTORE FILELISTONLY.

The command above with create the database files, and then restore the
backup.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog wrote:

Quote:

Originally Posted by

Mark D Powell (Mark.Powell@.eds.com) writes:

Quote:

Originally Posted by

I know a restore can recreate a database but since I want to change the
location of the datafiles will it in this case allow me to change them
on the screen where it shows the datafile names?

>
Don't know what the screen permits, and I don't want to engage in guessing
what it does.
>
The syntax is:
>
RESTORE DATABASE db FROM FILE = 'yourdump.bak',
WITH MOVE 'logicalfilename1' TO 'newlocation.mdf',
MOVE 'logicalfilename2' TO 'newlocation.ldf',
REPLACE
>
The logical file names talked about here, are those you see when you dp
sp_helpdb in the first column in the second result set. You can also
retrieve this with RESTORE FILELISTONLY.
>
The command above with create the database files, and then restore the
backup.
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

I used attach/detach and it seemed to work fine. The slow part was
moving the 1.2G and 3.5G datafiles to the new locations. I swear the
copies took 3X longer the making the backups I made before I tried the
detatch/attach.

Thanks to all
-- Mark D Powell --