Restore mdf file: back

Showing posts with label back. Show all posts

Friday, March 30, 2012

Restore deleted table from log?

Hi,
If a table was deleted, is it possible to roll back the deletion with the
transaction log?
ThanksCheck out www.lumigent.com.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"BDB" <bdb@.reply.to.group.com> wrote in message
news:%23iSygiyYGHA.508@.TK2MSFTNGP02.phx.gbl...
Hi,
If a table was deleted, is it possible to roll back the deletion with the
transaction log?
Thanks|||Okay this is what I have:
BACKUP LOG mydb
TO DISK= 'mydblog200604180415.bak'
WITH NO_TRUNCATE, INIT
go
use master
go
RESTORE LOG mydb
FROM DISK= 'mydblog200604180415.bak'
WITH STOPAT = 'Apr 18, 2006 3:30 PM'
I get this error:
Msg 3117, Level 16, State 1, Line 2
The log or differential backup cannot be restored because no files are ready
to rollforward.
Msg 3013, Level 16, State 1, Line 2
RESTORE LOG is terminating abnormally.
Can anyone help?|||"BDB" <bdb@.reply.to.group.com> wrote in message
news:%23iSygiyYGHA.508@.TK2MSFTNGP02.phx.gbl...
> Hi,
> If a table was deleted, is it possible to roll back the deletion with the
> transaction log?
You can restore the database and then all logs up to the point in time just
before it was deleted.
If you mean recover the table w/o doing that, http://www.lumigent.com/ may
help.
> Thanks
>|||When you restore log, you first need to restore database, then the log backups, in order. I suggest
you read some in Books Online about backup and restore to get a grip on how this work. Also, you
might want to check out: http://www.karaszi.com/SQLServer/info_restore_log_several_times.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"BDB" <bdb@.reply.to.group.com> wrote in message news:%23$zI%23BzYGHA.428@.TK2MSFTNGP02.phx.gbl...
> Okay this is what I have:
> BACKUP LOG mydb
> TO DISK= 'mydblog200604180415.bak'
> WITH NO_TRUNCATE, INIT
> go
> use master
> go
> RESTORE LOG mydb
> FROM DISK= 'mydblog200604180415.bak'
> WITH STOPAT = 'Apr 18, 2006 3:30 PM'
> I get this error:
> Msg 3117, Level 16, State 1, Line 2
> The log or differential backup cannot be restored because no files are ready to rollforward.
> Msg 3013, Level 16, State 1, Line 2
> RESTORE LOG is terminating abnormally.
>
> Can anyone help?
>|||Just keep in mind the below point in the article to minimise impact on
production db.
* Restore into another database and copy the affected data into the
production database.
Thanks
Ajay Rengunthwarsql

restore deleted records

Hi i am currentlly using the SQL sever 2000, i am
wondering is there anyway to restore back the deleted
records which i had deleted at the SQL server table with
the default configuration?
Help it's urgent!!
Thanks...
Regards
sharonSHARON wrote:
> Hi i am currentlly using the SQL sever 2000, i am
> wondering is there anyway to restore back the deleted
> records which i had deleted at the SQL server table with
> the default configuration?
> Help it's urgent!!
> Thanks...
> Regards
> sharon
As long as you are not running simple recovery mode you can use
Lumigent Log explorer to do this http://www.lumigent.com/
Otherwise the only way would be to restore your latest backup.
p.s. if you arent using simple recovery mode and perform regular
transaction log backups you could also restore to a point in time.
--
Br,
Mark Broadbent
mcdba , mcse+i
=============

Restore DB table - SQL Server 2000

Hi,
I made a release on system at 12pm 22/02 and overwrote a table by
mistake.
I have a back from from the night before (12 at night) but from the
time I did a backup and and the time I did the release some records
would be lost.
I want to recover the data lost.
Can I examine the transaction logs to get back the data?
Is there a tool in SQL server to do this or is there a free one?
TIA,
Darren
This was replied to in another group. Please don't multipost.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<darren.daniel@.iclployalty.com> wrote in message
news:1141131966.097622.3760@.p10g2000cwp.googlegrou ps.com...
> Hi,
> I made a release on system at 12pm 22/02 and overwrote a table by
> mistake.
>
> I have a back from from the night before (12 at night) but from the
> time I did a backup and and the time I did the release some records
> would be lost.
> I want to recover the data lost.
> Can I examine the transaction logs to get back the data?
> Is there a tool in SQL server to do this or is there a free one?
> TIA,
> Darren
>

Wednesday, March 28, 2012

Restore DB - SQL Server 2000

Hi,
I made a release on system at 12pm 22/02 and overwrote a table by
mistake.
I have a back from from the night before (12 at night) but from the
time I did a backup and and the time I did the release some records
would be lost.
I want to recover the data lost.
Can I examine the transaction logs to get back the data?
What would I need to do?
TIA,
Darren
So ,have you done BACKUP LOG file?
If yop do , just apply all log'd backups WITH NORECOVERY option except the
last one
Visit at Tibor's web site to find a greate example ,how to restore a
database at point of time
<darren.daniel@.iclployalty.com> wrote in message
news:1141131856.938247.59340@.z34g2000cwc.googlegro ups.com...
> Hi,
> I made a release on system at 12pm 22/02 and overwrote a table by
> mistake.
>
> I have a back from from the night before (12 at night) but from the
> time I did a backup and and the time I did the release some records
> would be lost.
> I want to recover the data lost.
> Can I examine the transaction logs to get back the data?
> What would I need to do?
> TIA,
> Darren
>

Restore DB - SQL Server 2000

