How to export database and import it locally by C# code?

Question

How to export database and import it locally by C# code?

c# sqlbulkcopy sql-server

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 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?

alik

View more on Stack Overflow

Dn24Z · Accepted Answer · 2/13/2015 8:27:33 PM

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

How to export database and import it locally by C# code?

Question

Fastest Entity Framework Extensions

Popular Answer

Performance

Expression Evaluator