How to "clone" a database from a remote server to LocalDB database by a C# application? No relationships back to the remote database are needed.
Application is written in C# using .NET 4.5.2 and supports two modes - online which connects to a remote MS SQL Server database, and offline which connects to a
LocalDB database. The application primarily targets newer versions of the servers (if it matters, supporting only version 2014 is ok).
Before the user goes offline it should ask the application to clone the remote database to the
LocalDB database (the local database is completely overwritten). The local database should be independent on the remote database, i.e. no slave nor replication.
Both the online and offline connection string contains name of the respective database. The application itself has no direct knowledge of the database name nor of the table names as this is managed by the connection strings and by the Entity Framework.
How to "clone" the remote database to a
LocalDB database (the remote database name and the
LocalDB database name might be different)?
I prefer a solution which does not require to launch an external program, but this is not a hard requirement.
Copying through Entity Framework no tracking entities is unacceptable slow.
I am aware of the
BACKUP DATABASE and
RESTORE DATABASE commands, but I have found the following difficulties:
They require me to specify the name of the database. Is there a way how to default them to the initial database specified as part of the connection string?
RESTORE DATABASE command contains names and paths of the respective data files on the disc (
MOVE parts). Is there a way how to process it with specifying just the database name without providing the data files path? Or how to get the data files paths via SQL commands (to get the file names, I will just create a blank database, got the file names, optionally drop the database and use the retrieved file names)?
Is there a better way doing this?
I use a stored procedure i created, but first you need to create a linked server:
IF EXISTS(SELECT name FROM sys.servers WHERE name = 'SERVER') BEGIN-- EXEC sp_dropserver 'SERVER', 'droplogins' END /****** Object: LinkedServer [SERVER] create LinkedServer ******/ EXEC master.dbo.sp_addlinkedserver @server = N'SERVER', @srvproduct=N'SQLNCLI', @provider=N'SQLNCLI', @datasrc=N'192.168.1.1' -- IP address of a server /* Add login data*/ EXEC sp_addlinkedsrvlogin @useself='FALSE', @rmtsrvname='SERVER', @rmtuser='User', @rmtpassword='Pass'
Then you can create stored procedure on local server or execute the query directly from application, also for safety I am using a transaction in this example:
USE [DB] GO /****** Object: StoredProcedure [dbo].[backupdatabase] ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[backupdatabase] AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; BEGIN TRY BEGIN TRANSACTION TRUNCATE TABLE [dbo].[table_1] INSERT INTO [dbo].[table_1] SELECT * FROM [SERVER].[DB].[dbo].[table_1] TRUNCATE TABLE [dbo].[table_2] INSERT INTO [dbo].[table_2] SELECT * FROM [SERVER].[DB].[dbo].[table_2] TRUNCATE TABLE [dbo].[table_3] INSERT INTO [dbo].[table_3] SELECT * FROM [SERVER].[DB].[dbo].[table_3] COMMIT END TRY BEGIN CATCH IF @@TRANCOUNT > 0 BEGIN ROLLBACK TRANSACTION DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int SELECT @ErrMsg = ERROR_MESSAGE(), @ErrSeverity = ERROR_SEVERITY() RAISERROR(@ErrMsg, @ErrSeverity, 1) END END CATCH END