Hi,
I made a release on system at 12pm 22/02 and overwrote a table by
mistake.
I have a back from from the night before (12 at night) but from the
time I did a backup and and the time I did the release some records
would be lost.
I want to recover the data lost.
Can I examine the transaction logs to get back the data?
What would I need to do?
TIA,
DarrenSo ,have you done BACKUP LOG file?
If yop do , just apply all log'd backups WITH NORECOVERY option except the
last one
Visit at Tibor's web site to find a greate example ,how to restore a
database at point of time
<darren.daniel@.iclployalty.com> wrote in message
news:1141131856.938247.59340@.z34g2000cwc.googlegroups.com...
> Hi,
> I made a release on system at 12pm 22/02 and overwrote a table by
> mistake.
>
> I have a back from from the night before (12 at night) but from the
> time I did a backup and and the time I did the release some records
> would be lost.
> I want to recover the data lost.
> Can I examine the transaction logs to get back the data?
> What would I need to do?
> TIA,
> Darren
>

Restore DB - SQL Server 2000

Hi,
I made a release on system at 12pm 22/02 and overwrote a table by
mistake.
I have a back from from the night before (12 at night) but from the
time I did a backup and and the time I did the release some records
would be lost.
I want to recover the data lost.
Can I examine the transaction logs to get back the data?
What would I need to do?
TIA,
DarrenSo ,have you done BACKUP LOG file?
If yop do , just apply all log'd backups WITH NORECOVERY option except the
last one
Visit at Tibor's web site to find a greate example ,how to restore a
database at point of time
<darren.daniel@.iclployalty.com> wrote in message
news:1141131856.938247.59340@.z34g2000cwc.googlegroups.com...
> Hi,
> I made a release on system at 12pm 22/02 and overwrote a table by
> mistake.
>
> I have a back from from the night before (12 at night) but from the
> time I did a backup and and the time I did the release some records
> would be lost.
> I want to recover the data lost.
> Can I examine the transaction logs to get back the data?
> What would I need to do?
> TIA,
> Darren
>

Wednesday, March 21, 2012

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
>>
>>
>>
>>
>

restore database back to sql 7.0?

Tuesday, March 20, 2012

Restore Database

Hi Everybody,

I was restoring the database. I have 2 backup files one is complete backup and another is transaction log back file. First I Resotred the complete backup file it restored fine but while I was restoring the transaction log backup file it showed the following error.

The preceding restore operation did not specify WITH NORECOVERY or WITH STANDBY. Restart the restore sequence, specifying WITH NORECOVERY or WITHSTANDBY for all but the final step.
RESTORE DATABASE is terminating abnormally.

Please help me. How to restore the database if we have two backup files?Specify WITH NORECOVERY, and then restore the transaction log. After that, it is just

restore database (name) with recovery|||But I want to do this job from Enterprise manager. I didn't find the check option for the [with recovery].

Restore Database

I'm trying to create a DTS to back up the database from
one server and restore it to another server but it keeps
telling me that the database cannot be restored because it
is in use. The connection for the restore points to the
server I want to restore to and I even created a new
database to restore to and check under current activity
and it not in use. What am I doing wrong?
Thanks.First, DTS is not the way to back up and restore a
database. You should be using BACKUP and RESTORE. And if
you have a connection into the DB for some reason, that
will cause conflict. The DB can have no connection in.
So switching to a traditional BACKUP/RESTORE will
hopefully help your problem.
>--Original Message--
>I'm trying to create a DTS to back up the database from
>one server and restore it to another server but it keeps
>telling me that the database cannot be restored because
it
>is in use. The connection for the restore points to the
>server I want to restore to and I even created a new
>database to restore to and check under current activity
>and it not in use. What am I doing wrong?
>Thanks.
>.
>|||You can use dts to accomplish your task. You can use
robocopy and use dts to run it.
>--Original Message--
>I'm trying to make this an automated nightly thing. The
>DTS was doing the backup and restore. I think I now
>understand about the connection being the problem. I
need
>to back up a database on another server so I think I
need
>to use a DTS so I can get to it? I'll try changing to
>just have the back up in the DTS and then schedule it
and
>then modify the schedule to restore the database to the
>local server after the backup completes.
>Thanks.
>>--Original Message--
>>First, DTS is not the way to back up and restore a
>>database. You should be using BACKUP and RESTORE. And
if
>>you have a connection into the DB for some reason, that
>>will cause conflict. The DB can have no connection in.
>>So switching to a traditional BACKUP/RESTORE will
>>hopefully help your problem.
>>
>>--Original Message--
>>I'm trying to create a DTS to back up the database
from
>>one server and restore it to another server but it
keeps
>>telling me that the database cannot be restored
because
>>it
>>is in use. The connection for the restore points to
the
>>server I want to restore to and I even created a new
>>database to restore to and check under current
activity
>>and it not in use. What am I doing wrong?
>>Thanks.
>>.
>>.
>.
>

Friday, March 9, 2012

Restore backup file

