Restore SQL Sever 2005 backup database

[Vinod]

Hi All,

Here are my requirements,

1. I would like to see all existed tables from a SQL Server 2005 database
which it may be in same machine(where access is running) or from a network
system.

2. For every one week it is required to restore back up database for SQL
Server 2005 (.bak file) which I would receive from client so that I can
expect latest data will be available in access tables as discussed in first
requirement. - Basically this functionality should run by clicking a button
from a form in Access.

3. Is there any chance to get link tables in access from .bak file (SQL
Server 2005) without restoring backup database for SQL Server 2005?

It would be great for me if anyone could share their thought or solution in
stepwise for above 3 requirements and it would be appereciated.

Advanced Thanks,
Vinod

 

[Douglas J. Steele]

I think you'd have far more luck asking this in a newsgroup related to SQL
Server.

 

[Vinod]

Thanks Douglas J. Steele for immediate response.

As mentioned, all 3 requirements should work from MS Access only. Guide me
how to post these queries in newsgroup related to SQL Server, since there is
no option available to select sql server in left panel of discussion group.

Thanks
Vinod

 

[David H]

I typically hit up SQL Server Central at www.sqlservercentral.com
There's a forum for newbies which makes asking questions a bit less
embarrassing.

You'll want to post there from this point forward.

(See below for additional comments)

Hi All,

Here are my requirements,

1. I would like to see all existed tables from a SQL Server 2005 database
which it may be in same machine(where access is running) or from a network
system.

You'll need to set up a ODBC connection use SQL Server as the datasource.
Its pretty straight forward.

2. For every one week it is required to restore back up database for SQL
Server 2005 (.bak file) which I would receive from client so that I can
expect latest data will be available in access tables as discussed in first
requirement. - Basically this functionality should run by clicking a button
from a form in Access.

Do you intend on importing data from SQL Server into an Access .mdb file?
I'd skip this point as its much easier just to connect to SQL Server.

3. Is there any chance to get link tables in access from .bak file (SQL
Server 2005) without restoring backup database for SQL Server 2005?

No. The .bak file *MUST* be restored first before you can access any of the
contents. SQL Server Express if freely available from Microsoft so there'd be
no additional cost. If your client is on an older version of SQL Server, the
..BAK file can be restored using SQL SE 2008, which is quite easy. The only
limitation to be aware of is that the DB's can't be larger than 4GB when
using SQL SE.

 

[Vinod]

Yes David you can forward.

I typically hit up SQL Server Central at www.sqlservercentral.com
There's a forum for newbies which makes asking questions a bit less
embarrassing.

You'll want to post there from this point forward.

(See below for additional comments)


You'll need to set up a ODBC connection use SQL Server as the datasource.
Its pretty straight forward.

Do you intend on importing data from SQL Server into an Access .mdb file?
I'd skip this point as its much easier just to connect to SQL Server.

No. The .bak file *MUST* be restored first before you can access any of the
contents. SQL Server Express if freely available from Microsoft so there'd be
no additional cost. If your client is on an older version of SQL Server, the
.BAK file can be restored using SQL SE 2008, which is quite easy. The only
limitation to be aware of is that the DB's can't be larger than 4GB when
using SQL SE.

 

[Vinod]

Thanks David,

Sorry I was not looking at your inline comments for each question.

I'm clear on your reply on #1 and #3. To answer for your question at #2 is
Yes.
To make it clear on #2 - Now I'm able to get link tables from 'test_1'
database (Sql server 2005). One week later it is required to update 'test_1'
database with client provided backup copy of 'test_1' from another server. In
this way linked tables in access will have latest data.

My requirement is as follows:
In Acces there will be a button called 'Update SQL Database', on clicking of
this button a form will be opened. There user will select client's backup
database file (path & file name) through a browse button, after this user
will click on 'Update' button then 'test_1' should be updated with selected
'.bak' file.

Note: '.bak' file may exist at 'ftp' or in network path.
I'm using SQL Sever Management studio 2005 and I don't think there will be
major difference between SQL Server Management studio and SQL Server Express.

Please help me by sharing your thoughts to fullfill my requirements.

Advanced Thanks
Vinod

 

[David H]

Thanks David,

Sorry I was not looking at your inline comments for each question.

I'm clear on your reply on #1 and #3. To answer for your question at #2 is
Yes.
To make it clear on #2 - Now I'm able to get link tables from 'test_1'
database (Sql server 2005). One week later it is required to update 'test_1'
database with client provided backup copy of 'test_1' from another server. In
this way linked tables in access will have latest data.

? - Are you physically IMPORTING the data from SQLServer to Access, or
merely LINKING to the tables in the SQLServer database.

My requirement is as follows:
In Acces there will be a button called 'Update SQL Database', on clicking of
this button a form will be opened. There user will select client's backup
database file (path & file name) through a browse button, after this user
will click on 'Update' button then 'test_1' should be updated with selected
'.bak' file.

Regardless of the answer above, Access cannot read the .bak file. The .bak
file *must* be restored by SQLServer first before you can access any data.
The restore can only be done by SQLServer, but it might be possible to
automate it via a stored procedure.

 

[Tony Toews [MVP]]

There's a forum for newbies which makes asking questions a bit less
embarrassing.

There's never an embarrassing question for someone to ask. We were
all newbies once.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[David H]

I know but sometimes I myself have asked questions that seem embarrassing,
such as how do you restore a .bak file to a server on which the database
doesn't exist using SQL Server Express 2008. (The restore window doesn't
state explicitly that you just type in the name of the new database.)

 

[Tony Toews [MVP]]

I know but sometimes I myself have asked questions that seem embarrassing,
such as how do you restore a .bak file to a server on which the database
doesn't exist using SQL Server Express 2008. (The restore window doesn't
state explicitly that you just type in the name of the new database.)

Have you noticed how Windows Vista and Windows 2008 Terminal Server
now have greyed letters something like "Enter password" in the
password box when it's empty. That's exactly the same problem. The
software designers take it for granted that everyone knows to key in a
password there. Which, of course, is not the case.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[David H]

No I hadn't noticed that because I will *NEVER* use Vista. When I bought a
new laptop for my personal use, I went to great lengths to the buy one with
Genuine Vista Business Downgrade to XP.

 

[Tony Toews [MVP]]

No I hadn't noticed that because I will *NEVER* use Vista. When I bought a
new laptop for my personal use, I went to great lengths to the buy one with
Genuine Vista Business Downgrade to XP.

I have a client running Win 2008 Terminal Server in which I log in. I
also run Vista in a Virtual PC session so I can test things.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/