Tag Info

New answers tagged

1

MySQL Workbench is a client querying and modelling tool and a very good one at that - it's purpose is not to perform server tasks - that's the job of the server. You can perform manual backups using Workbench, but I'm assuming that's not your long term goal. There are many ways you can perform backups. Take a look here for 10 of them. Some of the solutions ...


3

First, this assumption is questionable: "Cannot use transaction logs as it would affect my load performance - datasets are potentially huge with large transactions" When you do work in the database, your transactions are logged regardless. Full recovery mode just means your logged work sticks around until the next log backup. If non-logged operations ...


1

Stored Procedures are physically stored in the mysql.proc table as a MyISAM table Simply copy /var/lib/mysql/mysql/proc.frm /var/lib/mysql/mysql/proc.MYD /var/lib/mysql/mysql/proc.MYI If the new installation is the same major version of mysql, copying should do it for you It would be safer to go back to the old setup, start mysql, and dump the stored ...


-1

Further investigation and "Googling" solved the issue, The issue was with drive, It was compressed and SQL Server did not like that. Solution Removed the compression from Drive and this time backup completed without any errors and much faster.


0

This is the answer I've found for why the file autolocation doesn't work in SQL 2012 for me. I don't believe it has anything to do with the backup being from SQL 2008. In SQL Server 2012, during the restore of a full backup without MOVE clauses that indicate the target physical location of each file, on top of an existing database, the file autolocation ...


0

WITH REPLACE refers to the database, not its files (though the net effect would be the same I guess). If you have file names that match an existing database and you try to restore it as a different database, you will need to use WITH MOVE to prevent SQL Server from trying to overwrite the existing files (which it won't be able to do). What's happening in ...


1

Instead of writing your own solution, I would highly recommend to use Ola Hallengren's SQL Server Backup Solution. Refer to : Bad habits to kick : avoiding the schema prefix by Aaron Bertrand. Below should work for you (I have not tested it): CREATE SCHEMA job go CREATE PROC job.BACKUPS @dir varchar(max) AS BEGIN DECLARE @comando nvarchar(max) = ...


1

This: psql -U postgres -d utrechtTest > backupTest2.sql runs psql and writes its stdout to backupTest2.sql, which will be overwritten. You meant: psql -U postgres -d utrechtTest < backupTest2.sql which is better written as: psql -U postgres -d utrechtTest -f backupTest2.sql because if you use -f then psql can show line numbers.


1

What is the quickest way to restore a DB Enable Instant File Initialization. It will tremendously speed up your restore process. Is there a better way to do this than querying for the processes that are using the DB and killing them? Below code will help you : --- change the db_name as per your databaseName alter database db_name set single_user ...


0

I would like to post this as answer. You can use various methods to make sure you are able to knock out any connecton to database when you are trying to restore but there are repercussions when you use command ALTER DATABASE [yourdatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE What if a explicit DML command which is updating numerous rows is running in ...


2

You can set the database in SINGLE_USER mode. If you also specify ROLLBACK IMMEDIATE it won't wait for open transactions to finish but will just roll them back. ALTER DATABASE [yourdatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE do your actions ALTER DATABASE [yourdatabase] SET MULTI_USER


0

In pg_hba.conf, change this line: local all all peer to: local all all password Then you can log in locally using a password.


1

For InnoDB and other transactional data stores it should be OK purely from a referential integrity point of view as the normal crash recovery procedure will roll out any incomplete transactions that were in progress when the backup was taken, but there are potential problems that mean it is not recommended that you backup databases this way: Any ...


2

Yes, it is safe. There is a tool http://www.lenzg.net/mylvmbackup/ that uses LVM snapshots to take backups of MySQL. From InnoDB's standpoint a backup copy taken with LVM snapshots looks like as InnoDB has suddenly crashed. When you start MySQL from the backup copy it will start crash recovery. To save some time it's better to do the crash recovery ...


-1

I have tried to figure out this problem and it seems many times users posted the solution that if you make the cursor declaration insensitive then it starts working. So I tested it and yes it does just make sure you cursor declare is static and it starts working. The fact for which it fails is - Check your server level cursor threshold setting - if it is ...


-1

I had the same issue. The fix is simple, just add the following to my.cnf in the [mysqld] section innodb_log_file_size=48M (Use whatever size log file you need, 48M is OK for small Dbs) Then follow the instructions to restart mysql with a new log file size. The basics are (but READ the Manual!) stop mysql remove the ib_logfile* files from /var/lib/mysql ...


3

I will answer you question point wise like you asked No its not necessary that LSN of differential backup would match the LSN of full backup. LSN is basically a stamp of last records that accessed the log. You should have also included DatabaseBackupLSN/checkpointLSN and then you will note that DatabasebackupLSN of differential backup will match the ...


0

But this brings up a question about how to proceed when RESTORE VERIFY ONLY fails.Let's say a trx log backup completes successfully, but RESTORE VERIFY ONLY fails Restore verifyonly(as per BOL)Verifies the backup but does not restore it, and checks to see that the backup set is complete and the entire backup is readable. However, RESTORE VERIFYONLY does ...


0

Answer is simple you did not restored a full backup first. Differential backup is only valid from latest full backup because it has information about extents that has changed after full backup has been done. So unless you take full backup and restore and then restore a differential backup it will keep on failing with the error you got.


2

Depending on how up-to-date, you want the reports to be, you can use Log shipping : with delay of log restore on secondary and choosing the secondary to be read only with disconnect users when restoring the log. This will allow you to have read from secondary server. you just have to balance between the restore frequency on the secondary. log shipping ...


0

This error happens when Full back up is not restored before attempting to restore differential backup or full backup is restored with WITH RECOVERY option. Make sure database is not in operational conditional when differential backup is attempted to be restored. Please have a look on the below blog. ...



Top 50 recent answers are included