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,
SarahAre 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...
>> 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
>|||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...
>> 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
>>
>|||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...
>> 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,
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 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...
>> 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 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...
>> 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...
>> 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
>>
>>
>|||> We have Veritas backup agent for SQL on SQL 7.0 server, it should actually backup the database
If so, you are most probably fine. You would have a SQL Server backup of the database, and you can
use the TSQL RESTORE command to restore it.
> 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?
Most probably not. Read in Books Online about what the recovery process does and you will
understand. You could try without the log file and see if SQL Server can create a log file for you.
But if the attach failed, probably this will as well.
But first you need to determine whether you have a copy of the database file or a backup of the
database...
> Does anybody have any note before I do the "copy database wizard"?
Don't. Use sp_detach_db and sp_attach_db instead. Then you can use DTS for the other tasks that the
CDW has. This gives you control.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SG" <sguo@.coopervision.ca> wrote in message news:O84LYx$zFHA.3856@.tk2msftngp13.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...
>> 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...
>> 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 Tibor,
Thanks so much for your comments. I will check the backup again. That's the
best way.
Thanks,
sarah
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OH39WPA0FHA.720@.TK2MSFTNGP15.phx.gbl...
>> We have Veritas backup agent for SQL on SQL 7.0 server, it should
>> actually backup the database
> If so, you are most probably fine. You would have a SQL Server backup of
> the database, and you can use the TSQL RESTORE command to restore it.
>
>> 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?
> Most probably not. Read in Books Online about what the recovery process
> does and you will understand. You could try without the log file and see
> if SQL Server can create a log file for you. But if the attach failed,
> probably this will as well.
> But first you need to determine whether you have a copy of the database
> file or a backup of the database...
>
>> Does anybody have any note before I do the "copy database wizard"?
> Don't. Use sp_detach_db and sp_attach_db instead. Then you can use DTS for
> the other tasks that the CDW has. This gives you control.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "SG" <sguo@.coopervision.ca> wrote in message
> news:O84LYx$zFHA.3856@.tk2msftngp13.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...
>> 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...
>> 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
>>
>>
>>
>>
>