SQL Server 2008r2 backups

[wbodger]

I upgraded our ERP and db at the company that I work for and apparently didn't completely think thru where I placed the files that I restored from on the new server. So, I have this in msdb (well, this and more):

media_set_id	family_sequence_number	media_family_id	media_count	logical_device_name	physical_device_name
20	1	90947112-0000-0000-0000-000000000000	1	NULL	E:\SysproUpgradeBackups\Purch_db_201407022148.BAK
21	1	8A0011DB-0000-0000-0000-000000000000	1	NULL	E:\SysproUpgradeBackups\PurchaseRequest_db_201407022148.BAK
22	1	08B5CC76-0000-0000-0000-000000000000	1	NULL	E:\SysproUpgradeBackups\views_db_201407022149.BAK
23	1	BDF48D9D-0000-0000-0000-000000000000	1	NULL	E:\For Maleficent\Encoredb_db_201407130200.BAK
24	1	4E6240AD-0000-0000-0000-000000000000	1	NULL	E:\For Maleficent\EncoreCompanyK_db_201407022140.BAK
25	1	E3DD8488-0000-0000-0000-000000000000	1	NULL	F:\SysproConversion101414.bak
26	1	D5612394-0000-0000-0000-000000000000	1	NULL	F:\EncoreDBSysproConversion101414.bak
27	1	54E26BE5-0000-0000-0000-000000000000	1	NULL	E:\SysproUpgradeBackups\Aerospace_db_201410171425.BAK
28	1	0E3C5D4C-0000-0000-0000-000000000000	1	NULL	E:\SysproUpgradeBackups\AIP_db_201410171425.BAK
29	1	83EEC7EA-0000-0000-0000-000000000000	1	NULL	E:\SysproUpgradeBackups\Bamboo_db_201410171425.BAK
30	1	B1E2DC9A-0000-0000-0000-000000000000	1	NULL	E:\SysproUpgradeBackups\CIMData_db_201410171425.BAK
31	1	62074661-0000-0000-0000-000000000000	1	NULL	E:\SysproUpgradeBackups\EcompKviews_db_201410171425.BAK
32	1	F811AFC4-0000-0000-0000-000000000000	1	NULL	E:\SysproUpgradeBackups\EncoreCompanyK_db_201410171426.BAK
33	1	549984A9-0000-0000-0000-000000000000	1	NULL	E:\SysproUpgradeBackups\Encoredb_db_201410171439.BAK
34	1	4E70FC8B-0000-0000-0000-000000000000	1	NULL	E:\SysproUpgradeBackups\ExecSummary_db_201410171439.BAK
35	1	B47DAF3B-0000-0000-0000-000000000000	1	NULL	E:\SysproUpgradeBackups\FabricLogs_db_201410171439.BAK
36	1	3FBE08CC-0000-0000-0000-000000000000	1	NULL	E:\SysproUpgradeBackups\EncoreArchive_db_201410171425.BAK
37	1	C2DC5BFF-0000-0000-0000-000000000000	1	NULL	E:\SysproUpgradeBackups\FurnConsole_db_201410171439.BAK
38	1	9F3CB2A4-0000-0000-0000-000000000000	1	NULL	E:\SysproUpgradeBackups\FurnConsole2_db_201410171439.BAK
39	1	79841364-0000-0000-0000-000000000000	1	NULL	E:\SysproUpgradeBackups\FurnitureConsoleArchive_db_201410171440.BAK
40	1	73E88D82-0000-0000-0000-000000000000	1	NULL	E:\SysproUpgradeBackups\furnituredata_db_201410171440.BAK
41	1	0D005927-0000-0000-0000-000000000000	1	NULL	E:\SysproUpgradeBackups\gateway_db_201410171440.BAK
42	1	3494BC98-0000-0000-0000-000000000000	1	NULL	E:\SysproUpgradeBackups\IT_db_201410171440.BAK
43	1	087341DD-0000-0000-0000-000000000000	1	NULL	E:\SysproUpgradeBackups\JBK_db_201410171440.BAK
44	1	0B018DCB-0000-0000-0000-000000000000	1	NULL	E:\SysproUpgradeBackups\KaasNet_db_201410171440.BAK
45	1	AF0F8AE6-0000-0000-0000-000000000000	1	NULL	E:\SysproUpgradeBackups\Kaizen_db_201410171440.BAK
46	1	E77B411F-0000-0000-0000-000000000000	1	NULL	E:\SysproUpgradeBackups\onlinereports_db_201410171440.BAK
47	1	FFCB1749-0000-0000-0000-000000000000	1	NULL	E:\SysproUpgradeBackups\OnLineStatus_db_201410171440.BAK
48	1	1F01B767-0000-0000-0000-000000000000	1	NULL	E:\SysproUpgradeBackups\PackslipData_db_201410171440.BAK
49	1	6A119B64-0000-0000-0000-000000000000	1	NULL	E:\SysproUpgradeBackups\PrintCenter_db_201410171440.BAK
50	1	91E5F6E4-0000-0000-0000-000000000000	1	NULL	E:\SysproUpgradeBackups\Purch_db_201410171440.BAK
51	1	147BBABE-0000-0000-0000-000000000000	1	NULL	E:\SysproUpgradeBackups\PurchaseRequest_db_201410171440.BAK
52	1	01D0445B-0000-0000-0000-000000000000	1	NULL	E:\SysproUpgradeBackups\views_db_201410171440.BAK
53	1	4FFAA09A-0000-0000-0000-000000000000	1	NULL	E:\MSSQL\MSSQL10.MSSQLSERVER\MSSQL\Backup\Aerospace\Aerospace_backup_2014_10_23_220002_0580000.bak
54	1	558289D3-0000-0000-0000-000000000000	1	NULL	E:\MSSQL\MSSQL10.MSSQLSERVER\MSSQL\Backup\AIP\AIP_backup_2014_10_23_220002_0690000.bak
55	1	62BC970F-0000-0000-0000-000000000000	1	NULL	E:\MSSQL\MSSQL10.MSSQLSERVER\MSSQL\Backup\Bamboo\Bamboo_backup_2014_10_23_220002_0730000.bak
56	1	C1E38F65-0000-0000-0000-000000000000	1	NULL	E:\MSSQL\MSSQL10.MSSQLSERVER\MSSQL\Backup\EcompKviews\EcompKviews_backup_2014_10_23_220002_0770000.bak

