Backup & Restore only tables&stored procedures

[hortensiam]

Hi,
Does anybody tell me how can I copy from one server to a device only tables and stored procedures (no users..). Later, I want to restore on to another server.
thanks

 

[SQLBill]

Do you want just the table STRUCTURE? Or the data with the tables?

Stored Procedures, just script them off......
In Enterprise Manager, find the stored procedure, right click on it, select All tasks.... then select Generate SQL Script....

Same for the tables if you don't want the data with them.

-SQLBill

Posting advice: FAQ481-4875

 

[hortensiam]

I want copy table's structure, data and stored procedures
thanks,

 

[SQLBill]

The only way to copy a single table and it's data is to SELECT the data from the table INTO the new table.

You can not backup just a table and it's data. You have to backup the whole database.

-SQLBill

Posting advice: FAQ481-4875

 

[hortensiam]

My problem is not to pass the users from one server to another! How can I resolve this if I use BackUp and restore?

 

[mrdenny]

You'd have to delete the users after the restore, or use DTS to copy the objects accross.

When using backup and restore you can not specify what object you want to restore. You restore everything in the backup file.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[timscronin]

Be careful of your settings when using dts, when you import/export objects it can leave off indexes, fk, and identity columns. Thoroughly test your solution

 

[Catadmin]

The only time you can backup tables and restore just tables is if you somehow created files and/or filegroups that have only that tables worth of information on them. Then you can backup or restore those files/filegroups.

I've never done this myself, but I remember reading it was possible. Of course, if your DB isn't already designed that way, then you're SOL with Backup/restores. I would suggest scripting your individual table designs and your SPs. Then do either a Insert of all your table data from the other DB, or a Bulk Copy or even a DTS import.

Doing it this way should keep the users from being imported to the new DB.



Catadmin - MCDBA, MCSA
"Just because I'm paranoid doesn't mean the universe *isn't* out to get me!"

 

[AshleyRickards]

Right click on the db and select all tasks and generate sql script. select show all and select all tables and sp's. save it to a file create the new database. then open in query alalyzer and run it on the new db. you might need to tweak the script if it inlcudes db name etc ..

HTH

Ashley L Rickards
SQL DBA

 

[AshleyRickards]

also .. then backup the db and you can use it for a template or run the script on the Model db so when a new db is ceated you'll alway's have that structure.

Ashley L Rickards
SQL DBA