 | |
SSIS: exporting SQL tables to Access on scheduled basis
microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts
 |
| | Thread Tools | Display Modes |
#51
 
| |||
| |||
|
RE: SSIS: exporting SQL tables to Access on scheduled basis -
10-08-2008
, 10:02 AM
|
Your last suggestion was probably the best of all. With only Standard version, snapshots are out of the question. So do a pseudo-snapshot: Using SSMS, go through the following steps and script each one out to a script window. Read through each one so you understand what is going on: 1) Create a backup of your 'production' database using a script. Make it re-create/over-write the same file each time. 2) Restore that same backup file to *ANOTHER* database. Make sure the script has the "REPLACE" clause. (THis will allow a backup of Database A to overwrite on Database B). Edit this script with some MOVE clauses that re-name the mdf and ldf files because they obviously cannot have the same name and location as the original db. After both scripts have been tested, set them up in a SQL Agent Job that runs every morining at low server workload time. Lastly, point your Access apps to the 2nd Db. Keep us posted. -- Todd C [If this response was helpful, please indicate by clicking the appropriate answer at the bottom] |
#52
| |||
| |||
|
|
Your last suggestion was probably the best of all. With only Standard version, snapshots are out of the question. So do a pseudo-snapshot: Using SSMS, go through the following steps and script each one out to a script window. Read through each one so you understand what is going on: 1) Create a backup of your 'production' database using a script. Make it re-create/over-write the same file each time. 2) Restore that same backup file to *ANOTHER* database. Make sure the script has the "REPLACE" clause. (THis will allow a backup of Database A to overwrite on Database B). Edit this script with some MOVE clauses that re-name the mdf and ldf files because they obviously cannot have the same name and location as the original db. After both scripts have been tested, set them up in a SQL Agent Job that runs every morining at low server workload time. Lastly, point your Access apps to the 2nd Db. Keep us posted. -- Todd C [If this response was helpful, please indicate by clicking the appropriate answer at the bottom] |
#53
| |||
| |||
|
|
Your last suggestion was probably the best of all. With only Standard version, snapshots are out of the question. So do a pseudo-snapshot: Using SSMS, go through the following steps and script each one out to a script window. Read through each one so you understand what is going on: 1) Create a backup of your 'production' database using a script. Make it re-create/over-write the same file each time. 2) Restore that same backup file to *ANOTHER* database. Make sure the script has the "REPLACE" clause. (THis will allow a backup of Database A to overwrite on Database B). Edit this script with some MOVE clauses that re-name the mdf and ldf files because they obviously cannot have the same name and location as the original db. After both scripts have been tested, set them up in a SQL Agent Job that runs every morining at low server workload time. Lastly, point your Access apps to the 2nd Db. Keep us posted. -- Todd C [If this response was helpful, please indicate by clicking the appropriate answer at the bottom] |
#54
| |||
| |||
|
|
Your last suggestion was probably the best of all. With only Standard version, snapshots are out of the question. So do a pseudo-snapshot: Using SSMS, go through the following steps and script each one out to a script window. Read through each one so you understand what is going on: 1) Create a backup of your 'production' database using a script. Make it re-create/over-write the same file each time. 2) Restore that same backup file to *ANOTHER* database. Make sure the script has the "REPLACE" clause. (THis will allow a backup of Database A to overwrite on Database B). Edit this script with some MOVE clauses that re-name the mdf and ldf files because they obviously cannot have the same name and location as the original db. After both scripts have been tested, set them up in a SQL Agent Job that runs every morining at low server workload time. Lastly, point your Access apps to the 2nd Db. Keep us posted. -- Todd C [If this response was helpful, please indicate by clicking the appropriate answer at the bottom] |
#55
| |||
| |||
|
|
Your last suggestion was probably the best of all. With only Standard version, snapshots are out of the question. So do a pseudo-snapshot: Using SSMS, go through the following steps and script each one out to a script window. Read through each one so you understand what is going on: 1) Create a backup of your 'production' database using a script. Make it re-create/over-write the same file each time. 2) Restore that same backup file to *ANOTHER* database. Make sure the script has the "REPLACE" clause. (THis will allow a backup of Database A to overwrite on Database B). Edit this script with some MOVE clauses that re-name the mdf and ldf files because they obviously cannot have the same name and location as the original db. After both scripts have been tested, set them up in a SQL Agent Job that runs every morining at low server workload time. Lastly, point your Access apps to the 2nd Db. Keep us posted. -- Todd C [If this response was helpful, please indicate by clicking the appropriate answer at the bottom] |
#56
| |||
| |||
|
|
Your last suggestion was probably the best of all. With only Standard version, snapshots are out of the question. So do a pseudo-snapshot: Using SSMS, go through the following steps and script each one out to a script window. Read through each one so you understand what is going on: 1) Create a backup of your 'production' database using a script. Make it re-create/over-write the same file each time. 2) Restore that same backup file to *ANOTHER* database. Make sure the script has the "REPLACE" clause. (THis will allow a backup of Database A to overwrite on Database B). Edit this script with some MOVE clauses that re-name the mdf and ldf files because they obviously cannot have the same name and location as the original db. After both scripts have been tested, set them up in a SQL Agent Job that runs every morining at low server workload time. Lastly, point your Access apps to the 2nd Db. Keep us posted. -- Todd C [If this response was helpful, please indicate by clicking the appropriate answer at the bottom] |
#57
| |||
| |||
|
|
Your last suggestion was probably the best of all. With only Standard version, snapshots are out of the question. So do a pseudo-snapshot: Using SSMS, go through the following steps and script each one out to a script window. Read through each one so you understand what is going on: 1) Create a backup of your 'production' database using a script. Make it re-create/over-write the same file each time. 2) Restore that same backup file to *ANOTHER* database. Make sure the script has the "REPLACE" clause. (THis will allow a backup of Database A to overwrite on Database B). Edit this script with some MOVE clauses that re-name the mdf and ldf files because they obviously cannot have the same name and location as the original db. After both scripts have been tested, set them up in a SQL Agent Job that runs every morining at low server workload time. Lastly, point your Access apps to the 2nd Db. Keep us posted. -- Todd C [If this response was helpful, please indicate by clicking the appropriate answer at the bottom] |
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.