Help with BAK file

Hi all,
I'm not an expert with SQL2000 and need some help. I have a full backup "BAK
file" that was produced from an SQL2000 server. Is there any way that I can
use that file to rebuild that database on another SQL2000 server?
Thanks,
Clausdid you try to force the restore over the existing database?
cjobes wrote:
> Thanks for the quick answer.
> Well, I tried everything I can think of. I first created a Database with the
> same name as the database on the old server. Then I tried to restore, first
> selecting Device and pointing to the BAK file. I got a message "Device
> activation error. The physical file name xxx maybe incorect. File
> xxx_Data.mdf cannot be restored to yyy_Data.mdf. Use WITH MOVE to identify a
> valid location for the file." The same error msg follows with regard to the
> xxx_log.ldf file.
> Any idea?
> Claus
> "ilovesql" <ilovesql@.hotmail.com> wrote in message
> news:eFS3oBiSDHA.1724@.TK2MSFTNGP10.phx.gbl...
>>Sure, that what backups are for.
>>If the backup was created using single backup file then restore is even
>>easier.
>>To speedup restore and avoid network traffic, in case your database is
> big,
>>then copy the backup file to 2nd server and use the restore command.
>>One issue that you will face after restoring from different server is
>>security that you can deal with later, once restore is completed.
>>"cjobes" <cjobes@.nova-tech.org> wrote in message
>>news:#HHTC0hSDHA.632@.tk2msftngp13.phx.gbl...
>>Hi all,
>>I'm not an expert with SQL2000 and need some help. I have a full backup
>>"BAK
>>file" that was produced from an SQL2000 server. Is there any way that I
>>can
>>use that file to rebuild that database on another SQL2000 server?
>>Thanks,
>>Claus
>>
>>
>|||Yes I did check the checkbox.
"Maria" <maria.belli@.*nospam*rfsworld.com> wrote in message
news:%236X%232MiSDHA.1688@.TK2MSFTNGP11.phx.gbl...
> did you try to force the restore over the existing database?
> cjobes wrote:
> > Thanks for the quick answer.
> > Well, I tried everything I can think of. I first created a Database with
the
> > same name as the database on the old server. Then I tried to restore,
first
> > selecting Device and pointing to the BAK file. I got a message "Device
> > activation error. The physical file name xxx maybe incorect. File
> > xxx_Data.mdf cannot be restored to yyy_Data.mdf. Use WITH MOVE to
identify a
> > valid location for the file." The same error msg follows with regard to
the
> > xxx_log.ldf file.
> >
> > Any idea?
> > Claus
> >
> > "ilovesql" <ilovesql@.hotmail.com> wrote in message
> > news:eFS3oBiSDHA.1724@.TK2MSFTNGP10.phx.gbl...
> >
> >>Sure, that what backups are for.
> >>If the backup was created using single backup file then restore is even
> >>easier.
> >>To speedup restore and avoid network traffic, in case your database is
> >
> > big,
> >
> >>then copy the backup file to 2nd server and use the restore command.
> >>
> >>One issue that you will face after restoring from different server is
> >>security that you can deal with later, once restore is completed.
> >>
> >>"cjobes" <cjobes@.nova-tech.org> wrote in message
> >>news:#HHTC0hSDHA.632@.tk2msftngp13.phx.gbl...
> >>
> >>Hi all,
> >>
> >>I'm not an expert with SQL2000 and need some help. I have a full backup
> >>
> >>"BAK
> >>
> >>file" that was produced from an SQL2000 server. Is there any way that I
> >>
> >>can
> >>
> >>use that file to rebuild that database on another SQL2000 server?
> >>
> >>Thanks,
> >>Claus
> >>
> >>
> >>
> >>
> >
> >
>|||Have a look at the following KB articles
HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q314546
INF: Using the WITH MOVE Option with the RESTORE Statement
http://support.microsoft.com/default.aspx?scid=kb;EN-US;221465
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"cjobes" <cjobes@.nova-tech.org> wrote in message
news:eQK1KTiSDHA.2480@.tk2msftngp13.phx.gbl...
Yes I did check the checkbox.
"Maria" <maria.belli@.*nospam*rfsworld.com> wrote in message
news:%236X%232MiSDHA.1688@.TK2MSFTNGP11.phx.gbl...
> did you try to force the restore over the existing database?
> cjobes wrote:
> > Thanks for the quick answer.
> > Well, I tried everything I can think of. I first created a Database with
the
> > same name as the database on the old server. Then I tried to restore,
first
> > selecting Device and pointing to the BAK file. I got a message "Device
> > activation error. The physical file name xxx maybe incorect. File
> > xxx_Data.mdf cannot be restored to yyy_Data.mdf. Use WITH MOVE to
identify a
> > valid location for the file." The same error msg follows with regard to
the
> > xxx_log.ldf file.
> >
> > Any idea?
> > Claus
> >
> > "ilovesql" <ilovesql@.hotmail.com> wrote in message
> > news:eFS3oBiSDHA.1724@.TK2MSFTNGP10.phx.gbl...
> >
> >>Sure, that what backups are for.
> >>If the backup was created using single backup file then restore is even
> >>easier.
> >>To speedup restore and avoid network traffic, in case your database is
> >
> > big,
> >
> >>then copy the backup file to 2nd server and use the restore command.
> >>
> >>One issue that you will face after restoring from different server is
> >>security that you can deal with later, once restore is completed.
> >>
> >>"cjobes" <cjobes@.nova-tech.org> wrote in message
> >>news:#HHTC0hSDHA.632@.tk2msftngp13.phx.gbl...
> >>
> >>Hi all,
> >>
> >>I'm not an expert with SQL2000 and need some help. I have a full backup
> >>
> >>"BAK
> >>
> >>file" that was produced from an SQL2000 server. Is there any way that I
> >>
> >>can
> >>
> >>use that file to rebuild that database on another SQL2000 server?
> >>
> >>Thanks,
> >>Claus
> >>
> >>
> >>
> >>
> >
> >
>|||Thanks for the help. This was exactly what I was looking for and it worked.
Claus
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:ePqDfmiSDHA.2256@.TK2MSFTNGP11.phx.gbl...
> Have a look at the following KB articles
> HOW TO: Move Databases Between Computers That Are Running SQL Server
> http://support.microsoft.com/default.aspx?scid=kb;en-us;Q314546
> INF: Using the WITH MOVE Option with the RESTORE Statement
> http://support.microsoft.com/default.aspx?scid=kb;EN-US;221465
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "cjobes" <cjobes@.nova-tech.org> wrote in message
> news:eQK1KTiSDHA.2480@.tk2msftngp13.phx.gbl...
> Yes I did check the checkbox.
>
> "Maria" <maria.belli@.*nospam*rfsworld.com> wrote in message
> news:%236X%232MiSDHA.1688@.TK2MSFTNGP11.phx.gbl...
> > did you try to force the restore over the existing database?
> >
> > cjobes wrote:
> > > Thanks for the quick answer.
> > > Well, I tried everything I can think of. I first created a Database
with
> the
> > > same name as the database on the old server. Then I tried to restore,
> first
> > > selecting Device and pointing to the BAK file. I got a message "Device
> > > activation error. The physical file name xxx maybe incorect. File
> > > xxx_Data.mdf cannot be restored to yyy_Data.mdf. Use WITH MOVE to
> identify a
> > > valid location for the file." The same error msg follows with regard
to
> the
> > > xxx_log.ldf file.
> > >
> > > Any idea?
> > > Claus
> > >
> > > "ilovesql" <ilovesql@.hotmail.com> wrote in message
> > > news:eFS3oBiSDHA.1724@.TK2MSFTNGP10.phx.gbl...
> > >
> > >>Sure, that what backups are for.
> > >>If the backup was created using single backup file then restore is
even
> > >>easier.
> > >>To speedup restore and avoid network traffic, in case your database is
> > >
> > > big,
> > >
> > >>then copy the backup file to 2nd server and use the restore command.
> > >>
> > >>One issue that you will face after restoring from different server is
> > >>security that you can deal with later, once restore is completed.
> > >>
> > >>"cjobes" <cjobes@.nova-tech.org> wrote in message
> > >>news:#HHTC0hSDHA.632@.tk2msftngp13.phx.gbl...
> > >>
> > >>Hi all,
> > >>
> > >>I'm not an expert with SQL2000 and need some help. I have a full
backup
> > >>
> > >>"BAK
> > >>
> > >>file" that was produced from an SQL2000 server. Is there any way that
I
> > >>
> > >>can
> > >>
> > >>use that file to rebuild that database on another SQL2000 server?
> > >>
> > >>Thanks,
> > >>Claus
> > >>
> > >>
> > >>
> > >>
> > >
> > >
> >
>
>

