How can I restore the db successfully

I'm a new user to SQL Server.
I use the Restore Database function in the Entreprise Manager to restore a database. But I have no idea about why it prompt error when I click OK and say :
Cannot find file ID 2 on device c:\Program File\Microsoft SQL Server\MSSQL\BACKUP\abc'. RESTORE DATABASE is terminating abnormally.
On the other hand, if I have a backup database file from someone, how can I restore it in the normal way.
1st Step: New a Database (no change to the setting)
2nd Step: Right click DB->Restore Database-> but how can I choose the backup database file that someone give me.?
Best regards,
Grace
Hi,
How can I choose the backup database file that someone give me.?
In the Restore databaase window -- Select the option button "From Device" ,
Click the "Select Device" -- Click "Add"
and in the file name get the .BAK file from the directory and click "OK".
There you select Backup Number "View contents" . This
will show you all the files associated with the backup file.
If you have mutiple files select the one you require and click "OK" to
restore.
Cannot find file ID 2 on device c: ?
I feel that this Backup is taken in mutiple files (Devices) and you have
only one file. In that case you cant restore till u get the other file.
Thanks
Hari
MCDBA
"Grace" <anonymous@.discussions.microsoft.com> wrote in message
news:17BBF6CC-AC73-44FE-A31D-AE0848ED7B83@.microsoft.com...
> I'm a new user to SQL Server.
> I use the Restore Database function in the Entreprise Manager to restore a
database. But I have no idea about why it prompt error when I click OK and
say :
> Cannot find file ID 2 on device c:\Program File\Microsoft SQL
Server\MSSQL\BACKUP\abc'. RESTORE DATABASE is terminating abnormally.
> On the other hand, if I have a backup database file from someone, how can
I restore it in the normal way.
> 1st Step: New a Database (no change to the setting)
> 2nd Step: Right click DB->Restore Database-> but how can I choose the
backup database file that someone give me.?
> Best regards,
> Grace
|||Grace,
The restore dialog in EM can use the backup history produced when you took the backup. But that doesn't
necessarily reflect what you actually have on the backup files. In this case, it seems like the backup history
say that there should be at least two backups on the backup file, and there isn't. Perhaps the backup file
doesn't exist at all...
If you want to restore a backup for a database which doesn't exists, *do not* create the database first. If
you do, you will most probably not create it with the same file structure as you had when you took the backup.
Let the restore process create the database for you. Just enter the restore dialog, type in the database name,
and select "from device". Here you can specify the backup file from which you want to do the restore.
I strongly suggest you read about how backup and restore work in Books Online, without that knowledge, you
might find above a bit difficult to understand. Also, I find it easier to work with the BACKUP and RESTORE
commands from Query Analyzer instead of using a GUI like EM. At least until you have good understanding about
the architecture of backup and restore and then understand more of how it work and what EM does when you
select options in the GUI. Just my opinion, that is...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Grace" <anonymous@.discussions.microsoft.com> wrote in message
news:17BBF6CC-AC73-44FE-A31D-AE0848ED7B83@.microsoft.com...
> I'm a new user to SQL Server.
> I use the Restore Database function in the Entreprise Manager to restore a database. But I have no idea
about why it prompt error when I click OK and say :
> Cannot find file ID 2 on device c:\Program File\Microsoft SQL Server\MSSQL\BACKUP\abc'. RESTORE DATABASE
is terminating abnormally.
> On the other hand, if I have a backup database file from someone, how can I restore it in the normal way.
> 1st Step: New a Database (no change to the setting)
> 2nd Step: Right click DB->Restore Database-> but how can I choose the backup database file that someone give
me.?
> Best regards,
> Grace
|||You are trying to restore from database, which uses the history of backups. The history path is different from the path of the file on the disk you are trying to restore from.
To restore from the file you have, you should click the bullet marked device (change from database) and browse to the actual file system file you are trying to restore the database to(via select devices... then Add...button).
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...

how can i restore if my dropped a wrong table and recreated it?

