0

I have a C# / SQL Server project. and database is reachable from different places (no lan between that 3 places) and data in database is important so I am taking recovery or my database every hour for last 30 days.

Documents which I want to save are kind of fax, excel, word, pdf type data and not formatted. So its impossible to get data inside them.

Problem is how can I store documents in SQL Server I don't want to enlarge its size so much because of increasing backup size.

So what is the efficient solution?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Rapunzo
  • 966
  • 5
  • 21
  • 42

3 Answers3

2

If you want to store something it's going to take place. You have multiple choises:

  1. Store only file path in SQL and store files seperatly on server and have seperate backup process for them
  2. Compress files before putting them to sql server, it will save you some place especialy with plain text formats, though it won't help with allready compressed formats(.png, office .docx, .xlsx and so on)
  3. Use FILESTREAM and differential backups (Example)

Similar question: Store Files in SQL Server or keep them on the File Server?

Community
  • 1
  • 1
Pavel Krymets
  • 6,253
  • 1
  • 22
  • 35
2

It seems like your main issue is the size of your backup. If you are doing a full backup every hour then you could save space by doing a differential backup instead.

There is no need to backup everything if it hasn't all changed, so you would only need to backup the new data that hadn't been in the last backup.

This would save you a lot of space and time and is generally better practice.

I would suggest you consider implementing a backup rotation scheme. You can find more information on this here:

http://en.wikipedia.org/wiki/Backup_rotation_scheme

I would also suggest you save the file in the filestream data type field in order to reduce the performance impact of having large pages in the mdf file.

Robert
  • 3,328
  • 2
  • 24
  • 25
  • thank you, I think filestream is the best solution for me. let me try it. ı will share my solutuon if I can achieve what I want. – Rapunzo Feb 18 '12 at 08:39
0

If you worries about backups size - save documents in filesystem and in DB store only patches.

If you worries about backups consistency - store documents inside the DB

Oleg Dok
  • 21,109
  • 4
  • 45
  • 54
  • yes problem is mostly size. and I cannot save it in file system because I dont have a vpn connection between that 3 places. – Rapunzo Feb 18 '12 at 07:40
  • You need not - store files in filesystem on server. Probably you may want to use some custom CLR functions to save/return the contents of files to/from filesystem, – Oleg Dok Feb 18 '12 at 08:10