HELP w/ multiple table join on foreign keys

I'm scratching my head, not a sql expert so i need help w/ the following:

i have three tables in Access:
airportNames, facililtyID, facilityNames

i'm using facilityID as the intermediate table to join above tables.
i'm passing a value to the WHERE clause in my sql statement comparing airportNames.strCode.

Here's the sql that doesn't work, it generates an error:

SELECT facilityNames.strName, airportNames.strName, airportNames.strCode FROM airportNames
INNER JOIN (facilityNames
INNER JOIN (facilityID
ON facilityID.IDAirportNames = airportNames.IDAirportNames)
ON facilityNames.IDFacilityNames = facilityID.IDFacilityNames)
WHERE airportNames.strCode = 'ATL'

I know this can be done but i can't get it to work:eek:what does "can't get it to work" mean? does your query cause the server to crash? does the query actually run? if not, does it produce an error message? if so, what is the error message? if it runs, does it return any rows? no rows? the wrong rows?

please show your table layouts, and if possible, a few rows of sample data from each table|||in SQL Analyzer (a freebie) i get a dialog that says Error Number: -2147217900|||please show your table layouts, and if possible, a few rows of sample data from each table|||Jet (the database engine that underlies Microsoft Access and other tools) can be charitably described as tempermental. Does it work if you use:SELECT facilityNames.strName, airportNames.strName, airportNames.strCode
FROM airportNames
INNER JOIN (facilityNames
INNER JOIN (facilityID
ON facilityID.IDAirportNames = airportNames.IDAirportNames
ON facilityNames.IDFacilityNames = facilityID.IDFacilityNames))
WHERE airportNames.strCode = 'ATL'-PatP|||pat, you're a genius

