sql database integrity: environments

creating test SQL Server environments

Hi,

I was wondering if anyone has a neat (preferably automated) method of creating small testing databases from large production instances.
My requirement would be to copy the schema and a subset of configuration data from a production database into a test database. The subset of data would be a full copy of a subset of tables, rather than a subset of data within one or more tables. There is a mixture of SQL2000 and SQL2005 servers involved in this requirement. I'm familar with the scripting mechanisms of Enterprise Manager and Management studio and DTS packages, sufficent to perform a process like this manually, but want to productionise and schedule this process to be performed automatically.
I'm sure this must be a commonly performed task, so I'm interested to know if anyone has a "best practice" for this requirement.

Thanks,

BillI've always used backup/restore for this. of course that doesn't give you a subset of anything.

for me the issue with taking a subset would be: if in test you don't have all the data that prod is working with, how do you really know your stuff will work in prod?|||I've always used backup/restore for this. of course that doesn't give you a subset of anything.

for me the issue with taking a subset would be: if in test you don't have all the data that prod is working with, how do you really know your stuff will work in prod?

Thanks Jezemine, The problem with backup restore, is that my production database is 3Tb+. However nearly all of this is historic transactional data. A small subset (1 or 2 Gb) is the configuration data that dictates how any transactions I post into the database will process. So it is this data (which is as important as source code wrt to the behaviour of the system) that I want to replicate to any test environment.|||This won't help much at this point, but I frequently set up file groups in Microsoft SQL databases for just this purpose. The filegroups can be backed up individually, so it is easy to "pick and choose" at the table level what data you want.

If you are doing this "after the fact" where you already have a 3Tb database, my first thought would be to script the schema of the whole tamale (all tables) and play that script into an empty database container. Then I'd pick one of (linked server, DTS, BCP, copy wizard) to move the data from the production database to the container with only the schema in it.

-PatPsql

Creating tables with SQL Scripts (SOLVED)

In sql server 2003 I would create tables with generated sql scripts.
This way I could easily create duplicate dev environments.

Now in SQL 2005 Dev edition I try to run my scripts to create tables.
I create a db [MSS].
They run successfully but there is no table created.

(example script)USE [MSS]
GO
/****** Object: Table [dbo].[KataNames] Script Date: 08/17/2006 13:28:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[KataNames](
[Kata] [char](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[SequenceId] [int] IDENTITY(1,1) NOT NULL,
[Description] [char](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Style] [char](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

Any clue what else I have to do to let these scripts actually create tables or why if they run ok, where is my table ?

Thanks

Kim AllbritainDid you 'Refresh' the table list in SSMS?|||Right click your "Databases" node and choose "Refresh"|||Of course I refreshed the db list. I even restarted the app and looked again. No table, but successful completion of the script every time.

Is it possible to back up a sql 200 db on one machine and restore it to 2005 sql on another?|||

KAllbritain wrote:
Of course I refreshed the db list. I even
restarted the app and looked again. No table, but successful completion
of the script every time.
Is it possible to back up a sql 200 db on one machine and restore it to 2005 sql on another?

There isnt much point saying "Of course I did" we diddnt know that you did "yes I tried that" would have been much nicer

Ok, are you using an application to send these scripts? Like Access or .NET

If so, the application or the user account you are using to fire the scripts may not have the appropriate permissions to do so.

Alternatively, do you mean you fired the script in the Query Analayzer of SQL Server 2005 and still do not see the tables?

Do you have serveral SQL Servers on your network?

AFAIK there is no SQL Server 2003, and with regards to porting a 2000 to 2005 yes its possible, there is some documentation here; http://www.microsoft.com/technet/prodtechnol/sql/themes/default.mspx

take it slow, let us fully understand your situation so that we can resolve it = )|||I appreciate your response.

It was sql2000 dev version using 2003 .Net

on sql2005 dev:

The scripts are loaded into the query analyzer and run from there
while connected to the proper db on my local machine.
There are no other servers presently connected.

The script indicates the proper db as well so How can it report
successful without actually performing the create table?|||

If the purpose of the script is to CREATE an object, it is not successful until after it creates that object. If you want to verify syntax, you could, in SSMS, click on the green 'checkmark' icon on the toolbar, or [Query], [Parse] on the menu, or {ctrl}{F5}.

If you want to verify that it actually works, you could wrap it in a TRANSACTION and ROLLBACK when completed.

Or run it in a 'Test' database first.

|||Thanks

The scripts purpose is create a table. It works in sql2000 fine.
The message provided after executing the script is that it was "Successful", therefore I would expect the object (table) to be created.

I am cloning a small db in a new environment, change from 2000 to 2005.
The syntax is fine.

My problem is that if the syntax is fine, the script works in sql2000,
and SSMS says it worked, why doesn't it?

What else can I do?

I could build the tables by hand but why?|||OK I solved this. Just in case anyone else reads this thread.

The table was created by my scripts alright, just not in the db I thought I was in.

I was looking at db "Library" and it was highlighted, but when I opened the Query screen and connected it defaulted to the (Master)
db, so that's where The tables were.

Would be nice if the IDE for SQl2005 would default to the
db you were actually connected and working on instead of
re-connecting to something else.

But it worked ok.

Bye

Creating tables with SQL Scripts