March 3, 2016 at 6:48 am
Hello,
feel free to correct me if this is posted in the wrong forum.
Situation: I have a legacy MS SQL DB running, PROD DB mdf file size is 10Gb. (data from 2011 onwards)
This platform is part of a legacy BMS system (building management), and logs data like temperatures, flow volumes etc, linked to a rather old plc network, therefor cannot migrate to new platform.
I have tried attaching a copy of the mdf file to another MS SQL file but I'm running into a corruption issue.
I need a subset of the data in ACCESS or other local DB, for reporting purposes. Live reporting on the PROD DB is not possible due to performance.
My idea would be to have a query which selects the data that I want, and put that in a newly created DB/table.
I've been able to do a test connection to the platform's secundary server and import a complete table (only 500k data), but as said before, can't do that on the production file due to it's size.
How would I proceed with this, is there a way to apply some moderation in the sql query, in order not to pump the PROD DB to death?
I'm a DB/SQL newbie, just had a small intro to SQL 20yrs ago...
Thanks,
G
March 3, 2016 at 7:23 am
You could create a new instance of your database, make it read-only, and then report on that. If you don't need real-time data, then that's the easiest.
You could just do the restore whenever you needed it. And schedule the backup for a time when nobody is using the database.
Then you could use SSRS for reporting... Or Excel/PowerBI or Access... depends what kind of reporting you're doing.
March 3, 2016 at 7:36 am
thanks, but I cannot put any additional load on the legacy platform (WIN XP industrial type PC).
My goal would be to run the query one time to transfer the data to a new table in the access or other db, if possible throttled or I can schedule a maintenance window. But any additional action on the SQl server brings too much risk.
Thanks
G
March 3, 2016 at 7:43 am
Do you not have a backup you can restore to another instance?
You said "corruption issues", what seems to be the issue/error message?
March 3, 2016 at 7:50 am
The only backup I made was by copying this mdf and ldf file.
I'm a bit afraid the 10 GB size is the cause of the issue.
When I try to attach it to another instance:
Microsoft SQL-DMO (ODBC SQL State: HY000)
Error 3624:
Location: recovery.:2021
Expression: curr->GetOpCode () != LOP_NULL
SPID: 53
Process ID: 1876
Thanks
G
March 3, 2016 at 7:55 am
I would use either a proper backup script or you could right-click the database, and choose Backup... from the menu. Just to make sure you didn't damage it when it was being backed up. (Or you could run DBCC CheckDB).
Then just move it and attach it to the other instance, and test it out. Are you using the Developer edition of SQL Server or one of the others? (Can't remember the size limit on developer, but I know it has one.)
March 3, 2016 at 7:58 am
Do they not have any backups of the database (.bak files)?
How big is the log file and how big is the data file?
March 3, 2016 at 8:01 am
If you can connect to the SQL database from Access, you can link to the tables/views in SQL Server and then run a bunch of MakeTable queries in Access. Then create the relationships between your tables and create queries and reports...
March 3, 2016 at 8:08 am
with MakeTable queries you mean a series of pre-composed queries?
March 3, 2016 at 8:10 am
no, only copies of the mdf and ldf
10gb mdf and 1.2g ldf
March 3, 2016 at 8:10 am
version is ms sql 8.00
March 3, 2016 at 8:19 am
This might work - https://msdn.microsoft.com/en-GB/library/ms174385.aspx
Only try it on a test instance.
Otherwise taking a backup is going to be the best way to go, if they dont have one they really should. Otherwise slightly further down the line they will have bigger issues.
Good Luck!!
March 3, 2016 at 8:29 am
If you create a new database in Access, it won't have anything in it. So you create a connection to the SQL Server database, and then link to the data tables in SQL Server. At this point, there's no data in Access just links to SQL Server tables. Then you can create MakeTable queries that get the data from your SQL tables and bring the data into Access. At that point, you could delete the links to the SQL Server tables, because all the data you need is in Access. Be aware that Access has a maximum file size, though. If you have a version of SQL Server other than Developer, you will not have that restriction.
The maximum file size in Access has grown slowly over the years. It used to be 2GB, but could be 10GB for the 64-bit versions, I think. If you make a file that's too big in Access, I think it just won't open. Can't remember as I haven't seen it in a while.
March 3, 2016 at 11:24 am
Best approach would to create a backup file using the BACKUP command, and then restore that to another location. From there, do whatever you want, but start with the backup. I'd especially start with the backup because it seems very unclear whether or not a good backup of this system exists. If it's as precariously balanced as you say, then I'd want to be sure I had a good, tested (which means a restore has been run with it) backup.
"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
March 4, 2016 at 7:06 am
Echoing what others have said: back up the database and restore it elsewhere. You should be doing that regularly, whether or not you need to extract data for reporting purposes.
You say "Live reporting on the PROD DB is not possible due to performance." and then propose to perform reporting in Access? :w00t:
Sounds like the stability or performance of your SQL Server is in jeopardy; if you or your company would be disappointed if that server crashed, I'd think about figuring out why and fixing it or (if it's an ancient machine) migrating off it soon.
Rich
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply