0

I have SQL Server Express running on my machine, and I would like to create a backup of a database and restore this backup on my local machine so that I can use the .mdf and .ldf files locally.

I have a small C# program that manages this database and allows me to execute the backup creation and restore requests. However, when I want to copy/paste the .mdf and .ldf files locally, I don't have access rights. Only the administrator account and the SQL Service have access.

Is there a way to perform the restore locally without sharing a folder with the server?

Backup method: 

string cmd = "BACKUP DATABASE [" + "Database" + "] TO DISK='" + "\\\\srv-vm\\PJDB\\Backup\\Database.bak" +  "'";

using (SqlCommand command = new SqlCommand(cmd, con))
{
  if (con.State != ConnectionState.Open)
  {
      con.Open();
  }

  command.ExecuteNonQuery();
}

Restore method: 

SqlConnection objconnection = new SqlConnection(connectionstring);
ServerConnection con = new 
ServerConnection(objconnection.DataSource.ToString());
Server server = new Server(con);

Restore _dataBase = new Restore();

_dataBase.Database = "db";
_dataBase.Action = RestoreActionType.Database;
_dataBase.Devices.AddDevice("\\\\srv-vm\\PJDB\\Backup\\Database.bak", DeviceType.File);
// define "file relocation" - for all "logical" files in a SQL Server database,
// define a new physical location where the file will end up at          
RelocateFile relocateDataFile = new RelocateFile("Database", "\\\\srv-vm\\PJDB\\Backup" + "\\Database.mdf");
RelocateFile relocateLogFile = new RelocateFile("Database_log", "\\\\srv-vm\\PJDB\\Backup" + "\\Database_log.ldf");

_dataBase.RelocateFiles.Add(relocateDataFile);
_dataBase.RelocateFiles.Add(relocateLogFile);
_dataBase.ReplaceDatabase = true;
_dataBase.SqlRestore(server);
_dataBase.NoRecovery = false;
Dale K
  • 25,246
  • 15
  • 42
  • 71
Nicolas
  • 17
  • 3
  • How you take a backup from DB? Are you copy ldf and mdf files or execute backup commands? – Fred Mar 17 '20 at 08:38
  • I execute backup command in my C# program – Nicolas Mar 17 '20 at 08:40
  • 3
    `mdf` and `ldf` aren't backup files, they're the actual database files. You want the `bak` and `trn` files when you created the database/log backups – Thom A Mar 17 '20 at 08:50
  • Our SQL Server at work allows user to De-Attach the database and Re-Attach the database. Once the database is De-Attached you can copy the mdf file. I do this all the time to make backups of the database, and test the database without affecting other users (expect the short period of time to make the copies). I don't normally work locally. Instead I attach the original mdf file and the copy (under a different name) and test the copy. – jdweng Mar 17 '20 at 08:52
  • Yes but i cannot execute the restoration method using a .bak store on my local machine. The restoration method use the connection to the sql server db – Nicolas Mar 17 '20 at 08:53
  • Can i De-attach and re-attach db using C# method ? @jdweng – Nicolas Mar 17 '20 at 08:54
  • Check this question's answers https://stackoverflow.com/questions/3942207/how-can-i-backup-a-remote-sql-server-database-to-a-local-drive – Fred Mar 17 '20 at 08:54
  • 1
    Detaching and moving the files to a different server isn't backing up, nor is it restoring... – Thom A Mar 17 '20 at 09:07
  • The De-Attach and Re-Attach solution cannot works there are people using the DB and i can't de-attach a used db – Nicolas Mar 17 '20 at 09:12
  • Yes. I would first test commands using SQL Server Management Studio before trying with c#. Find database with explorer and then right click database and select "deattach". To attach open a new query window and do following : CREATE DATABASE [] ON (NAME = , FILENAME = ‘’) LOG ON (NAME = , FILENAME = ‘’) FOR ATTACH_REBUILD_LOG The same can be done using c# with a sqlclient ExecuteNonQuery command. – jdweng Mar 17 '20 at 09:15
  • 1
    And you cannot copy a database that is in use either. You need to perform a backup and use the backup files. Those files ***aren't*** `mdf` and `ldf` files. Those are the database and transaction log files being used by the running database. – Thom A Mar 17 '20 at 09:24
  • @jdweng Be sure to use SA when invoking sp_detach_db. Calling it with a Windows authenticated connection will change the file system ACLs on the .mdf and .ldf files which sometimes causes massive headaches when moving the files around or trying to reattach them. – AlwaysLearning Mar 17 '20 at 09:26
  • @Larnu I perform a backup file, but i need on my local computer the `.mdf` and `.ldf`. If i locally copy the backup file, i cannot perform the restoration because the SQL server will not have access to my backup file – Nicolas Mar 17 '20 at 09:31
  • 1
    You need to put the backup file somewhere the SQL Server Service Account has access to, @Nicolas . Don't go copying the `mdf` and `ldf` files. – Thom A Mar 17 '20 at 09:33
  • Ok so I have to set up a shared file for each of the 50 pc's? Isn't there a way to set up access permissions on `.mdf` and `.ldf` files in order to just run a copy process? @Larnu – Nicolas Mar 17 '20 at 10:15
  • Put them in a network drive. If you deattach the mdf/ldf, I haven't seen any access issues. I only have seen access issues when the mdf/ldf were created using archive. – jdweng Mar 17 '20 at 10:36
  • Even if I put them in a network drive and have access to the files, I don't have the rights to open them and therefore exploit them. I Always need administrator access, there's no way to get rid of these restrictions ? – Nicolas Mar 17 '20 at 13:08
  • Maybe you can refer to the msdn doc[Restore database to a new location; optionally rename the database using T-SQL](https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/restore-a-database-to-a-new-location-sql-server?view=sql-server-ver15#restore-database-to-a-new-location-optionally-rename-the-database-using-t-sql) to know how to use T-SQL to do it. – Jack J Jun Mar 18 '20 at 02:56

0 Answers0