Backup MySQL users
mysql -BNe “select concat(‘\”,user,’\’@\”,host,’\”) from mysql.user where user != ‘root'” | \ while read uh; do mysql -BNe “show grants for $uh” | sed ‘s/$/;/; s/\\\\/\\/g’; done > grants.sql
backup
restore all mysql database from a –all-database sql.gz file
Yes. Generally, to restore compressed backup files you can do the following: gunzip < alldb.sql.gz | mysql -u [uname] -p[pass] [dbname] Please consult How to Back Up and Restore a MySQL Database Note that the –all-databases option is applicable to backup only. The backup file itself will contain all the relevant CREATE DATABASE quux; commands … Read more
SQL Server command line backup statement
Here’s an example you can run as a batch script (copy-paste into a .bat file), using the SQLCMD utility in Sql Server client tools: BACKUP: echo off cls echo — BACKUP DATABASE — set /p DATABASENAME=Enter database name: :: filename format Name-Date (eg MyDatabase-2009.5.19.bak) set DATESTAMP=%DATE:~-4%.%DATE:~7,2%.%DATE:~4,2% set BACKUPFILENAME=%CD%\%DATABASENAME%-%DATESTAMP%.bak set SERVERNAME=your server name here echo. sqlcmd … Read more
Good Secure Backups Developers at Home [closed]
I prefer http://www.jungledisk.com/ . It’s based on Amazon S3, cheap, multiplatform, multiple machines with a single license.
mysqldump with db in a separate file
mysql -uroot -N -e ‘show databases’ | while read dbname; do mysqldump -uroot –complete-insert –some-other-options “$dbname” > “$dbname”.sql; done
SQL Server backup/restore vs. detach/attach
The Detach / Attach option is often quicker than performing a backup as it doesn’t have to create a new file. Therefore, the time from Server A to Server B is almost purely the file copy time. The Backup / Restore option allows you to perform a full backup, restore that, then perform a differential … Read more
MySQL Database backup automatically on a windows server
Best way to do this would be mysqldump.exe –user=YourUserName –password=YourPassword –host=localhost –port=3306 –result-file=”Path\dump.sql” –databases “DatabaseName1” “Database2″ mysqldump.exe –user=root –password=root –host=localhost –port=3306 –result-file=”c:\www\db\backup.%date:~10,4%%date:~7,2%%date:~4,2%.sql” –default-character-set=utf8 –single-transaction=TRUE –databases “dbtest1” “dbtest2” The pattern backup.%date:~10,4%%date:~7,2%%date:~4,2%.sql will create a unique name (backup20131010.sql) each time it will run Now you just need to call this command in your task scheduler. That’s it. … Read more
CouchDB backups and cloning the database
Another thing to be aware of is that you can copy files out from under a live database. Given that you may have a possibly large database, you could just copy it OOB from your test/production machine to another machine. Depending on the write load of the machines it may be advisable to trigger a … Read more
How can I retrieve the logical file name of the database from backup file
DECLARE @Table TABLE (LogicalName varchar(128),[PhysicalName] varchar(128), [Type] varchar, [FileGroupName] varchar(128), [Size] varchar(128), [MaxSize] varchar(128), [FileId]varchar(128), [CreateLSN]varchar(128), [DropLSN]varchar(128), [UniqueId]varchar(128), [ReadOnlyLSN]varchar(128), [ReadWriteLSN]varchar(128), [BackupSizeInBytes]varchar(128), [SourceBlockSize]varchar(128), [FileGroupId]varchar(128), [LogGroupGUID]varchar(128), [DifferentialBaseLSN]varchar(128), [DifferentialBaseGUID]varchar(128), [IsReadOnly]varchar(128), [IsPresent]varchar(128), [TDEThumbprint]varchar(128) ) DECLARE @Path varchar(1000)=’C:\SomePath\Base.bak’ DECLARE @LogicalNameData varchar(128),@LogicalNameLog varchar(128) INSERT INTO @table EXEC(‘ RESTORE FILELISTONLY FROM DISK=”’ +@Path+ ”’ ‘) SET @LogicalNameData=(SELECT LogicalName FROM @Table WHERE Type=”D”) … Read more