Misunderstanding of backup

[Darryl]

I always assumed that the backup & restore procedure stored simply
data, not structure.

Now I find that if I add a new table to a database, then restore using
an old backup, the new table is gone.

Is there any way to restore JUST the data from a backup? If not, is
there any way to archive & import just data? The import/export wizard
only seems to send a single table to a flat file, but I need all the
tables.

Is there perhaps a command-line parameterthat isn't available in the
wizard that would accomplish this?

[David Portas]

You can restore to a different database or server and then copy over
the data.
http://msdn.microsoft.com/library/de...ackpc_6ng9.asp

--
David Portas
SQL Server MVP
--

[David Portas]

You can restore to a different database or server and then copy over
the data.
http://msdn.microsoft.com/library/de...ackpc_6ng9.asp

--
David Portas
SQL Server MVP
--

[Darryl]

David Portas wrote:

You can restore to a different database or server and then copy over
the data.
http://msdn.microsoft.com/library/de...ackpc_6ng9.asp

--
David Portas
SQL Server MVP
--

Must the data be copied using the MOVE command, or can I simply
physically relocate the ldf & mdf files to the target server?

[Darryl]

David Portas wrote:

You can restore to a different database or server and then copy over
the data.
http://msdn.microsoft.com/library/de...ackpc_6ng9.asp

--
David Portas
SQL Server MVP
--

Must the data be copied using the MOVE command, or can I simply
physically relocate the ldf & mdf files to the target server?

[David Portas]

You can always detach and reattach the files (sp_attach_db and sp_detach_db)
but as you'll have to restore them first you may as well restore them where
you want them to start with.

--
David Portas
SQL Server MVP
--

[David Portas]

You can always detach and reattach the files (sp_attach_db and sp_detach_db)
but as you'll have to restore them first you may as well restore them where
you want them to start with.

--
David Portas
SQL Server MVP
--

[Darryl]

David Portas wrote:

You can always detach and reattach the files (sp_attach_db and sp_detach_db)
but as you'll have to restore them first you may as well restore them where
you want them to start with.

--
David Portas
SQL Server MVP
--

Once the new temp database is created, what procedure would you
recommend for copying just the data between databases?

[Erland Sommarskog]

Darryl (Da*****@yahoo.com) writes:

David Portas wrote:

You can always detach and reattach the files (sp_attach_db and
sp_detach_db) but as you'll have to restore them first you may as well
restore them where you want them to start with.

Once the new temp database is created, what procedure would you
recommend for copying just the data between databases?

Depends on why, and how often etc. If you want to do it on a regular
basis, replication could be the way to go. For a one-off thing,
it depends on whether the target database already has data, and
what you want to do with it etc.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp