How to export a database and import it locally using C # code?
How to "clone" a database from a remote server to a LocalDB database using a C # application? No connections to the remote database are required.
Background
The 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 the database LocalDB
. The app primarily targets newer server versions (if it matters, only 2014 version support is fine).
Before the user goes offline, he must ask the application to clone the remote database into the database LocalDB
(the local database has been completely overwritten). The local database must be independent of the remote database, that is, no slave or replication.
Both the online string and the offline string contain the name of the corresponding database. The application itself does not have direct knowledge of the database name or table names as this is controlled by connection strings and the Entity Framework.
Question
How do I "clone" a remote database into a database LocalDB
(remote database name and database name LocalDB
may be different)?
I prefer a solution that does not require an external program to run, but this is not a hard requirement.
Questions
Copying through the Entity Framework does not track that trackers are invalid.
I know the commands BACKUP DATABASE
and RESTORE DATABASE
, but I found the following difficulties:
-
They require me to provide the name of the database. Is there a default way to use the original database specified as part of the connection string?
-
The command
RESTORE DATABASE
contains the names and paths of the corresponding data files on disk (MOVE
parts). Is there a way to handle it by specifying the database name without specifying the path to the data files? Or how do I get the paths to the data files using SQL commands (to get the filenames, I just create an empty database, get the filenames, optionally drop the database, and use the resulting filenames)?
Is there a better way to do this?
source to share
I am using the created stored procedure, 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 a stored procedure on your local server or execute the query directly from the application, and also for security. I am using 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
source to share