This post explains taking the SQL Server Database Objects backup only schema not data in SQL Server 2008 R2. In an ETL server you may come across the situation where you need to change the database objects schema quite frequently especially when data source changes. In this case we may need maintain our schema or take the backup our database objects script some place where we can restore them back to the server.
What is SMO?
This can be achieved in earlier versions of SQL Server using Distributed Management Objects (DMO) but now it is no longer supported in SQL Server 2008 R2 and future versions. We can achieve the same using SQL Server Management Objects (SMO) in 2008 R2. SMO provides objects which are used to manage SQL Server.
SMO has different namespaces, Server is the base object in SMO. SMO object model is based on managed code and all are .NET assemblies.
One of the advantage of SMO over DMO is objects are loaded only when you specifically refer in your code so it improves your application performance while interacting server.
Open Visual Studio 2010 and create a new project of type console application, depending on your requirement you can choose your project types. you need to refer the following assemblies in your project to use SMO
You can find these assemblies in the following path C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\ folder
Connecting to the SQL server using Windows Authentication
write the following if it is SQL Server Authentication
Read the databases, tables, SPâ€™s and views by enumerating through the database collection and write the table schema to a file on disk
If you want to include the data as well in the script then write
options.ScriptData = true;
You can write the similar code to backup the views and functions schema as well
|Share this post :|