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?
Best Answer
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