Restoring.............

Hi,
I am very new to Microsoft Technologies, infact new to database world
:)I have a SQL-Server 7.0 back. How can i restore it? Moreover , can i
restore this backup by using SQL-Server 2000Enterprise edition, how
should i go about it?
Any help or comments will be highly appreciated.
-ErlandEasiest in the login run is to read about the RESTORE command in Books Onlin
e. After you understand
how that RESTORE command work, you will find it natural how to work the GUI
dialog in Enterprise
Manager (if you chose to use it instead of executing the RESTORE command in
Query Analyzer).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Erland" <Erland.Erikson@.gmail.com> wrote in message
news:1132234090.437642.38470@.f14g2000cwb.googlegroups.com...
> Hi,
> I am very new to Microsoft Technologies, infact new to database world
> :)I have a SQL-Server 7.0 back. How can i restore it? Moreover , can i
> restore this backup by using SQL-Server 2000Enterprise edition, how
> should i go about it?
> Any help or comments will be highly appreciated.
> -Erland
>|||Tibor Karaszi wrote:
> Easiest in the login run is to read about the RESTORE command in Books Onl
ine. After you understand
> how that RESTORE command work, you will find it natural how to work the GU
I dialog in Enterprise
> Manager (if you chose to use it instead of executing the RESTORE command i
n Query Analyzer).
>
osql
restore database blah from disk = 'c:\blah.dat'
something like that

Restoring to a different server

I have a back up of one of our database, which i want to restore onto our
Devolopment server. I have tied to do a 'restore' through Enterprise manager,
but it fails with a SQL 42000 error. It also says something about ' use WITH
MOVE'
You have different disk layout on your two servers. The EM GUI allows you
to MOVE a data or log file to a different drive. You also have the ability
to specify WITH MOVE when restoring a database via T-SQL within Query
Analyzer.
Bottom line: you will need to tell SQL Server to move the data and log
file(s) to a drive that exists on the machine that you are restoring to.
Lots of information is available within Books Online (within the SQL Server
program group) or Transact-SQL Help (available from Query Analyzer).
Keith
"Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
news:DC547DF1-D385-463E-96FF-16C7C9C1D6CE@.microsoft.com...
> I have a back up of one of our database, which i want to restore onto our
> Devolopment server. I have tied to do a 'restore' through Enterprise
manager,
> but it fails with a SQL 42000 error. It also says something about ' use
WITH
> MOVE'
>
|||Hi,
1. Using Restore filelistonly command identify the logical file names of the
database backup file
RESTORE FILELISTONLY from disk='c:\x.bak'
2. With the output of the above query use RESTORE database
RESTORE DATABASE <newdbname> from disk='c:\backup\x.bak'
WITH move 'logical_mdf_filename' to 'new physical name with path',
move 'logical_ldf_filename' to 'new physical log name with Path'
In the physical path give the available drive letters and folder names
Thanks
Hari
MCDBA
"Peter Newman" wrote:

> I have a back up of one of our database, which i want to restore onto our
> Devolopment server. I have tied to do a 'restore' through Enterprise manager,
> but it fails with a SQL 42000 error. It also says something about ' use WITH
> MOVE'
>

Restoring the Northwind and Pubs databases

Is there an easy way to get Northwind and Pubs back to their default state,
assuming that you were too stupid to back them up before you started mucking
around with them? Thanks.You can recreate the two databases from the two scripts: instpubs.sql, and
instnwnd.sql from the \install folder of your SQL Server installation or
the SQL Server CD.
Sincerely,
Yih-Yoon Lee
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.|||Alternatively, you can try attach Nothwind and pubs databases from the SQL
2K CD:
x86\DATA\northwnd.mdf
x86\DATA\northwnd.ldf
x86\DATA\pubs.mdf
x86\DATA\pubs_log.ldf
Richard
"Garrett Fitzgerald" <gfitzger@.nyx.net> wrote in message
news:uO$K4EbQDHA.2128@.TK2MSFTNGP12.phx.gbl...
> Is there an easy way to get Northwind and Pubs back to their default
state,
> assuming that you were too stupid to back them up before you started
mucking
> around with them? Thanks.
>
>|||Alan,
>> You also have to run a batch utility...
Thanks Alan. I wasnt aware of that.
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Alan Brewer [MSFT]" <alanbr@.microsoft.com> wrote in message
news:uwBiD8kQDHA.1564@.TK2MSFTNGP12.phx.gbl...
> You also have to run a batch utility to fully populate the data in pubs.
> Northwind can be resinstalled by just using the instnwnd.sql script.
> The processes to create pubs and Northwind are covered in these
Transact-SQL
> Reference topics:
>
http://msdn.microsoft.com/library/?url=/library/en-us/tsqlref/ts_pubs_2v8l.asp?frame=true
>
http://msdn.microsoft.com/library/?url=/library/en-us/tsqlref/ts_north_2ch1.asp?frame=true
> --
> Alan Brewer [MSFT]
> Lead Technical Writer
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
>

restoring the database

Hi,

