Problem copying a database for testing purposes

I want to make a database copy on the same computer for testing purposes.
In BOL I found in the Transact-SQL Reference:
It may be necessary to create a copy of an existing database on the same
computer for testing purposes. In this case, the database files for the
original database already exist, so different file names need to be
specified when the database copy is created during the restore operation.
Make a copy of a database using BACKUP and RESTORE:
This example uses both the BACKUP and RESTORE statements to make a copy of
the Northwind database (I changed the destination path)
BACKUP DATABASE Northwind
TO DISK = 'c:\Northwind.bak'
RESTORE FILELISTONLY FROM DISK = 'c:\Northwind.bak'
RESTORE DATABASE TestDB
FROM DISK = 'c:\Northwind.bak'
WITH MOVE 'Northwind' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\testdb.mdf',
MOVE 'Northwind_log' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\testdb.ldf'
GO
This works fine, using SQLserver 2000 Developer with the Query analyzer.
I deleted the testdb database (in the Enterprise manager, testdb.mdf and
.ldf also disapeared)
Then I tried the production database:
BACKUP DATABASE Vineadb
TO DISK = 'C:\Vineadb.bak'
RESTORE FILELISTONLY
FROM DISK = 'C:\Vineadb.bak'
RESTORE DATABASE TestDB
FROM DISK = 'C:\Vineadb.bak'
WITH MOVE 'Vineadb' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\testdb.mdf',
MOVE 'Vineadb_log' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\testdb.ldf'
GO
This didn't work, giving the message:
Processed 1848 pages for database 'Vineadb', file 'VineaDb_Data' on file 1.
Processed 1 pages for database 'Vineadb', file 'VineaDb_Log' on file 1.
BACKUP DATABASE successfully processed 1849 pages in 9.388 seconds (1.612
MB/sec).
(2 row(s) affected)
Server: Msg 3234, Level 16, State 2, Line 5 Logical file 'Vineadb' is not
part of database 'TestDB'. Use RESTORE FILELISTONLY to list the logical
file names. Server: Msg 3013, Level 16, State 1, Line 5 RESTORE DATABASE
is terminating abnormally
What could be the reason?
Is there a solution?
I am a Newbe on sqlserver
Vineadb.bak is created (15MB)
testdb.ldf and testdb.mdf are not created.What is the output of
RESTORE FILELISTONLY
FROM DISK = 'C:\Vineadb.bak'
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Henk Schreij" <henk@.schreijDOTnl> wrote in message
news:%23UeRnPh2DHA.2428@.tk2msftngp13.phx.gbl...
> I want to make a database copy on the same computer for testing purposes.
> In BOL I found in the Transact-SQL Reference:
> It may be necessary to create a copy of an existing database on the same
> computer for testing purposes. In this case, the database files for the
> original database already exist, so different file names need to be
> specified when the database copy is created during the restore operation.
> Make a copy of a database using BACKUP and RESTORE:
> This example uses both the BACKUP and RESTORE statements to make a copy of
> the Northwind database (I changed the destination path)
> BACKUP DATABASE Northwind
> TO DISK = 'c:\Northwind.bak'
> RESTORE FILELISTONLY FROM DISK = 'c:\Northwind.bak'
> RESTORE DATABASE TestDB
> FROM DISK = 'c:\Northwind.bak'
> WITH MOVE 'Northwind' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\testdb.mdf',
> MOVE 'Northwind_log' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\testdb.ldf'
> GO
> This works fine, using SQLserver 2000 Developer with the Query analyzer.
> I deleted the testdb database (in the Enterprise manager, testdb.mdf and
> .ldf also disapeared)
> Then I tried the production database:
> BACKUP DATABASE Vineadb
> TO DISK = 'C:\Vineadb.bak'
> RESTORE FILELISTONLY
> FROM DISK = 'C:\Vineadb.bak'
> RESTORE DATABASE TestDB
> FROM DISK = 'C:\Vineadb.bak'
> WITH MOVE 'Vineadb' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\testdb.mdf',
> MOVE 'Vineadb_log' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\testdb.ldf'
> GO
> This didn't work, giving the message:
> Processed 1848 pages for database 'Vineadb', file 'VineaDb_Data' on file
1.
> Processed 1 pages for database 'Vineadb', file 'VineaDb_Log' on file 1.
> BACKUP DATABASE successfully processed 1849 pages in 9.388 seconds (1.612
> MB/sec).
> (2 row(s) affected)
> Server: Msg 3234, Level 16, State 2, Line 5 Logical file 'Vineadb' is not
> part of database 'TestDB'. Use RESTORE FILELISTONLY to list the logical
> file names. Server: Msg 3013, Level 16, State 1, Line 5 RESTORE DATABASE
> is terminating abnormally
> What could be the reason?
> Is there a solution?
> I am a Newbe on sqlserver
> Vineadb.bak is created (15MB)
> testdb.ldf and testdb.mdf are not created.
>
>|||Jasper, using
RESTORE FILELISTONLY FROM DISK = 'C:\VineaDb.bak'
I get a Grid with:
. LogicalName
. Physical Name
. Type
. FileGroupName
. Size
1.
VineaDb_Data
C:\Program Files\Microsoft SQL Server\MSSQL\data\VineaDb_Data.MDF
D
PRIMARY
16449536
2.
VineaDb_Log
C:\Program Files\Microsoft SQL Server\MSSQL\data\VineaDb_Log.LDF
L
NULL
104595456
This is the path to the production database,
not to the just made C:\VineaDb.bak backup.
Could this be the reason?
Then I ask myself, why does it take these files, not the backup.
Remember, ... I am a newbie on SQLServer.
Henk.
-- Original Message --
From: "Jasper Smith" <jasper_smith9@.hotmail.com>
Newsgroups: microsoft.public.sqlserver.server
Sent: Wednesday, January 14, 2004 1:07 AM
Subject: Re: Problem copying a database for testing purposes
> What is the output of
> RESTORE FILELISTONLY
> FROM DISK = 'C:\Vineadb.bak'
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
> "Henk Schreij" <henk@.schreijDOTnl> wrote in message
> news:%23UeRnPh2DHA.2428@.tk2msftngp13.phx.gbl...
> > I want to make a database copy on the same computer for testing
purposes.
> >
> > In BOL I found in the Transact-SQL Reference:
> > ( . . . )
> > This example uses both the BACKUP and RESTORE statements to make a copy
of
> > the Northwind database (I changed the destination path)
> > ( . . . )
> > This works fine, using SQLserver 2000 Developer with the Query analyzer.
> > I deleted the testdb database (in the Enterprise manager, testdb.mdf and
> > .ldf also disapeared)
> >
> > Then I tried the production database:
> > BACKUP DATABASE Vineadb
> > TO DISK = 'C:\Vineadb.bak'
> > RESTORE FILELISTONLY
> > FROM DISK = 'C:\Vineadb.bak'
> > RESTORE DATABASE TestDB
> > FROM DISK = 'C:\Vineadb.bak'
> > WITH MOVE 'Vineadb' TO 'C:\Program Files\Microsoft SQL
> > Server\MSSQL\Data\testdb.mdf',
> > MOVE 'Vineadb_log' TO 'C:\Program Files\Microsoft SQL
> > Server\MSSQL\Data\testdb.ldf'
> > GO
> >
> > This didn't work, giving the message:
> >
> > Processed 1848 pages for database 'Vineadb', file 'VineaDb_Data' on file
> 1.
> > Processed 1 pages for database 'Vineadb', file 'VineaDb_Log' on file 1.
> > BACKUP DATABASE successfully processed 1849 pages in 9.388 seconds
(1.612
> > MB/sec).
> >
> > (2 row(s) affected)
> >
> > Server: Msg 3234, Level 16, State 2, Line 5 Logical file 'Vineadb' is
not
> > part of database 'TestDB'. Use RESTORE FILELISTONLY to list the
logical
> > file names. Server: Msg 3013, Level 16, State 1, Line 5 RESTORE
DATABASE
> > is terminating abnormally|||Its simply a case of not having the correct logical filename for the data
file
RESTORE DATABASE TestDB
FROM DISK = 'C:\Vineadb.bak'
WITH MOVE 'VineaDb_Data' TO 'C:\Program Files\Microsoft
SQLServer\MSSQL\Data\testdb.mdf',
MOVE 'VineaDb_Log' TO 'C:\Program Files\Microsoft
SQLServer\MSSQL\Data\testdb.ldf'
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Henk Schreij" <henk@.schreijDOTnl> wrote in message
news:%23JmoL7n2DHA.1924@.TK2MSFTNGP10.phx.gbl...
> Jasper, using
> RESTORE FILELISTONLY FROM DISK = 'C:\VineaDb.bak'
> I get a Grid with:
> . LogicalName
> . Physical Name
> . Type
> . FileGroupName
> . Size
> 1.
> VineaDb_Data
> C:\Program Files\Microsoft SQL Server\MSSQL\data\VineaDb_Data.MDF
> D
> PRIMARY
> 16449536
> 2.
> VineaDb_Log
> C:\Program Files\Microsoft SQL Server\MSSQL\data\VineaDb_Log.LDF
> L
> NULL
> 104595456
> This is the path to the production database,
> not to the just made C:\VineaDb.bak backup.
> Could this be the reason?
> Then I ask myself, why does it take these files, not the backup.
> Remember, ... I am a newbie on SQLServer.
> Henk.
> -- Original Message --
> From: "Jasper Smith" <jasper_smith9@.hotmail.com>
> Newsgroups: microsoft.public.sqlserver.server
> Sent: Wednesday, January 14, 2004 1:07 AM
> Subject: Re: Problem copying a database for testing purposes
>
> > What is the output of
> >
> > RESTORE FILELISTONLY
> > FROM DISK = 'C:\Vineadb.bak'
> >
> > --
> > HTH
> >
> > Jasper Smith (SQL Server MVP)
> >
> > I support PASS - the definitive, global
> > community for SQL Server professionals -
> > http://www.sqlpass.org
> >
> >
> > "Henk Schreij" <henk@.schreijDOTnl> wrote in message
> > news:%23UeRnPh2DHA.2428@.tk2msftngp13.phx.gbl...
> > > I want to make a database copy on the same computer for testing
> purposes.
> > >
> > > In BOL I found in the Transact-SQL Reference:
> > > ( . . . )
> > > This example uses both the BACKUP and RESTORE statements to make a
copy
> of
> > > the Northwind database (I changed the destination path)
> > > ( . . . )
> > > This works fine, using SQLserver 2000 Developer with the Query
analyzer.
> > > I deleted the testdb database (in the Enterprise manager, testdb.mdf
and
> > > .ldf also disapeared)
> > >
> > > Then I tried the production database:
> > > BACKUP DATABASE Vineadb
> > > TO DISK = 'C:\Vineadb.bak'
> > > RESTORE FILELISTONLY
> > > FROM DISK = 'C:\Vineadb.bak'
> > > RESTORE DATABASE TestDB
> > > FROM DISK = 'C:\Vineadb.bak'
> > > WITH MOVE 'Vineadb' TO 'C:\Program Files\Microsoft SQL
> > > Server\MSSQL\Data\testdb.mdf',
> > > MOVE 'Vineadb_log' TO 'C:\Program Files\Microsoft SQL
> > > Server\MSSQL\Data\testdb.ldf'
> > > GO
> > >
> > > This didn't work, giving the message:
> > >
> > > Processed 1848 pages for database 'Vineadb', file 'VineaDb_Data' on
file
> > 1.
> > > Processed 1 pages for database 'Vineadb', file 'VineaDb_Log' on file
1.
> > > BACKUP DATABASE successfully processed 1849 pages in 9.388 seconds
> (1.612
> > > MB/sec).
> > >
> > > (2 row(s) affected)
> > >
> > > Server: Msg 3234, Level 16, State 2, Line 5 Logical file 'Vineadb' is
> not
> > > part of database 'TestDB'. Use RESTORE FILELISTONLY to list the
> logical
> > > file names. Server: Msg 3013, Level 16, State 1, Line 5 RESTORE
> DATABASE
> > > is terminating abnormally
>
>|||Jasper,
Thank you,
This is what I needed.
Now it workes fine.
I'm so glad you told me that I was making a simple mistake.
I couldn't find out (being a newbie) what I was doing wrong.
Henk
"Jasper Smith" <jasper_smith9@.hotmail.com> schreef in bericht
news:e1EA65o2DHA.1744@.TK2MSFTNGP12.phx.gbl...
> Its simply a case of not having the correct logical filename for the data
> file
> RESTORE DATABASE TestDB
> FROM DISK = 'C:\Vineadb.bak'
> WITH MOVE 'VineaDb_Data' TO 'C:\Program Files\Microsoft
> SQLServer\MSSQL\Data\testdb.mdf',
> MOVE 'VineaDb_Log' TO 'C:\Program Files\Microsoft
> SQLServer\MSSQL\Data\testdb.ldf'
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
> "Henk Schreij" <henk@.schreijDOTnl> wrote in message
> news:%23JmoL7n2DHA.1924@.TK2MSFTNGP10.phx.gbl...
> >
> > Jasper, using
> > RESTORE FILELISTONLY FROM DISK = 'C:\VineaDb.bak'
> > I get a Grid with:
> > . LogicalName
> > . Physical Name
> > . Type
> > . FileGroupName
> > . Size
> > 1.
> > VineaDb_Data
> > C:\Program Files\Microsoft SQL Server\MSSQL\data\VineaDb_Data.MDF
> > D
> > PRIMARY
> > 16449536
> > 2.
> > VineaDb_Log
> > C:\Program Files\Microsoft SQL Server\MSSQL\data\VineaDb_Log.LDF
> > L
> > NULL
> > 104595456
> >
> > This is the path to the production database,
> > not to the just made C:\VineaDb.bak backup.
> > Could this be the reason?
> > Then I ask myself, why does it take these files, not the backup.
> > Remember, ... I am a newbie on SQLServer.
> >
> > Henk.
> >
> > -- Original Message --
> > From: "Jasper Smith" <jasper_smith9@.hotmail.com>
> > Newsgroups: microsoft.public.sqlserver.server
> > Sent: Wednesday, January 14, 2004 1:07 AM
> > Subject: Re: Problem copying a database for testing purposes
> >
> >
> > > What is the output of
> > >
> > > RESTORE FILELISTONLY
> > > FROM DISK = 'C:\Vineadb.bak'
> > >
> > > --
> > > HTH
> > >
> > > Jasper Smith (SQL Server MVP)
> > >
> > > I support PASS - the definitive, global
> > > community for SQL Server professionals -
> > > http://www.sqlpass.org
> > >
> > >
> > > "Henk Schreij" <henk@.schreijDOTnl> wrote in message
> > > news:%23UeRnPh2DHA.2428@.tk2msftngp13.phx.gbl...
> > > > I want to make a database copy on the same computer for testing
> > purposes.
> > > >
> > > > In BOL I found in the Transact-SQL Reference:
> > > > ( . . . )
> > > > This example uses both the BACKUP and RESTORE statements to make a
> copy
> > of
> > > > the Northwind database (I changed the destination path)
> > > > ( . . . )
> > > > This works fine, using SQLserver 2000 Developer with the Query
> analyzer.
> > > > I deleted the testdb database (in the Enterprise manager, testdb.mdf
> and
> > > > .ldf also disapeared)
> > > >
> > > > Then I tried the production database:
> > > > BACKUP DATABASE Vineadb
> > > > TO DISK = 'C:\Vineadb.bak'
> > > > RESTORE FILELISTONLY
> > > > FROM DISK = 'C:\Vineadb.bak'
> > > > RESTORE DATABASE TestDB
> > > > FROM DISK = 'C:\Vineadb.bak'
> > > > WITH MOVE 'Vineadb' TO 'C:\Program Files\Microsoft SQL
> > > > Server\MSSQL\Data\testdb.mdf',
> > > > MOVE 'Vineadb_log' TO 'C:\Program Files\Microsoft SQL
> > > > Server\MSSQL\Data\testdb.ldf'
> > > > GO
> > > >
> > > > This didn't work, giving the message:
> > > >
> > > > Processed 1848 pages for database 'Vineadb', file 'VineaDb_Data' on
> file
> > > 1.
> > > > Processed 1 pages for database 'Vineadb', file 'VineaDb_Log' on file
> 1.
> > > > BACKUP DATABASE successfully processed 1849 pages in 9.388 seconds
> > (1.612
> > > > MB/sec).
> > > >
> > > > (2 row(s) affected)
> > > >
> > > > Server: Msg 3234, Level 16, State 2, Line 5 Logical file 'Vineadb'
is
> > not
> > > > part of database 'TestDB'. Use RESTORE FILELISTONLY to list the
> > logical
> > > > file names. Server: Msg 3013, Level 16, State 1, Line 5 RESTORE
> > DATABASE
> > > > is terminating abnormally
> >
> >
> >
> >
>|||Jasper,
Thank you,
This is what I needed.
Now it workes fine.
I'm so glad you told me that I was making a simple mistake.
I couldn't find out (being a newbie) what I was doing wrong.
Henk
"Jasper Smith" <jasper_smith9@.hotmail.com> schreef in bericht
news:e1EA65o2DHA.1744@.TK2MSFTNGP12.phx.gbl...
> Its simply a case of not having the correct logical filename for the data
> file
> RESTORE DATABASE TestDB
> FROM DISK = 'C:\Vineadb.bak'
> WITH MOVE 'VineaDb_Data' TO 'C:\Program Files\Microsoft
> SQLServer\MSSQL\Data\testdb.mdf',
> MOVE 'VineaDb_Log' TO 'C:\Program Files\Microsoft
> SQLServer\MSSQL\Data\testdb.ldf'
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
>
> "Henk Schreij" <henk@.schreijDOTnl> wrote in message
> news:%23JmoL7n2DHA.1924@.TK2MSFTNGP10.phx.gbl...
> >
> > Jasper, using
> > RESTORE FILELISTONLY FROM DISK = 'C:\VineaDb.bak'
> > I get a Grid with:
> > . LogicalName
> > . Physical Name
> > . Type
> > . FileGroupName
> > . Size
> > 1.
> > VineaDb_Data
> > C:\Program Files\Microsoft SQL Server\MSSQL\data\VineaDb_Data.MDF
> > D
> > PRIMARY
> > 16449536
> > 2.
> > VineaDb_Log
> > C:\Program Files\Microsoft SQL Server\MSSQL\data\VineaDb_Log.LDF
> > L
> > NULL
> > 104595456
> >
> > This is the path to the production database,
> > not to the just made C:\VineaDb.bak backup.
> > Could this be the reason?
> > Then I ask myself, why does it take these files, not the backup.
> > Remember, ... I am a newbie on SQLServer.
> >
> > Henk.
> >
> > -- Original Message --
> > From: "Jasper Smith" <jasper_smith9@.hotmail.com>
> > Newsgroups: microsoft.public.sqlserver.server
> > Sent: Wednesday, January 14, 2004 1:07 AM
> > Subject: Re: Problem copying a database for testing purposes
> >
> >
> > > What is the output of
> > >
> > > RESTORE FILELISTONLY
> > > FROM DISK = 'C:\Vineadb.bak'
> > >
> > > --
> > > HTH
> > >
> > > Jasper Smith (SQL Server MVP)
> > >
> > > I support PASS - the definitive, global
> > > community for SQL Server professionals -
> > > http://www.sqlpass.org
> > >
> > >
> > > "Henk Schreij" <henk@.schreijDOTnl> wrote in message
> > > news:%23UeRnPh2DHA.2428@.tk2msftngp13.phx.gbl...
> > > > I want to make a database copy on the same computer for testing
> > purposes.
> > > >
> > > > In BOL I found in the Transact-SQL Reference:
> > > > ( . . . )
> > > > This example uses both the BACKUP and RESTORE statements to make
> > > > copy of the Northwind database (I changed the destination path)
> > > > ( . . . )
> > > > This works fine, using SQLserver 2000 Developer with Query analyzer.
> > > > I deleted the testdb database (in the Enterprise manager)
> > > >
> > > > Then I tried the production database:
> > > > BACKUP DATABASE Vineadb
> > > > TO DISK = 'C:\Vineadb.bak'
> > > > RESTORE FILELISTONLY
> > > > FROM DISK = 'C:\Vineadb.bak'
> > > > RESTORE DATABASE TestDB
> > > > FROM DISK = 'C:\Vineadb.bak'
> > > > WITH MOVE 'Vineadb' TO 'C:\Program Files\Microsoft SQL
> > > > Server\MSSQL\Data\testdb.mdf',
> > > > MOVE 'Vineadb_log' TO 'C:\Program Files\Microsoft SQL
> > > > Server\MSSQL\Data\testdb.ldf'
> > > > GO
> > > >
> > > > This didn't work, giving the message:
> > > >
> > > > Processed 1848 pages for db 'Vineadb', file 'VineaDb_Data' on file
1.
> > > > Processed 1 pages for db 'Vineadb', file 'VineaDb_Log' on file 1.
> > > > BACKUP DATABASE successfully processed 1849 pages in 9.388 sec
> > > > (2 row(s) affected)
> > > >
> > > > Server: Msg 3234, Level 16, State 2, Line 5 Logical file 'Vineadb'
> > > > is not part of database 'TestDB'.
> > > > Use RESTORE FILELISTONLY to list the logical
> > > > file names. Server: Msg 3013, Level 16, State 1, Line 5
> > > > RESTORE DATABASE is terminating abnormally