Question
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.
Background
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.
Question
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.
Issues
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?
The
RESTORE DATABASEcommand contains names and paths of the respective data files on the disc (MOVEparts). 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?
Popular Answer
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