CODEWITHSUNDEEP
c#sql-serverdocumentbinaryfiles
Rapunzo
Rapunzo

Reputation: 986

Save document in SQL Server database

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?

Upvotes: 0

Views: 1993

Answers (3)

Robert
Robert

Reputation: 3408

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.

Upvotes: 2

Pavel Krymets
Pavel Krymets

Reputation: 6293

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?

Upvotes: 2

Oleg Dok
Oleg Dok

Reputation: 21766

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

Upvotes: 0

Related Questions