Drop and Recreate all objects except tables

Could someone advise me on the best way to drop and recreate all objects in a database except tables (considering I must drop and create them in the correct order of dependency). More specifically, I would like to drop and recreate:

Indexes

Defaults

FK Constraints

Views

Procs

UDF

Check Constraints

Must I loop through every object in the database or is there any way to do this en masse?

Thanks.

I am currently working on creating a SSIS package that achieves this. I am using a ForEach Loop container (which contains a script task and Execute SQL task) for my tables, view, stored procedures and functions. The loop container uses a ForEach SMO enumerator. In my case I wanted to transfer objects from a different database so I added a Transfer SQL objects task to transfer objects. So far this is working pretty well.

I am having one issue though and that is with dropping my defaults. I haven't figured out how to get my SMO enumerator to pick up my defaults. If anyone can shed some light on how to configure this option I would appreciate it.

Thanks.

|||

Take a look at the Transfer object that allows you to create compound scripts in dependency order.

A little more elaborate, but you can also use the Scripter.

See also http://blogs.msdn.com/mwories/archive/2005/05/07/basic-scripting.aspx for a primer on scripting.

|||

Does anyone have any samples that show looping through every table in the database, then for each table dropping any foreign key constraints and indexes, then dropping the table itself?

Thanks.

Drop and Recreate all objects except tables

Could someone advise me on the best way to drop and recreate all objects in a database except tables (considering I must drop and create them in the correct order of dependency). More specifically, I would like to drop and recreate:

Indexes Defaults FK Constraints Views Procs UDF Check Constraints

Must I loop through every object in the database or is there any way to do this en masse?

Thanks.

I am currently working on creating a SSIS package that achieves this. I am using a ForEach Loop container (which contains a script task and Execute SQL task) for my tables, view, stored procedures and functions. The loop container uses a ForEach SMO enumerator. In my case I wanted to transfer objects from a different database so I added a Transfer SQL objects task to transfer objects. So far this is working pretty well.

I am having one issue though and that is with dropping my defaults. I haven't figured out how to get my SMO enumerator to pick up my defaults. If anyone can shed some light on how to configure this option I would appreciate it.

Thanks.

|||

Take a look at the Transfer object that allows you to create compound scripts in dependency order.

A little more elaborate, but you can also use the Scripter.

See also http://blogs.msdn.com/mwories/archive/2005/05/07/basic-scripting.aspx for a primer on scripting.

|||

Does anyone have any samples that show looping through every table in the database, then for each table dropping any foreign key constraints and indexes, then dropping the table itself?

Thanks.

Drop and Recreate all objects except tables

Could someone advise me on the best way to drop and recreate all objects in a database except tables (considering I must drop and create them in the correct order of dependency). More specifically, I would like to drop and recreate:

Indexes Defaults FK Constraints Views Procs UDF Check Constraints

Must I loop through every object in the database or is there any way to do this en masse?

Thanks.

I am currently working on creating a SSIS package that achieves this. I am using a ForEach Loop container (which contains a script task and Execute SQL task) for my tables, view, stored procedures and functions. The loop container uses a ForEach SMO enumerator. In my case I wanted to transfer objects from a different database so I added a Transfer SQL objects task to transfer objects. So far this is working pretty well.

I am having one issue though and that is with dropping my defaults. I haven't figured out how to get my SMO enumerator to pick up my defaults. If anyone can shed some light on how to configure this option I would appreciate it.

Thanks.

|||

Take a look at the Transfer object that allows you to create compound scripts in dependency order.

A little more elaborate, but you can also use the Scripter.

See also http://blogs.msdn.com/mwories/archive/2005/05/07/basic-scripting.aspx for a primer on scripting.

|||

Does anyone have any samples that show looping through every table in the database, then for each table dropping any foreign key constraints and indexes, then dropping the table itself?

Thanks.

|||You can use SMO for dropping and recreating the Database objects.
|||Looping to all databases in the Server
public Database SqlPackageAdminServer(string ConnectionString, string sDatabase)
{

Server oServer = GetServer(ConnectionString);
Database oDatabase = oServer.Databases[sDatabase];
return oDatabase;
}

private Server GetServer(string ConnectionString)
{
SqlConnection oSqlConnection = new SqlConnection(ConnectionString);
oSqlConnection.Open();
ServerConnection oServerConnection = new ServerConnection(oSqlConnection);
Server oServer = new Server(oServerConnection);
return oServer;
}

public DataTable GetAllDatabases(Server oServer)
{
DataTable oDataTable = new DataTable("Databases");
foreach (Database oDatabase in oServer.Databases)
{
if (oDatabase.IsSystemObject != true)
{
AddDataToDataTable(oDatabase.Name, oDataTable);
}

}
return oDataTable;
}

Looping to all the tables in the database
foreach (Table oTable in DataBase.Tables)
{
/// oTable.Name is the name of the table
}

to use this code you should include
using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Server;
using System.Data.SqlClient;
using Microsoft.SqlServer.Management.Common;
using System.Data;

Drives on Server and How to Define Raid for them

We are looking for advise. I know that there is no right or wrong answer. "It
depends."
We currently have a server with the OS as Windows Server 2003 SP2 and SQL
Server 2000 SP3. This Server has 6 physical drives; only 3 of these physical
drives are being used. These 3 physical drives are 1 container with Raid 5.
This 1 container is divided into 3 logical drives.
We would like to fill the other 3 physical drive slots and create another
container. We were thinking of making this Raid 1.
I should put in my disclaimer that Raid 10 is out of the question and so is
SAN. The company just doesn't have the money.
Is Raid 1 the best choice? This is my first question.
Next is how should we split up the files among the containers.
For example, OS, log and swap file on container 1 with Raid 1 and datafiles
on container 2 with Raid 5?
What are most people doing? Is there a standard? Can people provide examples
of what they are doing or provide suggestions?
Is there a microsoft recommendation for a windows server 2003/sql server
2000 with 6 physical drives on how the drives and raid should be set up? Is
there a cook book recipe? My management wants facts. They want certified
microsoft documentation on this subject.
I was thinking what if some type of external drive could be attached. The
first container of 3 physical drives could be Raid5 and the second container
of 3 physical drives could be Raid 1 and then the external drive could be
Raid 0 for tempdb. Maybe this is a bad idea?If you can completely redo the server from the ground up I would do consider
either a) raid1 set for OS/apps/pagefile and a raid10 set for all sql server
data and log files (with perhaps tempdb on the raid 1 or b) raid1 same as
above but make 4 remaining drives a raid5 set if you need the space or if
read performance is key.
if you can redo from scratch then if you want all 3 new drives involved in
raid to prevent single-disk-failure issue then you are pretty much stuck
with raid5 and it's inherent write performance issue. You could go with 2
disk raid1 and use the third drive alone as a backup drive. That sounds
pretty good.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"lcerni" <lcerni@.discussions.microsoft.com> wrote in message
news:410EFCF8-8D93-4722-A689-CAE1BF237A9B@.microsoft.com...
> We are looking for advise. I know that there is no right or wrong answer.
> "It
> depends."
> We currently have a server with the OS as Windows Server 2003 SP2 and SQL
> Server 2000 SP3. This Server has 6 physical drives; only 3 of these
> physical
> drives are being used. These 3 physical drives are 1 container with Raid
> 5.
> This 1 container is divided into 3 logical drives.
> We would like to fill the other 3 physical drive slots and create another
> container. We were thinking of making this Raid 1.
> I should put in my disclaimer that Raid 10 is out of the question and so
> is
> SAN. The company just doesn't have the money.
> Is Raid 1 the best choice? This is my first question.
> Next is how should we split up the files among the containers.
> For example, OS, log and swap file on container 1 with Raid 1 and
> datafiles
> on container 2 with Raid 5?
> What are most people doing? Is there a standard? Can people provide
> examples
> of what they are doing or provide suggestions?
> Is there a microsoft recommendation for a windows server 2003/sql server
> 2000 with 6 physical drives on how the drives and raid should be set up?
> Is
> there a cook book recipe? My management wants facts. They want certified
> microsoft documentation on this subject.
> I was thinking what if some type of external drive could be attached. The
> first container of 3 physical drives could be Raid5 and the second
> container
> of 3 physical drives could be Raid 1 and then the external drive could be
> Raid 0 for tempdb. Maybe this is a bad idea?|||There is no way to document what you should have because there are too many
factors to be considered. Mainly it comes down to how you use your database,
how the server overall is configured and what your transactions rates will
be. If you have a very low volume server then having it all on one Raid 5
may be fine. But as you get into higher transactions rates or more data
manipulation you will eventually need to split the logs onto another
physical array. Adding a Raid 1 for the OS /Swap file / Logs is probably
fine if this is not a large system and if you have enough memory to avoid
lots of direct disk access. But I don't recommend creating multiple logical
drives on either the Raid 5 or the Raid 1. That would do nothing for
performance and leave you with the possibility you may run out of room on
any one of the logical drives. One last comment. You said no Raid 10 but
you will have 1 slot left if you do 3 Disk Raid 5 and 2 disk Raid 1. You can
make a 4 disk Raid 10 and a 2 disk Raid 1. Or you can add the other drive to
the Raid 5 which would give better performance over the 3 disk Raid 5.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"lcerni" <lcerni@.discussions.microsoft.com> wrote in message
news:410EFCF8-8D93-4722-A689-CAE1BF237A9B@.microsoft.com...
> We are looking for advise. I know that there is no right or wrong answer.
> "It
> depends."
> We currently have a server with the OS as Windows Server 2003 SP2 and SQL
> Server 2000 SP3. This Server has 6 physical drives; only 3 of these
> physical
> drives are being used. These 3 physical drives are 1 container with Raid
> 5.
> This 1 container is divided into 3 logical drives.
> We would like to fill the other 3 physical drive slots and create another
> container. We were thinking of making this Raid 1.
> I should put in my disclaimer that Raid 10 is out of the question and so
> is
> SAN. The company just doesn't have the money.
> Is Raid 1 the best choice? This is my first question.
> Next is how should we split up the files among the containers.
> For example, OS, log and swap file on container 1 with Raid 1 and
> datafiles
> on container 2 with Raid 5?
> What are most people doing? Is there a standard? Can people provide
> examples
> of what they are doing or provide suggestions?
> Is there a microsoft recommendation for a windows server 2003/sql server
> 2000 with 6 physical drives on how the drives and raid should be set up?
> Is
> there a cook book recipe? My management wants facts. They want certified
> microsoft documentation on this subject.
> I was thinking what if some type of external drive could be attached. The
> first container of 3 physical drives could be Raid5 and the second
> container
> of 3 physical drives could be Raid 1 and then the external drive could be
> Raid 0 for tempdb. Maybe this is a bad idea?