Store SQL data that is changing

[grupomacarena]

Hello!

I have a SQL Server database that is modified every three months (some rows are erased). So far I ran a query in SQL, downloaded and stored the Excel files (no more than 2.5 MB each, in total 10 MB annually) but I don't want to make it manually for Power BI.

I need a way to automatically backup the original data in an online database (Azure or simply OneDrive Excel file...I don't mind).

I want to query the last three months of data, and add it to the online database automatically. What would be the easiest option? Thanks in advance! 🙂

[grupomacarena]

Thanks for the answer @Shahfaisal 

That's exactly what I'd like to do: run a query from the SQL Server and copy it somewhere else. Azure SQL Database is a valid option. How can I perform this?

[Shahfaisal · Accepted solution]

---

@grupomacarena wrote:

Thanks for the answer @Shahfaisal 

That's exactly what I'd like to do: run a query from the SQL Server and copy it somewhere else. Azure SQL Database is a valid option. How can I perform this?

---

Follow this for instructions on how to do it -https://docs.microsoft.com/en-us/azure/data-factory/tutorial-hybrid-copy-data-tool

Also,  if you don't need this process to run on a schedule and you want to do it manually every 3 months, you can use SQL Server Import/Export Tool - https://docs.microsoft.com/en-us/sql/integration-services/import-export-data/start-the-sql-server-im...

[Shahfaisal]

One of the simplest option is to use Azure Data Factory (or SSIS if you have an on-prem SQL Server license) to copy the tables from the SQL Server database to an Azure SQL Database.

I wouldn't store the data in Excel if I have the option to use a database - keeping data in a relational database gives you a lot of options to easily anlayze, move, etc.