I have a back up file of a database which is residing in a different server.
I need to restore the database with the .bak file which is available with
me. How should I proceed to set up this.RESTORE FILELISTONLY
FROM DISK = 'F:\backupfilename.bak'
This will provide you the logical filename of backup .mdf and .ldf file.
RESTORE DATABASE DB_NAME_TO_BE_RESTORED
FROM DISK = 'F:\backupfilename.bak'
WITH MOVE 'Logical_Name_Data' TO 'F:\MSSQL\DATA\Physical_Name_Data.mdf',
MOVE 'Logical_Data_Log' TO 'E:\MSSQL\LOG\Physical_Name_Log.ldf',
STATS = 1, REPLACE
GO
Please revert if you have any queries.
Thanks,
Sree
"VBB" wrote:
> I have a back up file of a database which is residing in a different server.
> I need to restore the database with the .bak file which is available with
> me. How should I proceed to set up this.|||I executed the following statements
*************************************
USE master
GO
RESTORE FILELISTONLY
FROM DISK = 'C:\Bkp\Xchange.BAK'
RESTORE DATABASE XChange_DB
FROM DISK = 'C:\Bkp\Xchange.BAK'
WITH MOVE 'L_XChange_Data' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\P_XChange.mdf',
MOVE 'L_XChange_Log' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\P_XChange.ldf',
STATS = 1, REPLACE
GO
***************************************************
I got the following error
(2 row(s) affected)
Server: Msg 3234, Level 16, State 2, Line 3
Logical file 'L_XChange_Data' is not part of database 'XChange_DB'. Use
RESTORE FILELISTONLY to list the logical file names.
Server: Msg 3013, Level 16, State 1, Line 3
RESTORE DATABASE is terminating abnormally.
Please let me know what has went wrong.
"Sreejith G" wrote:
> RESTORE FILELISTONLY
> FROM DISK = 'F:\backupfilename.bak'
> This will provide you the logical filename of backup .mdf and .ldf file.
> RESTORE DATABASE DB_NAME_TO_BE_RESTORED
> FROM DISK = 'F:\backupfilename.bak'
> WITH MOVE 'Logical_Name_Data' TO 'F:\MSSQL\DATA\Physical_Name_Data.mdf',
> MOVE 'Logical_Data_Log' TO 'E:\MSSQL\LOG\Physical_Name_Log.ldf',
> STATS = 1, REPLACE
> GO
> Please revert if you have any queries.
> Thanks,
> Sree
> "VBB" wrote:
> > I have a back up file of a database which is residing in a different server.
> > I need to restore the database with the .bak file which is available with
> > me. How should I proceed to set up this.|||You need to provide the logical filename that you got from the output of
"RESTORE FILELISTONLY" +>
RESTORE FILELISTONLY
FROM DISK = 'F:\backupfilename.bak'
as input of
RESTORE DATABASE DB_NAME_TO_BE_RESTORED
FROM DISK = 'F:\backupfilename.bak'
WITH MOVE 'Logical_Name_Data' TO 'F:\MSSQL\DATA\Physical_Name_Data.mdf',
MOVE 'Logical_Data_Log' TO 'E:\MSSQL\LOG\Physical_Name_Log.ldf',
STATS = 1, REPLACE
GO
Its not like you have to run that in one shot.
Thanks,
Sree
"VBB" wrote:
> I executed the following statements
> *************************************
> USE master
> GO
> RESTORE FILELISTONLY
> FROM DISK = 'C:\Bkp\Xchange.BAK'
> RESTORE DATABASE XChange_DB
> FROM DISK = 'C:\Bkp\Xchange.BAK'
> WITH MOVE 'L_XChange_Data' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\P_XChange.mdf',
> MOVE 'L_XChange_Log' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\P_XChange.ldf',
> STATS = 1, REPLACE
> GO
> ***************************************************
> I got the following error
>
> (2 row(s) affected)
> Server: Msg 3234, Level 16, State 2, Line 3
> Logical file 'L_XChange_Data' is not part of database 'XChange_DB'. Use
> RESTORE FILELISTONLY to list the logical file names.
> Server: Msg 3013, Level 16, State 1, Line 3
> RESTORE DATABASE is terminating abnormally.
> Please let me know what has went wrong.
> "Sreejith G" wrote:
> > RESTORE FILELISTONLY
> > FROM DISK = 'F:\backupfilename.bak'
> >
> > This will provide you the logical filename of backup .mdf and .ldf file.
> >
> > RESTORE DATABASE DB_NAME_TO_BE_RESTORED
> > FROM DISK = 'F:\backupfilename.bak'
> > WITH MOVE 'Logical_Name_Data' TO 'F:\MSSQL\DATA\Physical_Name_Data.mdf',
> > MOVE 'Logical_Data_Log' TO 'E:\MSSQL\LOG\Physical_Name_Log.ldf',
> > STATS = 1, REPLACE
> > GO
> >
> > Please revert if you have any queries.
> >
> > Thanks,
> > Sree
> >
> > "VBB" wrote:
> >
> > > I have a back up file of a database which is residing in a different server.
> > > I need to restore the database with the .bak file which is available with
> > > me. How should I proceed to set up this.|||IT WORKED.
Thank you very much for your help.
"Sreejith G" wrote:
> You need to provide the logical filename that you got from the output of
> "RESTORE FILELISTONLY" +>
> RESTORE FILELISTONLY
> FROM DISK = 'F:\backupfilename.bak'
> as input of
> RESTORE DATABASE DB_NAME_TO_BE_RESTORED
> FROM DISK = 'F:\backupfilename.bak'
> WITH MOVE 'Logical_Name_Data' TO 'F:\MSSQL\DATA\Physical_Name_Data.mdf',
> MOVE 'Logical_Data_Log' TO 'E:\MSSQL\LOG\Physical_Name_Log.ldf',
> STATS = 1, REPLACE
> GO
> Its not like you have to run that in one shot.
> Thanks,
> Sree
> "VBB" wrote:
> > I executed the following statements
> > *************************************
> > USE master
> > GO
> > RESTORE FILELISTONLY
> > FROM DISK = 'C:\Bkp\Xchange.BAK'
> > RESTORE DATABASE XChange_DB
> > FROM DISK = 'C:\Bkp\Xchange.BAK'
> > WITH MOVE 'L_XChange_Data' TO 'C:\Program Files\Microsoft SQL
> > Server\MSSQL\Data\P_XChange.mdf',
> > MOVE 'L_XChange_Log' TO 'C:\Program Files\Microsoft SQL
> > Server\MSSQL\Data\P_XChange.ldf',
> > STATS = 1, REPLACE
> > GO
> >
> > ***************************************************
> > I got the following error
> >
> >
> > (2 row(s) affected)
> >
> > Server: Msg 3234, Level 16, State 2, Line 3
> > Logical file 'L_XChange_Data' is not part of database 'XChange_DB'. Use
> > RESTORE FILELISTONLY to list the logical file names.
> > Server: Msg 3013, Level 16, State 1, Line 3
> > RESTORE DATABASE is terminating abnormally.
> >
> > Please let me know what has went wrong.
> >
> > "Sreejith G" wrote:
> >
> > > RESTORE FILELISTONLY
> > > FROM DISK = 'F:\backupfilename.bak'
> > >
> > > This will provide you the logical filename of backup .mdf and .ldf file.
> > >
> > > RESTORE DATABASE DB_NAME_TO_BE_RESTORED
> > > FROM DISK = 'F:\backupfilename.bak'
> > > WITH MOVE 'Logical_Name_Data' TO 'F:\MSSQL\DATA\Physical_Name_Data.mdf',
> > > MOVE 'Logical_Data_Log' TO 'E:\MSSQL\LOG\Physical_Name_Log.ldf',
> > > STATS = 1, REPLACE
> > > GO
> > >
> > > Please revert if you have any queries.
> > >
> > > Thanks,
> > > Sree
> > >
> > > "VBB" wrote:
> > >
> > > > I have a back up file of a database which is residing in a different server.
> > > > I need to restore the database with the .bak file which is available with
> > > > me. How should I proceed to set up this.|||Can you please provide me your email id. So that I can contact you if I have
any issues with SQL Server.
"Sreejith G" wrote:
> You need to provide the logical filename that you got from the output of
> "RESTORE FILELISTONLY" +>
> RESTORE FILELISTONLY
> FROM DISK = 'F:\backupfilename.bak'
> as input of
> RESTORE DATABASE DB_NAME_TO_BE_RESTORED
> FROM DISK = 'F:\backupfilename.bak'
> WITH MOVE 'Logical_Name_Data' TO 'F:\MSSQL\DATA\Physical_Name_Data.mdf',
> MOVE 'Logical_Data_Log' TO 'E:\MSSQL\LOG\Physical_Name_Log.ldf',
> STATS = 1, REPLACE
> GO
> Its not like you have to run that in one shot.
> Thanks,
> Sree
> "VBB" wrote:
> > I executed the following statements
> > *************************************
> > USE master
> > GO
> > RESTORE FILELISTONLY
> > FROM DISK = 'C:\Bkp\Xchange.BAK'
> > RESTORE DATABASE XChange_DB
> > FROM DISK = 'C:\Bkp\Xchange.BAK'
> > WITH MOVE 'L_XChange_Data' TO 'C:\Program Files\Microsoft SQL
> > Server\MSSQL\Data\P_XChange.mdf',
> > MOVE 'L_XChange_Log' TO 'C:\Program Files\Microsoft SQL
> > Server\MSSQL\Data\P_XChange.ldf',
> > STATS = 1, REPLACE
> > GO
> >
> > ***************************************************
> > I got the following error
> >
> >
> > (2 row(s) affected)
> >
> > Server: Msg 3234, Level 16, State 2, Line 3
> > Logical file 'L_XChange_Data' is not part of database 'XChange_DB'. Use
> > RESTORE FILELISTONLY to list the logical file names.
> > Server: Msg 3013, Level 16, State 1, Line 3
> > RESTORE DATABASE is terminating abnormally.
> >
> > Please let me know what has went wrong.
> >
> > "Sreejith G" wrote:
> >
> > > RESTORE FILELISTONLY
> > > FROM DISK = 'F:\backupfilename.bak'
> > >
> > > This will provide you the logical filename of backup .mdf and .ldf file.
> > >
> > > RESTORE DATABASE DB_NAME_TO_BE_RESTORED
> > > FROM DISK = 'F:\backupfilename.bak'
> > > WITH MOVE 'Logical_Name_Data' TO 'F:\MSSQL\DATA\Physical_Name_Data.mdf',
> > > MOVE 'Logical_Data_Log' TO 'E:\MSSQL\LOG\Physical_Name_Log.ldf',
> > > STATS = 1, REPLACE
> > > GO
> > >
> > > Please revert if you have any queries.
> > >
> > > Thanks,
> > > Sree
> > >
> > > "VBB" wrote:
> > >
> > > > I have a back up file of a database which is residing in a different server.
> > > > I need to restore the database with the .bak file which is available with
> > > > me. How should I proceed to set up this.|||sreejith.gopalakrishnan@.ustri.com
Always welcome!!!
Thanks
Sree
"VBB" wrote:
> Can you please provide me your email id. So that I can contact you if I have
> any issues with SQL Server.
> "Sreejith G" wrote:
> > You need to provide the logical filename that you got from the output of
> > "RESTORE FILELISTONLY" +>
> >
> > RESTORE FILELISTONLY
> > FROM DISK = 'F:\backupfilename.bak'
> >
> > as input of
> >
> > RESTORE DATABASE DB_NAME_TO_BE_RESTORED
> > FROM DISK = 'F:\backupfilename.bak'
> > WITH MOVE 'Logical_Name_Data' TO 'F:\MSSQL\DATA\Physical_Name_Data.mdf',
> > MOVE 'Logical_Data_Log' TO 'E:\MSSQL\LOG\Physical_Name_Log.ldf',
> > STATS = 1, REPLACE
> > GO
> >
> > Its not like you have to run that in one shot.
> >
> > Thanks,
> > Sree
> >
> > "VBB" wrote:
> >
> > > I executed the following statements
> > > *************************************
> > > USE master
> > > GO
> > > RESTORE FILELISTONLY
> > > FROM DISK = 'C:\Bkp\Xchange.BAK'
> > > RESTORE DATABASE XChange_DB
> > > FROM DISK = 'C:\Bkp\Xchange.BAK'
> > > WITH MOVE 'L_XChange_Data' TO 'C:\Program Files\Microsoft SQL
> > > Server\MSSQL\Data\P_XChange.mdf',
> > > MOVE 'L_XChange_Log' TO 'C:\Program Files\Microsoft SQL
> > > Server\MSSQL\Data\P_XChange.ldf',
> > > STATS = 1, REPLACE
> > > GO
> > >
> > > ***************************************************
> > > I got the following error
> > >
> > >
> > > (2 row(s) affected)
> > >
> > > Server: Msg 3234, Level 16, State 2, Line 3
> > > Logical file 'L_XChange_Data' is not part of database 'XChange_DB'. Use
> > > RESTORE FILELISTONLY to list the logical file names.
> > > Server: Msg 3013, Level 16, State 1, Line 3
> > > RESTORE DATABASE is terminating abnormally.
> > >
> > > Please let me know what has went wrong.
> > >
> > > "Sreejith G" wrote:
> > >
> > > > RESTORE FILELISTONLY
> > > > FROM DISK = 'F:\backupfilename.bak'
> > > >
> > > > This will provide you the logical filename of backup .mdf and .ldf file.
> > > >
> > > > RESTORE DATABASE DB_NAME_TO_BE_RESTORED
> > > > FROM DISK = 'F:\backupfilename.bak'
> > > > WITH MOVE 'Logical_Name_Data' TO 'F:\MSSQL\DATA\Physical_Name_Data.mdf',
> > > > MOVE 'Logical_Data_Log' TO 'E:\MSSQL\LOG\Physical_Name_Log.ldf',
> > > > STATS = 1, REPLACE
> > > > GO
> > > >
> > > > Please revert if you have any queries.
> > > >
> > > > Thanks,
> > > > Sree
> > > >
> > > > "VBB" wrote:
> > > >
> > > > > I have a back up file of a database which is residing in a different server.
> > > > > I need to restore the database with the .bak file which is available with
> > > > > me. How should I proceed to set up this.