What do I need to do to safely remove the non-standard backup file locations from my history without damaging the backup history itself? I have been having trouble with my daily differentials as well as restoring from database (from file works, but that is a bit cumbersome if I have to go thru 15 hours of transaction logs backed up every 15 minutes) and I think this may be the root of it.

Thanks,
Willie

 

[SQLBill]

Your backup's may have a problem anyways....do you do differential and/or log backups? Or are your databases in Simple recovery mode?

If you are doing differential and/or log backups, those other backups could be 'interrupting' the backup chain. For example, you do a full back up Bamboo, then you do several log backups of Bamboo, now SysproUpgrade does a backup.....if that backup is a Full one..that started a new backup chain and you'll need to be able to restore it for any logs following it. Likewise, if it is a diff or log backup it is part of the backup chain.

If you only do Full backups...it's not an issue and you could probably delete the entries you don't want. However, I've never done this and am not sure of any impact it could have.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[wbodger]

For each database I do a full backup sunday night, then transaction log backups every fifteen minutes until I do a differential m-s evening. So:

Sunday Full backup
transaction logs
Monday differential
transaction logs
Tuesday differential
transaction logs etc.

Did I misunderstand the differentials in this scheme? I thought that if I wanted to restore to noon on Wednesday I could restore the most recent full backup (Sunday), then the most recent differential (Tuesday, which would encompass the differential from Monday) and finally the log files up to the point that I was looking for. Is this not the sequence?

Thanks,
Willie

 

[SQLBill]

Sorry I didn't respond before...

Yes, that process works and is how it is supposed to be done. From the file names, I can't tell which are full, differential, or log backups. It appeared to me that you were doing backups but that the vendor has backups 'built in' that are also being done.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[wbodger]

OK, thanks for the confirmation. I think I will take a shot at implementing Ola Hallengren's maintenance scripts.

Thanks,
Willie