restore database back to sql 7.0?

Hi,
I have a SQL7.0 (NT4) with all the databases. Recently a new SQL 2000
(win2k) is installed. I was transferring one of the database (db1) from 7.0
to 2000 by using copy database wizard. But I found that instead of copying,
it actually moved the db1 to the new server. Since the new server is not in
production stage, I want to restore the db1 back to 7.0. I can see the data
and log files are still there. How can I have it operational again?
Please help me with this, I would greatly appreciate it.
Thanks a lot,
Sarah
Are the original data and log files still on the 7.0 server? If so, you can
reattach the original files to 7.0 using sp_attach_db. Otherwise, you'll
need to restore the 7.0 database from a 7.0 backup.
"SG" <sguo@.coopervision.ca> wrote in message
news:Oc5Dmw0zFHA.1252@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have a SQL7.0 (NT4) with all the databases. Recently a new SQL 2000
> (win2k) is installed. I was transferring one of the database (db1) from
> 7.0 to 2000 by using copy database wizard. But I found that instead of
> copying, it actually moved the db1 to the new server. Since the new server
> is not in production stage, I want to restore the db1 back to 7.0. I can
> see the data and log files are still there. How can I have it operational
> again?
> Please help me with this, I would greatly appreciate it.
> Thanks a lot,
> Sarah
>
|||SG,
As I understand it a restore from version 2000 to 7 is not possible and
would require the database/objects/data to be recreated via scripts, DTS
and/or BCP.
Was there a script location that contains the schema and data files for the
upgrade? Might be able to leverage from that.
HTH
Jerry
"SG" <sguo@.coopervision.ca> wrote in message
news:Oc5Dmw0zFHA.1252@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have a SQL7.0 (NT4) with all the databases. Recently a new SQL 2000
> (win2k) is installed. I was transferring one of the database (db1) from
> 7.0 to 2000 by using copy database wizard. But I found that instead of
> copying, it actually moved the db1 to the new server. Since the new server
> is not in production stage, I want to restore the db1 back to 7.0. I can
> see the data and log files are still there. How can I have it operational
> again?
> Please help me with this, I would greatly appreciate it.
> Thanks a lot,
> Sarah
>
|||SG,
Optionally if you have a backup of the v7 database you could just restore
it - would be alot easier if the backup exists!
HTH
Jerry
"SG" <sguo@.coopervision.ca> wrote in message
news:Oc5Dmw0zFHA.1252@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have a SQL7.0 (NT4) with all the databases. Recently a new SQL 2000
> (win2k) is installed. I was transferring one of the database (db1) from
> 7.0 to 2000 by using copy database wizard. But I found that instead of
> copying, it actually moved the db1 to the new server. Since the new server
> is not in production stage, I want to restore the db1 back to 7.0. I can
> see the data and log files are still there. How can I have it operational
> again?
> Please help me with this, I would greatly appreciate it.
> Thanks a lot,
> Sarah
>
|||Thanks Lori and Jerry for your quick response. Both of you have a same
solution which is to use backup. We are using veritas backup exec to backup
SQL7.0. I've been trying to restore the database to the server. For some
reason, all failed.
Yes, all of the data and log files are still on SQL 7.0 under
\mssql\data\db1_data.mdf and db1_log.ldf.
I will try Lori, reattach the db1 to see first, if it is not working, I will
come back to you.
Thanks a lot,
Sarah
"SG" <sguo@.coopervision.ca> wrote in message
news:Oc5Dmw0zFHA.1252@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have a SQL7.0 (NT4) with all the databases. Recently a new SQL 2000
> (win2k) is installed. I was transferring one of the database (db1) from
> 7.0 to 2000 by using copy database wizard. But I found that instead of
> copying, it actually moved the db1 to the new server. Since the new server
> is not in production stage, I want to restore the db1 back to 7.0. I can
> see the data and log files are still there. How can I have it operational
> again?
> Please help me with this, I would greatly appreciate it.
> Thanks a lot,
> Sarah
>
|||Hi Lori,
I tried, query:
sp_attach_db 'db1', 'd:\mssql7\data\db1_data.mdf',
'd:\mssql7\data\db1_log.ldf'
But I got the error message saying, "I/O error (bad page id)......
connection broken.
What does this mean? Is the file corrupt?
Thanks,
Sarah
"SG" <sguo@.coopervision.ca> wrote in message
news:uZMVgA1zFHA.908@.tk2msftngp13.phx.gbl...
> Thanks Lori and Jerry for your quick response. Both of you have a same
> solution which is to use backup. We are using veritas backup exec to
> backup SQL7.0. I've been trying to restore the database to the server. For
> some reason, all failed.
> Yes, all of the data and log files are still on SQL 7.0 under
> \mssql\data\db1_data.mdf and db1_log.ldf.
> I will try Lori, reattach the db1 to see first, if it is not working, I
> will come back to you.
> Thanks a lot,
> Sarah
> "SG" <sguo@.coopervision.ca> wrote in message
> news:Oc5Dmw0zFHA.1252@.TK2MSFTNGP09.phx.gbl...
>
|||Not sure, but usually I/O errors indicate database corruption or hardware
issues. Did you do a dbcc checkdb before you did the copy/move? Are you
sure that the files you're trying to attach are SQL 7.0 files?
"SG" <sguo@.coopervision.ca> wrote in message
news:e4bFrp2zFHA.3180@.TK2MSFTNGP14.phx.gbl...
> Hi Lori,
> I tried, query:
> sp_attach_db 'db1', 'd:\mssql7\data\db1_data.mdf',
> 'd:\mssql7\data\db1_log.ldf'
> But I got the error message saying, "I/O error (bad page id)......
> connection broken.
> What does this mean? Is the file corrupt?
> Thanks,
> Sarah
> "SG" <sguo@.coopervision.ca> wrote in message
> news:uZMVgA1zFHA.908@.tk2msftngp13.phx.gbl...
>
|||attach is only guaranteed if you actually detached the database first. Are you saying that you don't
have a database backup of the 7.0 database? That you only "ripped" the database files using Veritas?
You could try creating a database on the 7.0 server with same db name and same file names, stop the
SQL Server, delete the new files and copy your old files in place of the new ones.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SG" <sguo@.coopervision.ca> wrote in message news:e4bFrp2zFHA.3180@.TK2MSFTNGP14.phx.gbl...
> Hi Lori,
> I tried, query:
> sp_attach_db 'db1', 'd:\mssql7\data\db1_data.mdf', 'd:\mssql7\data\db1_log.ldf'
> But I got the error message saying, "I/O error (bad page id)......
> connection broken.
> What does this mean? Is the file corrupt?
> Thanks,
> Sarah
> "SG" <sguo@.coopervision.ca> wrote in message news:uZMVgA1zFHA.908@.tk2msftngp13.phx.gbl...
>
|||Hi Lori,
Yes, I'm sure that file is SQL7.0. But I forgot one thing, while I was doing
"Copy database" from 7.0 to 2k, there was an error "detach failed". I guess
that is where the problem was. Instead of copying, it did move. Before done
this database, I did a small database test, it was fine. Don't know what
happened.
Any idea?
Thanks,
Sarah
"Lori Clark" <lclark@.dbadvisor.com> wrote in message
news:uSfds42zFHA.904@.tk2msftngp13.phx.gbl...
> Not sure, but usually I/O errors indicate database corruption or hardware
> issues. Did you do a dbcc checkdb before you did the copy/move? Are you
> sure that the files you're trying to attach are SQL 7.0 files?
> "SG" <sguo@.coopervision.ca> wrote in message
> news:e4bFrp2zFHA.3180@.TK2MSFTNGP14.phx.gbl...
>
|||Hi Tibor,
I did get an error while I was copy the database about "Detach database
failed".
We have Veritas backup agent for SQL on SQL 7.0 server, it should actually
backup the database not only "ripped" the files. I might need to try your
suggestion in this case. If I create the new database, I like to use the
data file, but not the log file. Can I use new log file with the old data
file?
It turned really mess now. Does anybody have any note before I do the "copy
database wizard"? I do have another very important database needs to be
transferred from 7.0 to 2k. I cannot afford to have the same mistake.
Appreciate your idea,
Sarah
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Oa3uJ67zFHA.464@.TK2MSFTNGP15.phx.gbl...
> attach is only guaranteed if you actually detached the database first. Are
> you saying that you don't have a database backup of the 7.0 database? That
> you only "ripped" the database files using Veritas? You could try creating
> a database on the 7.0 server with same db name and same file names, stop
> the SQL Server, delete the new files and copy your old files in place of
> the new ones.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "SG" <sguo@.coopervision.ca> wrote in message
> news:e4bFrp2zFHA.3180@.TK2MSFTNGP14.phx.gbl...
>