Question

Execute the steps below using the "AdventureWorksDW2016CTP3" database

----Create a SQL transaction that manages the scheduling of PTO/SICK Report as well as a periodic database backup.

Here is the transaction query that I have but how do I add the periodic database backup part?

INSERT INTO PTO_SICK_REPORT (EMP_ID, FIRST_NAME, LAST_NAME, VACCATION_HOURS, SICK_HOURS) SELECT EMP_ID, FIRST_NAME, LAST_NAME, VACCATION_HOURS, SICK_HOURS FROM EMPLOYEE;

COMMIT;

SELECT * FROM PTO_SICK_REPORT ;

Answer 1

Backing up Database (MySQL):-

Back up From the Command Line (using mysqldump)

If you have shell or telnet access to your web server, you can backup your MySQL data by using the mysqldump command. This command connects to the MySQL server and creates an SQL dump file. The dump file contains the SQL statements necessary to re-create the database. Here is the proper syntax:

$ mysqldump --opt -u [uname] -p[pass] [dbname] > [backupfile.sql]

• [uname] Your database username
• [pass] The password for your database (note there is no space between -p and the password)
• [dbname] The name of your database
• [backupfile.sql] The filename for your database backup
• [--opt] The mysqldump option

eg:- $ mysqldump -u root -p MYDB > myDB_backup.sql

If you want to back up all the databases in the server at one time you should use the --all-databases option. It tells MySQL to dump all the databases it has in storage.

$ mysqldump -u root -p --all-databases > alldb_backup.sql

Restoring/Loading your MySQL Database:-

To re-create the Tutorials database you should follow two steps:

• Create an appropriately named database on the target machine
• Load the file using the mysql command:

$ mysql -u [uname] -p[pass] [db_to_restore] < [backupfile.sql]

Have a look how you can restore your myDB_backup.sql file to the MYDB database.

$ mysql -u root -p MYDB < myDB_backup.sql

Please let me know in case of any clarifications required. Thanks!