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`. http://stackoverflow.com/questions/15944123/sql-server-back-restore-login-failed — Rick S, Feb 23 '17 at 16:37

score 1 · Answer 1 · answered Feb 23 '17 at 18:49

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 '17 at 18:49

Phil Pledger

466
2
3

I think I was searching on SID to avoid this problem thank u so much – Rami ZK Feb 23 '17 at 19:07
Did this answer your question? – Phil Pledger Feb 24 '17 at 19:29
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 – Rami ZK Feb 27 '17 at 20:20
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? – Phil Pledger Feb 28 '17 at 16:49
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 – Rami ZK Feb 28 '17 at 17:48

Import and Export data mdf sqlserver

1 Answers1