0

I'm quite new to SQL Server and some employee asked me if is possible to make monthly database backups.

We do daily full backups to NAS server via Cobian and we use SQL Server Management Studio to make maintenance plan for daily full backup as well.

My problem is that the database that I want to make the monthly backup is used by a software that is not developed by us so I have no clue how to make queries to extract data from date types, etc. The database has a lot of tables.

All I want is to extract data from January only and make a backup with January data only so i can send to external company. Is that even possible without T-SQL queries?

migrated from stackoverflow.com Apr 29 '14 at 16:16

This question came from our site for professional and enthusiast programmers.

2

No. What you seem to want is not a Backup of the database but some sort of extraction or export. A SQL Server backup performs the extraction of the whole contents of the database, in an internal format, so the database is restorable as a whole in a coherent point in time.

If you want what is usually known as export of the database, and it seems you need a partial export (just exporting a subset of the records). As you suspect, there is no way to do this without: 1/ well knowing the structure of data inside the database, and the relationships and constraings on tables, columns, ... 2/ running queries against the tables in order to extract just the rows you want. 3/ knowing which format do you want the data in.

Even if you get to extract just the data you want, it would be not easy to use this data as a backup, because restoring this data into the original database in the event of a data loss or Corruption would not be an easy task...

If you just want to save some space, you can replace the full backups by differential ones. you would need to save the first full backup in order to be able to restore with a differential one, but the sizes of the generated files would be much smaller.

hth

  • 2
    You could accomplish something along these lines with filegroups and partitions, depending on the make-up of the data, but making just the January backup usable for anything is not going to be the most intuitive thing in the world. It also will require Enterprise Edition for partitioning (you could do this without that specific feature, but it would be hard) and for piecemeal restore. – Aaron Bertrand Apr 29 '14 at 17:47
  • Hi Aaron, like roberto said what i might want is get all data from january not backup but i may still have the same problem since i dont have enough knowledge of the database to extrat data from it. The DB is used by a software for Human Resources Department developed by an external company... In my case maybe the best solution is asking the company help me out or even without the DB knoledge im still able to do it with only SQL Administration tools of some kind ?? – user3585859 Apr 30 '14 at 8:27
  • Indeed, the exportation of data from the database should be a feature provided by the application developer, as it must support software (and so, database schema) evolutions. The problem is that they might charge you for this additional work – Roberto Apr 30 '14 at 18:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.