but it's probably not jet being finicky, it's probably that we have our table and column names bacwards

there is no table called facilityID, that's a column used to join tables

his syntax is all wrong, and i tried to fix it and got confused

i cannot guess which columns the tables should be joined on without some more info|||I was just working from the original posting... I don't have a clue what they've really got, but at least what they posted was consistent even if it may not have been correct.

-PatP|||Here's what the three tables involved look like in structure and data:

Table airportNames

IDAirportNames | strName | strCode
1 | Hartsfield | ATL
2 | Birmingham | BHM

Table facilityID

IDFacility | IDFacilityNames | IDAirportNames
1 | 1 | 1
2 | 0 | 1

Table facilityNames

IDFacilityNames | strName
1 | XYZ facility

Does this help?? FYI, I'm using access 2000.|||Pat Phelan, tried your query but i still get an error. man, either access is a pos, this sql analyzer thing is a pos or both. i need to get this thing working:(|||SELECT facilityNames.strName
, airportNames.strName
, airportNames.strCode
FROM (
airportNames
INNER
JOIN facilityID
on facilityID.IDAirportNames
= airportNames.IDAirportNames
)
INNER
JOIN facilityNames
ON facilityNames.IDFacilityNames
= facilityID.IDFacilityNames
WHERE airportNames.strCode = 'ATL'|||dude, i've been sweating this for a couple of days, thanks for you time on this, if you're ever doing any Flash Actionscript work and need some help, i'm all ears...paul at mediacurrent dot com.

suffice it to say that your sql statement returned exactly what i'd been looking for. now i need to do a full data migration and see what happens...stay tuned. BTW, can i use the INNER JOIN statement in SQL Server or does it only accept JOIN?|||INNER is an optional keyword, JOIN == INNER JOIN

if you migrate this to sql server, you may want to remove the microsoft access parentheses from the FROM clause

flash actionscript? thanks for the offer, but (a) i'm not quite ready to tackle flash yet, and (b) i cannot think of anything that i'd use it for|||one more question, i've read some stuff on indexing foreign keys for better join performance. would you recommend this? can a foreign key be indexed if duplicate keys exist in the column?|||yes, do create indexes on foreign keys

and yes, they can