|
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
Microsoft.SqlServer.ConnectionInfo.dll
Microsoft.SqlServer.Smo.dll
Microsoft.SqlServer.SmoEnum.dll
Microsoft.SqlServer.SqlEnum.dll
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
1: Server strSqlServer = new Server(@"kalyan-pc");
2: //Using windows authentication
3: strSqlServer.ConnectionContext.LoginSecure = true;
4: strSqlServer.ConnectionContext.Connect();
write the following if it is SQL Server Authentication
1: strSqlServer.ConnectionContext.LoginSecure = false;
2: strSqlServer.ConnectionContext.Login = "yourusername";
3: strSqlServer.ConnectionContext.Password = "yourpassword";
Read the databases, tables, SP’s and views by enumerating through the database collection and write the table schema to a file on disk
1: foreach (Database strDatabase in strSqlServer.Databases)
2: {
3:
4: foreach (Table strTable in strDatabase.Tables)
5: {
6: ScriptingOptions options = new ScriptingOptions();
7: options.IncludeIfNotExists = true;
8: options.NoCollation = true;
9: StringCollection strSchema = strTable.Script(options);
10: // Write file content to disk
11: string fileName = string.Format("{0}.sql", strTable.Name);
12: File.WriteAllText(Path.Combine(@"C:\Backup", fileName),
13: strSchema.ToString());
14: }
15: }
If you want to include the data as well in the script then write
options.ScriptData = true;
1: // Loop stored procedures
2: foreach (StoredProcedure storedProcedure in strDatabase.StoredProcedures)
3: {
4: // Exclude system stored procedures
5: if (!storedProcedure.IsSystemObject)
6: {
7: StringCollection strSchema = storedProcedure.Script();
8:
9: // Write file content to disk
10: string fileName = string.Format("{0}.sql", storedProcedure.Name);
11: File.WriteAllText(Path.Combine(@"C:\Backup", fileName),
12: strSchema.ToString());
13:
14: }
15: }
You can write the similar code to backup the views and functions schema as well
1: // Loop views
2: foreach (View view in strDatabase.Views)
3: {
4: // Exclude system stored procedures
5: if (!view.IsSystemObject)
6: {
7: StringCollection strSchema = view.Script();
8:
9: // Write file content to disk
10: string fileName = string.Format("{0}.sql", view.Name);
11: File.WriteAllText(Path.Combine(@"C:\Backup", fileName),
12: strSchema.ToString());
13:
14: }
15: }
| Share this post : |  |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
Nifty tip! Thanks, Kalyan.