Import and Export data mdf sqlserver

Question

I have a localdatabase "office.mdf" I want my application to be able to export the database and import it again I tried to achieve that using backup query

DB.ExecuteNonQuery(@"BACKUP DATABASE [" + Directory.GetCurrentDirectory() + @"\office.mdf] TO DISK = N'" + distination + "'");

and restore query

 DB.ExecuteNonQuery(@"USE [master]; RESTORE DATABASE [" + Directory.GetCurrentDirectory() + @"\office.mdf] " + 
        @"FROM DISK = N'" + source + "'  WITH FILE = 1 , NOUNLOAD, REPLACE, STATS = 10 , NORECOVERY , " +
        @" MOVE 'office_log' TO '" +  Directory.GetCurrentDirectory() + @"\office_log.ldf'" +
        @" MOVE 'office' TO '" + Directory.GetCurrentDirectory() + @"\office.mdf'");

I build my project and I run it on another device then I export the database , I imported the database successfully but after that I couldn't connect to the database anymore and I got message that says

Login failed for user "myuser"

so the first thing how can I solve this problem and after that what is the best approach to export the database and import it back from another device how can I merge the existing database with the exported when (So I won't loose the existing data)

To fix the login error use sp_change_users_login. stackoverflow.com/questions/15944123/… — Rick S
– Rick S, Commented Feb 23, 2017 at 16:37

Phil Pledger · Accepted Answer · 2017-02-23 18:49:30Z

1

In SQL Server, Logins are stored at the instance level in the master database while Users are stored in each user database and are mapped to a Login.

It sounds like the Login that you are trying to use to access the database does not exist on destination server or the SIDs do not match (which would normally be corrected with sp_change_users_login).

One option would be to use CREATE LOGIN ... WITH SID, and use the same SID on all servers where you will be restoring this database.

answered Feb 23, 2017 at 18:49

Phil Pledger

4762 silver badges3 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Rami ZK Over a year ago

I think I was searching on SID to avoid this problem thank u so much

Phil Pledger Over a year ago

Did this answer your question?

Rami ZK Over a year ago

it's useful but unfortunately it didn't because the main problem is that I want to export and import data between databases , thanks anyway

Phil Pledger Over a year ago

Your question seemed to be related to restoring backups and security. Would you like to add some more details about what you are trying to accomplish and what is not working?

Rami ZK Over a year ago

my main question is I want to export and import database from device to another but I tried to do it using backup query so I'm asking if there is another way to accomplish that

Collectives™ on Stack Overflow

Import and Export data mdf sqlserver

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related