database back up

[oneshadow]

Does anyone know how to make a back up data (eg. pubs database) and I mean only its data, not including users or roles.

I'm using SQL Server 7.0 SP2.

Thanks in advance.

 

[foxdev]

Onto what media would you like to make the backup?

Have you explored the wealth of information on this topic in the Books Online (BOL) available from your SQL Server program group?

Robert Bradley

http://www.foxdev.com/

 

[TomSark]

You could use BCP to extract the data from the table into files, and do the reverse to populate some tables from those file. But since, by definition, a database is comprised of objects including tables, users, etc. you cannot simply backup only selected objects of the database.

Hope this helps...

Tom

 

[foxdev]

Or, in its simplest form, you could just copy the .MDF file.

It would help if we knew the goal of this backup, the environment, the size, the media, etc.

Robert Bradley

http://www.foxdev.com/

 

[oneshadow]

Thanks for the answer.

I have 2 servers : A and B.
A is operational server, B is testing server.

I created a user 'X' in Northwind database in server A.
I made a back up and then restore it in server B.
I find out that 'X' is automatically created in server B and all original users in Northwind server B were replaced.

foxdev : I've tried to copy the MDF file but it coz the database is mark unaccessible by SQL Server :-(

TomSark : perhaps it's true that we can't simply backup only selected object of the database.

Thanks again

 

[TomSark]

If it's users that you are concerned with, I would suggest that you maintain scripts that create and drops the logins and the database accounts on the servers you are interested in and run them appropriately after the restore occurs.

Tom