Is it possible to restore it if i dropped a wrong table and recreated it?
can i restore the whole table without backups?
thanks a lot..If the transaction is committed, you cannot roll back this operation per se.
If you do regular database and transaction log backups, you can now backup t
he transaction log and then use
your latest db backup and then all subsequent backups to restore up until ju
st before that operations.
If not, you *might* be able to undo the operation using some 3:rd party tool
, but all of them requires that
the transaction log records are still inside the transaction log. See my web
-site, the link page (log reader
products).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Tea" <tea@.softhome.net> wrote in message news:uSbUJB2FEHA.712@.tk2msftngp13.phx.gbl...[colo
r=darkred]
> Is it possible to restore it if i dropped a wrong table and recreated it?
> can i restore the whole table without backups?
> thanks a lot..
>[/color]|||Hi,
To Add on to Tibors post, You database should be set in "FULL" recovery
model.
In that case if you have FULL database backup + all the transaction log
backups you can do a point in time recovery.
How to do:
1. Perform a transaction log backup of the database
2. Restore the FULL backup to a new database with NORECOVERY
3. Restore the Subsequent transaction log backups with NORECOVERY till the
last trasnaction log file backed up in step - 1
4. Restore the Last trasnaction log backup with RECOVERY and STOPAT date and
time.
In this case your new database will restored till the time you have
mentioned in STOPAT option.
Thanks
Hari
MCDBA
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ulvP0F2FEHA.2308@.tk2msftngp13.phx.gbl...
> If the transaction is committed, you cannot roll back this operation per
se.
> If you do regular database and transaction log backups, you can now backup
the transaction log and then use
> your latest db backup and then all subsequent backups to restore up until
just before that operations.
> If not, you *might* be able to undo the operation using some 3:rd party
tool, but all of them requires that
> the transaction log records are still inside the transaction log. See my
web-site, the link page (log reader
> products).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Tea" <tea@.softhome.net> wrote in message
news:uSbUJB2FEHA.712@.tk2msftngp13.phx.gbl...
it?
>

how can i restore if my dropped a wrong table and recreated it?

Is it possible to restore it if i dropped a wrong table and recreated it?
can i restore the whole table without backups?
thanks a lot..
If the transaction is committed, you cannot roll back this operation per se.
If you do regular database and transaction log backups, you can now backup the transaction log and then use
your latest db backup and then all subsequent backups to restore up until just before that operations.
If not, you *might* be able to undo the operation using some 3:rd party tool, but all of them requires that
the transaction log records are still inside the transaction log. See my web-site, the link page (log reader
products).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Tea" <tea@.softhome.net> wrote in message news:uSbUJB2FEHA.712@.tk2msftngp13.phx.gbl...
> Is it possible to restore it if i dropped a wrong table and recreated it?
> can i restore the whole table without backups?
> thanks a lot..
>
|||Hi,
To Add on to Tibors post, You database should be set in "FULL" recovery
model.
In that case if you have FULL database backup + all the transaction log
backups you can do a point in time recovery.
How to do:
1. Perform a transaction log backup of the database
2. Restore the FULL backup to a new database with NORECOVERY
3. Restore the Subsequent transaction log backups with NORECOVERY till the
last trasnaction log file backed up in step - 1
4. Restore the Last trasnaction log backup with RECOVERY and STOPAT date and
time.
In this case your new database will restored till the time you have
mentioned in STOPAT option.
Thanks
Hari
MCDBA
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ulvP0F2FEHA.2308@.tk2msftngp13.phx.gbl...
> If the transaction is committed, you cannot roll back this operation per
se.
> If you do regular database and transaction log backups, you can now backup
the transaction log and then use
> your latest db backup and then all subsequent backups to restore up until
just before that operations.
> If not, you *might* be able to undo the operation using some 3:rd party
tool, but all of them requires that
> the transaction log records are still inside the transaction log. See my
web-site, the link page (log reader
> products).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Tea" <tea@.softhome.net> wrote in message
news:uSbUJB2FEHA.712@.tk2msftngp13.phx.gbl...
it?
>
sql

How can I restore a SQL 6.5 database backup to a SQL 7.0/2000 server?

Thanks,
Pedro Rodrigues
pedro@.markdata.ptYou can't. You need to get it into a 6.5 SQL Server and then use the Upgrade
Wizard that comes with 7.0 (and 2000) to get the data over to 7.0/2000.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Pedro Cunha Rodrigues" <pedro@.markdata.pt> wrote in message
news:OWmocMnvDHA.2340@.TK2MSFTNGP12.phx.gbl...
> Thanks,
> Pedro Rodrigues
> pedro@.markdata.pt
>

How can I restore a SQL 6.5 database backup to a SQL 7.0/2000 server?

Thanks,
Pedro Rodrigues
pedro@.markdata.ptYou can't. You need to get it into a 6.5 SQL Server and then use the Upgrade
Wizard that comes with 7.0 (and 2000) to get the data over to 7.0/2000.
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Pedro Cunha Rodrigues" <pedro@.markdata.pt> wrote in message
news:OWmocMnvDHA.2340@.TK2MSFTNGP12.phx.gbl...

quote:

---

> Thanks,
> Pedro Rodrigues
> pedro@.markdata.pt
>

---

How can I restore a SQL 2005 database on my SQL 2000?

My customer has SQL Server 2005. I have SQL Server 2000 on my laptop (on XP Pro). I believe I have an empty database the same as theirs, but I need to restore their data. They sent me a normal SQL Backup, but my laptop threw a fit and cannot work with it.

They are now trying to export the database to Microsoft Access and send me that, but I won't get that until next week now. However is there a better and easier solution for those of us with insufficient experience of this, please?

Many thanks,
CasparThere is no way to restore SQL 2005 backup on SQL 2000.

Easiest way in this case is - install Express Edition of SQL 2005, restore your backup there and then import it to SQL 2000.|||Or you might consider skipping the import to SQL 2000, and just work on it using SQL 2005 Express on your laptop.

-PatP|||Thank you so much for this advice. I am in the process of trying it, having found downloads at: http://msdn.microsoft.com/vstudio/express/sql/download/ where I am offered 2 choices - I have chosen the left-hand pair of files for a simpler setup.

If I am successful in restoring the database to this then if I do decided I want to import it into SQL 2000 then how to I go about that, please?

Many thanks,
Caspar

How can I restore a field back to Null?

Hello,
I need to restore fields for records meeting a certain criteria back to
null. Is there a way to do this in a stored procedure?
dbuchanan
I figured it out.
\\
Update tbl040cmpt SET
cmSmallint08 = cmSmallint05,
cmSmallint04 = null
FROM tbl040cmpt
WHERE fkJob = 'd8779793-5f1a-4092-bad3-bf3ee5b50c3e'
//
dbuchanan

How can I restore a field back to Null?

Hello,
I need to restore fields for records meeting a certain criteria back to
null. Is there a way to do this in a stored procedure?
dbuchananI figured it out.
\\
Update tbl040cmpt SET
cmSmallint08 = cmSmallint05,
cmSmallint04 = null
FROM tbl040cmpt
WHERE fkJob = 'd8779793-5f1a-4092-bad3-bf3ee5b50c3e'
//
dbuchanansql

How can I restore a field back to Null?

Hello,
I need to restore fields for records meeting a certain criteria back to
null. Is there a way to do this in a stored procedure?
dbuchananI figured it out.
\\
Update tbl040cmpt SET
cmSmallint08 = cmSmallint05,
cmSmallint04 = null
FROM tbl040cmpt
WHERE fkJob = 'd8779793-5f1a-4092-bad3-bf3ee5b50c3e'
//
dbuchanan

How Can I Restore a Database to Different Files and ...............

Hi,

I have a database that over time has become spread over different files, file groups all of various sizes.

I want to restore this database to a different set of files/filegroups and evenly spread.

It appears that I can only resotore a database to number/of and size of files from which it was backed up..

