I have used SMO objects to generate SQL Script for database objecrs for SQL Server. This works well when the database is in the local network. But it takes a lot of time if database is in a remote server. What would be the best and fastest way of generating scripts for SQL Server objects when database is in remote server.
Add a comment
|
2 Answers
I would use SQL Server Management Studio
From the Database item right click and select tasks, generate scripts. Many options there for you to enjoy.
(You can even have it script data in the tables.)
1 Comment
Anshuman Roy
I have to do this programmatically using c#.
You can try precaching required objects to improve performance with PrefetchObjects method:
var server = new Server(new ServerConnection(connectionString));
var database = server.Databases[databaseName];
database.PrefetchObjects(typeof(Table));
If you're trying to implement anything related to database schema migration, check out Wizardby.
3 Comments
Anshuman Roy
I tried this: Server server = new Server(con); var database = server.Databases[vstrDatabase]; database.PrefetchObjects(typeof(Table)); foreach(Table tbl in database.Tables) { sbr = new StringBuilder(); StringCollection script = tbl.Script(); foreach (string str in script) { } } tbl.Script takes plenty of time for remote server, so the whole process is taking a long time. I need to create scripts for all object. So what would be the best way.
springy76
The help for PrefetchObjects states This enables collections to be fully populated with objects by making one network trip to the instance of SQL Server. -- have you ever watched this one roundtrip using Profiler? INSANE!
springy76
It generates thousands of roundtrip pairs
USE [YourDbName] + EXEC something -- this still takes ages against a server connected via internet. I today tried database.PrefetchObjects(typeof(Statistic)) against a server inside the LAN and it did not come to an end within minutes.