Copy just the Database Structure not the Data

I have been working on my windows app, using SQL Server 2005 (& C#) but I now have loads of junk data, so I want to copy the structure without any data to a new set of files that can become the production version.

I assume I can use backup and restore in future when I want a copy of my production data to use for future testing.

How would I take just one table from my test database and add it to my production database without retyping the design?

Apart from the size limitation is there any advantage in buying a version of SQL Server over using the Express edition?

hi,

GrahamY wrote:

I have been working on my windows app, using SQL Server 2005 (& C#) but I now have loads of junk data, so I want to copy the structure without any data to a new set of files that can become the production version.

you can script out the metadata using SQL Server Management Studio Express, but this still works for 1 object at the time...

or you can have a look at this toolkit for a more comprehensive generation "wizard"..

or, you can have a look at 2 free prjs of mines, amScript and amInsert that can produce DDL scripts as long as INSERT INTO scripts to populate existing tables...

then you can execute the DDL script to create a db "clone"....

I assume I can use backup and restore in future when I want a copy of my production data to use for future testing.

backup/restore feature can be handy, but not if you want to only have an "empty" database..

How would I take just one table from my test database and add it to my production database without retyping the design?

see above..

Apart from the size limitation is there any advantage in buying a version of SQL Server over using the Express edition?

the full blown editions offer other "features" missing in Express editions as the complete SSIS integration, BI tools and features, mirroring features, more cpu support, more ram support ... http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx

regards

Copy db - lose login mapping

Whenever I copy my production db back to my development server i lose the
user mapping for my app account and have to drop the db user, then remap the
login to the correct db. Then I have to reassign permissions to the user.
How can I prevent this from happening?
Thanks
Hi
http://dimantdatabasesolutions.blogspot.com/2007/04/sql-or-windows-authentication.html
"ken s" <kens@.discussions.microsoft.com> wrote in message
news:4B16A51F-FAD3-4D86-8D5D-1C0A03DBD660@.microsoft.com...
> Whenever I copy my production db back to my development server i lose the
> user mapping for my app account and have to drop the db user, then remap
> the
> login to the correct db. Then I have to reassign permissions to the user.
> How can I prevent this from happening?
> Thanks
>

Copy db - lose login mapping

Whenever I copy my production db back to my development server i lose the
user mapping for my app account and have to drop the db user, then remap the
login to the correct db. Then I have to reassign permissions to the user.
How can I prevent this from happening?
ThanksHi
http://dimantdatabasesolutions.blogspot.com/2007/04/sql-or-windows-authentication.html
"ken s" <kens@.discussions.microsoft.com> wrote in message
news:4B16A51F-FAD3-4D86-8D5D-1C0A03DBD660@.microsoft.com...
> Whenever I copy my production db back to my development server i lose the
> user mapping for my app account and have to drop the db user, then remap
> the
> login to the correct db. Then I have to reassign permissions to the user.
> How can I prevent this from happening?
> Thanks
>sqlsql

Copy db - lose login mapping

Whenever I copy my production db back to my development server i lose the
user mapping for my app account and have to drop the db user, then remap the
login to the correct db. Then I have to reassign permissions to the user.
How can I prevent this from happening?
ThanksThat's because the info to tie the user to the login is gone - the
users are orphaned. There is an sp you can use to fix orphaned users.
I am not where I can look it up but I have a SQL script that takes
care of mapping my users to the logins. I am assuming the logins and
users are the same on both systems. You can also check the
knowledgebase and look at the information on moving users and keeping
thier passwords, etc. when they move.
On Fri, 21 Dec 2007 15:05:00 -0800, ken s
<kens@.discussions.microsoft.com> wrote:

>Whenever I copy my production db back to my development server i lose the
>user mapping for my app account and have to drop the db user, then remap th
e
>login to the correct db. Then I have to reassign permissions to the user.
>How can I prevent this from happening?
>Thanks
>|||Hi
http://dimantdatabasesolutions.blog...on.
html
"ken s" <kens@.discussions.microsoft.com> wrote in message
news:4B16A51F-FAD3-4D86-8D5D-1C0A03DBD660@.microsoft.com...
> Whenever I copy my production db back to my development server i lose the
> user mapping for my app account and have to drop the db user, then remap
> the
> login to the correct db. Then I have to reassign permissions to the user.
> How can I prevent this from happening?
> Thanks
>

Converting text data to ntext data

Hi all,

My organization have a web-based application and needs it to support multilingual so we will be adapting our app to use unicode. However, one of our problems is to convert existing data from text to ntext. I couldn't find anything that document this. What is the best way to do that? I would like to be able to migrate the data from an existing text column to another ntext column in the table.

I brief you about my system, I used List manager system to store the messages and distribute to all members. Right now,by design the Lyris system keep the message in the text field which mean it 's not support multilanguage directly because of unicode field. We needs to create new Db which has the data structure as same as Lyris but just one difference is keep the message in unicode format (ntext) which we need the sql script to automatically update the new record get from Lyris to new DB.

If I can't do that, what are the options? If it's possible, I would like to be able to do this in sql script.

Thanks a mil, in advance.

Eddie

Moving to engine folder.|||

You should be able to accomplish this by using a command similar to the following:

ALTER TABLE table_name ALTER COLUMN column_name ntext

go

After that you should run DBCC CLEANTABLE command to reclaim space occupied by old column. Also you space requirement for the altered column will double (just plan for that).

Alternatively, you can bulk copy your data out, modify the table definition, truncate the table and bulk copy data back in.

HTH,

Boris.