Extracting Select data from SQL to Access

Question

Extracting Select data from SQL to Access

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

gijs.wuyts SSC Enthusiast Points: 141 More actions · Answer 1

thanks for the many answers. I will try that backup method first

My goal is to run a query to extrqct the data that I need, and use this to populate an Access DB. then I can report on it without putting load on the PROD DB. That will be step two, using a maketable query

G

jasona.work SSC Guru Points: 50118 More actions · Answer 2

gijs.wuyts (3/4/2016)
thanks for the many answers. I will try that backup method first
My goal is to run a query to extrqct the data that I need, and use this to populate an Access DB. then I can report on it without putting load on the PROD DB. That will be step two, using a maketable query
G

Again, as many have said, once you get a backup *AND* restore the backup somewhere else (grab an unused desktop machine or something) you could then use the restored database for your reporting without putting any load on the production system.

What I would suggest as the steps:

1. Get a second PC to use

2. Install either the same version of SQL Server, or one a version or two newer at most

3. Backup (using the SQL BACKUP DATABASE command, NOT a copy of the mdf / ldf) the production DB (best to do this after hours)

4. Copy the resulting backup file (there will be only one, unless you specify multiple files) to the 2nd PC

5. Restore (using the SQL RESTORE command) the database to the 2nd PC

6. If the restore works, you've not got a copy of your production DB, which is exactly the same as the production DB to the point when the backup completed, which you could then use for your reporting.

7. Now, set up a on-going backup plan, possibly including frequent restores to your 2nd PC to keep the reporting data relatively current, and get those backup files OFF the production machine.

Whether your backup plan is someone nightly running a BACKUP DATABASE, or you schedule it using Agent or Task Scheduler, doesn't matter (Task Scheduler would be a bit more work, you'd have to use SQLCMD) because if / when that production server dies, you've got the database and the data. If you leave those backups on the PC, if the hard drive fails, how will you get at them to restore them?

pietlinden SSC Guru Points: 63506 More actions · Answer 3

Or you could install Reporting Services and do the reporting there... Kind of depends what kind of reporting you're doing. One thing that Access doesn't do well is crosstabs/matrix reports.

Grant Fritchey SSC Guru Points: 398898 More actions · Answer 4

pietlinden (3/4/2016)
Or you could install Reporting Services and do the reporting there... Kind of depends what kind of reporting you're doing. One thing that Access doesn't do well is crosstabs/matrix reports.

But, SSRS doesn't offload the access to the data. That would still have to be on the production server, which is apparently under undo strain. SSRS wouldn't relieve that, it would only add to it.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Wayne West SSC-Insane Points: 22586 More actions · Answer 5

I've worked with Access since the 1.0 edition, and I would not recommend trying to cram 10 gig of data in to it. I've never tried, it's possible others have and have had no problems doing it. But I would not. That amount of data, especially if you're going to do moderately complex reporting, really cries out for SQL Server. The 2016 edition of Access claims to have a SQL Server back end, but I don't know what that entails.

What I would recommend is get SQL Server Express edition: it supports a database up to 10 gig, I would expect that you might be able to trim down your database slightly -- just because a database file is 10 gig doesn't mean there's 10 gig of information in it.

If you have the free disk space, create a copy of the database, trim out anything that you don't need for reporting to reduce the size, then move a copy of that to your Express installation.

Express edition free, you can run it on your PC and develop your reporting system from there. As has been previously said, your employer really needs to look at upgrading your server if the production database is running on XP and choking on 10 gig, that's not a really huge amount of data. I understand that you're on a legacy system, you might be able to upgrade the SQL Server by setting the Compatibility Level to something lower to fool the PLCs. It would take some experimenting.

-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]