SQL Server DB Backup and Restore

C#을 이용한 MS SQL Server Backup Restore 프로그램밍 ( * Local PC 백업 )

SQL Server Database Backup and Restore using C# - Part 1 

https://youtu.be/5E0q5cZjblk

DB 백업 / 리스토어 Design 화면 만들기. 

SQL Server Database Backup and Restore using C# - Part 2

https://youtu.be/AfYxy9tiQZQ

Local Backup 소스 영역 만들기.

SQL Server Database Backup and Restore using C# - Part 3

https://youtu.be/HuPYmY0XLjk

Local Restore 소스 영역 만들기

Local Backup 소스 영역

/*

#

# MySQL local Backup 소스 영역

#

*/

/// using 선언

using System.Data.SqlClient;

폼안에 SqlConnection 인스턴트 선언

private Sqlconnection conn;

private SqlCommand command;

private SqlDataReader reader;

string sql = "";

string connectionString ="";

/// BtnCoonet_Click 작업

try

{

connectionString = "Data Source=" + txtDataSource.Text + "; User Id="+ txtUserId.Text + "; Password="+ txtPassword.Text+"";

conn = new SqlConnection(connectionString);

conn.Open();

//sql = "EXEC sp_databases";//(1) DB목록뽑기

sql = "SELCT * FROM sys.database d WHERE d.database_id > 4";//(2) 

command =  new SqlCommand(sql, conn);

reader = command.ExecuteReader();

// ComboBax(cmbDatabase)에 목록 추가하기

cmbDatabases.Items.Clear();

while (reader.Read())

{

cmbDatabases.Items.Add(reader[0].ToString());

}

reader.Dispose();

conn.Close();

conn.Dispose();

// (3) 

txtDataSource.Enabled = false;

txtPassword.Enabled = false;

txtUserId.Enabled = false;

BtnConnect.Enabled = false;

BtnDisconnect.Enabled = true; //접속완료시 Disconnect 활성화

BtnBackup.Enabled = true;

BtnRestore.Enabled = true;

cmbDatabases.Enabled = true;

}

catch (Exception ex)

{

MessageBox.Show(ex.Message);

}

/// BtnDisconnect_Click 작업

txtDataSource.Enabled = false;

txtUserId.Enabled = true;

txtPassword.Enabled = true;

//txtConnect.Enabled = true;

cmbDatabases.Enabled = false;

BtnBackup.Enabled = false;

BtnRestore.Enabled = false;

/// Form1_Load 작업

BtnDisconnect.Enabled = false;

dmbDatabases.Enabled = false;

BtnBackup.Enabled = false;

BtnRestore.Enabled = false;

/// BtnBackup_Click  작업

try

{

if (cmbDatabases.Text.CompareTo("") == 0)

{

MessageBox.Show("Please select a Database.");

return;

}

conn = new SqlConnection(connectionString);

conn.Open();

sql = "BACKUP DATABASE "+cmdDatabase.Text+" TO DISK = '"+txtBackupFileLoc.Text+"\\"+cmdDatabases.Text+"-"+DataTime.Now.Ticks.ToString()+".bak'";

command = new SqlCommand(sql, conn);

command.ExecuteNonQuery();

conn.Close();

conn.Dispose();

MessageBox.Show("Successfully Databa Backup Completed");

}

catch (Exception ex)

{

MessageBox.Show(ex.Message);

}

/// BtnBrowse_Click 작업

FolderBrowserDialog dlg = new FolderBrowerDialog();

if (dlg.ShowDiaglog() == DialogReuslt.OK)

{

txtBackupFileLoc.Text = dlg.SelectedPath;

}

Local Restore 소스 영역

/*

#

# MySQL local Restore 소스영역

#

*/

/// BtnDBFileBrowse_Click 작업

OpenFileDialog dlg = new OpenFileDialog();

dlg.Filter = "Backup Files(*.bak)|*.bak|All Files(*.*)|*.*";

dlg.FilterIndex = 0;

if (dlg.ShowDialog() == DialogResult.OK)

{

txtRestoreFileLoc.Text = dlg.FileName;

}

/// BtnRestore_Click 작업

try

{

if (cmbDatabases.Text.CompareTo("") == 0)

{

MessageBox.Show("Please select a Database.");

return;

}

if (txtRestoreFileLoc.Text.Equals(""))

{

MessageBox.Show("Please select a Backup File");

return;

}

conn = new SqlConnection(connectionString);

conn.Open();

sql = "Alter Database " + "DB명" + " Set SINGLE_USER WITH ROLLBACK IMMEDIATE;";

sql += "Restore DATABASE " + "DB명" + " FROM DISK='" + ucTxtRestoreFileLoc.strText +"' WITH REPLACE;";
        sql += "ALTER DATABASE "+ "DB명"+" SET MULTI_USER";

command = new SqlCommand(sql, conn);

command.ExecuteNonQuery();

conn.Close();

conn.Dispose();

MessageBox.Show("Successfully Database Restore Completed");

}

catch (Exception ex)

{

MessageBox.Show(ex.Message);

}

[ 참고사항 ]

DB를 복원할때, 사용을 하고 있으면 문제가 되기에, 혹시 사용을 하고 있는 DB를 복원하려면, Catalog를 선택해줘야 합니다.

string connectionString = "Data Source=localhost;Initial Catalog=master;User ID=sa; Password=패스워드";//OK

string connectionString = "Data Source=localhost;Trusted_Connection=True;";//OK

string connectionString = "Data Source=localhost;User ID=sa; Password=패스워드;"//OK;

string connectionString = "Data Source=localhost;Initial Catalog={DB명};User ID=sa; Password=패스워드";//Error

initial Catalog={특정DB명} 으로 하게 되면, Database Restore시 에러가 발생합니다.

[ 위의 부분적인 Source 내용 파일 ]

DBBackupRestore.cs

[ 참고 사항 ]

참고: MsSql-Backup-Restore-명령으로-데이터베이스-복사

MSSQL에 DB  백업 / 복원 명령 (백업/복원 마법사 사용불가시)

Backup >

BACKUP DATABASE 데이터베이스명 TO DISK ='D:\백업파일명.bak'

Restore >

RESTORE FILELISTONLY <-- 명령으로 백업 세트의 Logical Name을 알아냅니다.

RESTORE FILELISTONLY FROM DISK='D:\백업파일.bak'

Logical Name을 확인후, WITH MOVE 옵션 뒤에 입력, mdf, ldf 파일의 경로를 지정합니다.

RESTORE DATABASE 데이터베이스명 FROM DISK ='D:\백업파일.bak'

WITH MOVE '데이터베이스명' TO 'D:\백업파일.mdf'

MOVE '데이터베이스_Log' TO 'D:\백업파일_log.ldf'

[ 참고 사항 2 ]

How to backup Sql Database Programmatically in C#

http://www.mssqltips.com/sqlservertip/1849/backup-and-restore-sql-server-databases-programmatically-with-smo/

The following Link has explained complete details about how to back sql server 2008 database using c#

Sql Database backup can be done using many way. You can either use Sql Commands like in the other answer or have create your own class to backup data.

But these are different mode of backup.

1. Full Database Backup
2. Differential Database Backup
3. Transaction Log Backup
4. Backup with Compression

But the disadvantage with this method is that it needs your sql management studio to be installed on your client system.

[ 참고 사항 2-2 ]

Backup and Restore SQL Server databases programmatically with SMO

Getting started with SQL Server Management Objects (SMO)

[ 참고 사항 3 ]

[DB] MSSQL Server 'Suspect' 모드에서 복구하기

MS SQL을 사용하다 보면 드물게 ‘Suspect’ 모드로 나타나는 경우 가 있습니다. (한글은 ‘주의대상’) 

이는 H/W 또는 O/S상에서 실수로 인해 발생합니다. (예: Transaction log파일 삭제)

발생예시

• 해당 시스템이 지정된 파일을 찾을 수 없을 때 발생합니다.
• 데이터 또는 로그가 존재하는 장치를 열수 없을 때 발생합니다.
• SQL서버가 트렌젝션 중에 다운되거나 재시작되었을 때, 트렌젝션 로그가 손상되면 발생합니다.
• 안티바이러스 프로그램 등으로 인해 SQL서버가 특정 데이터나 로그파일에 접근할 수 없을 때 발생합니다.

문제가 발생하면 처음에는 SQL Server가 Device file에 배타적 잠금을 시도합니다. 만약 해당 파일이 다른 프로세스에 의해 사용 중이거나 파일이 없으면 SQL Server는 에러를 출력합니다.

