MSSQL backup restoration and associated application status

[moosemaimer]

We have a commercial program that runs on top of a SQL instance, that used to be supported by a dedicated contractor until they decided not to renew his services. The program has a tendency to throw errors relating to the database, and while there are tools to resolve them (recalculating inventory totals), the last time I ran it some of our counts got out of whack and it took the staff quite some time to reconcile it. There's a script running every night to create a full .bak of the database, and we have a test company the contractor would occasionally refresh from production, so I'd like to try running the resolution tool on the backup before going after prod.

Restoring the backup seems to be a straightforward procedure, but how likely is it that a program which runs off the database will just keep on spinning once that's done? It's the test company so it's not like we would actually lose anything, but I'd just like to know if this is something I should schedule for off-hours to allow for a restart if necessary.

We have a support contract with the vendor, but it's for X number of calls/issues, so I'd rather not burn one if I don't have to.

 

[moosport2]

You can run the full back once a day and multiple differential backup. Or enable full recovery and take log backups at regular intervals.

If no one is using the application after hours, schedule it to backup at night. Run your index defrag after.

 

[moosemaimer]

The backup script runs like a top, that wasn't the issue... I was asking if the program which gets data from the SQL instance would notice/stop working if a restore was performed on one of the databases. It didn't, btw... logged in immediately after running the restore and everything was fine.

As fine as this software could ever hope to be called, anyway.

 

[Cool Modine]

The best practice is to stop the application before running the restore.

 

[moosport2]

Yes. If you are restoring to overwrite the live database where users are potentially accessing. The best is disable the logins before starting restore.

 

[moosemaimer]

The admin console on the program lets me see who's logged in and to what, so I made sure everybody was out before running the restore (it's a small company, I only need to check in with < a dozen people). Program is a little odd... there's a runtime that gets installed on client machines, but you run the executable from a share on the server, and then pick which company (database) you want to be working with.