(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 
