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….


SQL Server – Max Degree of Parallelism (MAXDOP)

When SQL Server runs on a computer with more than one microprocessor/CPU or on SMP (a computer-architecture where two or more identical processors can connect to a single shared main memory and I/O and can perform the same functions. In case of multi-core processors, the SMP architecture applies to the cores, treating them as separate…