Sql 2008 R2 Database Backup

We purchased a backup system for all our servers, where it backs up every 15  or 30 minutes.
If there is a total server failure the system allows it to be virtualized so the users can keep working after the virtualized server comes up and stays up until the real server is fixed.  I have questioned them on the SQL server because I wasn't sure if it was backing up correctly.
They assured me it is. When I go to the BDR or Shadow protect server ( which is the system we bought) I can go to an server and look at the backups. For SQL I see all the drives, folders, etc. It basically takes a copy of everything every 30 minutes. In my SQLDATA  folder I see all the databases and log files that are just copied over. There is no .bak on anything so I know its just a copy. We do not have enterprise edition so is this okay? I know I need to restore to find out and that is my next question. How would I restore these, Just drop the database and attach the one I moved over from the  backup.  Would this be the proper way to test?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

nemws1Database AdministratorCommented:
No need to drop an existing database - just fetch the files (data and log) for a database from the backup (here they're in C:\Restore) and attach them under a different name.  Try to use a database that you know is being accessed a lot.  Also test multiple restores.

USE [master]
( FILENAME = N'C:\Restore\OldDBName_Data.mdf' ),
( FILENAME = N'C:\Restore\OldDBName_Log.ldf' )

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
Well after attach you should try the DBCC CHECKDB commamd to test the database.

If you need other tests you can start a teansaction in a test database, change/insert some value. Do not commit, wait until its backed up. Examine whats been backed up i e is it there or not. Then youll know if it's transactional consistent.

Regards Marten
One more point, when you are copying the files to the same drive where the original exists, you can't copy it with the same name. So, rename the file and copy and create database with attach.
Creating Active Directory Users from a Text File

If your organization has a need to mass-create AD user accounts, watch this video to see how its done without the need for scripting or other unnecessary complexities.

Marten RuneSQL Expert/Infrastructure ArchitectCommented:
Well I have one more point to!
Either do this on off hours, or on a separate server.
So you want to know if SQL Server data is being backed up and "they assure you that it is". Presumably you have some doubts, or else you wouldn't be writing here!

Get a spare machine and do a restore of your complete system. That is really the only way that you can be sure that what you have is a Backup and not a File-Of-Random-Bits. You should also practice doing a restore every month or so - more frequently to begin with - so that when your boss asks "if disaster strikes how long do we have to wait without computer systems?" you'll have cold hard figures to give him and not some optimistic guesstimate.


nemws1Database AdministratorCommented:
Also, ask your ops guys to give you access to the documentation for whatever backup solution they are using.  If it has a module that can specifically and correctly back up SQL Server files, it should be documented how you go about verifying backups and how you do recoveries.
David ToddSenior DBACommented:

If it is a real SQL backup, most of the third party tools will trigger a line or two in the MSDB backup tables.

jtanoAuthor Commented:
Thanks for all the info. Some pertained to me more than others hence the different points.
I appreciate it
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.