|
|
Posted by Erland Sommarskog on 04/23/06 13:57
(maflatoun@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, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|