Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


SQL Server 2008r2 backups

SQL Server 2008r2 backups

SQL Server 2008r2 backups

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.


RE: SQL Server 2008r2 backups

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.


The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875: What should I know before I post?

RE: SQL Server 2008r2 backups

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?


RE: SQL Server 2008r2 backups

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.


The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875: What should I know before I post?

RE: SQL Server 2008r2 backups

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


Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close