Two of my hard drives went down yesterday. and with it went the Sql server database.The data recovery guys recovered the back up database (.bak) file.I have since reinstalled SQL Server 2000 and now when i try and restore the .bak file it says the Microsoft tape back up file set is not valid when i try and open the .bak file.

Whats the right way to add my databse to the SQl Server2000 using my .bak file and if anyone has come across the error they could help.

Thanks

LDid you apply all SPs that your old server used to have?|||what do u mean by sps|||what do u mean by sps

Stored Procedure|||It's "Service Packs", not stored procedures...how do you apply a stored procedure anyway?|||Restore the databases...which she can't do. DOH!!! :)

You should always verify your backups, especially if writing directly to tape. Any luck though after applying the service packs? Don't you guys have a DBA who can help with this?|||It's "Service Packs", not stored procedures...how do you apply a stored procedure anyway?

Yes. It should be Service Packs|||Hi Lohit,

hows was your process of restoring the backup?

restoring table or view or sp

Actually I have very big size of databse. Also its back up is obvious that also biger size.

In case of only data loss of one table or view or sp or change then restore whole database then get back previous backup database & restore table i.e. it consuming too much time.

I want to restore only one table or view in faster way. how i can do it. plz give any suggestions

If you database was made of seperate files you could restore individual files.

To restore only a table you need a third party product such as litespeed.

I am not sure if any product can restore only schema components i.e views and sps. Standard practice is to keep the SQL scripts for Views and SPs. Do you not have these?

|||

what is litespeed & how i can get it. give the idea plz

|||

in sql server 2005 you can use Database snapshot as

a part of your backup and restore strategy.

A database snapshot is a readonly replica of your database

so in case a table is accidentally deleted, you can copy the deleted

data from the snapshot.

other use of the snapshot is for reporting purposes so this startegy will also

boost the database performance as well

restoring suspect database

We have some databases whose files are on a SAN. Someone shut the SAN down
before the sql server box and when sql server came back up the databases on
the SAN were marked "suspect". I've been able to reboot the sql server box in
the past and it clears up the problem but it didn't this time.
I don't want to try sp_resetstatus at this time (in the middle of the day)
because there are production databases on the box and I don't want to have to
restart sql server. The last backup that was done was file backup by Veritas.
Is it possible that just running "RESTORE DATABASE byrrod WITH RECOVERY"
will work? What does this command do when a file is not specified?
Can I use the restore command if I just have an .mdf file? I thought that I
might be able to do a detach/attach but I guess because the database is in
the "suspect" mode, I can't do that. Would removing the database and creating
a new one by the same name and then doing the detach/attach work?
Thanks,
--
Dan D.Seems you are investigating every possible action except the correct action ;-)
Do a log backup using the NO_TRUNCATE option (the option is required since your database is
suspect). Restore the latest database backup and all subsequent log backups (including this last log
backup). Zero data loss.
> Is it possible that just running "RESTORE DATABASE byrrod WITH RECOVERY"
> will work? What does this command do when a file is not specified?
No. The command assumes you first did restore of a database of log backup using either NORECOVERY or
STANDBY. It does a very specific thing: do the UNDO work that weren't performed by that last
restore.
> Can I use the restore command if I just have an .mdf file?
No. If you are *very* lucky, you can do attach. As for the rest of the attach alternatives you
mention, I wouldn't even go there. Do it the proper way (as I listed in top of this post).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:BAD5B056-6C16-46EC-A043-2E047C7D6E5E@.microsoft.com...
> We have some databases whose files are on a SAN. Someone shut the SAN down
> before the sql server box and when sql server came back up the databases on
> the SAN were marked "suspect". I've been able to reboot the sql server box in
> the past and it clears up the problem but it didn't this time.
> I don't want to try sp_resetstatus at this time (in the middle of the day)
> because there are production databases on the box and I don't want to have to
> restart sql server. The last backup that was done was file backup by Veritas.
> Is it possible that just running "RESTORE DATABASE byrrod WITH RECOVERY"
> will work? What does this command do when a file is not specified?
> Can I use the restore command if I just have an .mdf file? I thought that I
> might be able to do a detach/attach but I guess because the database is in
> the "suspect" mode, I can't do that. Would removing the database and creating
> a new one by the same name and then doing the detach/attach work?
> Thanks,
> --
> Dan D.|||Hi
sp_resetstatus is your only option.
Restore will not work as the Db is in suspect mode and not loading mode.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:BAD5B056-6C16-46EC-A043-2E047C7D6E5E@.microsoft.com...
> We have some databases whose files are on a SAN. Someone shut the SAN down
> before the sql server box and when sql server came back up the databases
> on
> the SAN were marked "suspect". I've been able to reboot the sql server box
> in
> the past and it clears up the problem but it didn't this time.
> I don't want to try sp_resetstatus at this time (in the middle of the day)
> because there are production databases on the box and I don't want to have
> to
> restart sql server. The last backup that was done was file backup by
> Veritas.
> Is it possible that just running "RESTORE DATABASE byrrod WITH RECOVERY"
> will work? What does this command do when a file is not specified?
> Can I use the restore command if I just have an .mdf file? I thought that
> I
> might be able to do a detach/attach but I guess because the database is in
> the "suspect" mode, I can't do that. Would removing the database and
> creating
> a new one by the same name and then doing the detach/attach work?
> Thanks,
> --
> Dan D.|||Thanks.
--
Dan D.
"Tibor Karaszi" wrote:
> Seems you are investigating every possible action except the correct action ;-)
> Do a log backup using the NO_TRUNCATE option (the option is required since your database is
> suspect). Restore the latest database backup and all subsequent log backups (including this last log
> backup). Zero data loss.
>
> > Is it possible that just running "RESTORE DATABASE byrrod WITH RECOVERY"
> > will work? What does this command do when a file is not specified?
> No. The command assumes you first did restore of a database of log backup using either NORECOVERY or
> STANDBY. It does a very specific thing: do the UNDO work that weren't performed by that last
> restore.
>
> > Can I use the restore command if I just have an .mdf file?
> No. If you are *very* lucky, you can do attach. As for the rest of the attach alternatives you
> mention, I wouldn't even go there. Do it the proper way (as I listed in top of this post).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:BAD5B056-6C16-46EC-A043-2E047C7D6E5E@.microsoft.com...
> > We have some databases whose files are on a SAN. Someone shut the SAN down
> > before the sql server box and when sql server came back up the databases on
> > the SAN were marked "suspect". I've been able to reboot the sql server box in
> > the past and it clears up the problem but it didn't this time.
> >
> > I don't want to try sp_resetstatus at this time (in the middle of the day)
> > because there are production databases on the box and I don't want to have to
> > restart sql server. The last backup that was done was file backup by Veritas.
> >
> > Is it possible that just running "RESTORE DATABASE byrrod WITH RECOVERY"
> > will work? What does this command do when a file is not specified?
> >
> > Can I use the restore command if I just have an .mdf file? I thought that I
> > might be able to do a detach/attach but I guess because the database is in
> > the "suspect" mode, I can't do that. Would removing the database and creating
> > a new one by the same name and then doing the detach/attach work?
> >
> > Thanks,
> > --
> > Dan D.
>|||Thanks.
--
Dan D.
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> sp_resetstatus is your only option.
> Restore will not work as the Db is in suspect mode and not loading mode.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:BAD5B056-6C16-46EC-A043-2E047C7D6E5E@.microsoft.com...
> > We have some databases whose files are on a SAN. Someone shut the SAN down
> > before the sql server box and when sql server came back up the databases
> > on
> > the SAN were marked "suspect". I've been able to reboot the sql server box
> > in
> > the past and it clears up the problem but it didn't this time.
> >
> > I don't want to try sp_resetstatus at this time (in the middle of the day)
> > because there are production databases on the box and I don't want to have
> > to
> > restart sql server. The last backup that was done was file backup by
> > Veritas.
> >
> > Is it possible that just running "RESTORE DATABASE byrrod WITH RECOVERY"
> > will work? What does this command do when a file is not specified?
> >
> > Can I use the restore command if I just have an .mdf file? I thought that
> > I
> > might be able to do a detach/attach but I guess because the database is in
> > the "suspect" mode, I can't do that. Would removing the database and
> > creating
> > a new one by the same name and then doing the detach/attach work?
> >
> > Thanks,
> > --
> > Dan D.
>
>

