Copy database - same server

Hi;
I want to copy a database on my server, creating another database (different
name) on the same server copying everything - tables, data, diagrams, stored
procedures - everything. (I am making a test DB that I can use for my unit
tests.)
What's the easiest way to do this? I tried backup/restore but it wants the
database name to stay the same.
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.comBackup and restore work just fine, I do this all the time. Just specify the
desired database name in
the RESTORE command and use the MOVE option to specify the names of the data
base files to be created
for you.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:BF2CE48C-EB00-4BCC-B9E5-71D9491445C3@.microsoft.com...
> Hi;
> I want to copy a database on my server, creating another database (differe
nt
> name) on the same server copying everything - tables, data, diagrams, stor
ed
> procedures - everything. (I am making a test DB that I can use for my unit
> tests.)
> What's the easiest way to do this? I tried backup/restore but it wants the
> database name to stay the same.
> --
> thanks - dave
> david_at_windward_dot_net
> http://www.windwardreports.com
>|||Try the following:
Assume you have to copy pubs to pubs_2
'create database pubs_2' for the first time.
then,
create a job with following steps:
backup database pubs to disk = 'c:\pubs.bak'
go
restore database pubs_2 from disk ='c:\pubs.bak' with replace,
move 'pubs' to 'C:\Program Files\Microsoft SQL
Server\MSSQL\data\pubs_2.mdf',
move 'pubs_log' to 'C:\Program Files\Microsoft SQL
Server\MSSQL\data\pubs_2_log.ldf'
Thanks
Veera
Tibor Karaszi wrote:
[vbcol=seagreen]
> Backup and restore work just fine, I do this all the time. Just specify th
e desired database name in
> the RESTORE command and use the MOVE option to specify the names of the da
tabase files to be created
> for you.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "David Thielen" <thielen@.nospam.nospam> wrote in message
> news:BF2CE48C-EB00-4BCC-B9E5-71D9491445C3@.microsoft.com...|||Hi;
Tried that (thank you) but got this error message:
Processed 120 pages for database 'WindwardPortal', file
'WindwardPortal_Data' on file 5.
Processed 1 pages for database 'WindwardPortal', file 'WindwardPortal_Log'
on file 5.
BACKUP DATABASE successfully processed 121 pages in 0.283 seconds (3.477
MB/sec).
Server: Msg 3234, Level 16, State 2, Line 1
Logical file 'WindwardPortal' is not part of database 'PortalTest'. Use
RESTORE FILELISTONLY to list the logical file names.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com
"Veera" wrote:

> Try the following:
> Assume you have to copy pubs to pubs_2
> 'create database pubs_2' for the first time.
> then,
> create a job with following steps:
> backup database pubs to disk = 'c:\pubs.bak'
> go
> restore database pubs_2 from disk ='c:\pubs.bak' with replace,
> move 'pubs' to 'C:\Program Files\Microsoft SQL
> Server\MSSQL\data\pubs_2.mdf',
> move 'pubs_log' to 'C:\Program Files\Microsoft SQL
> Server\MSSQL\data\pubs_2_log.ldf'
>
> Thanks
> Veera
> Tibor Karaszi wrote:
>
>|||ps - script is:
backup database WindwardPortal to disk = 'c:\temp\WindwardPortal.bak'
go
restore database PortalTest from disk ='c:\temp\WindwardPortal.bak' with
replace,
move 'WindwardPortal' to 'C:\Program Files\Microsoft SQL
Server\MSSQL\data\PortalTest.mdf',
move 'WindwardPortal_log' to 'C:\Program Files\Microsoft SQL
Server\MSSQL\data\PortalTest_log.ldf'
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com
"Veera" wrote:

> Try the following:
> Assume you have to copy pubs to pubs_2
> 'create database pubs_2' for the first time.
> then,
> create a job with following steps:
> backup database pubs to disk = 'c:\pubs.bak'
> go
> restore database pubs_2 from disk ='c:\pubs.bak' with replace,
> move 'pubs' to 'C:\Program Files\Microsoft SQL
> Server\MSSQL\data\pubs_2.mdf',
> move 'pubs_log' to 'C:\Program Files\Microsoft SQL
> Server\MSSQL\data\pubs_2_log.ldf'
>
> Thanks
> Veera
> Tibor Karaszi wrote:
>
>|||> Processed 120 pages for database 'WindwardPortal', file
> 'WindwardPortal_Data' on file 5.
> Processed 1 pages for database 'WindwardPortal', file 'WindwardPortal_Log'
> on file 5.
> BACKUP DATABASE successfully processed 121 pages in 0.283 seconds (3.477
> MB/sec).
> Server: Msg 3234, Level 16, State 2, Line 1
> Logical file 'WindwardPortal' is not part of database 'PortalTest'. Use
> RESTORE FILELISTONLY to list the logical file names.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
The error message included a suggestion. DId you try it? It will show you
the logical names that you need to move. Apparently, you are not using the
correct logical file name for the data file. You tried to move
'WindwardPortal' when it should have been 'WindwardPortal_Data'|||> 'create database pubs_2' for the first time.
Above is only a waste of time. And that creation will probably not result in
the newly created
database having the same file layout as the one to restore, so the restore n
eed to be performed
using REPLACE which means that SQL Server will delete the database and then
create it when executing
the restore command. If the database doesn't exist, don't create it before t
he restore. the restore
process will do this for you.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Veera" <veerabahu.c@.gmail.com> wrote in message
news:1142873234.396195.127690@.e56g2000cwe.googlegroups.com...
> Try the following:
> Assume you have to copy pubs to pubs_2
> 'create database pubs_2' for the first time.
> then,
> create a job with following steps:
> backup database pubs to disk = 'c:\pubs.bak'
> go
> restore database pubs_2 from disk ='c:\pubs.bak' with replace,
> move 'pubs' to 'C:\Program Files\Microsoft SQL
> Server\MSSQL\data\pubs_2.mdf',
> move 'pubs_log' to 'C:\Program Files\Microsoft SQL
> Server\MSSQL\data\pubs_2_log.ldf'
>
> Thanks
> Veera
> Tibor Karaszi wrote:
>
>|||Hi,
Scott pointed out correctly :
the command should be, (as your logical file name is different),
......
......
move 'WindwardPortal_data' to 'C:\Program Files\Microsoft SQL
Server\MSSQL\data\PortalTest.mdf'
.....
.....
--
Thanks Tibor for your valuable information, Many thanks.