Sample Header Ad - 728x90

Is there any possibility to use SMO objects to copy a database from MS SQL Server 2014 Express (server address) to localhost?

-1 votes
1 answer
594 views
I need to implement in c# a functionality for copying a certain database from server address to localhost (I'm aware of the backup/restore option - I have this implemented when I want to export and import databases that are stored locally). I've tried the following source code but when I tried to run the transferData I get an error "The Integration Services component is not installed or you do not have permission to use it.". I'm using SQL Express 2014.I have installed also SharedManagementObjects and SQLSysClrTypes.msi. I've also noticed that in my c# solution I have some references to Smo assemblies like NuGet installation but they are for SQL Server 2008. Locally I can find some smo assemblies in C:\Program Files (x86)\Microsoft SQL Server\120\SDK\Assemblies or C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies. Should I reference them in my project? I've tried the following code, but I'm getting exception in transferDatabase.TransferData();: Server sourceServer = new Server(sourceServerName); Server destinationServer = new Server(destinationServerName); try { sourceServer.ConnectionContext.LoginSecure = true; sourceServer.ConnectionContext.Connect(); destinationServer.ConnectionContext.LoginSecure = true; destinationServer.ConnectionContext.Connect(); Microsoft.SqlServer.Management.Smo.Database databaseSource = sourceServer.Databases[databaseName]; Microsoft.SqlServer.Management.Smo.Database databaseDestination = new Microsoft.SqlServer.Management.Smo.Database(destinationServer, databaseName); databaseDestination.Create(); Transfer transferDatabase = new Transfer(databaseSource) { CopyAllObjects = false, CopyAllSchemas = true, //Copy all user defined data types from source to destination CopyAllUserDefinedDataTypes = true, //Copy all tables from source to destination CopyAllTables = true }; //Copy all constraints transferDatabase.Options.DriAllKeys = true; //Copy all defaults transferDatabase.Options.DriDefaults = true; //Drops the existing tables transferDatabase.DropDestinationObjectsFirst = true; //Copy data of all source tables to destination tables //It actually generates INSERT statement for destination transferDatabase.CopyData = true; //Copy all stored procedure from source to destination transferDatabase.CopyAllStoredProcedures = true; //specify the destination server name transferDatabase.DestinationServer = destinationServer.Name; //specify the destination database name transferDatabase.DestinationDatabase = databaseDestination.Name; //TransferData method transfers the schema objects and data //whatever you have specified to destination database transferDatabase.TemporaryPackageDirectory = @"C:\Data\"; transferDatabase.Options.ContinueScriptingOnError = true; transferDatabase.TransferData(); } catch (Exception ex) { log.Error(ex.Message); } finally { if (sourceServer.ConnectionContext.IsOpen) { sourceServer.ConnectionContext.Disconnect(); } if (destinationServer.ConnectionContext.IsOpen) { destinationServer.ConnectionContext.Disconnect(); } }
Asked by Elena2020 (71 rep)
Jan 25, 2021, 06:11 PM
Last activity: Jan 25, 2021, 08:26 PM