[AccessD] How to work Offline with SQL Server

I run nightly backup/restore jobs on my  live and dev servers.

This is the TSQL (watch for wrap):

BACKUP DATABASE [MachinesNew] TO  DISK = N'C:\Program Files\Microsoft
SQL Server\MSSQL\BACKUP\MachinesNew.Bak' WITH  INIT ,  NOUNLOAD ,
NAME = N'MachinesNew backup',  NOSKIP ,  STATS = 10,  NOFORMAT

I then have a vb.net job run each night a few minutes after the back
up that copies the
.bak file from the live server to the dev server

A few minutes after that I have a restore job run on the dev server.

I originally did this to have a back up of the data. Created the job
and let it run. It worked great!
When I finally got around to making design changes, I worked all day
on these new stored procedures.
I came in the next morning and all my changes were gone! I couldn't
figure out what happened, then it hit me!
It turns out the dev db was being overwritten by the live one at night :) DOH!


David

On Fri, Jan 8, 2010 at 1:59 PM, jwcolby <jwcolby at colbyconsulting.com> wrote:
> In this specific case I remote desktop directly into the server and work on that.  I am really
> asking "how do you keep the test and dev database in sync" at least structure wise.  Using the
> mapped drive / mdb it is trivial to just copy the back end be to the local drive.  That also gets
> the latest data.
>
> John W. Colby
> www.ColbyConsulting.com
>
>
> Rusty Hammond wrote:
>> If the database is large then we have a separate test server we use.
>> For smaller databases, we've used the free SQL Server Express and
>> installed it on the developer's machine - so the database is on the
>> local machine for testing.
>>
>>
>>
>> -----Original Message-----
>> From: accessd-bounces at databaseadvisors.com
>> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
>> Sent: Friday, January 08, 2010 3:05 PM
>> To: Access Developers discussion and problem solving
>> Subject: [AccessD] How to work Offline with SQL Server
>>
>> When my clients use Access MDBs for the data store it is a trivial
>> matter to set up mapped drives, and then link live / offline.
>>
>> 1) Create a "live data directory" and store the live data be in that.
>> 2) Share that and map it as the X: drive on the workstations.
>> 3) Link the FE to the BE on that mapped X: drive
>> 4) Create a dev directory on my dev machine.
>> 5) Share that.
>> 6) Create a batch MapLive.Bat file that maps the Live directory as X:
>> drive.
>> 7) Create a batch MapLocal.Bat file that maps my local share as the X:
>> drive.
>> 8) Run MapLocal when debugging.  Any changes to data is written to the
>> local copy of the BE on my hard drive.
>> 9) Run MapLive when updating live data.  Any data changes written to the
>> live database.
>>
>> So, how do I achieve the same flexibility using SQL Server?  In this
>> case the data resides in a database on a server.  In order to have the
>> same kind of isolation from test to live I would have to maintain a
>> mirror of the database somewhere.  It seems rather unwieldy.
>>
>> Does anyone do this kind of thing using SQL Server as the data store?
>> --
>> John W. Colby
>> www.ColbyConsulting.com
>> --
>> AccessD mailing list
>> AccessD at databaseadvisors.com
>> http://databaseadvisors.com/mailman/listinfo/accessd
>> Website: http://www.databaseadvisors.com
>> **********************************************************************
>> WARNING: All e-mail sent to and from this address will be received,
>> scanned or otherwise recorded by the CPI Qualified Plan Consultants, Inc.
>> corporate e-mail system and is subject to archival, monitoring or review
>> by, and/or disclosure to, someone other than the recipient.
>> **********************************************************************
>>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>