restoring suspect database

We have some databases whose files are on a SAN. Someone shut the SAN down
before the sql server box and when sql server came back up the databases on
the SAN were marked "suspect". I've been able to reboot the sql server box i
n
the past and it clears up the problem but it didn't this time.
I don't want to try sp_resetstatus at this time (in the middle of the day)
because there are production databases on the box and I don't want to have t
o
restart sql server. The last backup that was done was file backup by Veritas
.
Is it possible that just running "RESTORE DATABASE byrrod WITH RECOVERY"
will work? What does this command do when a file is not specified?
Can I use the restore command if I just have an .mdf file? I thought that I
might be able to do a detach/attach but I guess because the database is in
the "suspect" mode, I can't do that. Would removing the database and creatin
g
a new one by the same name and then doing the detach/attach work?
Thanks,
--
Dan D.Seems you are investigating every possible action except the correct action
;-)
Do a log backup using the NO_TRUNCATE option (the option is required since y
our database is
suspect). Restore the latest database backup and all subsequent log backups
(including this last log
backup). Zero data loss.

> Is it possible that just running "RESTORE DATABASE byrrod WITH RECOVERY"
> will work? What does this command do when a file is not specified?
No. The command assumes you first did restore of a database of log backup us
ing either NORECOVERY or
STANDBY. It does a very specific thing: do the UNDO work that weren't perfor
med by that last
restore.

