Hi frends Any one having idea about this task please help me

Amit Amit

Hi frends
Any one having idea about this task please help me

We would have the same database schema in Production and Development as well for an application

the data that is there is production is real time and the data in the Dev is test data or development data.

right now the requirement is on a daily basis, I want the data from Production to the Dev database .. say today I restored a backup of production database to Dev.. both the databases would have same data right

tomorrow, you might have new data into the production database ,which is not in the Dev database.. so that differential data (the data that is loaded for that particular day i.e today to tomorrow) should be created as back up and it should be restored to the Development database

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Vishal Yelve4y ago
You can create Store Procedure and add that to SQL jobs which will run daily.

I have two databases - One EmployeeDb and another is Employee2DB

- Taking backup of EmployeeDb and restoring on Employee2DB database.
Query to take backup Example
-- backup

declare @backuppath as nvarchar(max)
set @backuppath  = N'C:\WorkingProjects\Practice\DataBase\Employee\EmployeeDB_'
+ CONVERT(nvarchar,YEAR(getdate()))
+ CONVERT(nvarchar,Month(getdate())) +
+ CONVERT(nvarchar,DAY(getdate())) + '.bak'
Backup Database [EmployeeDB] to DISK = @backuppath WITH NOFORMAT, NOINIT, NAME=N'EmployeeDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
Query to take restore backup Example
RESTORE DATABASE Employee2DB FROM  DISK = N'C:\WorkingProjects\Practice\DataBase\Employee\DBBackup\EmployeeDB_2022722.bak'
WITH MOVE N'EmployeeDB' TO N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\Employee2DB.mdf',
     MOVE N'EmployeeDB_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\Employee2DB_log.ldf',
RECOVERY, -- 'with recovery' is optional here - it's the default if not specified - database will be available
REPLACE;
Sachin Singh4y ago
You can use windows task scheduler and mysqldump
follow this article
https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html
Rajanikant Hawaldar4y ago
Create a query or stored procedure that would insert records from last 24 hours of production into dev.
Create a job for same and schedule this job to run on daily basis may be everyday at 12 am