By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,806 Members | 1,787 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,806 IT Pros & Developers. It's quick & easy.

SQL Server 2005 Import/Export not copying stored procedures

P: n/a
Hi,

In SQL 2000 if I wanted to take a complete copy of another running sql
database all did was create a new database locally and right-click it
and select import and point to another database and click copy
everything (stored procedures as well) and it did it for. I can't seem
to find the same functionality in SQL 2005. You can copy tables and
views but not the whole database. Is there another way of doing this?
Our SQL database is hosted externaly and they recommend using the
import/export feature to do it. Does anyone know I can copy everything
(such stored procedures, data table relations...etc)

Tanks
MA.

Apr 23 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
(ma*******@gmail.com) writes:
In SQL 2000 if I wanted to take a complete copy of another running sql
database all did was create a new database locally and right-click it
and select import and point to another database and click copy
everything (stored procedures as well) and it did it for. I can't seem
to find the same functionality in SQL 2005. You can copy tables and
views but not the whole database. Is there another way of doing this?
Our SQL database is hosted externaly and they recommend using the
import/export feature to do it. Does anyone know I can copy everything
(such stored procedures, data table relations...etc)


You can try right-clicking the database and select Copy Database. Since
the source database appears to be located remotely you would have to use
the SMO method. Unfortunately, this method has plenty of problems, and I've
filed quite a few bug reports on it.

The best approach is to keep the source code for the database under
version control, so when you need a database, you build the database from
sources. To copy the data, you can easily generate BCP commands by
inquiring the system tables. For BCP out:

SELECT 'bcp somedb.' + schema_name(schema_id) + '.' + name +
' out ' + name + '.bcp -U user -P pw -S server -n'
FROM sys.tables

And BCP in:

SELECT 'bcp somedb.' + schema_name(schema_id) + '.' + name +
' in ' + name + '.bcp -T -S server -n ' +
CASE WHEN EXISTS (SELECT *
FROM sys.columns c
WHERE c.object_id = t.object_id
AND c.is_identity = 1)
THEN '-E'
ELSE ''
END
FROM sys.tables t
I've assumed there that you log into the remote host with SQL authentication
and use trusted connection at home.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Apr 23 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.