C# .NET - Remote sqlserver database backup and restore on local

Asked By kailash on 28-May-11 09:22 AM
i am create .net c# application for Database backup and restore i am able to backup and restore
database backup from local sql server ,and when i connect from remote sqlserver i get backup that time backup
file will be store on remote system and it will restore from remote server,if i remove from  remote system then everything lost.so i want to store backup file on my local pc and restore from local computer
how it is possible

Thank you

TSN ... replied to kailash on 28-May-11 02:33 PM
hi..

ackup and Restore is one of the high availability model. In this article, I would like to perform Backup on Remote server and Performing Restore the backup file(.bak) from Remote Server into local SQL Server.

How to access the Remote Server shared path from local server ?

We can use Map network drive with Remote Server.

How to perform the Map network Drive with Remote Server ?

We have two Servers (ServerA and ServerB),

If we want to perform Database Backup from ServerA and Put the backup file(.bak) to Remote Server(ServerB).

If we want to perform Database Restore on ServerA from ServerB - The Backup file(.bak) will be there in Remote Server(ServerB).


 

Implementing Map Network Drive

Login into the Remote server where you want to perform Backup, Restore and retain / refer the Backup file:



Give the User name and Password to login into the Remote server…

Decide which path you want to share and Map to the Network Map drive…

I would like to create one Folder in D:\ drive named Database Backup and share the folder as given below…







Click the Apply and OK button on Backup Database Properties dialog box.

Login into Local Server/Machine


Login into local Server/Machine where you want to perform Backup and Restore activity…

Right click on My Computer



Click Map Network Drive… as given above



Choose the Drive like Z:\

Click the Browse Button and points the Remote server and Shared Path what you have shared on previous section..
Or
Simply enter the Server IP and Shared Path as given below (\\RemoteServer IP\Database Backup)



Login credential dialog box appeared.

Give the Remote server login credential as given below

User Name : DomainName\UserName
(or)
User Name : UserName

Password   : **********



Click the OK button, Now the system will try to browse the Mapped network drive of the Remote server… as given below



Login into SQL Server…. Try to access the Mapped network drive (Z:) as given below



According to the result given above, The system could not find the drive Z:\, Because the Network Map drive is not identifiable by the SQL Server.

OK. What should be done ?

We have to make the Network Map drive (Z:\) accessible by the SQL Server as given below



Once done the above step, Now The mapped network drive is accessible…. by the SQL Server

Try to access the Network mapped drive from SQL Server as given below…



YES.  Now we can access the Network Mapped drive (Z:)

Backup the Database to Remote Server


Let’s try to Backup a database to Remote server (Z:)

Right Click on Database which you want to backup to Remote server…

Right Click on Database under Object Explore --> Click Tasks --> Click Backup -->






The Database Backup successfully completed and The backup file stored in Remote server(Z:)

Try to make sure by list-out the Backup file from Remote Server…

 

Restore the backup file(.Bak) from Remote Server


Let’s try to Restore the Backup file from Remote Server to Local SQL Server

Right Click on Database which you want to be restored from the Remote server backup backup file(.Bak)…

Right Click on Database under Object Explore --> Click Tasks --> Click Restore --> Click Database…










Now the Backup file from Remote server successfully restored in local SQL Server..

If you want to remove the Mapped network drive… follow the steps as given below



Once ran the script successfully, Follow the steps as given below…





Let’s try to access the Network Map Drive



No, We could not access the Network map drive…
Jitendra Faye replied to kailash on 30-May-11 02:02 AM
Backing up or restoring your MS SQL Server database is a relatively simple process. However; in order to either back up or restore your database you will first need a few programs installed on your computer.
http://www.bodhost.com/You will need to get the Ms SQL Server Utilities which include Enterprise Manager, Client Network Utilities, Query Analyzer and, of course, the Import/Export utility which you will use to transfer your data back and forth via DTS.
All of these are available when you install Microsoft MSDE.


+++++++++++++++++++++++++++++++++++++++++++++
Backing Up Your SQL Server Database
+++++++++++++++++++++++++++++++++++++++++++++

First we will explore the easiest way to do this which is by creating a brand new local database and then copying all of the objects from your existing remote database to the new local one.

1. Create a new blank database in Enterprise Manager.
2. Open your Import/Export Utility program which is the Data Transformation Services Import/Export Wizard.
3. Click Next and Select "Microsoft OLE DB Provider" for SQL Server in the Data Source drop down menu.
4. In the Server drop down menu select your remote server. If there are no servers listed - you will need to establish a connection to your databases.
=========
NOTE:: Please search the forum for How to Connect to an MS SQL Server Database using Enterprise Manager.
=========
5. Click the Radio Button that says "Use SQL Server Authentication" then enter your remote SQL Server Username and Password, select your SQL Server Database Name from the drop down menu and click next.
6. Now we enter our local database information just like we did the remote database information in steps 3-5 above. Click Next.
7. On this page we want to select the 3rd option Copy objects and data between SQL Server databases and click next.
8. On the Select Object to Copy page we will keep all of the defaults and simply click next to get to the next page.
9. Select the check box Run immediately and click next and then click finish.

If you followed the steps above correctly you will see the message "Successfully copied objects from Microsoft SQL Server to Microsoft SQL Server" and you will now have a copy of your remote SQL Server database on your local machine including all of your users and their permissions for each object.

=========
Here is the problem with the above routine.
First we don't want to keep creating new databases each time we back up our remote database. What we would rather do is create one similar local database and then keep updating that database when new information needs to be backed up from our remote or visa versa. This is where DTS, or the Data Transformation Services comes into play.
=========

Now let's assume we want to back up only one table from the database we just created above. This is slightly different from what we did above.

1. Start out by following the procedures 1-6 above until we get to the "Select Table Copy or Query" page.
2. Now on this page instead of selecting the 3rd radio button we are going to select the 1st one: "Copy Table(s) view(s) from the source database and click next.
3. This bring up a grid with three columns being Source, Destination and Transform. We will select only on of the table for this example to demonstrate but when you make a backup of your database you will want to back up all the tables and follow the same steps outlined here.
4. Select a table from the destination column, preferably one that has a unique ID field. You will see information appear in the destination column when you do.
5. Use the drop down menu from the destination column and select the table you wish to copy this information to. Usually this is the same table. Watch out for owner names because sometimes DTS wants to give the destination table a new owner ame rather than the one that it there such as DBO.
6. Once you have your destination table selected properly, go to the transform column and click on the grey box with the 3 dots...
7. On the new window that pops up there are 2 columns of radio buttons. In the left column we want to select either "Delete Rows in destination table" or "Append rows in destination table". Select Delete Rows so our tables will match. If you select Append by accident you will get an error when you try to run the project.
8. On the right column of radio buttons, and if we selected a table with a unique id field, we want to select "Enable identity insert". This will allow DTS to insert identity columns into our existing table without causing an error.
9. Click OK to close the popup window and then click next.
10. Select Run immediately, click next and then Finish.

If you followed the steps above you should see your DTS Package run successfully and get the message "Successfully copied X Table(s) from Microsoft SQL Server to Microsoft SQL Server".

There you have it.
Restore Your SQL Server Database


If you want to restore your remote database simply reverse your source and destination http://www.bodhost.com/%20 in the steps above.
kailash replied to Jitendra Faye on 30-May-11 10:07 AM
i want to use smo clases in c# i dont want to use any wizard i need only code through
get .bak file refrence on local system and i want to restore that .bak file any remote system

Thank You

Asked By kailash on 30-May-11 10:12 AM
i have stored .bak file on remote system but when i go for code that time

Restore sqlRestore = new Restore();
       BackupDeviceItem deviceItem = new BackupDeviceItem(DATABASE_NAME + ".bak", DeviceType.File);
           
       sqlRestore.Devices.Add(deviceItem);
if gives error that .bak file not open on remote server
so what i need to store on local i am storing .bak file on my own location if i define create same location on remote system then shared that directory but how that bak file will store on my local system

Thank you