> Can I use the restore command if I just have an .mdf file?
No. If you are *very* lucky, you can do attach. As for the rest of the attac
h alternatives you
mention, I wouldn't even go there. Do it the proper way (as I listed in top
of this post).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:BAD5B056-6C16-46EC-A043-2E047C7D6E5E@.microsoft.com...
> We have some databases whose files are on a SAN. Someone shut the SAN down
> before the sql server box and when sql server came back up the databases o
n
> the SAN were marked "suspect". I've been able to reboot the sql server box
in
> the past and it clears up the problem but it didn't this time.
> I don't want to try sp_resetstatus at this time (in the middle of the day)
> because there are production databases on the box and I don't want to have
to
> restart sql server. The last backup that was done was file backup by Verit
as.
> Is it possible that just running "RESTORE DATABASE byrrod WITH RECOVERY"
> will work? What does this command do when a file is not specified?
> Can I use the restore command if I just have an .mdf file? I thought that
I
> might be able to do a detach/attach but I guess because the database is in
> the "suspect" mode, I can't do that. Would removing the database and creat
ing
> a new one by the same name and then doing the detach/attach work?
> Thanks,
> --
> Dan D.|||Hi
sp_resetstatus is your only option.
Restore will not work as the Db is in suspect mode and not loading mode.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:BAD5B056-6C16-46EC-A043-2E047C7D6E5E@.microsoft.com...
> We have some databases whose files are on a SAN. Someone shut the SAN down
> before the sql server box and when sql server came back up the databases
> on
> the SAN were marked "suspect". I've been able to reboot the sql server box
> in
> the past and it clears up the problem but it didn't this time.
> I don't want to try sp_resetstatus at this time (in the middle of the day)
> because there are production databases on the box and I don't want to have
> to
> restart sql server. The last backup that was done was file backup by
> Veritas.
> Is it possible that just running "RESTORE DATABASE byrrod WITH RECOVERY"
> will work? What does this command do when a file is not specified?
> Can I use the restore command if I just have an .mdf file? I thought that
> I
> might be able to do a detach/attach but I guess because the database is in
> the "suspect" mode, I can't do that. Would removing the database and
> creating
> a new one by the same name and then doing the detach/attach work?
> Thanks,
> --
> Dan D.|||Thanks.
--
Dan D.
"Tibor Karaszi" wrote:

> Seems you are investigating every possible action except the correct actio
n ;-)
> Do a log backup using the NO_TRUNCATE option (the option is required since
your database is
> suspect). Restore the latest database backup and all subsequent log backup
s (including this last log
> backup). Zero data loss.
>
> No. The command assumes you first did restore of a database of log backup
using either NORECOVERY or
> STANDBY. It does a very specific thing: do the UNDO work that weren't perf
ormed by that last
> restore.
>
> No. If you are *very* lucky, you can do attach. As for the rest of the att
ach alternatives you
> mention, I wouldn't even go there. Do it the proper way (as I listed in to
p of this post).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:BAD5B056-6C16-46EC-A043-2E047C7D6E5E@.microsoft.com...
>|||Thanks.
--
Dan D.
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> sp_resetstatus is your only option.
> Restore will not work as the Db is in suspect mode and not loading mode.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:BAD5B056-6C16-46EC-A043-2E047C7D6E5E@.microsoft.com...
>
>

restoring suspect database

We have some databases whose files are on a SAN. Someone shut the SAN down
before the sql server box and when sql server came back up the databases on
the SAN were marked "suspect". I've been able to reboot the sql server box in
the past and it clears up the problem but it didn't this time.
I don't want to try sp_resetstatus at this time (in the middle of the day)
because there are production databases on the box and I don't want to have to
restart sql server. The last backup that was done was file backup by Veritas.
Is it possible that just running "RESTORE DATABASE byrrod WITH RECOVERY"
will work? What does this command do when a file is not specified?
Can I use the restore command if I just have an .mdf file? I thought that I
might be able to do a detach/attach but I guess because the database is in
the "suspect" mode, I can't do that. Would removing the database and creating
a new one by the same name and then doing the detach/attach work?
Thanks,
Dan D.
Seems you are investigating every possible action except the correct action ;-)
Do a log backup using the NO_TRUNCATE option (the option is required since your database is
suspect). Restore the latest database backup and all subsequent log backups (including this last log
backup). Zero data loss.

> Is it possible that just running "RESTORE DATABASE byrrod WITH RECOVERY"
> will work? What does this command do when a file is not specified?
No. The command assumes you first did restore of a database of log backup using either NORECOVERY or
STANDBY. It does a very specific thing: do the UNDO work that weren't performed by that last
restore.

