0

Is it possible to move schema and data from SQL Server database to another (residing in different physical machines). The option should be supported for SQL Server 2005 onwards.

I found out a few COM/.NET assemblies which can do some of those but we would be supporting a Java centric solution.

So is there any system stored procedure etc which can accomplish the task ?

praveen
  • 231
  • 2
  • 11
  • What do you mean 'move' the schema and data? What network / OS / permissions limitations do you have? Can you just backup the database, copy the file across and and restore it on the other server? Can you use MSSQL replication? Can you write .NET command-line tools and call them from Java? – Pondlife Feb 10 '12 at 12:24
  • Nope we cannot write any .NET specific code. We are under the constraint that only Java/SQL Centric solutions are acceptable. We can assume that no limitation is there on us and we can try backing up and restoring a database but is there any way to do it automatically. – praveen Feb 13 '12 at 08:11
  • and by move schema and data - i mean replicate the entire schema like tables and procedures and triggers. And for selective tables move the data present in the table as well. – praveen Feb 13 '12 at 08:14

2 Answers2

2

Thanks for the extra details. If you can only use Java and TSQL, then your options are somewhat limited because the APIs for working with MSSQL are all in .NET and most third-party tools are also .NET-based.

You haven't mentioned which OS you want to run your code on, what your goal is (deployment? high availability? reporting?) or how often you need to do this (once? on demand? scheduled?) but assuming it's Windows and that you can use the MSSQL command-line tools (sqlcmd.exe, bcp.exe etc.) then here are some possibilities:

  • BACKUP/RESTORE - this copies the entire database, so you can't manage individual objects and of course if you overwrite the target database then you lose all the existing data
  • Replication - copies data and objects between servers, but can be complex to set up and manage
  • Use Integration Services to create packages that copy data and objects between servers
  • Use a third-party tool with command-line support and drive it from Java
  • Write your own programs to get SQL scripts from source control and execute them against the correct databases using sqlcmd.exe; you can use bcp.exe to export and import data from specific tables

Unfortunately the Java-only restriction means that you can't use SMO, which provides a full API for managing MSSQL objects, including generating DDL for existing objects. And Visual Studio database projects are presumably also not possible, which means you cannot use VSDB deployment features.

Pondlife
  • 15,992
  • 6
  • 37
  • 51
-1

You should be able to make calls to the SQL Server Management Objects (SMO) from Java the same as from any other programming language. The documentation is here. Look through the example code. There are Java examples in there.

Grant Fritchey
  • 2,645
  • 19
  • 21
  • 1
    Where are the Java examples? Java is not a CLR language and cannot access SMO directly. As the documentation you linked to says, "SMO is a .NET assembly. To program by using the Microsoft .NET Framework, you must choose a language that is supported by the common language runtime. In the SMO reference, there is syntax for managed languages such as Visual Basic .NET and Visual C# .NET." See also this question: http://stackoverflow.com/questions/283679/how-can-i-call-net-code-from-java – Pondlife Feb 10 '12 at 13:28
  • Could be wrong, but I've seen Java coding in the Community Content before. – Grant Fritchey Feb 10 '12 at 14:53