하지만, Device와 Database에는 문제가 있는 것은 아니며, 복구를 위해서는 반드시 Device와 Database를 이용할 수 있는 상태여야 합니다.

해결

문제를 해겨하기 위해서는 다음과 같은 명령어를 실행합니다.

1.EXEC sp_resetstatus ‘DB이름’;

2.ALTER DATABASE DB이름 SET EMERGENCY

3.DBCC CHECKDB (‘DB이름’)

4.ALTER DATABASE DB이름 SET SINGLE_USER WITH ROLLBACK IMMEDIATE

5.DBCC CHECKDB (‘DB이름’, REPAIR_ALLOW_DATA_LOSS)

6.ALTER DATABASE DB이름 SET MULTI_USER

위 명령어는 DB의 논리적, 물리적 일관성을 검증하고 복구하기 위한 일련의 작업을 실행합니다.

sp_resetstatus 저장프로시저는 Suspect를 해제합니다. sp_resetstatus는 System Administrator만 실행할 수 있습니다.

다음에는 DB를 EMERGENCY모드로 전환하며, 이 때부터 DB는 READ_ONLY가 되며 오로지 sysadmin fixed server roles만이 서버에 접근할 수 있게 됩니다.

DBCC checkdb(‘DB이름’) 명령을 통해 master 데이터베이스에 대해 일관성 검증을 실행합니다. 다음 단계는 현재 DB에 존재하는 트렌젝션들을 롤백하고 DB를 Single User 모드로 전환합니다. 다음으로 DBCC를 통해 DB의 복구를 시도하며 최종적으로 다시 Multi User 모드로 전환합니다.

만약 이 단계를 거쳤는데도 Suspect가 해제되지 않을 경우에는, 또 다른 문제가 있는 것입니다. 이 경우 실행 가능한 가장 좋은 대안은 DB를 백업으로부터 복구하는 것입니다.

기타 (DBCC CHECKDB 명령어)

위에서 DBCC CHECKDB 명령어는 DB의 일관성과 무결성을 점검하며, 오류로부터 데이터를 복구하는 명령입니다.

• 인덱스와 데이터 페이지가 정확하게 연결되어 있는지 점검합니다.
• 인덱스가 가장 최신의 것이며 정확히 정렬되어 있는지 점검합니다.
• 포인터의 무결성을 검증합니다.
• 각 페이지의 데이터가 최신의 것인지 점검합니다.
• 페이지 오프셋이 최신의 것이 맞는지 점검합니다.

옵션: 다음과 같은 3가지 옵션 중에서 선택할 수 있습니다.

1. REPAIR_FAST: 데이터의 손실이 없는 사소한 오류를 수정하며 시간이 걸리지 않는 작업을 합니다.
2. REPAIR_REBUILD: REPAIR_FAST가 하는 작업과 더하여 인덱스를 재생성하는 등 시간이 걸리지만 데이터는 손실되지 않는 작업을 수행합니다.
3. REPAIR_ALLOW_DATA_LOSS: REPAIR_REBUILD의 모든 작업과 더불어 할당오류, 구조적 행 오류, 페이지 오류, 손상된 텍스트 개체 삭지 및 수정을 위한 할당, 할당 취소 등의 작업이 이루어 집니다.

[ 싱글/멀티 유저 모드 ]

USE master;
GO
ALTER DATABASE AdventureWorks2012
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE AdventureWorks2012
SET READ_ONLY;
GO
ALTER DATABASE AdventureWorks2012
SET MULTI_USER;
GO

출처: Set a Database to Single-user Mode

[ 싱글 -> 멀티 유저 모드로 변환 ]

ALETER DATABASE SnowPOS

SET MULTI_USER;

GO

실질적으로 상단위 복원 부분은 다음과 같이 수정이 좋을 수 있습니다.

sql = "Alter Database " + "DB명" + " Set SINGLE_USER WITH ROLLBACK IMMEDIATE;";

sql += "Restore DATABASE " + "DB명" + " FROM DISK='" + ucTxtRestoreFileLoc.strText +"' WITH REPLACE;";

sql +="Alter Database "+ "DB명" + " Set MULTI_USER;";

추가로, 컨넥션에 Initial Catalog=master 를 추가하는것을 권합니다.

출처: Set database from SINGLE USER mode to MULTI USER