// Copyright 1999-2014. Parallels IP Holdings GmbH. All Rights Reserved. using System; using System.Collections.Generic; using System.Text; using psacommon; using System.Data.SqlClient; using System.IO; using psaobsolete; using System.Collections; using System.Xml; using System.Security.AccessControl; using System.Text.RegularExpressions; using System.Security.Principal; using Microsoft.SqlServer.Management.Common; using Microsoft.SqlServer.Management.Smo; namespace dbbackup { class DbBackupManager { public enum ServerType { MS_SQL, MySql } static int Main(string[] args) { try { CmdArgsParser parser = new CmdArgsParser(args); if (parser.Count == 0) { Usage(); return 0; } switch (parser[0]) { case "--backup": DbBackupManager backupMng = new DbBackupManager( parser["-server"], GetType(parser["-server-type"]), GetPort(parser.Contains("-port") ? parser["-port"] : ""), parser["-server-login"], parser["-server-pwd"]); backupMng.Backup( parser["-database"], parser["-backup-path"], parser.Contains("-temp-dir") ? parser["-temp-dir"] : "", parser.Contains("-net-dir") ? parser["-net-dir"] : "", parser.Contains("-net-user") ? parser["-net-user"] : "", parser.Contains("-net-pwd") ? parser["-net-pwd"] : ""); break; case "--restore": DbBackupManager restoreMng = new DbBackupManager( parser["-server"], GetType(parser["-server-type"]), GetPort(parser.Contains("-port") ? parser["-port"] : ""), parser["-server-login"], parser["-server-pwd"]); restoreMng.Restore( parser["-database"], parser["-backup-path"], parser.Contains("-temp-dir") ? parser["-temp-dir"] : "", parser.Contains("-net-dir") ? parser["-net-dir"] : "", parser.Contains("-net-user") ? parser["-net-user"] : "", parser.Contains("-net-pwd") ? parser["-net-pwd"] : "", parser.Contains("-max-file-size") ? Int64.Parse(parser["-max-file-size"]) : -1, parser.Contains("-max-log-size") ? Int64.Parse(parser["-max-log-size"]) : -1, parser.Contains("-drop-logins"), parser.Contains("-grant-alter-database"), parser.Contains("-backup-operator")); break; case "--copy": DbBackupManager copyMng = new DbBackupManager( parser["-src-server"], GetType(parser["-server-type"]), GetPort(parser.Contains("-src-port") ? parser["-src-port"] : ""), parser["-src-server-login"], parser["-src-server-pwd"]); copyMng.Copy( parser["-src-database"], parser["-dst-server"], GetPort(parser.Contains("-dst-port") ? parser["-dst-port"] : ""), parser["-dst-server-login"], parser["-dst-server-pwd"], parser["-dst-database"], parser.Contains("-with-data"), parser.Contains("-copy-if-logins-exist")); // this flag was added only for MSP break; case "--check-orphaned-users": DbBackupManager checkMng = new DbBackupManager( parser["-server"], ServerType.MS_SQL, null, parser["-server-login"], parser["-server-pwd"]); checkMng.CheckOrphanedUsers(parser["-database"]); break; case "--repair-orphaned-user": DbBackupManager repairMng = new DbBackupManager( parser["-server"], ServerType.MS_SQL, null, parser["-server-login"], parser["-server-pwd"]); repairMng.RepairOrphanedUser(parser["-database"], parser["-db-user"], parser["-db-user-pwd"]); break; case "--check-network-settings": DbBackupManager.CheckNetworkSettings(parser["-net-dir"], parser["-net-user"], parser["-net-pwd"]); break; case "--help": Usage(); break; default: throw new CommandLineException(string.Format("Unexpected command {0}", parser[0])); } return 0; } catch (DbBackupException ex) { Log.Write(LogType.Error, ex); return ex.RetCode; } catch (Exception ex) { Log.Write(LogType.Error, ex); return 1; } } private string m_server; private ServerType m_type; private int? m_port; private string m_serverLogin; private string m_serverPass; private Version m_serverVersion; public DbBackupManager(string server, ServerType type, int? port, string serverLogin, string serverPass) { m_server = server; m_type = type; m_port = port; m_serverLogin = serverLogin; m_serverPass = serverPass; } private static ServerType GetType(string str) { switch (str) { case "mysql": return ServerType.MySql; case "mssql": return ServerType.MS_SQL; default: throw new CommandLineException(string.Format("Unexpected type '{0}' of database server. Must be 'mysql' or 'mssql'.", str)); } } private static int? GetPort(string str) { if (string.IsNullOrEmpty(str)) return null; int port; if (!int.TryParse(str, out port)) throw new CommandLineException(string.Format("Incorrect port '{0}'. Must be integer value.", str)); return port; } private static int defaultConnectionTimeout = 60; private static int defaultCommandTimeout = 36000; private static int getConfigDWORDParam(string name) { return psaobsolete.psa.getInstance().get_ConfigDWORD(name); } private static int getConnectionTimeout() { try { return getConfigDWORDParam("ADOConnectionTimeout"); } catch { return defaultConnectionTimeout; } } public static int getCommandTimeout() { try { return getConfigDWORDParam("ADOCommandTimeout"); } catch { return defaultCommandTimeout; } } private string MsConnectionString { get { return string.Format("Data Source={0};UID={2};PWD={3};Connect Timeout={4}", m_server, m_serverLogin, m_serverLogin, m_serverPass, getConnectionTimeout()); } } private void Backup(string database, string backupPath, string tempDir, string netDir, string netUser, string netPass) { if (string.IsNullOrEmpty(database)) { throw new ArgumentException("Invalid -database parameter value"); } if (string.IsNullOrEmpty(backupPath)) { throw new ArgumentException("Invalid -backup-path parameter value"); } RemoteWinConnection netConnection = null; try { string backupDir = Path.GetDirectoryName(backupPath); if (string.IsNullOrEmpty(backupDir)) { backupDir = Path.GetPathRoot(backupPath); } string backupFileName = Path.GetFileName(backupPath); string workingTempDir = !string.IsNullOrEmpty(tempDir) ? tempDir : backupDir; string workingNetDir = !string.IsNullOrEmpty(netDir) ? netDir : workingTempDir; string workingTempPath = Path.Combine(workingTempDir, backupFileName); string workingNetPath = Path.Combine(workingNetDir, backupFileName); if (workingNetDir.StartsWith(@"\\") && !string.IsNullOrEmpty(netUser)) { netConnection = new RemoteWinConnection(); netConnection.ConnectToNetworkResource(GetWinHostName(workingNetDir), new RemoteWinConnection.ConnectionParameters(netUser, netPass)); } if (!string.IsNullOrEmpty(netDir) || workingTempPath != backupPath) { workingTempPath = GetRealBackupPath(database, backupFileName, workingTempDir, false); workingNetPath = GetRealBackupPath(database, backupFileName, workingNetDir, true); } switch (m_type) { case ServerType.MS_SQL: MsBackup(database, workingTempPath); break; case ServerType.MySql: MyBackup(database, workingTempPath); break; default: throw new DbBackupException(DbBackupException.OTHER_ERROR, "Unexpected type of database server."); } if (workingNetPath != backupPath) { try { File.Copy(workingNetPath, backupPath); } finally { try { File.Delete(workingNetPath); } catch (Exception ex) { Log.Write(LogType.Error, ex); } } } } catch (DbBackupException) { throw; } catch (Exception ex) { throw new DbBackupException(DbBackupException.DB_BACKUP_ERROR, string.Format("Unable to backup database '{0}'", database, database), ex); } finally { try { if (netConnection != null) netConnection.CancelNetworkConnection(); } catch (Exception ex) { Log.Write(LogType.Error, ex); } } } private void MsBackup(string database, string backupPath) { using (SqlConnection connection = new SqlConnection(MsConnectionString)) { connection.Open(); ExecuteMsCommand(string.Format("BACKUP DATABASE [{0}] TO DISK='{1}'", database, backupPath), connection); } } private string GetRealBackupPath(string database, string backupFileName, string path, bool create) { if (path.EndsWith(":")) path += @"\"; string realDir = Path.Combine(path, m_server.Replace(Path.DirectorySeparatorChar, '_').Replace(Path.AltDirectorySeparatorChar, '_')); if (create && !Directory.Exists(realDir)) Directory.CreateDirectory(realDir); realDir = Path.Combine(realDir, database.Replace(Path.DirectorySeparatorChar, '_').Replace(Path.AltDirectorySeparatorChar, '_')); if (create && !Directory.Exists(realDir)) Directory.CreateDirectory(realDir); return Path.Combine(realDir, backupFileName); } public static string GetWinHostName(string path) { if (!path.StartsWith(@"\\")) throw new DbBackupException(DbBackupException.OTHER_ERROR, string.Format("Path is not network path ({0})", path)); int startIndex = 2; int endIndex = path.IndexOf(@"\", startIndex); if (endIndex < startIndex) return path.Substring(startIndex); return path.Substring(startIndex, endIndex - startIndex); } private void MyBackup(string database, string backupPath) { ExecUtils.ExecResult result = ExecUtils.execExtUtil( Path.Combine(psa.getInstance()["MYSQL_BIN_D"], "mysqldump"), "-Q -R " + ExecUtils.escapeCmdArgument("-h" + m_server) + " " + ExecUtils.escapeCmdArgument("-P" + m_port) + " " + ExecUtils.escapeCmdArgument("-u" + m_serverLogin) + " " + ExecUtils.escapeCmdArgument("-p" + m_serverPass) + " " + ExecUtils.escapeCmdArgument(database) + " > " + ExecUtils.escapeCmdArgument(backupPath) ); if (result.ExitCode != 0) { throw new Exception(result.OutputString); } } private void Restore(string database, string backupPath, string tempDir, string netDir, string netUser, string netPass, long maxFileSize, long maxLogSize, bool dropLogins, bool grantAlterDatabase, bool backupOperator) { if (string.IsNullOrEmpty(database)) { throw new ArgumentException("Invalid -database parameter value"); } if (string.IsNullOrEmpty(backupPath)) { throw new ArgumentException("Invalid -backup-path parameter value"); } string backupDir = Path.GetDirectoryName(backupPath); if (string.IsNullOrEmpty(backupDir)) { backupDir = Path.GetPathRoot(backupPath); } string backupFileName = Path.GetFileName(backupPath); string workingTempDir = !string.IsNullOrEmpty(tempDir) ? tempDir : backupDir; string workingNetDir = !string.IsNullOrEmpty(netDir) ? netDir : workingTempDir; string workingTempPath = Path.Combine(workingTempDir, backupFileName); string workingNetPath = Path.Combine(workingNetDir, backupFileName); RemoteWinConnection netConnection = null; try { if (workingNetDir.StartsWith(@"\\") && !string.IsNullOrEmpty(netUser)) { netConnection = new RemoteWinConnection(); netConnection.ConnectToNetworkResource(GetWinHostName(workingNetDir), new RemoteWinConnection.ConnectionParameters(netUser, netPass)); } if (!string.IsNullOrEmpty(netDir) || workingTempPath != backupPath) { workingTempPath = GetRealBackupPath(database, backupFileName, workingTempDir, false); workingNetPath = GetRealBackupPath(database, backupFileName, workingNetDir, true); File.Copy(backupPath, workingNetPath, true); } switch (m_type) { case ServerType.MS_SQL: MsRestore(database, workingTempPath, maxFileSize, maxLogSize, dropLogins, grantAlterDatabase, backupOperator); break; case ServerType.MySql: MyRestore(database, workingTempPath); break; default: throw new DbBackupException(DbBackupException.OTHER_ERROR, "Unexpected type of database server."); } } catch (DbBackupException) { throw; } catch (Exception ex) { throw new DbBackupException(DbBackupException.DB_RESTORE_ERROR, string.Format("Unable to restore database '{0}'", database), ex); } finally { try { if (workingNetPath != backupPath) { if (File.Exists(workingNetPath)) { File.Delete(workingNetPath); } } } catch (Exception ex) { Log.Write(LogType.Error, ex); } try { if (netConnection != null) netConnection.CancelNetworkConnection(); } catch (Exception ex) { Log.Write(LogType.Error, ex); } } } private void checkMsDump(string backupPath) { using (SqlConnection connection = new SqlConnection(MsConnectionString)) { connection.Open(); detectMsSqlServerVersion(connection); try { ExecuteMsCommand(string.Format("RESTORE VERIFYONLY FROM DISK='{0}'", backupPath), connection); } catch (Exception ex) { throw new ApplicationException("Not valid backup:", ex); } } } private Tuple getMaxFileSizeFromDump(string backupPath) { long maxDbFileSize = 0; long maxDbLogFileSize = 0; int countDbFiles = 0; int countLogFiles = 0; using (SqlConnection connection = new SqlConnection(MsConnectionString)) { connection.Open(); try { using (SqlDataReader reader = ExecuteMsQuery(string.Format("RESTORE FILELISTONLY FROM DISK='{0}'", backupPath), connection)) { while (reader.Read()) { string fileType = reader["Type"].ToString(); var fileSize = Int64.Parse(reader["Size"].ToString()); switch (fileType) { case "D": countDbFiles++; if (fileSize > maxDbFileSize) { maxDbFileSize = fileSize; } break; case "L": countLogFiles++; if (fileSize > maxDbLogFileSize) { maxDbLogFileSize = fileSize; } break; } } } } catch (Exception ex) { throw new ApplicationException("Unable to determine database file size:", ex); } } if (countDbFiles > 1) { throw new ApplicationException("Unable to restore the Microsoft SQL database: Dump contains more than one database files"); } if (countLogFiles > 1) { throw new ApplicationException("Unable to restore the Microsoft SQL database: Dump contains more than one log files"); } return new Tuple(maxDbFileSize, maxDbLogFileSize); } private void MsRestore(string database, string backupPath, long maxFileSize, long maxLogSize, bool dropLogins, bool grantAlterDatabase, bool backupOperator) { List neededLogins = new List(); checkMsDump(backupPath); var fileSize = getMaxFileSizeFromDump(backupPath); if (maxFileSize != -1) { if (fileSize.Item1 > maxFileSize) { throw new ApplicationException(String.Format("Unable to restore the Microsoft SQL database: '{0}': File size ({1}Mb) in backup is greater than the maximum allowed size: {2}Mb", database, fileSize.Item1 / 1048576, maxFileSize / 1048576)); } } if (maxLogSize != -1) { if (fileSize.Item2 > maxLogSize) { throw new ApplicationException(String.Format("Unable to restore the Microsoft SQL database: '{0}': Log file size ({1}Mb) in backup is greater than the maximum allowed size: {2}Mb", database, fileSize.Item2 / 1048576, maxLogSize / 1048576)); } } using (SqlConnection connection = new SqlConnection(MsConnectionString)) { connection.Open(); detectMsSqlServerVersion(connection); if (!dropLogins) { CollectNeededLogins(connection, database, neededLogins); } } using (SqlConnection connection = new SqlConnection(MsConnectionString)) { connection.Open(); string alterDatabaseQuery = string.Format( @"IF EXISTS (SELECT 1 FROM master.dbo.sysdatabases WHERE name = '{0}') BEGIN ALTER DATABASE [{0}] SET SINGLE_USER WITH ROLLBACK IMMEDIATE END", database); ExecuteMsCommand(alterDatabaseQuery, connection); string optionsString = GetMovingString(database, backupPath, connection); if (!string.IsNullOrEmpty(optionsString) && !string.IsNullOrEmpty(optionsString.Trim())) { optionsString += ", REPLACE"; } string restoreStr = string.Format("RESTORE DATABASE [{0}] FROM DISK='{1}' {2}", database, backupPath, optionsString); ExecuteMsCommand(restoreStr, connection); if (!dropLogins) { FixNeededLogins(database, connection, neededLogins, grantAlterDatabase, backupOperator); } // I found bug http://bugs.plesk.ru/show_bug.cgi?id=118699 . This function for this bug FixUsers(database, connection); } WriteMsDbUsers(database, false); } private void detectMsSqlServerVersion(SqlConnection connection) { SqlCommand sqlCommand = connection.CreateCommand(); sqlCommand.CommandText = "SELECT @@VERSION"; string version = (string) sqlCommand.ExecuteScalar(); Match versionMatch = Regex.Match(version, @"-\s(?\d+[\d\.]*)"); if (versionMatch.Success == true) m_serverVersion = new Version(versionMatch.Groups["version"].Value); } private string GetMovingString(string database, string workingPath, SqlConnection connection) { string dataPath = ""; string logsPath = ""; PSAUtils.GetSQLDataLocations(connection, ref dataPath, ref logsPath); string movingStr = "WITH "; using (SqlDataReader reader = ExecuteMsQuery(string.Format("RESTORE FILELISTONLY FROM DISK='{0}'", workingPath), connection)) { string delimiter = ""; while (reader.Read()) { string logicalName = reader["LogicalName"].ToString(); string physicalName = reader["PhysicalName"].ToString(); string fileType = reader["Type"].ToString(); string newPhysicalFile = database + "_" + Path.GetFileNameWithoutExtension(physicalName) + "_" + Guid.NewGuid().ToString("N") + Path.GetExtension(physicalName); string newPhysicalName = Path.Combine((fileType == "L" ? logsPath : dataPath), newPhysicalFile); movingStr += delimiter + string.Format("MOVE '{0}' TO '{1}'", logicalName, newPhysicalName); delimiter = ", "; } } if (!movingStr.Contains("MOVE")) { return ""; } return movingStr; } private void CollectNeededLogins(SqlConnection connection, string database, List neededLogins) { ServerConnection smoConnection = null; try { smoConnection = new ServerConnection(connection); Server smoServer = new Server(smoConnection); Database smoDatabase = smoServer.Databases[database]; if (smoDatabase != null) { foreach (User user in smoDatabase.Users) { if (!string.IsNullOrEmpty(user.Login) && !IsAuxilaryUser(user)) neededLogins.Add(user.Login); } } } finally { if (smoConnection != null) smoConnection.Disconnect(); } } private void FixNeededLogins(string database, SqlConnection connection, List neededLogins, bool grantAlterDatabase, bool backupOperator) { foreach (string login in neededLogins) { if (!IsLoginExist(login, connection)) continue; if (!IsUserExists(database, login, connection)) { ExecuteMsCommand(string.Format("exec [{0}].dbo.sp_grantdbaccess '{1}'", database, login), connection); ExecuteMsCommand(string.Format("exec [{0}].dbo.sp_addrolemember 'db_securityadmin', '{1}'", database, login), connection); ExecuteMsCommand(string.Format("exec [{0}].dbo.sp_addrolemember 'db_ddladmin', '{1}'", database, login), connection); ExecuteMsCommand(string.Format("exec [{0}].dbo.sp_addrolemember 'db_datareader', '{1}'", database, login), connection); ExecuteMsCommand(string.Format("exec [{0}].dbo.sp_addrolemember 'db_datawriter', '{1}'", database, login), connection); if (backupOperator) { ExecuteMsCommand(string.Format("exec [{0}].dbo.sp_addrolemember 'db_backupoperator', '{1}'", database, login), connection); } ExecuteMsCommand(string.Format("USE [{0}];GRANT EXECUTE TO [{1}] AS [dbo]", database, login), connection); ExecuteMsCommand(string.Format("GRANT SHOWPLAN TO [{0}] AS [dbo]", login), connection); ExecuteMsCommand(string.Format("GRANT VIEW DATABASE STATE TO [{0}] AS [dbo]", login), connection); ExecuteMsCommand(string.Format("GRANT CONNECT REPLICATION TO [{0}] AS [dbo]", login), connection); ExecuteMsCommand(string.Format("GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [{0}] AS [dbo]", login), connection); if (grantAlterDatabase) { ExecuteMsCommand(string.Format("GRANT ALTER TO [{0}] AS [dbo]", login), connection); } } BindLoginToUser(database, login, connection); } } private bool IsUserExists(string database, string userName, SqlConnection connection) { using (SqlDataReader reader = ExecuteMsQuery(string.Format("select * from [{0}].dbo.sysusers where name = '{1}'", database, userName), connection)) { return reader.Read(); } } private void WriteMsDbUsers(string database, bool checkOrphaned) { ServerConnection smoConnection = null; try { smoConnection = new ServerConnection(m_server, m_serverLogin, m_serverPass); Server smoServer = new Server(smoConnection); Database smoDatabase = smoServer.Databases[database]; using (XmlTextWriter xmlWriter = new XmlTextWriter(Console.Out)) { xmlWriter.Formatting = Formatting.Indented; xmlWriter.WriteStartElement("users"); foreach (User user in smoDatabase.Users) { if (IsAuxilaryUser(user)) continue; Log.Write(LogType.Warning, user.Name + " " + user.Login + " " + user.ID); xmlWriter.WriteStartElement("user"); xmlWriter.WriteAttributeString("name", user.Name); if (!checkOrphaned) { xmlWriter.WriteAttributeString("login", user.Login); } xmlWriter.WriteEndElement(); } xmlWriter.WriteEndElement(); } } finally { if (smoConnection != null) smoConnection.Disconnect(); } } private void FixUsers(string database, SqlConnection connection) { ServerConnection smoConnection = null; try { smoConnection = new ServerConnection(m_server, m_serverLogin, m_serverPass); Server smoServer = new Server(smoConnection); Database smoDatabase = smoServer.Databases[database]; foreach (User dbUser in smoDatabase.Users) { if (IsAuxilaryUser(dbUser)) continue; try { ExecuteMsCommand(string.Format("USE [{0}];DENY ALTER ANY DATABASE DDL TRIGGER TO {1} AS [dbo]", database, dbUser), connection); } catch (Exception ex) { Log.Write(LogType.Info, ex); } } } finally { if (smoConnection != null) smoConnection.Disconnect(); } } private bool IsAuxilaryUser(User user) { if (user.ID == 1) // it's user "dbo" { return true; } if (user.ID == 2) // it's user "guest" { return true; } if (user.ID == 4 && m_serverVersion != null && m_serverVersion.Major > 8) // it's user "sys" on MS SQL 2005 { return true; } if (user.Name == "INFORMATION_SCHEMA") { return true; } if (user.Login.Contains(@"\")) { return true; } return false; } private void MyRestore(string database, string backupPath) { try { ExecMyScript(string.Format("DROP DATABASE IF EXISTS `{0}`", database), ""); ExecMyScript(string.Format("CREATE DATABASE `{0}`", database), ""); ExecMyScript(File.ReadAllText(backupPath), database); } catch (DbBackupException) { throw; } catch (Exception ex) { throw new DbBackupException(DbBackupException.DB_RESTORE_ERROR, string.Format("Unable to restore database '{0}'", database), ex); } } private void ExecMyScript(string script, string database) { string fileName = null; try { fileName = Path.GetTempFileName(); File.WriteAllText(fileName, script); ExecUtils.ExecResult result = ExecUtils.execExtUtil( Path.Combine(psa.getInstance()["MYSQL_BIN_D"], "mysql"), ExecUtils.escapeCmdArgument("-h" + m_server) + " " + ExecUtils.escapeCmdArgument("-P" + m_port) + " " + ExecUtils.escapeCmdArgument("-u" + m_serverLogin) + " " + ExecUtils.escapeCmdArgument("-p" + m_serverPass) + " " + ExecUtils.escapeCmdArgument(database) + " < " + ExecUtils.escapeCmdArgument(fileName)); if (result.ExitCode != 0) { throw new Exception(result.OutputString); } } finally { if (fileName != null) { File.Delete(fileName); } } } private void CheckOrphanedUsers(string database) { WriteMsDbUsers(database, true); } private void RepairOrphanedUser(string database, string dbUser, string dbUserPwd) { try { using (SqlConnection connection = new SqlConnection(MsConnectionString)) { connection.Open(); if (IsLoginExist(dbUser, connection)) { if (LoginMappings(dbUser) == 1 && IsLoginInBase(dbUser, database)) { //all ok } else { throw new ApplicationException(string.Format("Login '{0}' already exists and has users in other databases", dbUser)); } } else { ExecuteMsCommand(string.Format("exec master.dbo.sp_addlogin '{0}', N'{1}', [{2}]", dbUser, dbUserPwd, database), connection); } BindLoginToUser(database, dbUser, connection); } } catch (DbBackupException) { throw; } catch (Exception ex) { throw new DbBackupException(DbBackupException.DB_RESTORE_ERROR, string.Format("Unable to repair orphaned user '{0}'", dbUser), ex); } } private void BindLoginToUser(string database, string login, SqlConnection connection) { ExecuteMsCommand(string.Format("exec [{0}].dbo.sp_change_users_login 'update_one', '{1}', '{1}'", database, login), connection); } private int LoginMappings(string login) { ServerConnection smoConnection = null; try { smoConnection = new ServerConnection(m_server, m_serverLogin, m_serverPass); Server smoServer = new Server(smoConnection); Login dbLogin = smoServer.Logins[login]; DatabaseMapping[] dbMappings = dbLogin.EnumDatabaseMappings(); int ret = 0; for (int i = 0; i < dbMappings.Length; i++) { if (!string.IsNullOrEmpty(dbMappings[i].UserName)) { ret++; } } return ret; } finally { if (smoConnection != null) smoConnection.Disconnect(); } } private bool IsLoginInBase(string login, string homeDb) { ServerConnection smoConnection = null; try { smoConnection = new ServerConnection(m_server, m_serverLogin, m_serverPass); Server smoServer = new Server(smoConnection); Login dbLogin = smoServer.Logins[login]; DatabaseMapping[] dbMappings = dbLogin.EnumDatabaseMappings(); for (int i = 0; i < dbMappings.Length; i++) { if (!string.IsNullOrEmpty(dbMappings[i].UserName)) { if (dbMappings[i].DBName == homeDb) { return true; } } } return false; } finally { if (smoConnection != null) smoConnection.Disconnect(); } } private bool IsLoginExist(string login, SqlConnection connection) { using (SqlDataReader reader = ExecuteMsQuery(string.Format("select * from master.dbo.syslogins where name = '{0}'", login), connection)) { return reader.Read(); } } private int ExecuteMsCommand(string commandStr, SqlConnection connection) { SqlCommand command = new SqlCommand(commandStr, connection); command.CommandTimeout = getCommandTimeout(); return command.ExecuteNonQuery(); } private SqlDataReader ExecuteMsQuery(string queryStr, SqlConnection connection) { SqlCommand command = new SqlCommand(queryStr, connection); return command.ExecuteReader(); } private void Copy(string srcDbName, string dstSrv, int? dstPort, string dstSrvLogin, string dstSrvPwd, string dstDbName, bool withData, bool copyIFLoginsExist) { try { switch (m_type) { case ServerType.MS_SQL: MsCopy(srcDbName, dstSrv, dstSrvLogin, dstSrvPwd, dstDbName, withData, copyIFLoginsExist); break; case ServerType.MySql: MyCopy(srcDbName, dstSrv, dstPort, dstSrvLogin, dstSrvPwd, dstDbName, withData); break; default: throw new DbBackupException(DbBackupException.OTHER_ERROR, "Unexpected type of database server."); } } catch (DbBackupException) { throw; } catch (Exception ex) { throw new DbBackupException(DbBackupException.DB_COPY_ERROR, string.Format("Unable to copy database '{0}' to database '{1}''", srcDbName, dstDbName), ex); } } private void MsCopy(string srcDbName, string dstSrv, string dstSrvLogin, string dstSrvPwd, string dstDbName, bool withData, bool copyIFLoginsExist) { string srcConnString = string.Format("Data Source={0};UID={1};PWD={2};Initial Catalog={3};Connect Timeout={4};Max Pool Size=1000;Persist Security Info=True;", m_server, m_serverLogin, m_serverPass, srcDbName, getConnectionTimeout() ); using (SqlConnection srcConn = new SqlConnection(srcConnString)) { string dstConnString = string.Format("Data Source={0};UID={1};PWD={2};Initial Catalog={3};Connect Timeout={4};Persist Security Info=True;", dstSrv, dstSrvLogin, dstSrvPwd, dstDbName, getConnectionTimeout() ); using (SqlConnection dstConn = new SqlConnection(dstConnString)) { using (MsCopyProvider prov = new MsCopyProvider(srcConn, dstConn)) { prov.Copy(withData, copyIFLoginsExist); } } } } private void MyCopy(string srcDbName, string dstSrv, int? dstPort, string dstSrvLogin, string dstSrvPwd, string dstDbName, bool withData) { MyCopyProvider prov = new MyCopyProvider(m_server, m_port, m_serverLogin, m_serverPass, srcDbName); prov.Copy(dstSrv, dstPort, dstSrvLogin, dstSrvPwd, dstDbName, withData); } private static void CheckNetworkSettings(string netDir, string netUser, string netPass) { RemoteWinConnection netConnection = null; try { if (netDir.StartsWith(@"\\")) { netConnection = new RemoteWinConnection(); netConnection.ConnectToNetworkResource(GetWinHostName(netDir), new RemoteWinConnection.ConnectionParameters(netUser, netPass)); } } catch (Exception ex) { throw new DbBackupException(DbBackupException.DB_BACKUP_ERROR, "Unable to connect to the network share", ex); } finally { try { if (netConnection != null) netConnection.CancelNetworkConnection(); } catch (Exception ex) { Log.Write(LogType.Error, ex); } } } private static void Usage() { Console.WriteLine("Usage: dbbackup [OPTIONS]"); Console.WriteLine(""); Console.WriteLine("Commands:"); Console.WriteLine(""); Console.WriteLine("--backup - backup database"); Console.WriteLine(" -server=server"); Console.WriteLine(" -server-type=mysql|mssql"); Console.WriteLine(" [-port=port]"); Console.WriteLine(" -server-login=login"); Console.WriteLine(" -server-pwd=pwd"); Console.WriteLine(" -database=database"); Console.WriteLine(" -backup-path=path"); Console.WriteLine(" [-temp-dir=dir]"); Console.WriteLine(" [-net-dir=dir]"); Console.WriteLine(" [-net-user=user]"); Console.WriteLine(" [-net-pwd=pwd]"); Console.WriteLine(""); Console.WriteLine("--restore - restore database"); Console.WriteLine(" -server=server"); Console.WriteLine(" -server-type=mysql|mssql"); Console.WriteLine(" [-port=port]"); Console.WriteLine(" -server-login=login"); Console.WriteLine(" -server-pwd=pwd"); Console.WriteLine(" -database=database"); Console.WriteLine(" -backup-path=path"); Console.WriteLine(" [-temp-dir=dir]"); Console.WriteLine(" [-net-dir=dir]"); Console.WriteLine(" [-net-user=user]"); Console.WriteLine(" [-net-pwd=pwd]"); Console.WriteLine(" [-max-file-size=file_size]"); Console.WriteLine(" [-max-log-size=log_size]"); Console.WriteLine(""); Console.WriteLine("--check-orphaned-users"); Console.WriteLine(" -server=server"); Console.WriteLine(" -server-login=login"); Console.WriteLine(" -server-pwd=pwd"); Console.WriteLine(" -database=database"); Console.WriteLine(""); Console.WriteLine("--repair-orphaned-user"); Console.WriteLine(" -server=server"); Console.WriteLine(" -server-login=login"); Console.WriteLine(" -server-pwd=pwd"); Console.WriteLine(" -database=database"); Console.WriteLine(" -db-user-user"); Console.WriteLine(""); Console.WriteLine("--copy - copy database"); Console.WriteLine(" -src-server=server"); Console.WriteLine(" -server-type=mysql|mssql"); Console.WriteLine(" [-src-port=port]"); Console.WriteLine(" -src-server-login=login"); Console.WriteLine(" -src-server-pwd=pwd"); Console.WriteLine(" -src-database=database"); Console.WriteLine(" -dst-server=server"); Console.WriteLine(" [-dst-port=port]"); Console.WriteLine(" -dst-server-login=login"); Console.WriteLine(" -dst-server-pwd=pwd"); Console.WriteLine(" -dst-database=database"); Console.WriteLine(" -with-data"); Console.WriteLine(""); Console.WriteLine("--help - display this information"); } } }