> Can I use the restore command if I just have an .mdf file?
No. If you are *very* lucky, you can do attach. As for the rest of the attach alternatives you
mention, I wouldn't even go there. Do it the proper way (as I listed in top of this post).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:BAD5B056-6C16-46EC-A043-2E047C7D6E5E@.microsoft.com...
> We have some databases whose files are on a SAN. Someone shut the SAN down
> before the sql server box and when sql server came back up the databases on
> the SAN were marked "suspect". I've been able to reboot the sql server box in
> the past and it clears up the problem but it didn't this time.
> I don't want to try sp_resetstatus at this time (in the middle of the day)
> because there are production databases on the box and I don't want to have to
> restart sql server. The last backup that was done was file backup by Veritas.
> Is it possible that just running "RESTORE DATABASE byrrod WITH RECOVERY"
> will work? What does this command do when a file is not specified?
> Can I use the restore command if I just have an .mdf file? I thought that I
> might be able to do a detach/attach but I guess because the database is in
> the "suspect" mode, I can't do that. Would removing the database and creating
> a new one by the same name and then doing the detach/attach work?
> Thanks,
> --
> Dan D.
|||Hi
sp_resetstatus is your only option.
Restore will not work as the Db is in suspect mode and not loading mode.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:BAD5B056-6C16-46EC-A043-2E047C7D6E5E@.microsoft.com...
> We have some databases whose files are on a SAN. Someone shut the SAN down
> before the sql server box and when sql server came back up the databases
> on
> the SAN were marked "suspect". I've been able to reboot the sql server box
> in
> the past and it clears up the problem but it didn't this time.
> I don't want to try sp_resetstatus at this time (in the middle of the day)
> because there are production databases on the box and I don't want to have
> to
> restart sql server. The last backup that was done was file backup by
> Veritas.
> Is it possible that just running "RESTORE DATABASE byrrod WITH RECOVERY"
> will work? What does this command do when a file is not specified?
> Can I use the restore command if I just have an .mdf file? I thought that
> I
> might be able to do a detach/attach but I guess because the database is in
> the "suspect" mode, I can't do that. Would removing the database and
> creating
> a new one by the same name and then doing the detach/attach work?
> Thanks,
> --
> Dan D.
|||Thanks.
Dan D.
"Tibor Karaszi" wrote:

> Seems you are investigating every possible action except the correct action ;-)
> Do a log backup using the NO_TRUNCATE option (the option is required since your database is
> suspect). Restore the latest database backup and all subsequent log backups (including this last log
> backup). Zero data loss.
>
> No. The command assumes you first did restore of a database of log backup using either NORECOVERY or
> STANDBY. It does a very specific thing: do the UNDO work that weren't performed by that last
> restore.
>
> No. If you are *very* lucky, you can do attach. As for the rest of the attach alternatives you
> mention, I wouldn't even go there. Do it the proper way (as I listed in top of this post).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:BAD5B056-6C16-46EC-A043-2E047C7D6E5E@.microsoft.com...
>
|||Thanks.
Dan D.
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> sp_resetstatus is your only option.
> Restore will not work as the Db is in suspect mode and not loading mode.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:BAD5B056-6C16-46EC-A043-2E047C7D6E5E@.microsoft.com...
>
>

Restoring SQL 7 database to SQL 2000

Can you back up a SQL 7 database and restored it to a SQL
2000 Server?
Are there any issues with doing that?
ThanksStev,
Yes, you can.You may face some issues if the database users/logins wont
match.See the below articles for details:
User Logon and/or Permission Errors After Restoring Dump
http://www.support.microsoft.com/?id=168001
How to Resolve Permission Issues When a Database Is Moved Between SQL
Servers
http://www.support.microsoft.com/?id=240872
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Stev" <stev@.partnervest.com> wrote in message
news:019801c355ea$dc150860$a101280a@.phx.gbl...
> Can you back up a SQL 7 database and restored it to a SQL
> 2000 Server?
> Are there any issues with doing that?
> Thanks|||In addition, if you chose a Windows collation for SQL2000 during install,
you may need to convert the database to match the server collation depending
on it's use of tempdb and/or interaction with other databases on the server
as when it is restored/attached to SQL2000 it will have a SQL collation. See
BOL for more details on collation
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Stev" <stev@.partnervest.com> wrote in message
news:019801c355ea$dc150860$a101280a@.phx.gbl...
Can you back up a SQL 7 database and restored it to a SQL
2000 Server?
Are there any issues with doing that?
Thanks

Restoring rows accidentaly modified

Hello!
How can I Restore rows which are accidentaly modified.
I forgetted to put where condition in "Update" Query.
Now I want my data back. I have not taken back up manually yet.
I am using SQL Server 2000.
Please help.
Thank you,There is a way, but none via sql. As far as I know, you'rebasically SoL. Transactions can be rolled back, but you can't doit after it's already been commited. There's applications outthere that supposedly can do it, but I can't for the life of meremember the names of them.
Sorry man.
|||

Tsk tsk tsk ... let this be a lesson.
1) ***NEVER*** develop a DML script (UPD,INS,DEL) in production -- that's what a dev database is for
2) Backup your database every night or if possible, more often

|||

Alex Papadimoulis wrote:

Tsk tsk tsk ... let this be a lesson.

1) ***NEVER*** develop a DML script (UPD,INS,DEL) in production -- that's what a dev database is for

Well, I would assert that there are times where a DML script isneeded in production, and you don't have the luxury of using a devdatabase first. My suggestion is to ALWAYS wrap it in atransaction, with a ROLLBACK at the end, until you obtain the desiredresults.

BEGIN TRANSACTION
UPDATE
someTable
SET
someColumn = 'someValue'
WHERE
someCondition = 'True'
ROLLBACK -- change to COMMIT once you know you are updating the correct records

I ALWAYS ALWAYS ALWAYS do this, lesson learned the hard way.

Restoring problem

When i try to restore my back up i got this error:

Solution?

I'm using SQL 2005 evaluation version, and restore file is generated by SQL 2005 Standard editon.

|||

Hi ozonce,

By "Sql 2005 Evaluation version", do you mean sql express? Based on my understanding and experience, I think the bakup file (*.bak) is compatible in Sql 2005 and Sql Express which means: you can backup your database in sql express and then restore it in sql2005 and vice versa--unless you do NOT specify full text index feature enabled in sql2005 since Full-text index is not supported in sql express. I just make a test to verfiy it: I created a bak file in sql2005 standard version and restored it successfully in sql express. So, there must be some thing wrong in your bak up file or some operation faults. BTW, do you use the same database name between your current database and the backup database ?

Hope my suggestion helps

restoring one particular table

Hi All,
I am taking transaction log backup every six hours. How do
i restore one particular transaction log from which i can
get the table back.How can i go about pls help me as its
very urgent.
TIA
RegardsYou can't restore a single table from a transaction log backup. Is that
specifcally what you're asking to do? You might want to look at LogExplorer
from www.lumigent.com if you need to reconstruct a single table...
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Adil" <anonymous@.discussions.microsoft.com> wrote in message
news:03ea01c3baaf$2c6e38d0$a301280a@.phx.gbl...
> Hi All,
> I am taking transaction log backup every six hours. How do
> i restore one particular transaction log from which i can
> get the table back.How can i go about pls help me as its
> very urgent.
> TIA
> Regards
>|||Without the third party tool that Brian mentioned, you would typically
restore the database to a second location (different database name) and DTS
the table you want back into production.
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Adil" <anonymous@.discussions.microsoft.com> wrote in message
news:03ea01c3baaf$2c6e38d0$a301280a@.phx.gbl...
> Hi All,
> I am taking transaction log backup every six hours. How do
> i restore one particular transaction log from which i can
> get the table back.How can i go about pls help me as its
> very urgent.
> TIA
> Regards
>

Restoring Master to get SQL login back

Hi Everyone, I'm hoping someone can help me.
We've had a SQL login deleted (special application one) and I understand the best way I can get it back, is to restore the Master database. Is this true ? All the manuals are working on the fact that Master has become corrupt & that it should be rebuilt before restoring. Ours is not corrupt, so am I ok to skip rebuilding it first ?

CheersI wouldn't do that in production environment, because you lose definition of all logins, linked servers, dbs etc. created after the backup you pick to restore. What about restoring master db on test server and using DTS Transfer Logins Task or script out the login and run the script on your server? mojza|||As to rebuild, you don't need to do that. Just restore master from backup over existing master. Look up How to restore the master database (Transact-SQL) topic in Books Online. mojza|||If you know the particulars of the login (name and password), it is much easier to re-add the login, rather than restore all of master. Assuming the user has been dropped as well (which Enterprise Manager cheerfully does for you), you would actually not get any benefit from restoring master, as all of the user's permissions in the application database would still be lost. Restoring the user's permissions is what is going to bog you down much more.

If the user was not deleted, then you can run sp_change_users_login to restore the link between the database user and the (new) login.

Restoring from Backup and Fulltext Catelog

Dear Friends,
When deploying the application I send a back up of the data base to be
restored on clients place.
there is a table in the database which a has FullText Index associated with
it. However since it is in a different machince the client server cannot
recognise the Full-Text catelog.
I tried to drop / edit/ rebulid the Full Text index.
While editing and rebuilding it shows that the FullText catelog is not
found.
I am unable to drop it as it says that it contains indexes.
Command used was : EXEC sp_fulltext_catalog 'LLBPSEPGDataCatelog', 'drop'
What should be the usuall way in this scenario when restoring the back of
database in different server?
Please give us some hint on this..
Regards
Sathian
Review the notes in
http://support.microsoft.com/default...b;en-us;240867
I find the best way to fix this sort of a problem is to update
[database_name].dbo.sysfulltextcatalogs to reflect a valid path, and then
rebuild the catalog.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Sathian" <sathian.t@.in.bosch.com> wrote in message
news:d2bhiv$i11$1@.ns2.fe.internet.bosch.com...
> Dear Friends,
> When deploying the application I send a back up of the data base to be
> restored on clients place.
> there is a table in the database which a has FullText Index associated
with
> it. However since it is in a different machince the client server cannot
> recognise the Full-Text catelog.
> I tried to drop / edit/ rebulid the Full Text index.
> While editing and rebuilding it shows that the FullText catelog is not
> found.
> I am unable to drop it as it says that it contains indexes.
> Command used was : EXEC sp_fulltext_catalog 'LLBPSEPGDataCatelog', 'drop'
> What should be the usuall way in this scenario when restoring the back of
> database in different server?
> Please give us some hint on this..
> Regards
> Sathian
>

Restoring from Backup and Fulltext Catelog

