Transfer SQL Server database schema objects and data to another server or database with SMO

Transfer SQL Server database schema objects and data to another server or database with SMO Have you wondered how Database Copy Wizard works behind the scenes? Do you have a requirement to create a copy of your database (say copy of your production database for development or testing) programmatically? In this article, I am going…


Backup and Restore SQL Server databases programmatically with SMO

Backup and Restore SQL Server databases programmatically with SMO In this article I am going to provide examples to SQL Server Database Administrators on how to backup and restore SQL Server databases programmatically with SMO. I will start with how you can issue different types (Full, Differential and Log) of backups with SMO and how…


Generate SQL Scripts for database objects with SMO

Generate SQL Scripts for database objects with SMO In this article I take about how you can generate SQL object scripts programmatically. Though you can do this through SQL Server Management Studio (SSMS) there might be times (more details on usage scenarios given below) when you would need to create SQL scripts automatically. Click here…


Accessing SQL Server programmatically with SQL Server Management Objects (SMO)

Accessing SQL Server programmatically with SQL Server Management Objects (SMO) SQL Server 2005 and 2008 provide SQL Server Management Objects (SMO), a collection of namespaces which in turn contain different classes, interfaces, delegates and enumerations, to programmatically work with and manage a SQL Server instance. SMO extends and supersedes SQL Server Distributed Management Objects (SQL-DMO)…


Database Impersonation with EXEC AS in SQL Server

Database Impersonation with EXEC AS in SQL ServerSQL Server 2005/2008 provides the ability to change the execution/security context with the EXEC or EXECUTE AS clause. You can explicitly change the execution context by specifying a login or user name in an EXECUTE AS statement for batch execution or by specifying the EXECUTE AS clause in…


Executing dynamic SQL scripts on remote SQL Server with EXEC AT statement

Executing dynamic SQL scripts on remote SQL Server with EXEC AT statement With SQL Server 2000, we had OPENQUERY and OPENROWSET to execute a pass-through query on the specified server, but it has several inherent limitations. Starting with SQL Server 2005 we have another more elegant way using “EXEC AT” to execute a pass-through query…


Reorganize and Rebuild Index in SQL Server 2005 and 2008

Reorganize and Rebuild Index in SQL Server 2005 and 2008 Once you have identified the high fragmentation level in your database, which could be a bottleneck in your SQL Server performance, what is the next step of fixing this high fragmentation? In this article, I am going to discuss the different methods and its feasibility…


Identifying fragmentation level in SQL Server 2005 and 2008

Identifying fragmentation level in SQL Server 2005 and 2008 While indexes can speed up execution of queries several fold as they can make the querying process faster, there is overhead associated with them. They consume additional disk space and require additional time to update themselves whenever data is updated, deleted or appended in a table….


Spatial Data Types (GEOMETRY and GEOGRAPHY) in SQL Server 2008

Spatial Data Types (GEOMETRY and GEOGRAPHY) in SQL Server 2008 SQL Server 2008 provides support for geographical data through the inclusion of new spatial data types, which you can use to store and manipulate location-based information. These native data types come in the form of two new data types viz. GEOGRAPHY and GEOMETRY. These two…


FILESTREAM Data Type in SQL Server 2008

FILESTREAM Data Type in SQL Server 2008 The new SQL Server 2008 FILESTREAM data type enables SQL Server applications to store unstructured data, such as documents and images, on the file system with a pointer to the data in the database. This enables client applications to leverage the rich NTFS streaming APIs and performance of…