recover northstar

I'm new to SQL server.
We have a windows 2003 server, and I installed SQL server
2000 on C and database file on D. I tried to follow a
book to use SQL server to backup Northwind database on
another disk E:
First I backuped the northwind database on E, then I
tried to restore it to E: and gave it another name in the
restore as field: E:\test.ldf E:\test.mdf
It restored OK, then I saw two files in E: test.ldf. and
test.mdf
Then I thought I can delete these two testing files.
It didn't allow me to do so and gave message you can't
delete them because another program is using it. Then I
close SQL server, this time I can delete them.
But when I start SQL server again, I found the NOrthwind
database is missing, no items and suspect shown there.
The worst thing is I deleted the backup file too before I
found this.
What should I do now to recover the northstar database,
do I need to reinstall it? and I can't figure out why I
deleted NOrthwind database in C while I'm not intend to
do so?
Thanks for any suggestionsAnn,
Its a bad practice to delete the mdf and ldf files directly from the OS
level irrespective whether SQL Server is running or not. did you check the
recycle bin if the files are there?If you want to delete a database, use the
T-SQL command DROP DATABASE or enterprise manager after making sure that no
connection is accessing it.If you have only deleted the .mdf and .ldf files
for the test database then it shouldnt affect Northwind database.May be ,
accidentally, you deleted the other files too.Is the Northwind database
being shown as 'suspect'? If so, it may be because the files are
missing.Doublecheck that by referring the errorlogs.Try deleting the
database. The script to recreate Northwind is also installed with sql
setup.Search for the file named 'instnwnd.sql ' in the SQL Server machine
and execute it .If done properly, this would recreate Northwind complete
with data and all stuff.You can either use Query analyzer or osql utility as
explained in:
Northwind Sample Database
http://msdn.microsoft.com/library/?url=/library/en-us/tsqlref/ts_north_2ch1.asp?frame=true
In case you dont find the file in the server, you can find a copy in the SQL
setup CD.
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Ann" <xiaowei.cao@.state.mn.us> wrote in message
news:0b4501c34cab$75c46670$a001280a@.phx.gbl...
> I'm new to SQL server.
> We have a windows 2003 server, and I installed SQL server
> 2000 on C and database file on D. I tried to follow a
> book to use SQL server to backup Northwind database on
> another disk E:
> First I backuped the northwind database on E, then I
> tried to restore it to E: and gave it another name in the
> restore as field: E:\test.ldf E:\test.mdf
> It restored OK, then I saw two files in E: test.ldf. and
> test.mdf
> Then I thought I can delete these two testing files.
> It didn't allow me to do so and gave message you can't
> delete them because another program is using it. Then I
> close SQL server, this time I can delete them.
> But when I start SQL server again, I found the NOrthwind
> database is missing, no items and suspect shown there.
> The worst thing is I deleted the backup file too before I
> found this.
> What should I do now to recover the northstar database,
> do I need to reinstall it? and I can't figure out why I
> deleted NOrthwind database in C while I'm not intend to
> do so?
> Thanks for any suggestions
>
>|||Ann,
Don't know the exact steps, but I think what you may have done is
restored the Northwind database from the backup file and told it to move
the physical database files (.mdf (data) and .ldf (log)) to the E drive.
This did not create a new database with the new file names, it restored
the existing database into the new filenames, and deleted the original
database files.
When you tried to delete the files and were told the files were in use,
SQL Server was using them.
When you deleted these files from disk after stopping SQL server, it
caused SQL server to mark the database suspect (unavailable/corrupted).
SQL server databases are not file based databases, you should ignore any
files created by SQL server except the backup files generated by the
backup process.
To create a copy of a database, backup the database, create a new
database with a different database name, restore the backup file to this
new database you just created. Make sure you check the force restore
flag in the options tab of the restore dialog.
Abel Fontes
Ann wrote:
> I'm new to SQL server.
> We have a windows 2003 server, and I installed SQL server
> 2000 on C and database file on D. I tried to follow a
> book to use SQL server to backup Northwind database on
> another disk E:
> First I backuped the northwind database on E, then I
> tried to restore it to E: and gave it another name in the
> restore as field: E:\test.ldf E:\test.mdf
> It restored OK, then I saw two files in E: test.ldf. and
> test.mdf
> Then I thought I can delete these two testing files.
> It didn't allow me to do so and gave message you can't
> delete them because another program is using it. Then I
> close SQL server, this time I can delete them.
> But when I start SQL server again, I found the NOrthwind
> database is missing, no items and suspect shown there.
> The worst thing is I deleted the backup file too before I
> found this.
> What should I do now to recover the northstar database,
> do I need to reinstall it? and I can't figure out why I
> deleted NOrthwind database in C while I'm not intend to
> do so?
> Thanks for any suggestions
>
>