loadingunit.netlify.com

1. Phpmyadmin Sql Dump
2. Sql Dump File Location
3. Sql Server Dump File

Nov 08, 2017 File with “LOG” extension is a snippet of ERRORLOG file when the dump was generated. This would help someone to look at exactly Errorlog part when the dump was generated. File with “MDMP” extension (called as minidump file) is a small memory footprint of the SQL process when the issue happened. Errorlog is the file written by SQL Server with general error/informational messages. This should tell you which process is generating the AV errors. You can open that file in notepad to get more info. In the short-term, you can delete files named SQLDumpnnnn.mdmp, since they are not active (though you'll lose the debug info contained in them).

Introduction

While working with a number of databases, creating a recovery plan can be challenging, especially if we want a foolproof design. Automated backup and restore strategies ensure the recovery plan is successful. However, strategies relying on frequent backups, although part of a sound strategy, can start causing issues when available storage space becomes limited. Old database backups are in many cases unnecessary, and it is often prudent to delete those backups to increase available storage. This can be done manually or automatically. There are several options to delete old backup files automatically in SQL Server:

• Delete old database backup files automatically in SQL Server using a SQL Server Agent Job

• Delete old database backup files automatically in SQL Server using a SQL Server Maintenance plan

Delete old database backup files automatically in SQL Server using SQL Server Agent:

For this option, the SQL Server Agent service must be installed and running. It requires basic knowledge and understanding of Transact-SQL (T-SQL) scripting, but the process is then entirely automated, so there is no further input from the user needed. It is necessary to create a stored procedure first upon which the SQL Server Agent job we make would call to execution. The advantage of this is the ability to use the same stored procedure across different jobs with different input parameters. To create a SQL Server Agent scheduled to delete old backup files, we must do the following steps:

1. SQL Server Agent service must be running. In the Object Explorer and check the icon beside SQL Server Agent. Should the message “Agent XPs disabled” is shown beside it, go to Control Panel/System and Security/Administrative Tools and run Services (the exact location may vary due to different operating systems). Locate the SQL Server Agent service for the corresponding SQL Server instance, select it and click on Start in the top left of the window, or simply right click on it and select Start. If the service is already running, you can skip this step:

2. Create a user stored procedure which will use the input from the SQL Server Agent scheduled job to delete old backup files. Right click on the database upon which we want to act and select New Query:

   In the new query window enter the following T-SQL:

   As it can be seen in this query, an extended system stored procedure xp_delete_file is being used. It reads the file header to check what type of file it is and will only delete certain types based on the input parameters we choose. After running the query, we should end up with a stored procedure:

3. After creating the stored procedure, we need to create a scheduled job with SQL Server Agent which will use the stored procedure with our parameters to delete the old backup files.

   To do that, right click on SQL Server Agent and select New then Job…

4. On the General tab, enter a descriptive name and optionally a description for the job:

5. On Steps tab, go to New…:

6. In the New Job Step window, under General tab, enter a descriptive job name, under Database select the database which we want the job to work on and under Command: insert the following line:

   usp_DeleteOldBackupFiles ‘D:MSSQL_DBBackups’, ‘bak’, 720

   To explain the line above:

   usp_DeleteOldBackupFiles – calls the stored procedure we created earlier

   ‘D:MSSQL_DBBackups’ – the first parameter tells the stored procedure where to look
   ‘bak’ – the second parameter tells what extension or file type to look

   Note: for the extension, do not use dot before the extension as the xp_delete_file already takes that into account. ‘.bak’ is incorrect use as opposed to ‘bak’, which is correct use.

   720 – the third parameter which tells the stored procedure the number of hours a backup file must be older than to get deleted.

7. Under Advanced tab, choose what will be done after this step is done successfully. Seeing that this is the only step, we can select ‘Quit the job reporting success’. Here we can also set how many times the step will retry as well as time interval between tries in minutes.

8. Next tab is Schedules. Under this tab we can set up when will this job work.

   Go to New…:

9. In the new window, enter a descriptive name for the schedule. Also, for Schedule Type check that it is set to Recurring for proper scheduled operation. After that, we can set up the schedule using the options below:

   The rest of the tabs is optional in this use case, though, we could use Notifications tab to set up email notifications when the job is completed.

10. After completing the previous step, our Job is created. It will run according to schedule, deleting old database backup files as set in the job itself. SQL Server Management Studio by default doesn’t show any real-time notifications when the job is performed. If we want to check the job history, we can right click on the job and select View History:

11. In the new window we can see the history for the selected job which executed successfully:

Delete old database backup files automatically in SQL Server using SQL Server Maintenance plan:

SQL Server Maintenance plans are another way of deleting old backup files by using the cleanup task.

1. When connected to the server, expand it and the Management folder after. Then right click on Maintenance Plans and click on Maintenance Plan Wizard

2. In the Maintenance Plan Wizard, click Next

3. Enter a descriptive name and, optionally, a description, and in the bottom right click on Change…

   

4. When New Job Schedule window pops up, check if the Schedule type is set to Recurring. After that, we can set up the schedule using the options below. After that, click OK.

5. Check if everything is correct before clicking on Next:

6. In the next window, check Maintenance Cleanup Task and click on Next:

   

7. In the following window, because we have only one task, there is no ordering, so we proceed by clicking on Next:

8. In the following window:

   1. Under Delete files of the following type: we select Backup files by clicking on the radio button.

   2. Under File location: we select Search folder and delete files based on an extension. Under that, we specify where to search for the folders and what type of an extension to look for. Also, we can check the Include first-level subfolders option if the backups are stored in separate subfolders.

      Note: The file extension we input must not contain dot (‘.’) – ‘.bak’ is incorrect, ‘bak’ is correct

   3. Under File age: we check the option Delete files based on the age of the file at task run time and specify the age of the files below

   After checking that everything is correct, we proceed by clicking Next:

9. In the next window, we may select that a report is written and/or mailed to the email address we input every time the Maintenance plan runs.

10. In the next window we press Finish to complete the creation of our Maintenance Plan. After that we can check under Management → Maintenance Plans for our newly created plan:

Downloads

Please download the script(s) associated with this article on our GitHub repository

If you lose your data in MariaDB, but have been using mysqldump to make regular backups of your data in MariaDB, you can use the dump files to restore your data. This is the point of the back-ups, after all. To restore a dump file, it's just a matter of having the mysql client execute all of the SQL statements that the file contains. There are some things to consider before restoring from a dump file, so read this section all of the way through before restoring. One simple and perhaps clumsy method to restore from a dump file is to enter something like the following:

Phpmyadmin Sql Dump

Again, this is not using mysqldump. The mysqldump utility is only for making back-up copies, not restoring databases. Instead, you would use the mysql client, which will read the dump file's content in order to batch execute the SQL statements that it contains. Notice that the redirect for STDOUT is not used here, but the redirect for the standard input (STDIN); the less-than sign is used since the dump file is an input source. Also, notice that in this example a database isn't specified. That's given within the dump file. You may want to stop MariaDB before doing a restore, and then start it again when done.

Restoring One Table

The problem with restoring from a dump file is that you may overwrite tables or databases that you wish you hadn't. For instance, your dump file might be a few days old and only one table may have been lost. If you restore all of the databases or all of the tables in a database, you would be restoring the data back to it's state at the time of the backup, a few days before. This could be quite a disaster. This is why dumping by database and table can be handy. However, that could be cumbersome.

Sql Dump File Location

A simple and easy method of limiting a restoration would be to create temporarily a user who only has privileges for the table you want to restore. You would enter a GRANT statement like this:

These two SQL statements allow the temporary user to have the needed SELECT privileges on all of the tables of db1 and ALL privileges for the table1 table. Now when you restore the dump file containing the whole db1 database, only table1 will be replaced with the back-up copy. Of course, MariaDB will generate errors. To overlook the errors and to proceed with the restoration of data where no errors are generated (i.e., table1), use the --force option. Here's what you would enter at the command-line for this situation:

Comments

Sql Server Dump File