I want to redistribute a 40GB file, using EMPTY is taking for ever and then eventually fails.

What can I do?

Thanks for your help

Try adding several new data files, then doing a shrink-empty on the big file to get it to be spread out to the new files. If the big data file is your primary data file, that will not work.

Suggestion two is to Rebuild the clustered index for several of your larger tables into the new files. This will move the data.

How can I restore a database from a UNC?

It appears that Management Studio will not allow you to perform a restore from a UNC path. Is this true or am I missing the option to open the magic UNC door? I am very new to SQL2005 and this was buried in SQL2000, but it was possible to do in Enterprise Manager.

Have you tried using the raw TSQL query to start the restore, and not use the GUI Interface.

|||

Not to be snotty, but you didn't answer the question. Your point is taken, there are other ways to do this. I can use a script to restore from a UNC path. I am still interested in getting an answer to the question I posted, not to the underlying assumption that I am looking for any way to accomplish a restore from UNC.

As a DBA, I do many operations by script; however, there are times that, for a number of reasons, the UI is a better option. Since this was something that could be done in SQL2000 through the UI, I thought that perhaps Microsoft would also provide a means to do it through the UI in 2005. We have non-technical folks who need to do restores from different sources for demos. It is more practical to teach them how to use the UI to grab the backup they need for their laptop rather than scripting the numerous possibilities or having them alter a script.

So, if anyone knows if Management Studio can be used to do a UNC restore, please share with me how it can be done.

|||Hi,

this is sure possible: Select Restore Files and Filegroup --> Fome Device --> Add --> Enter a Full qualified name in the filename textbox like \\m2-jenss\SomeShare\Somebakfile.bak

and you are done.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||You nailed it. I initially tried that, but permissions foiled me, but I misread it as a limitation of the UI because the Selected Path pointed to a local drive and the field was disabled. Apparently, anything you put in File name will override the Selected Path entry if using the UNC path. Thanks a ton!

How Can I recover master database from master's backup?

I did a new instalation, and I want to recover master
database from master's backup, but the system show me the
next message RESTORE DATABASE must be in single user mode
when trying to restore the master database.
It's very important to me get information about the
aplication's users but I can't do that.
The system don't permit to put in single user mode to
master then I don't no what to do for resolving this.
Somebody Help me ?
Thanks LorenaSee my other post. No need to re-post.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Lorena" <anonymous@.discussions.microsoft.com> wrote in message
news:bba401c40dea$4e4130a0$a001280a@.phx.gbl...
> I did a new instalation, and I want to recover master
> database from master's backup, but the system show me the
> next message RESTORE DATABASE must be in single user mode
> when trying to restore the master database.
> It's very important to me get information about the
> aplication's users but I can't do that.
> The system don't permit to put in single user mode to
> master then I don't no what to do for resolving this.
> Somebody Help me ?
> Thanks Lorena
>sql

How can i purchase this MSDE, and where should i go

How can i purchase this MSDE, and where should i go, Where can i purchase
MSDE bakup/restore alone.
MSDE is free.
u have to use command line utilities for the backup / restore, but thats
free too.
You can buy third party utilities to simplify your task but is not
mandatory.
Hope that helps.
"Guest_MSDE" <Guest_MSDE@.discussions.microsoft.com> wrote in message
news:F5F88619-E66D-4B79-B5A8-1F395DA0B831@.microsoft.com...
> How can i purchase this MSDE, and where should i go, Where can i purchase
> MSDE bakup/restore alone.
|||A free tool for back up and restore:
www.xfair.com
Rahul Kumar
http://dotnetyogi.blogspot.com
This message is provided "AS IS" with no warranties, and confers no rights.
Any opinions or policies stated within it are my own and do not necessarily
constitute those of my employer.
"Guest_MSDE" <Guest_MSDE@.discussions.microsoft.com> wrote in message
news:F5F88619-E66D-4B79-B5A8-1F395DA0B831@.microsoft.com...
> How can i purchase this MSDE, and where should i go, Where can i purchase
> MSDE bakup/restore alone.