Dear Friends,
When deploying the application I send a back up of the data base to be
restored on clients place.
there is a table in the database which a has FullText Index associated with
it. However since it is in a different machince the client server cannot
recognise the Full-Text catelog.
I tried to drop / edit/ rebulid the Full Text index.
While editing and rebuilding it shows that the FullText catelog is not
found.
I am unable to drop it as it says that it contains indexes.
Command used was : EXEC sp_fulltext_catalog 'LLBPSEPGDataCatelog', 'drop'
What should be the usuall way in this scenario when restoring the back of
database in different server?
Please give us some hint on this..
Regards
SathianReview the notes in
http://support.microsoft.com/defaul...kb;en-us;240867
I find the best way to fix this sort of a problem is to update
[database_name].dbo.sysfulltextcatalogs to reflect a valid path, and then
rebuild the catalog.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Sathian" <sathian.t@.in.bosch.com> wrote in message
news:d2bhiv$i11$1@.ns2.fe.internet.bosch.com...
> Dear Friends,
> When deploying the application I send a back up of the data base to be
> restored on clients place.
> there is a table in the database which a has FullText Index associated
with
> it. However since it is in a different machince the client server cannot
> recognise the Full-Text catelog.
> I tried to drop / edit/ rebulid the Full Text index.
> While editing and rebuilding it shows that the FullText catelog is not
> found.
> I am unable to drop it as it says that it contains indexes.
> Command used was : EXEC sp_fulltext_catalog 'LLBPSEPGDataCatelog', 'drop'
> What should be the usuall way in this scenario when restoring the back of
> database in different server?
> Please give us some hint on this..
> Regards
> Sathian
>

Restoring from 7.0 to 2000 Enterprise Edition...