restore back up from sql 2005 beta 2 to sql 2005 original

hai,

When i try to restore the backup which has been taken in sql2005 beta 2 and restore it in sql2005 original it is displaying the following error message.

System.Data.SqlClient.SqlError: The database was backed up on a server running version 9.00.0852. That version is incompatible with this server, which is running version 9.00.1399. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server. (Microsoft.SqlServer.Smo)

please let me know how i can restore the back.

I am moving this to the SQL Server Database Engine forum.|||You may have to script out the schema and then copy the data from the Beta2 database to the RTM (1399) version database. You might be able to use Visual Studio for Database Professionals, aka "DataDude" to do this.|||Your only other alternative is to restore from sql 2005 beta 2 to sql 2005 beta 3 and then from sql 2005 beta 3 to sql 2005 RTM

restore Back file to new server database

I've been reading articles on "how to restore a database to a new location",
"how to restore a database with a new name", etc.
What I need is a step by step on how to:
Using a preexisting database backup,
point it to a different server,
create that database on that different server,
apply the full back up,
do a "point in time" restore from the original database transaction log.
I thought I could do most ofthis but I am having NO luck in getting the
database to restore to a new database with the same name on a different
server.
How is this done?It is easier if you post the TSQL commands you are executing. Basically, you:
On the originating server:
BACKUP DATABASE dbname
BACKUP LOG dbname
BACKUP LOG dbname
On the destination server:
RESTORE DATABASE dbname ... WITH NORECOVERY...
If the database exists and the database file structure doesn't match with the original, you need to
use the REPLACE option. If you need to move files, you need to use the MOVE option for each file.
RESTORE LOG dbname... WITH NORECOVERY...
RESTORE LOG dbname... WITH RECOVERY, STOPAT.....
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"vidro" <vidro@.discussions.microsoft.com> wrote in message
news:839B9ED3-82F7-4A34-9FBA-E78FE88D36AA@.microsoft.com...
> I've been reading articles on "how to restore a database to a new location",
> "how to restore a database with a new name", etc.
> What I need is a step by step on how to:
> Using a preexisting database backup,
> point it to a different server,
> create that database on that different server,
> apply the full back up,
> do a "point in time" restore from the original database transaction log.
> I thought I could do most ofthis but I am having NO luck in getting the
> database to restore to a new database with the same name on a different
> server.
> How is this done?|||There seems to be a couple of things going on that I'm not sure about;
The following is the way I have things typed, I'm confused with the "From"
peremeter. If this is the device I'm restoring from, is this than going to
be the literal name of the back up file?
I also get the following error:
"One or more of the options (stopat) are not supported for this statement"
my comments typed in the Query Analyzer;
RECOVER DATABASE Market
FROM ?
WITH RECOVERY, STOPAT = 'SEPT 14, 2005 10:00 A.M'
"Tibor Karaszi" wrote:
> It is easier if you post the TSQL commands you are executing. Basically, you:
> On the originating server:
> BACKUP DATABASE dbname
> BACKUP LOG dbname
> BACKUP LOG dbname
> On the destination server:
> RESTORE DATABASE dbname ... WITH NORECOVERY...
> If the database exists and the database file structure doesn't match with the original, you need to
> use the REPLACE option. If you need to move files, you need to use the MOVE option for each file.
> RESTORE LOG dbname... WITH NORECOVERY...
> RESTORE LOG dbname... WITH RECOVERY, STOPAT.....
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "vidro" <vidro@.discussions.microsoft.com> wrote in message
> news:839B9ED3-82F7-4A34-9FBA-E78FE88D36AA@.microsoft.com...
> > I've been reading articles on "how to restore a database to a new location",
> > "how to restore a database with a new name", etc.
> > What I need is a step by step on how to:
> > Using a preexisting database backup,
> > point it to a different server,
> > create that database on that different server,
> > apply the full back up,
> > do a "point in time" restore from the original database transaction log.
> >
> > I thought I could do most ofthis but I am having NO luck in getting the
> > database to restore to a new database with the same name on a different
> > server.
> >
> > How is this done?
>|||FROM is either a predefined backupdevice (read about sp_addumpdevice):
RESTORE DATABASE Market
FROM myBackupDevice
WITH RECOVERY, STOPAT = '20051014 20:00'
Or a physical file name:
RESTORE DATABASE Market
FROM DISK = 'C:\myBackupDevice.BAK'
WITH RECOVERY, STOPAT = '20051014 20:00'
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"vidro" <vidro@.discussions.microsoft.com> wrote in message
news:4800BDEF-7112-4621-B287-0A91BA679B32@.microsoft.com...
> There seems to be a couple of things going on that I'm not sure about;
> The following is the way I have things typed, I'm confused with the "From"
> peremeter. If this is the device I'm restoring from, is this than going to
> be the literal name of the back up file?
> I also get the following error:
> "One or more of the options (stopat) are not supported for this statement"
> my comments typed in the Query Analyzer;
> RECOVER DATABASE Market
> FROM ?
> WITH RECOVERY, STOPAT = 'SEPT 14, 2005 10:00 A.M'
> "Tibor Karaszi" wrote:
>> It is easier if you post the TSQL commands you are executing. Basically, you:
>> On the originating server:
>> BACKUP DATABASE dbname
>> BACKUP LOG dbname
>> BACKUP LOG dbname
>> On the destination server:
>> RESTORE DATABASE dbname ... WITH NORECOVERY...
>> If the database exists and the database file structure doesn't match with the original, you need
>> to
>> use the REPLACE option. If you need to move files, you need to use the MOVE option for each file.
>> RESTORE LOG dbname... WITH NORECOVERY...
>> RESTORE LOG dbname... WITH RECOVERY, STOPAT.....
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "vidro" <vidro@.discussions.microsoft.com> wrote in message
>> news:839B9ED3-82F7-4A34-9FBA-E78FE88D36AA@.microsoft.com...
>> > I've been reading articles on "how to restore a database to a new location",
>> > "how to restore a database with a new name", etc.
>> > What I need is a step by step on how to:
>> > Using a preexisting database backup,
>> > point it to a different server,
>> > create that database on that different server,
>> > apply the full back up,
>> > do a "point in time" restore from the original database transaction log.
>> >
>> > I thought I could do most ofthis but I am having NO luck in getting the
>> > database to restore to a new database with the same name on a different
>> > server.
>> >
>> > How is this done?
>>|||And if I wanted to restore from the transaction log, the following being the
appopriate syntex?
RESTORE LOG market
FROM DRIVE = 'E:\DRIVE\market.trn OR market.lfd'
WITH RECOVERY, STOPAT = '20050914 20:00'
"Tibor Karaszi" wrote:
> FROM is either a predefined backupdevice (read about sp_addumpdevice):
> RESTORE DATABASE Market
> FROM myBackupDevice
> WITH RECOVERY, STOPAT = '20051014 20:00'
>
> Or a physical file name:
> RESTORE DATABASE Market
> FROM DISK = 'C:\myBackupDevice.BAK'
> WITH RECOVERY, STOPAT = '20051014 20:00'
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "vidro" <vidro@.discussions.microsoft.com> wrote in message
> news:4800BDEF-7112-4621-B287-0A91BA679B32@.microsoft.com...
> > There seems to be a couple of things going on that I'm not sure about;
> > The following is the way I have things typed, I'm confused with the "From"
> > peremeter. If this is the device I'm restoring from, is this than going to
> > be the literal name of the back up file?
> >
> > I also get the following error:
> > "One or more of the options (stopat) are not supported for this statement"
> >
> > my comments typed in the Query Analyzer;
> >
> > RECOVER DATABASE Market
> > FROM ?
> > WITH RECOVERY, STOPAT = 'SEPT 14, 2005 10:00 A.M'
> >
> > "Tibor Karaszi" wrote:
> >
> >> It is easier if you post the TSQL commands you are executing. Basically, you:
> >>
> >> On the originating server:
> >> BACKUP DATABASE dbname
> >> BACKUP LOG dbname
> >> BACKUP LOG dbname
> >>
> >> On the destination server:
> >> RESTORE DATABASE dbname ... WITH NORECOVERY...
> >> If the database exists and the database file structure doesn't match with the original, you need
> >> to
> >> use the REPLACE option. If you need to move files, you need to use the MOVE option for each file.
> >> RESTORE LOG dbname... WITH NORECOVERY...
> >> RESTORE LOG dbname... WITH RECOVERY, STOPAT.....
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> Blog: http://solidqualitylearning.com/blogs/tibor/
> >>
> >>
> >> "vidro" <vidro@.discussions.microsoft.com> wrote in message
> >> news:839B9ED3-82F7-4A34-9FBA-E78FE88D36AA@.microsoft.com...
> >> > I've been reading articles on "how to restore a database to a new location",
> >> > "how to restore a database with a new name", etc.
> >> > What I need is a step by step on how to:
> >> > Using a preexisting database backup,
> >> > point it to a different server,
> >> > create that database on that different server,
> >> > apply the full back up,
> >> > do a "point in time" restore from the original database transaction log.
> >> >
> >> > I thought I could do most ofthis but I am having NO luck in getting the
> >> > database to restore to a new database with the same name on a different
> >> > server.
> >> >
> >> > How is this done?
> >>
> >>
>|||Correct. I missed that in my prior post where I specified RESTORE DATABASE with STOPAT, which isn't
possible. STOPAT is only possible for LOG restore.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"vidro" <vidro@.discussions.microsoft.com> wrote in message
news:8CCDAB6D-9C3D-4FCA-89B1-0D46A12983A7@.microsoft.com...
> And if I wanted to restore from the transaction log, the following being the
> appopriate syntex?
> RESTORE LOG market
> FROM DRIVE = 'E:\DRIVE\market.trn OR market.lfd'
> WITH RECOVERY, STOPAT = '20050914 20:00'
> "Tibor Karaszi" wrote:
>> FROM is either a predefined backupdevice (read about sp_addumpdevice):
>> RESTORE DATABASE Market
>> FROM myBackupDevice
>> WITH RECOVERY, STOPAT = '20051014 20:00'
>>
>> Or a physical file name:
>> RESTORE DATABASE Market
>> FROM DISK = 'C:\myBackupDevice.BAK'
>> WITH RECOVERY, STOPAT = '20051014 20:00'
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "vidro" <vidro@.discussions.microsoft.com> wrote in message
>> news:4800BDEF-7112-4621-B287-0A91BA679B32@.microsoft.com...
>> > There seems to be a couple of things going on that I'm not sure about;
>> > The following is the way I have things typed, I'm confused with the "From"
>> > peremeter. If this is the device I'm restoring from, is this than going to
>> > be the literal name of the back up file?
>> >
>> > I also get the following error:
>> > "One or more of the options (stopat) are not supported for this statement"
>> >
>> > my comments typed in the Query Analyzer;
>> >
>> > RECOVER DATABASE Market
>> > FROM ?
>> > WITH RECOVERY, STOPAT = 'SEPT 14, 2005 10:00 A.M'
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> It is easier if you post the TSQL commands you are executing. Basically, you:
>> >>
>> >> On the originating server:
>> >> BACKUP DATABASE dbname
>> >> BACKUP LOG dbname
>> >> BACKUP LOG dbname
>> >>
>> >> On the destination server:
>> >> RESTORE DATABASE dbname ... WITH NORECOVERY...
>> >> If the database exists and the database file structure doesn't match with the original, you
>> >> need
>> >> to
>> >> use the REPLACE option. If you need to move files, you need to use the MOVE option for each
>> >> file.
>> >> RESTORE LOG dbname... WITH NORECOVERY...
>> >> RESTORE LOG dbname... WITH RECOVERY, STOPAT.....
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >> Blog: http://solidqualitylearning.com/blogs/tibor/
>> >>
>> >>
>> >> "vidro" <vidro@.discussions.microsoft.com> wrote in message
>> >> news:839B9ED3-82F7-4A34-9FBA-E78FE88D36AA@.microsoft.com...
>> >> > I've been reading articles on "how to restore a database to a new location",
>> >> > "how to restore a database with a new name", etc.
>> >> > What I need is a step by step on how to:
>> >> > Using a preexisting database backup,
>> >> > point it to a different server,
>> >> > create that database on that different server,
>> >> > apply the full back up,
>> >> > do a "point in time" restore from the original database transaction log.
>> >> >
>> >> > I thought I could do most ofthis but I am having NO luck in getting the
>> >> > database to restore to a new database with the same name on a different
>> >> > server.
>> >> >
>> >> > How is this done?
>> >>
>> >>
>>|||I may have bigger problems than what I thought. The file I was looking at is
evidently a copy of the original database from a few months ago, the
extensions was not visible and it was offering itself as a back up file was
why I thought it was a BAK.
It does not appear that a full backup has been done on this database prior
to a few days ago.
The problem is some time in the past 4 weeks some data has evaporated, the
thought was to do a STOPAT recovery with the transaction logs but if we don't
have a full back up prior to 4 weeks ago is this going to be possible?
"Tibor Karaszi" wrote:
> Correct. I missed that in my prior post where I specified RESTORE DATABASE with STOPAT, which isn't
> possible. STOPAT is only possible for LOG restore.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "vidro" <vidro@.discussions.microsoft.com> wrote in message
> news:8CCDAB6D-9C3D-4FCA-89B1-0D46A12983A7@.microsoft.com...
> > And if I wanted to restore from the transaction log, the following being the
> > appopriate syntex?
> >
> > RESTORE LOG market
> > FROM DRIVE = 'E:\DRIVE\market.trn OR market.lfd'
> > WITH RECOVERY, STOPAT = '20050914 20:00'
> >
> > "Tibor Karaszi" wrote:
> >
> >> FROM is either a predefined backupdevice (read about sp_addumpdevice):
> >>
> >> RESTORE DATABASE Market
> >> FROM myBackupDevice
> >> WITH RECOVERY, STOPAT = '20051014 20:00'
> >>
> >>
> >>
> >> Or a physical file name:
> >>
> >> RESTORE DATABASE Market
> >> FROM DISK = 'C:\myBackupDevice.BAK'
> >> WITH RECOVERY, STOPAT = '20051014 20:00'
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> Blog: http://solidqualitylearning.com/blogs/tibor/
> >>
> >>
> >> "vidro" <vidro@.discussions.microsoft.com> wrote in message
> >> news:4800BDEF-7112-4621-B287-0A91BA679B32@.microsoft.com...
> >> > There seems to be a couple of things going on that I'm not sure about;
> >> > The following is the way I have things typed, I'm confused with the "From"
> >> > peremeter. If this is the device I'm restoring from, is this than going to
> >> > be the literal name of the back up file?
> >> >
> >> > I also get the following error:
> >> > "One or more of the options (stopat) are not supported for this statement"
> >> >
> >> > my comments typed in the Query Analyzer;
> >> >
> >> > RECOVER DATABASE Market
> >> > FROM ?
> >> > WITH RECOVERY, STOPAT = 'SEPT 14, 2005 10:00 A.M'
> >> >
> >> > "Tibor Karaszi" wrote:
> >> >
> >> >> It is easier if you post the TSQL commands you are executing. Basically, you:
> >> >>
> >> >> On the originating server:
> >> >> BACKUP DATABASE dbname
> >> >> BACKUP LOG dbname
> >> >> BACKUP LOG dbname
> >> >>
> >> >> On the destination server:
> >> >> RESTORE DATABASE dbname ... WITH NORECOVERY...
> >> >> If the database exists and the database file structure doesn't match with the original, you
> >> >> need
> >> >> to
> >> >> use the REPLACE option. If you need to move files, you need to use the MOVE option for each
> >> >> file.
> >> >> RESTORE LOG dbname... WITH NORECOVERY...
> >> >> RESTORE LOG dbname... WITH RECOVERY, STOPAT.....
> >> >>
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://www.solidqualitylearning.com/
> >> >> Blog: http://solidqualitylearning.com/blogs/tibor/
> >> >>
> >> >>
> >> >> "vidro" <vidro@.discussions.microsoft.com> wrote in message
> >> >> news:839B9ED3-82F7-4A34-9FBA-E78FE88D36AA@.microsoft.com...
> >> >> > I've been reading articles on "how to restore a database to a new location",
> >> >> > "how to restore a database with a new name", etc.
> >> >> > What I need is a step by step on how to:
> >> >> > Using a preexisting database backup,
> >> >> > point it to a different server,
> >> >> > create that database on that different server,
> >> >> > apply the full back up,
> >> >> > do a "point in time" restore from the original database transaction log.
> >> >> >
> >> >> > I thought I could do most ofthis but I am having NO luck in getting the
> >> >> > database to restore to a new database with the same name on a different
> >> >> > server.
> >> >> >
> >> >> > How is this done?
> >> >>
> >> >>
> >>
> >>
>|||Are you saying that the earlier database backup if from a few days ago, and you want to do point in
time restore to an earlier time? If so, you can't. A log backup can only be applied on a database
backup. First restore database backup, then each log backup taken after that database backup,
optionally using STOPAT for the last log restore.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"vidro" <vidro@.discussions.microsoft.com> wrote in message
news:B894C0C8-D6EB-4DE2-827F-9E9871A75C35@.microsoft.com...
>I may have bigger problems than what I thought. The file I was looking at is
> evidently a copy of the original database from a few months ago, the
> extensions was not visible and it was offering itself as a back up file was
> why I thought it was a BAK.
> It does not appear that a full backup has been done on this database prior
> to a few days ago.
> The problem is some time in the past 4 weeks some data has evaporated, the
> thought was to do a STOPAT recovery with the transaction logs but if we don't
> have a full back up prior to 4 weeks ago is this going to be possible?
> "Tibor Karaszi" wrote:
>> Correct. I missed that in my prior post where I specified RESTORE DATABASE with STOPAT, which
>> isn't
>> possible. STOPAT is only possible for LOG restore.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "vidro" <vidro@.discussions.microsoft.com> wrote in message
>> news:8CCDAB6D-9C3D-4FCA-89B1-0D46A12983A7@.microsoft.com...
>> > And if I wanted to restore from the transaction log, the following being the
>> > appopriate syntex?
>> >
>> > RESTORE LOG market
>> > FROM DRIVE = 'E:\DRIVE\market.trn OR market.lfd'
>> > WITH RECOVERY, STOPAT = '20050914 20:00'
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> FROM is either a predefined backupdevice (read about sp_addumpdevice):
>> >>
>> >> RESTORE DATABASE Market
>> >> FROM myBackupDevice
>> >> WITH RECOVERY, STOPAT = '20051014 20:00'
>> >>
>> >>
>> >>
>> >> Or a physical file name:
>> >>
>> >> RESTORE DATABASE Market
>> >> FROM DISK = 'C:\myBackupDevice.BAK'
>> >> WITH RECOVERY, STOPAT = '20051014 20:00'
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >> Blog: http://solidqualitylearning.com/blogs/tibor/
>> >>
>> >>
>> >> "vidro" <vidro@.discussions.microsoft.com> wrote in message
>> >> news:4800BDEF-7112-4621-B287-0A91BA679B32@.microsoft.com...
>> >> > There seems to be a couple of things going on that I'm not sure about;
>> >> > The following is the way I have things typed, I'm confused with the "From"
>> >> > peremeter. If this is the device I'm restoring from, is this than going to
>> >> > be the literal name of the back up file?
>> >> >
>> >> > I also get the following error:
>> >> > "One or more of the options (stopat) are not supported for this statement"
>> >> >
>> >> > my comments typed in the Query Analyzer;
>> >> >
>> >> > RECOVER DATABASE Market
>> >> > FROM ?
>> >> > WITH RECOVERY, STOPAT = 'SEPT 14, 2005 10:00 A.M'
>> >> >
>> >> > "Tibor Karaszi" wrote:
>> >> >
>> >> >> It is easier if you post the TSQL commands you are executing. Basically, you:
>> >> >>
>> >> >> On the originating server:
>> >> >> BACKUP DATABASE dbname
>> >> >> BACKUP LOG dbname
>> >> >> BACKUP LOG dbname
>> >> >>
>> >> >> On the destination server:
>> >> >> RESTORE DATABASE dbname ... WITH NORECOVERY...
>> >> >> If the database exists and the database file structure doesn't match with the original, you
>> >> >> need
>> >> >> to
>> >> >> use the REPLACE option. If you need to move files, you need to use the MOVE option for each
>> >> >> file.
>> >> >> RESTORE LOG dbname... WITH NORECOVERY...
>> >> >> RESTORE LOG dbname... WITH RECOVERY, STOPAT.....
>> >> >>
>> >> >> --
>> >> >> Tibor Karaszi, SQL Server MVP
>> >> >> http://www.karaszi.com/sqlserver/default.asp
>> >> >> http://www.solidqualitylearning.com/
>> >> >> Blog: http://solidqualitylearning.com/blogs/tibor/
>> >> >>
>> >> >>
>> >> >> "vidro" <vidro@.discussions.microsoft.com> wrote in message
>> >> >> news:839B9ED3-82F7-4A34-9FBA-E78FE88D36AA@.microsoft.com...
>> >> >> > I've been reading articles on "how to restore a database to a new location",
>> >> >> > "how to restore a database with a new name", etc.
>> >> >> > What I need is a step by step on how to:
>> >> >> > Using a preexisting database backup,
>> >> >> > point it to a different server,
>> >> >> > create that database on that different server,
>> >> >> > apply the full back up,
>> >> >> > do a "point in time" restore from the original database transaction log.
>> >> >> >
>> >> >> > I thought I could do most ofthis but I am having NO luck in getting the
>> >> >> > database to restore to a new database with the same name on a different
>> >> >> > server.
>> >> >> >
>> >> >> > How is this done?
>> >> >>
>> >> >>
>> >>
>> >>
>>

Friday, March 30, 2012

Restore deleted table from log?

restore deleted records

Restore DB table - SQL Server 2000

Wednesday, March 28, 2012

Restore DB - SQL Server 2000

Restore DB - SQL Server 2000

Restore DB - SQL Server 2000

Wednesday, March 21, 2012

restore database back to sql 7.0?

restore database back to sql 7.0?

restore database back to sql 7.0?

Tuesday, March 20, 2012

Restore Database

Restore Database

Friday, March 9, 2012

Restore backup file

Restore backup file

Restore backup file

restore back up from sql 2005 beta 2 to sql 2005 original

restore Back file to new server database

restore Back file to new server database

Restore mdf file

Blog Archive

About Me