[SOLVED] MS SQL Express, I'm confused

[Richard Chapman]

I have a customer with a MS database for his business. I am trying to get to where we can do some DR testing as they have never tested a restore. I am finding that it appears there are two database systems running, but only the one for the business data. I do know they had a system failure some years ago and someone came in and "fixed" it, and "may" have upgraded the system to v2008 from v2005. They run MS Dynamics POS on top of the DB.
My questions are as follows.

1. in the dbview attachment, I see what appears to me as two instances of the base system. They both appear to update separate sets of files ( see captureboth.jpg). Am I seeing what I think I am?

2. What should I be backing up here? My research tells me that I should only back up the DB's under SERVER, and not server\sqlexpress, but I'm not sure.

I have downloaded and installed MS SQL Server Management Studio Express, but I cannot connect to the main business database (parkway) and view anything. It appears from the logons I see and the homework I have done, none have the correct authority, But I have to dig through their scribbled notes and see if I can find some other logon or something to allow that or I can't do any backups except through the POS Manager and that is a manual process. I have tried using the POS manager login/password in Studio Express to no avail.

I am pretty green in the SQL world, so any thoughts would be greatly appreciated.

Richard

 

[YeOldeStonecat]

No SQL 2008 showing there....I only see 2x different installs of SQL 2005, two different service pack versions.
You can tell exact file location by right clicking the desired database, go to properties, buncha stuff you can do from there.

Why all the green "offline/shared" symbols on the folders 'n files?

 

[Richard Chapman]

That would be their Carbonite Personal Basic backup software indicators. That has been the backup they use. So far I cannot log on to the Parkway database. It seems the logon does not have proper security. I get this when I try to use the properties dialog.

The server principal "%.*ls" is not able to access the database "%.*ls" under the current security context.

I don't know if I need to back up both instances or not.

 

[BlackLabTechs]

Based on your pic, I don't see the need to backup anything in the MSSQL.2 instance as those are just system db's in there. There's no user databases in that folder. Now, doesn't mean the user db's aren't stored in a different folder for that instance though so you very well could end up needing that info in those system db's.

I would login using sql manager as you are trying to be sure. To help searching notes for passwords, the username will most likely be 'sa'. That's the admin login for sql based authentication. You can also try logging in using windows credentials and using "Domain\Admin".

 

[YeOldeStonecat]

That would be their Carbonite Personal Basic backup software indicators. That has been the backup they use. So far I cannot log on to the Parkway database. It seems the logon does not have proper security. I get this when I try to use the properties dialog.

The server principal "%.*ls" is not able to access the database "%.*ls" under the current security context.

I don't know if I need to back up both instances or not.

Ahh OK....just making sure it wasn't either shared, or set to sync offline files or something odd. Can't see the details closely.

Any other directories for the SQL 08?

 

[nlinecomputers]

What is running on these DB?

 

[Richard Chapman]

No other directories, so I must be wrong on 2008. MS Dynamics Retail POS system

 

[Richard Chapman]

Last night, I thought to set the program properties for Studio Express to Administrator Privilege Level under Compatibility. That allowed me to log on to the database.

 

[YeOldeStonecat]

I'd probably be checking to see if SQL is making a backup, and make sure that directory is being backed up. And then exclude the live SQL directories in the screenies above. I have heard of horror stories from other techs that use those home grade backup programs for live databases. They're fine for word documents and pictures of the family. I wouldn't trust it for production databases. If I took on a new client who used that backup software I'd be sure to cover my back with a more proper biz grade backup product. But in the mean time, ensure that SQL was spitting out a backup copy of the database, and then back up that directory. Since those backup copies are not "live".

With SQL, and other databases...it's not so much a "file in use" thing that you'd normally get around with VSS writers, it's the fact that database engines hold a lot of data in system memory. So sweeping through to try to copy a file will not get data that is up in RAM at the time.

 

[nlinecomputers]

It is an SQL database. How is carbonite personal going to properly backup that?

 

[nlinecomputers]

LOL. Simultaneous posts for the win. What Stonecat said.

 

[fencepost]

I'll throw in a quick note - if you're running scheduled backups out of your database then backing those up with a non-database-aware method (e.g. Carbonite), make sure that either A) your backup files aren't named *.bak or B) that your backup method doesn't exclude *.bak.

I've not been bitten by that, but I have seen it and always try to check for it now. Sucks when your backup software deems your database backups as "expendable"

 

[BlackLabTechs]

Also to note, that db is 3.8 gb. I believe it was sql 2008 when native backup compression was introduced. So for 2005, you can compress the backup file after the fact. You should be able to get under 500 Meg without any issues. If the backup software you are using has built in compression before uploading, that may work just as well.

 

[eric76]

I would use Acronis Backup and Recovery to make a nightly image. It supports SQL and you can convert the image (.tib) to a VMware virtual machice and check the backup. This way you don't make to worry about anything. If your server goes down for any reason you have a exact backup with SQL ready to go.

As Yeoldestonecat said earlier - not all backup software supports SQL.

 

[YeOldeStonecat]

Even with SQL aware/compliant backup software, my preference...I still like SQL (or whatever database you're working with)....to do its own native backup each night. And I include that in the backup. I want that warm 'n fuzzy knowing I can ALWAYS revert the database back to "last night's" status. They're very complicated, I don't want to rely on backups that are SQL aware.