| Groups | Blog | Home |
sql server new users : Backup-Restore Script
I am new at this and I am stuck...
I am trying to backup an entire database and restore it on the same SQL server with a different name. I have this in the Query Analyzer BACKUP DATABASE merchant TO DISK = 'c:\merchant.bak' RESTORE FILELISTONLY FROM DISK = 'c:\merchant.bak' RESTORE DATABASE TestDB FROM DISK = 'c:\merchant.bak' WITH MOVE 'merchant' TO 'c:\testdb.mdf', MOVE 'merchant_log' TO 'c:\testdb.ldf' GO I get the following error Processed 2304 pages for database 'merchant', file 'Struky3_Data' on file 13. Processed 1 pages for database 'merchant', file 'Struky3_Log' on file 13. BACKUP DATABASE successfully processed 2305 pages in 2.539 seconds (7.434 MB/sec). (2 row(s) affected) Server: Msg 3234, Level 16, State 2, Line 5 Logical file 'merchant' 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. Can anyone help with this? I want to copy the entire database called MERCHANT to another database
When you ran:
RESTORE FILELISTONLY FROM DISK = 'c:\merchant.bak' .... what was the output? Failing that, run: merchant.dbo.sp_helpfile It should list the logical file names, as well as the physical names. -- Tom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA SQL Server MVP Columnist, SQL Server Professional Toronto, ON Canada www.pinnaclepublishing.com .. [quoted text, click to view] "Brian Branco" <bbranco@bncconsulting.com> wrote in message I am new at this and I am stuck...news:OTr7Uh5HFHA.3072@tk2msftngp13.phx.gbl... I am trying to backup an entire database and restore it on the same SQL server with a different name. I have this in the Query Analyzer BACKUP DATABASE merchant TO DISK = 'c:\merchant.bak' RESTORE FILELISTONLY FROM DISK = 'c:\merchant.bak' RESTORE DATABASE TestDB FROM DISK = 'c:\merchant.bak' WITH MOVE 'merchant' TO 'c:\testdb.mdf', MOVE 'merchant_log' TO 'c:\testdb.ldf' GO I get the following error Processed 2304 pages for database 'merchant', file 'Struky3_Data' on file 13. Processed 1 pages for database 'merchant', file 'Struky3_Log' on file 13. BACKUP DATABASE successfully processed 2305 pages in 2.539 seconds (7.434 MB/sec). (2 row(s) affected) Server: Msg 3234, Level 16, State 2, Line 5 Logical file 'merchant' 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. Can anyone help with this? I want to copy the entire database called MERCHANT to another database
The logical file name is: STRUCKY3, the physical name is merchant.
Thanks... [quoted text, click to view] "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message news:O$a7tq5HFHA.3612@TK2MSFTNGP09.phx.gbl... > When you ran: > > RESTORE FILELISTONLY > FROM DISK = 'c:\merchant.bak' > > ... what was the output? Failing that, run: > > merchant.dbo.sp_helpfile > > It should list the logical file names, as well as the physical names. > > -- > Tom > > ---------------------------------------------------- > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA > SQL Server MVP > Columnist, SQL Server Professional > Toronto, ON Canada > www.pinnaclepublishing.com > . > "Brian Branco" <bbranco@bncconsulting.com> wrote in message > news:OTr7Uh5HFHA.3072@tk2msftngp13.phx.gbl... > I am new at this and I am stuck... > > I am trying to backup an entire database and restore it on the same SQL > server with a different name. > > I have this in the Query Analyzer > BACKUP DATABASE merchant > TO DISK = 'c:\merchant.bak' > RESTORE FILELISTONLY > FROM DISK = 'c:\merchant.bak' > RESTORE DATABASE TestDB > FROM DISK = 'c:\merchant.bak' > WITH MOVE 'merchant' TO 'c:\testdb.mdf', > MOVE 'merchant_log' TO 'c:\testdb.ldf' > GO > > I get the following error > > Processed 2304 pages for database 'merchant', file 'Struky3_Data' on file > 13. > Processed 1 pages for database 'merchant', file 'Struky3_Log' on file 13. > BACKUP DATABASE successfully processed 2305 pages in 2.539 seconds (7.434 > MB/sec). > > (2 row(s) affected) > > Server: Msg 3234, Level 16, State 2, Line 5 > Logical file 'merchant' 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. > > > Can anyone help with this? > > I want to copy the entire database called MERCHANT to another database > >
You need to specify the Logical file name instead of the physical name when
your restore with the MOVE option. RESTORE DATABASE TestDB FROM DISK = 'c:\merchant.bak' WITH MOVE 'strucky3_data' TO 'c:\testdb.mdf', MOVE 'strucky3_log' TO 'c:\testdb.ldf' (...here I assume that the logical file name for the data file is 'strucky3_data' and the logfile is 'strucky3_log', but that's what you can get from the RESTORE FILELISTONLY command). You can also look up the RESTORE command in BOL. Regards Steen [quoted text, click to view] Brian Branco wrote: > The logical file name is: STRUCKY3, the physical name is merchant. > > Thanks... > > > "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message > news:O$a7tq5HFHA.3612@TK2MSFTNGP09.phx.gbl... >> When you ran: >> >> RESTORE FILELISTONLY >> FROM DISK = 'c:\merchant.bak' >> >> ... what was the output? Failing that, run: >> >> merchant.dbo.sp_helpfile >> >> It should list the logical file names, as well as the physical names. >> >> -- >> Tom >> >> ---------------------------------------------------- >> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA >> SQL Server MVP >> Columnist, SQL Server Professional >> Toronto, ON Canada >> www.pinnaclepublishing.com >> . >> "Brian Branco" <bbranco@bncconsulting.com> wrote in message >> news:OTr7Uh5HFHA.3072@tk2msftngp13.phx.gbl... >> I am new at this and I am stuck... >> >> I am trying to backup an entire database and restore it on the same >> SQL server with a different name. >> >> I have this in the Query Analyzer >> BACKUP DATABASE merchant >> TO DISK = 'c:\merchant.bak' >> RESTORE FILELISTONLY >> FROM DISK = 'c:\merchant.bak' >> RESTORE DATABASE TestDB >> FROM DISK = 'c:\merchant.bak' >> WITH MOVE 'merchant' TO 'c:\testdb.mdf', >> MOVE 'merchant_log' TO 'c:\testdb.ldf' >> GO >> >> I get the following error >> >> Processed 2304 pages for database 'merchant', file 'Struky3_Data' on >> file >> 13. >> Processed 1 pages for database 'merchant', file 'Struky3_Log' on >> file 13. BACKUP DATABASE successfully processed 2305 pages in 2.539 >> seconds (7.434 MB/sec). >> >> (2 row(s) affected) >> >> Server: Msg 3234, Level 16, State 2, Line 5 >> Logical file 'merchant' 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. >> >> >> Can anyone help with this? >> >> I want to copy the entire database called MERCHANT to another >> database
Don't see what you're looking for? Try a search.
|
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |