TIP - SQL To Access Daily Backup In Case SQL Server Crashes

[T111]

If using an Microsoft Access frontend linked to SQL backend and in the event that the SQL server is down the user would like to look at the data (or as close to the current data as possible).

Solution - Create a DTS package in SQL scheduled to run daily exporting the information to a local Microsoft Access Database. Make sure you include the "create and drop" option - so the tables don't duplicate the information but refresh the local Access database with the current updates. With this in place if the SQL server is down the user(s) can view yesterday’s information (which is all stored locally in an access database). This should help until the live SQL server is back up and running.

Note - you might only want to select critical tables due to the size of the data being locally stored in the access application.

 

[lameid]

Or if your backing up the SQL databases to files, you could restore them to the personal edition of SQL server on a machine. Just set it up ahead of time. You won't have to waste space or backup time on an Acces backup. With this method you should even beable to grab the most recent log files and get up to the transaction log current information.

 

[T111]

Lameid, I acknowledge that SQL personal edition is handy but the procedure I have in place is that if no IT (SQL tech guy) is around the end user simply opens a different front end and voila - the entire database(from the previous day) at a click. And besides if the backup if held on the SQL server then that maybe inaccessible.