• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 382
  • Last Modified:

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?
4 Solutions
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

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.
Has Powershell sent you back into the Stone Age?

If managing Active Directory using Windows Powershell® is making you feel like you stepped back in time, you are not alone.  For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why.

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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now