Hi,
I have read restore on BOL , but i am unable to restore a SQL 7.0 back
up to SQL Server 2000. I have tried things given in the following
article
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_7_backpt_15ro.asp
and i have also tried to restore the back up by using the following
procedure.
My database name is "Hello". I have first created a new database named
"Hello" by using Enterprise Manager and then i opened up Query Analyzer
and choose the database named 'Hello' from dropdown and wrote down the
following
Restore DATABASE HELLO
>From disk = 'd:\Documents and
Settings\Administrator\DEsktop\backup\Hello'
WITH Move 'Hello' to 'D:\test\Hello.mdf'
Go
But i get some errors as in "Exclusive access could not be obtained
because the datavase is in use' and 'Restore Database is terminating
abnormally.
Note: My SQL 7.0 back up stored in the location 'd:\Documents and
Settings\Administrator\DEsktop\backup\Hello'. I am using SQL Server
2000Enterprise Edition on Windows 2000 Advanced Server.
Please do reply me with what i ammissing. I have read quite a few
things on BOL and on MSDN. However, i am stumped. I would really
appreciate any help as im a newbie so please point me to the solution
or to any article / online-resource that would lead me to the solution.
Take care and looking forward.
Please do reply me what i am missing here.The problem is that you are selecting the Hello database in Query Analyzer
and then, when you run the restore command, the Hello database is already in
use.
Supposing that you want to replace the existing Hello database, select
another database in Query Analyzer, like master, and run the same command.
Ben Nevarez
"Erland" <Erland.Erikson@.gmail.com> wrote in message
news:1132539237.114914.201650@.o13g2000cwo.googlegroups.com...
> Hi,
> I have read restore on BOL , but i am unable to restore a SQL 7.0 back
> up to SQL Server 2000. I have tried things given in the following
> article
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_7_backpt_15ro.asp
> and i have also tried to restore the back up by using the following
> procedure.
> My database name is "Hello". I have first created a new database named
> "Hello" by using Enterprise Manager and then i opened up Query Analyzer
> and choose the database named 'Hello' from dropdown and wrote down the
> following
> Restore DATABASE HELLO
>>From disk = 'd:\Documents and
> Settings\Administrator\DEsktop\backup\Hello'
> WITH Move 'Hello' to 'D:\test\Hello.mdf'
> Go
> But i get some errors as in "Exclusive access could not be obtained
> because the datavase is in use' and 'Restore Database is terminating
> abnormally.
> Note: My SQL 7.0 back up stored in the location 'd:\Documents and
> Settings\Administrator\DEsktop\backup\Hello'. I am using SQL Server
> 2000Enterprise Edition on Windows 2000 Advanced Server.
> Please do reply me with what i ammissing. I have read quite a few
> things on BOL and on MSDN. However, i am stumped. I would really
> appreciate any help as im a newbie so please point me to the solution
> or to any article / online-resource that would lead me to the solution.
> Take care and looking forward.
> Please do reply me what i am missing here.
>|||Hi Erland,
The error indicates that you have the DB locked.
You can't restore when you have the Hello database opened as you have by
selecting the Hello database from teh dropdown menu.
Change databases to any other database and try again. If you still get the
error close down all the EM and QA sessions and try it again.
You can use the sp_who command to see how is in what database
kind regards
Greg O
--
Looking to use CLR in SQL 2005. Try some pre-build CLR Functions and SP
AGS SQL 2005 Utilities, over 20+ functions
http://www.ag-software.com/?tabid=38
"Erland" <Erland.Erikson@.gmail.com> wrote in message
news:1132539237.114914.201650@.o13g2000cwo.googlegroups.com...
> Hi,
> I have read restore on BOL , but i am unable to restore a SQL 7.0 back
> up to SQL Server 2000. I have tried things given in the following
> article
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_7_backpt_15ro.asp
> and i have also tried to restore the back up by using the following
> procedure.
> My database name is "Hello". I have first created a new database named
> "Hello" by using Enterprise Manager and then i opened up Query Analyzer
> and choose the database named 'Hello' from dropdown and wrote down the
> following
> Restore DATABASE HELLO
>>From disk = 'd:\Documents and
> Settings\Administrator\DEsktop\backup\Hello'
> WITH Move 'Hello' to 'D:\test\Hello.mdf'
> Go
> But i get some errors as in "Exclusive access could not be obtained
> because the datavase is in use' and 'Restore Database is terminating
> abnormally.
> Note: My SQL 7.0 back up stored in the location 'd:\Documents and
> Settings\Administrator\DEsktop\backup\Hello'. I am using SQL Server
> 2000Enterprise Edition on Windows 2000 Advanced Server.
> Please do reply me with what i ammissing. I have read quite a few
> things on BOL and on MSDN. However, i am stumped. I would really
> appreciate any help as im a newbie so please point me to the solution
> or to any article / online-resource that would lead me to the solution.
> Take care and looking forward.
> Please do reply me what i am missing here.
>|||Hi ,
Many thanks for your message. I tried the following by choosing
someother database from dropdown in QueryAnalyzer.
Restre Database Hello
from disk='d:\Documents and
Settings\Administrator\Desktop\backup\MSSQL7\BACKUP\Hello'
iwth move 'hello' to ''d:\Documents and
Settings\Administrator\Desktop\backup\MSSQL7\BACKUP\Hello.mdf'
Go
but i get the following errors
i)-Logical file 'hello' is not part of database 'hello'. Use Restore
filelistonly to list the logical file names
ii)-Restore database is terminating abnormally.
Then i tried the following
Restore filelistonly
from disk='d:\Documents and
Settings\Administrator\Desktop\backup\MSSQL7\BACKUP\Hello'
Restore Database Hello
from disk='d:\Documents and
Settings\Administrator\Desktop\backup\MSSQL7\BACKUP\Hello'
with move 'hello' to ''d:\Documents and
Settings\Administrator\Desktop\backup\MSSQL7\BACKUP\Hello.mdf'
Go
but i get the same two errors . Just to remind you i am trying to
restore a SQL 7.0 back up on SQL Server 2000 and my SQL 7.0 back is
named as Hello and store at following location
D:\Documents and
Settings\Administrator\Desktop\backup\MSSQL7\BACKUP\Hello
Now i don't have any idea what i am missing here. Any help or comments
will be highly appreciated. Please help.
Thanks
-Erland.
GregO wrote:
> Hi Erland,
> The error indicates that you have the DB locked.
> You can't restore when you have the Hello database opened as you have by
> selecting the Hello database from teh dropdown menu.
> Change databases to any other database and try again. If you still get the
> error close down all the EM and QA sessions and try it again.
> You can use the sp_who command to see how is in what database
> kind regards
> Greg O
> --
> Looking to use CLR in SQL 2005. Try some pre-build CLR Functions and SP
> AGS SQL 2005 Utilities, over 20+ functions
> http://www.ag-software.com/?tabid=38
>
> "Erland" <Erland.Erikson@.gmail.com> wrote in message
> news:1132539237.114914.201650@.o13g2000cwo.googlegroups.com...
> > Hi,
> > I have read restore on BOL , but i am unable to restore a SQL 7.0 back
> > up to SQL Server 2000. I have tried things given in the following
> > article
> > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_7_backpt_15ro.asp
> >
> > and i have also tried to restore the back up by using the following
> > procedure.
> > My database name is "Hello". I have first created a new database named
> > "Hello" by using Enterprise Manager and then i opened up Query Analyzer
> > and choose the database named 'Hello' from dropdown and wrote down the
> > following
> > Restore DATABASE HELLO
> >>From disk = 'd:\Documents and
> > Settings\Administrator\DEsktop\backup\Hello'
> > WITH Move 'Hello' to 'D:\test\Hello.mdf'
> > Go
> > But i get some errors as in "Exclusive access could not be obtained
> > because the datavase is in use' and 'Restore Database is terminating
> > abnormally.
> > Note: My SQL 7.0 back up stored in the location 'd:\Documents and
> > Settings\Administrator\DEsktop\backup\Hello'. I am using SQL Server
> > 2000Enterprise Edition on Windows 2000 Advanced Server.
> > Please do reply me with what i ammissing. I have read quite a few
> > things on BOL and on MSDN. However, i am stumped. I would really
> > appreciate any help as im a newbie so please point me to the solution
> > or to any article / online-resource that would lead me to the solution.
> > Take care and looking forward.
> > Please do reply me what i am missing here.
> >

Restoring database question plz help!

Here is the question if i reinstall sql server 2000 ent can i use my full back up file to reproduce all of my prior existing database and do i need the data file like the .ldf, .mdf, .ndf to do this and if so i do i do this without the data filesYour backup file should be all you need to restore your database to the moment that it was backed up. You will need to restore log files to recover any changes since the backup was made.
You should test your backup/restore plan first to make sure it works and you know how to do it before you wipe out your current installation!|||OK thanx but i cant test this because my sql server service doesnt want to start and i have admin rights. it givin me a unexpected error is the best thing here to reinstall sql server and then restore the back up file.|||basically rebuild the ole thing again.