copy data structure to a new database

iccsi (inunghatgmaildotcom) writes:
> I would like to copy my database to a new location. Is it possible to
> use copy and paste like copy a mdb file or I have to generate scripts
> from my database and run the scripts against new database.

The best way to copy a database is through BACKUP/RESTORE.

First do:

BACKUP DATABASE db TO DISK = '' WITH INIT

Next run this: "sp_helpdb db". In the leftmost column you have the logical
names of the files, in the third colunm, you have the physical names. You
will need these for the next step.

RESTORE DATABSE dbcopy FROM DISK = ''
WITH MOVE 'logical1' TO 'physical1',
MOVE 'logical2' TO 'physical2',
REPLACE

Logical1 and Logical2 should be exactly the logical names you got from
sp_helpdb. Physical1 and Physical2 can be any paths. In all you want is
a copy of the database, you can copy the names from sp_helpdb, but change
them at the end, to match the name of the new database.



--
Erland Sommarskog, SQL Server MVP, esquelatsommarskogdotse

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

On Apr 21, 6:02*pm, Erland Sommarskog wrote:
> iccsi (inu...atgmaildotcom) writes:
> > I would like to copy my database to a new location. Is it possible to
> > use copy and paste like copy a mdb file or I have to generate scripts
> > from my database and run the scripts against new database.
>
> The best way to copy a database is through BACKUP/RESTORE.
>
> First do:
>
> * *BACKUP DATABASE db TO DISK = '' WITH INIT
>
> Next run this: "sp_helpdb db". In the leftmost column you have the logical *
> names of the files, in the third colunm, you have the physical names. You
> will need these for the next step.
>
> * *RESTORE DATABSE dbcopy FROM DISK = ''
> * *WITH MOVE 'logical1' TO 'physical1',
> * * * * MOVE 'logical2' TO 'physical2',
> * *REPLACE
>
> Logical1 and Logical2 should be exactly the logical names you got from
> sp_helpdb. Physical1 and Physical2 can be any paths. In all you want is
> a copy of the database, you can copy the names from sp_helpdb, but change
> them at the end, to match the name of the new database.
>
> --
> Erland Sommarskog, SQL Server MVP, esq...atsommarskogdotse
>
> Links for SQL Server Books Online:
> SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

Thanks for helping,
Does it copy data as well?
I would like to copy structure only, but not data.

Thanks again,

iccsi (inunghatgmaildotcom) writes:
> Does it copy data as well?
> I would like to copy structure only, but not data.

BACKUP/RESTORE operates on a low level, and essentially backs up database
indiscrimantly. This means that there is a high degree of certainty that
you get a faithful copy of the original database.

From this follows that if you want to copy only the schema, but not the
data, it is not the right tool.

There are two options:
1) BACKUP/RESTORE and cleanse all tables manually.
2) Use the scripting options in Enterprise Manager.

I would recommend the latter. As I recall, the scripting engine in
Enterprise Manager did a fairly good job. Right-click the database,
select All Tasks from the context menu and then Generate SQL scrtips.

Of course, the best way to is keep all your code and table definitions
under version control. In this case you don't need any script-generation
tool.


--
Erland Sommarskog, SQL Server MVP, esquelatsommarskogdotse

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

Hello Guys,
This could also be easily accomplished by detaching and copying to .*df files
(renaming them of course) and then reattaching with a new db name...

---
Regards,
NumbLock

--------
There are only 10 kinds of people in the world. Those who understand binary
and those who don't
--------

> iccsi (inunghatgmaildotcom) writes:
>
>> Does it copy data as well?
>> I would like to copy structure only, but not data.
> BACKUP/RESTORE operates on a low level, and essentially backs up
> database indiscrimantly. This means that there is a high degree of
> certainty that you get a faithful copy of the original database.
>
> From this follows that if you want to copy only the schema, but not
> the data, it is not the right tool.
>
> There are two options:
> 1) BACKUP/RESTORE and cleanse all tables manually.
> 2) Use the scripting options in Enterprise Manager.
> I would recommend the latter. As I recall, the scripting engine in
> Enterprise Manager did a fairly good job. Right-click the database,
> select All Tasks from the context menu and then Generate SQL scrtips.
>
> Of course, the best way to is keep all your code and table definitions
> under version control. In this case you don't need any
> script-generation tool.
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

NumbLock (galenathendersonsystemsincdotcom) writes:
> This could also be easily accomplished by detaching and copying to .*df
> files (renaming them of course) and then reattaching with a new db
> name...

That's an alternative to BACKUP/RESTORE when you want an exact copy.
I recommend using BACKUP/RESTORE over detach/attach. BACKUP/RESTORE
is simpler, with fewer files to keep track of. And the database is
never taken offline.

In this case Iccsi wanted only to copy the schema, why scripting is
a better method.


--
Erland Sommarskog, SQL Server MVP, esquelatsommarskogdotse

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

Hello Erland,
Gotcha. Should've read the post a bit more carefully. Cheerio.

---
Regards,
NumbLock

--------
There are only 10 kinds of people in the world. Those who understand binary
and those who don't
--------

> NumbLock (galenathendersonsystemsincdotcom) writes:
>
>> This could also be easily accomplished by detaching and copying to
>> .*df files (renaming them of course) and then reattaching with a new
>> db name...
>>
> That's an alternative to BACKUP/RESTORE when you want an exact copy. I
> recommend using BACKUP/RESTORE over detach/attach. BACKUP/RESTORE is
> simpler, with fewer files to keep track of. And the database is never
> taken offline.
>
> In this case Iccsi wanted only to copy the schema, why scripting is a
> better method.
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx