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.comHi David,
use "copy database". wizard to copy. see to that you keep your copying
database offline.
Amarnath
"David Thielen" wrote:
> 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.com
>|||Hi;
I tried that - the copy wizard won't allow the source & dest server to be
the same.
--
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com
"Amarnath" wrote:
> Hi David,
> use "copy database". wizard to copy. see to that you keep your copying
> database offline.
> Amarnath
> "David Thielen" wrote:
> > 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.com
> >|||Backup 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 database 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 (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.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:
> Backup 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 database 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 (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.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:
> > Backup 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 database 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 (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.com
> > >
>|||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:
> > Backup 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 database 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 (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.com
> > >
>|||> 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 need 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 the 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:
>> Backup 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 database 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 (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.com
>> >
>|||that did it - thank you everyone!
--
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com
"Scott Morris" 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'
>
>|||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.