.:: CODE SNIPPET ::.

"Your time is limited, so don't waste it living someone else's life"

How to back up and Restore sql database


when establish a new software, you usually encounter a problem when using sql database is back up and restore database

this artical mention about this sequently thing.

firstly, i have a connection function like this

SqlConnection connection;

public void OpenCnn()

{
connection = new SqlConnection(@"Data Source=MINHHOANG-PCSQLEXPRESS;Initial Catalog=QuanLyKhachSan;Integrated Security=True");
while (connection.State != ConnectionState.Open)
{
connection.Open();
}
}

1. Back up Data

OpenCnn();
SqlCommand cmd = new SqlCommand("backup database QuanLyKhachSan to disk=N"Directory path to *.BAK file'", connection);

cmd.ExecuteNonQuery();
CloseCnn();

2. Restore Data

familiar with Back up, but you have two more things to do that.

first, use master to set the Restrict Access of your database to SINGLE_USER. This makes the status of  your database’s accessibility is single. Because at the same point of time, there are so many services or applications open your connection. Certainly, you can not restore a database which being used by more than one program, right?

USE Master ALTER DATABASE  QuanLyKhachSan SET  SINGLE_USER WITH ROLLBACK IMMEDIATE

in SQL Server Management Studio program, you also create a Query and try this

EXEC SP_WHO2

to know which application and services  come to use your database.

you can kill the applications (not services)  that running on your database by using

KILL …

second, you  restore your database

RESTORE DATABASE QuanLyKhachSan FROM DISK = N' directory path to *bak file'WITH file=1, RECOVERY

finnally, don’t forget to change your Restrict Access to MULTI_USER by

ALTER DATABASE  QuanLyKhachSan SET  MULTI_USER WITH ROLLBACK IMMEDIATE

try this and it will run throught !!

Good luck

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: