SQL Server 2008 r2 and default backup files

Hello, WillingtonIS!

That should be the default path for the backup.

However, here is a guide on how to change that location.

Additionally, you should be able to do a search for *.bak to find the location of the backup files.

Cheers!
Sean

Firstly, you pay the contractor, so get him/her to document the back up procedure for you, and include file locations.

Secondly, to be more helpful, you can find the maintenance plan and check. If you know how to run some SQL, this will tell you the backups that have run in the last day

Where is says GETDATE() - 1. Change the 1 to however many days you want to go back. The last column tells you where it is written to.

I found this a while ago on the web, not sure where I'm afraid.

SELECT CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS ServerName, 
       msdb.dbo.backupset.database_name,  
       CASE msdb..backupset.type  
           WHEN 'D' THEN 'Database'  
           WHEN 'L' THEN 'Log File'  
       END AS backup_type,  
       msdb.dbo.backupmediafamily.physical_device_name
  FROM msdb.dbo.backupmediafamily  
  JOIN msdb.dbo.backupset
    ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
 WHERE CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 1
 ORDER BY msdb.dbo.backupset.database_name, msdb.dbo.backupset.backup_finish_date

Select allOpen in new window

That would be great if I could understand it.  I'm not a DBA at all.  I understand how to do small things and I can change the path of the backup too.  I'm just trying to figure out where the default is stored.  And according to that path that I posted, it's no where to be found?

the idiot contract is back on the box.  I'll check this out and try to run the query.

I ran the query and this was my result:
C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\XT_backup_2015_12_17_020004_6256811.trn

the problem is C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\ doesn't exist?  there is no directory in SQL  Server\MSSQL10_50....

You're assuming that the backups have been running. If they've been running, they'll be in that folder. If the folder doesn't exist SQL won't create one for it, so it is possible the maintenance job either isn't completing successfully, or it isn't being run at all.

Is it possible that there is another backup program running like BackupExec?

What is the contractors reason for not telling you? I would ensure the contract is not renewed for the contractor in question.

No backup exec isn't running.  Actually I'm trying to determine if I need to purchase the sql part or I can get away with just the backup files only.  As far as I know it's been run because it's a script.  I don't want to change the path because I don't know if that will mess up the script.  The contractor is just being a D...K! (sorry for the language!  I ran the backup myself manually and that path is no where to be found???  When I open C:\Program Files Microsoft SQL Server the only thing that appears is 90 and 100

I'm right clicking on the database and going to Tasks and selecting Back up   see attached.
SQL.png

On that server.

If the maintenance plan has run without error, and you're running management studio on the server where the database is located, then the folders that are referenced in the maintenance plan must exist, otherwise the maintenance job will fail and the job history will tell you it failed. Either that or the backups are being done another way.

Yes my inexperience shows I was on the wrong box!  I found it. Thanks.  I'm going to have my place purchase the sql remote agent and be safe.

Ran the script and that told me exactly where it was, however, I was on the wrong server.  I was on the application server and not the database server.  I didn't realize because SQL Server is on the app server as well.

Don't feel annoyed. It's caught me out once or twice. Hence why I asked!!

:)

Glad you got it sorted though.

You might hear a little giggling from this side of the screen but only because we've all done that or something just as silly in the past.

Just glad you found a resolution.

LOL thanks.  As I said I'm a baby in the SQL Server field.

hehe, so am I and I've been programming SQL for 15 years!!