May 6, 2008 at 11:25 am
Hello,
I have 2 .BAK files from the same database (with the same datbase name, which is pbfamily). I need to restore one .BAK as pbfamily and the other as pbfamily_new.
I receive "Reissue the statement using the WITH REPLACE option to overwrite the XXX database." I have restored one .BAK as pbfamily successfully, but the other .BAK fails. Can you help me find the correct way of restoring this database into 2 databases?
Thanks,
Paul
May 6, 2008 at 11:35 am
Restore database pbfamily from disk='Path\filename.bak' WITH REPLACE
the above command will overwrite the pbfamily database.
(a) RESTORE FILELISTONLY FROM DISK = 'c:\xyz.bak' :- This command will give u the logical file names in the backup set.
(b) RESTORE DATABASE Pbfamily_new
FROM DISK = 'c:\xyz.bak'
WITH MOVE 'LogicaldataFilename got from Step (a) ' TO 'c:\yourfolder\YourNewDatabaseName.mdf',
MOVE 'LogicalLogfile name got from Step(a)' TO 'c:\yourfolder\YourNewDatabaseName.ldf'
Above statement will restore the database to new location.
[font="Verdana"]- Deepak[/font]
May 6, 2008 at 3:29 pm
Just to confirm:
I have 1 database
I have 2 .BAK files, both from the same database. One is from last month and the other is the current date. Lets say the files are named A.BAK and B.BAK
I need to restore A.BAK into A database, and restore B.BAK into B database
So, I follow you correctly, I do this:
RESTORE FILELISTONLY FROM DISK = 'c:\B.bak'
RESTORE DATABASE Pbfamily_new
FROM DISK = 'c:\B.bak'
WITH MOVE 'LogicaldataFilename got from above command ' TO 'c:\yourfolder\YourNewDatabaseName.mdf',
MOVE 'LogicalLogfile name got from above command' TO 'c:\SQL\Data\YourNewDatabaseName.ldf'
Is this corrrect
May 6, 2008 at 3:55 pm
I think I may have the wrong names:
Here is what I get when I run the commands:
RESTORE FILELISTONLY FROM DISK = 'C:\SQL-Backups\pbfamily\pbFamily_db_200805040200.BAK'
Results:
PBFamily_Data d:\Program Files\Microsoft SQL Server\MSSQL$SQL2000\data\PBFamily_Data.MDFDPRIMARY380108835184372080640
PBFamily_Logd:\Program Files\Microsoft SQL Server\MSSQL$SQL2000\data\PBFamily_Log.LDFLNULL1042022435184372080640
When I run this one:
RESTORE DATABASE Pbfamily_new
FROM DISK = 'C:\SQL-Backups\pbfamily\pbFamily_db_200805040200.BAK'
WITH MOVE 'PBFamily_Data.mdf' TO 'C:\SQL Server\MSSQL\Data\pbfamily_new_Data.mdf',
MOVE 'PBFamily_log.ldf' TO 'C:\SQL Server\MSSQL\Data\pbfamily_new_Log.ldf'
Results:
Msg 3234, Level 16, State 2, Line 3
Logical file 'PBFamily_Data.mdf' is not part of database 'Pbfamily_new'. Use RESTORE FILELISTONLY to list the logical file names.
Msg 3013, Level 16, State 1, Line 3
RESTORE DATABASE is terminating abnormally.
May 6, 2008 at 9:32 pm
You need to mention the logical file name i.e PBFamily_Data and
PBFamily_Log. I have modified the script please try with this one and let me know if thats good to go.
RESTORE DATABASE Pbfamily_new
FROM DISK = 'C:\SQL-Backups\pbfamily\pbFamily_db_200805040200.BAK'
WITH MOVE 'PBFamily_Data' TO 'C:\SQL Server\MSSQL\Data\pbfamily_new_Data.mdf',
MOVE 'PBFamily_Log' TO 'C:\SQL Server\MSSQL\Data\pbfamily_new_Log.ldf'
[font="Verdana"]- Deepak[/font]
May 7, 2008 at 11:10 am
Hi Deepak,
I tried the command I get:
Msg 3101, Level 16, State 1, Line 4
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Line 4
RESTORE DATABASE is terminating abnormally.
There are no users using the DB, so I am suprised I see this message.
Then I used the follow to have only one user use the DB:
Alter Database pbfamily_new SET SINGLE_USER With ROLLBACK IMMEDIATE
Then I ran the command again and received the same message as above.
Then I used to go back to multi-user:
ALTER DATABASE pbfamily_new SET MULTI_USER;
Thanks for you help, but still stuck.
May 7, 2008 at 11:19 am
before running the above command please change the database context to master using the below command,
Use MASTER
now try to once again run the command I mentioned in my earlier post..if still you receive the same error message then run sp_who2 and find the spid which is accessing your database and kill that spid using the below command and then once again restore it.
Kill spid
[font="Verdana"]- Deepak[/font]
May 7, 2008 at 11:55 am
I receive this:
Msg 3141, Level 16, State 0, Line 6
The database to be restored was named 'pbFamily'. Reissue the statement using the WITH REPLACE option to overwrite the 'Pbfamily_new' database.
Msg 3013, Level 16, State 1, Line 6
RESTORE DATABASE is terminating abnormally.
Note that the original DB is named "pbfamily". I need to restore it as "pbfamily_new". I have already have restored one of the .BAK file of the same database as "pbfamily".
This is what I ran:
RESTORE DATABASE Pbfamily_new
FROM DISK = 'C:\SQL-Backups\pbfamily\pbFamily_db_200805040200.BAK'
WITH MOVE 'PBFamily_Data' TO 'C:\SQL Server\MSSQL\Data\pbfamily_new_Data.mdf',
MOVE 'PBFamily_Log' TO 'C:\SQL Server\MSSQL\Data\pbfamily_new_Log.ldf'
May 7, 2008 at 3:55 pm
If the Pbfamily_new database already exists on your system, either drop it first or run this:
[font="Courier New"]RESTORE DATABASE Pbfamily_new
FROM DISK = 'C:\SQL-Backups\pbfamily\pbFamily_db_200805040200.BAK'
WITH MOVE 'PBFamily_Data' TO 'C:\SQL Server\MSSQL\Data\pbfamily_new_Data.mdf',
MOVE 'PBFamily_Log' TO 'C:\SQL Server\MSSQL\Data\pbfamily_new_Log.ldf' ,
REPLACE[/font]
Note the REPLACE option specified. This will overwrite any existing database called (in this case) Pbfamily_new.
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
May 7, 2008 at 5:58 pm
Thank you, that worked!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply