Created
February 28, 2017 20:50
-
-
Save cafeasp/4ed47a43a1abff4e95456f706aa45352 to your computer and use it in GitHub Desktop.
C# SQL Backup
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
public class BackupService | |
{ | |
private readonly string _connectionString; | |
private readonly string _backupFolderFullPath; | |
private readonly string[] _systemDatabaseNames = { "master", "tempdb", "model", "msdb" }; | |
public BackupService(string connectionString, string backupFolderFullPath) | |
{ | |
_connectionString = connectionString; | |
_backupFolderFullPath = backupFolderFullPath; | |
} | |
public void BackupAllUserDatabases() | |
{ | |
foreach (string databaseName in GetAllUserDatabases()) | |
{ | |
BackupDatabase(databaseName); | |
} | |
} | |
public void BackupDatabase(string databaseName) | |
{ | |
string filePath = BuildBackupPathWithFilename(databaseName); | |
using (var connection = new SqlConnection(_connectionString)) | |
{ | |
var query = String.Format("BACKUP DATABASE [{0}] TO DISK='{1}'", databaseName, filePath); | |
using (var command = new SqlCommand(query, connection)) | |
{ | |
connection.Open(); | |
command.ExecuteNonQuery(); | |
} | |
} | |
} | |
private IEnumerable<string> GetAllUserDatabases() | |
{ | |
var databases = new List<String>(); | |
DataTable databasesTable; | |
using (var connection = new SqlConnection(_connectionString)) | |
{ | |
connection.Open(); | |
databasesTable = connection.GetSchema("Databases"); | |
connection.Close(); | |
} | |
foreach (DataRow row in databasesTable.Rows) | |
{ | |
string databaseName = row["database_name"].ToString(); | |
if (_systemDatabaseNames.Contains(databaseName)) | |
continue; | |
databases.Add(databaseName); | |
} | |
return databases; | |
} | |
private string BuildBackupPathWithFilename(string databaseName) | |
{ | |
string filename = string.Format("{0}-{1}.bak", databaseName, DateTime.Now.ToString("yyyy-MM-dd")); | |
return Path.Combine(_backupFolderFullPath, filename); | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment