June 3, 2011 at 5:39 am
I have never written a SQL script before. 1000's of Selects, Inserts, Updates and Deletes but not one script. Fortunately, my first script should be simple enough. I need to backup Database A which has a Full Text Search Catalog. Then I need to restore that database on the same SQL 2008 server to a database with a different name, B.
I have been doing this in SSMS. For the Restore, I make sure Database B is the database to Restore. I select Database A as the Source. From Options I select Overwrite. This much I think I could find on line.
The next thing I do is I have to rename the files because after selecting the Restore from Database A to Database B, the file names are for Database A. I change the three file names from Database A to Database B and then execute the Restore.
Would someone please post the code to backup A and restore as B. Or if you know of a link (I searched backup but could not find an similar post. There were too many results.) would you share it?
Thanks,
pat
June 3, 2011 at 6:17 am
last time I looked there was a "script" option in SSMS for this - or you could capture in profiler - or you could just use BOL and lookup restore database.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
June 3, 2011 at 6:43 am
First of all u need to run, filelistonly to find out the logical file names:
restore filelistonly from disk='E:\A.bak'
And then running below little script should do it for u:
restore database B from disk='E:\A.bak'
with file=1,
move 'A' to 'E:\b.mdf',
move 'A_log' to 'E:\b_log.ldf'
alter database B modify file (name='A', newname='B')
alter database B modify file (name='A_log', newname='B_log')
Does that work for u??
-Yogesh
June 5, 2011 at 8:06 am
Grasshopper,
I hit a snag. When executing your code, I receive:
The backup set holds a backup of a database other than the existing 'B' database.
I think this will be cured if I can set the option "Overwrite existing database (With Replace). I assume this is an option of the Restore command but I cannot find a reference to it in the BOL or through Google. Restoring with your setting probably works good for resotring a backup of A to A but I need to restore backup of A to B. Any ideas?
Thanks,
pat
June 6, 2011 at 12:40 am
Try this:
RESTORE DATABASE B
FROM DISK = 'E:\A.bak'
WITH REPLACE, MOVE 'A_Data' TO 'E:\B.mdf', MOVE 'A_Log' TO 'E:\B_Log.ldf'
ALTER DATABASE B
MODIFY FILE (NAME = 'A_Data', NEWNAME = 'B_data')
GO
ALTER DATABASE B
MODIFY FILE (NAME = 'A_Log', NEWNAME = 'B_log')
GO
June 6, 2011 at 12:49 am
mpdillon (6/5/2011)
Grasshopper,I hit a snag. When executing your code, I receive:
The backup set holds a backup of a database other than the existing 'B' database.
I think this will be cured if I can set the option "Overwrite existing database (With Replace). I assume this is an option of the Restore command but I cannot find a reference to it in the BOL or through Google. Restoring with your setting probably works good for resotring a backup of A to A but I need to restore backup of A to B. Any ideas?
Thanks,
pat
Hi,
Read the Step by step backup Restore This will help you.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
June 6, 2011 at 4:59 am
Martusha and Muthukkumaran Kaliyamoorthy,
Thank you both. I am still doing something wrong.
The table names are:
A.bak
A.mdf
A_Log.ldf
B.Mdf
B_Log.ldf
These 5 files are located in the D:\SQLData\ folder.
Here is my current script:
RESTORE DATABASE B
FROM DISK = 'D:\SQLData\A.bak'
WITH REPLACE, MOVE 'A' TO 'D:\SQLData\B.mdf', MOVE 'A_Log' TO 'D:\SQLData\B_Log.ldf'
ALTER DATABASE B
MODIFY FILE (NAME = 'A', NEWNAME = 'B')
GO
ALTER DATABASE B
MODIFY FILE (NAME = 'A_Log', NEWNAME = 'B_log')
GO
Here is the error message:
Msg 3234, Level 16, State 2, Line 1
Logical file 'A' is not part of database 'B'. Use RESTORE FILELISTONLY to list the logical file names.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Msg 5041, Level 16, State 1, Line 5
MODIFY FILE failed. File 'A' does not exist.
Msg 5041, Level 16, State 1, Line 1
MODIFY FILE failed. File 'A_Log' does not exist.
To me the error message seems to say the Restore fails before it can do the Alter name. This seems like a Catch 22. I can't do a restore until the name is changed. But I can't change the name until the data is Restored.
Suggestions? Am I just making a syntax error?
pat
June 6, 2011 at 5:07 am
As Yogeshwar Phull said, try:
"First of all u need to run, filelistonly to find out the logical file names:
restore filelistonly from disk='E:\A.bak' "
And then run your script
June 6, 2011 at 5:16 am
mathusa,
Ok. I added that.
restore filelistonly from disk='D:\SQLData\A.bak'
RESTORE DATABASE B
FROM DISK = 'D:\SQLData\A.bak'
WITH REPLACE, MOVE 'A' TO 'D:\SQLData\B.mdf', MOVE 'A_Log' TO 'D:\SQLData\B_Log.ldf'
ALTER DATABASE B
MODIFY FILE (NAME = 'A', NEWNAME = 'B')
GO
ALTER DATABASE B
MODIFY FILE (NAME = 'A_Log', NEWNAME = 'B_log')
GO
It seems like it does the Filelistonly but then errors out exactly the same.
(2 row(s) affected)
Msg 3234, Level 16, State 2, Line 3
Logical file 'A' is not part of database 'B'. Use RESTORE FILELISTONLY to list the logical file names.
Msg 3013, Level 16, State 1, Line 3
RESTORE DATABASE is terminating abnormally.
Msg 5041, Level 16, State 1, Line 7
MODIFY FILE failed. File 'A' does not exist.
Msg 5041, Level 16, State 1, Line 1
MODIFY FILE failed. File 'A_Log' does not exist.
Does it matter that I used SSMS console to create an empty A database. Then I used SSMS to "Restore" a backup of a database named "GMF" to A. I did this so A would contain some data.
pat
June 6, 2011 at 5:21 am
Can you post me the result so that i 'll help you. (From Server B restoring server)
RESTORE filelistonly FROM DISK = 'D:\SQLData\A.bak'
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
June 6, 2011 at 5:31 am
The Results are in the attached jpg.
What I find interesting is that the FileListOnly has a Logical name for the A log file as GMF_Log but the Physical name is A_1. Yet the image of Windows Explorer shows the Physical name is A_Log. I am very confussed.
I believe the GMF name is coming from the creation of A. I used SSMS to create a Blank database. Then I RESTORED another database, GMF, to A using the Overwrite option in SSMS. I also changed the Physical file names in SSMS before executing the RESTORE.
pat
June 6, 2011 at 5:51 am
Lets try
RESTORE DATABASE B
FROM DISK = 'D:\SQLData\A.bak'
WITH REPLACE,
MOVE 'GMF' TO 'D:\SQLData\B.mdf',
MOVE 'GMF_Log' TO 'D:\SQLData\B_Log.ldf'
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
June 6, 2011 at 5:53 am
You have to give the correct logical name to the restore clause otherwise the SQL will not take/create the database.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
June 6, 2011 at 6:18 am
One thing leads to another. Your code worked. Now I have additional questions. When I restore GMF to A as I did origianlly using SSMS, shouldn't I be changing the Name From GMF to A? How do I do that in SSMS?
The below code worked great. Thank you so much. The problem was that when I did the Restore from GMF to A so that A would have data, the logical name of A was still GMF.
restore filelistonly from disk='D:\SQLData\A.bak'
RESTORE DATABASE B
FROM DISK = 'D:\SQLData\A.bak'
WITH REPLACE, MOVE 'GMF' TO 'D:\SQLData\B.mdf', MOVE 'GMF_Log' TO 'D:\SQLData\B_Log.ldf'
ALTER DATABASE B
MODIFY FILE (NAME = 'GMF', NEWNAME = 'B')
GO
ALTER DATABASE B
MODIFY FILE (NAME = 'GMF_Log', NEWNAME = 'B_log')
GO
The following query returns the Logical name
USE master;
GO
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'A')
Using TSQL I can change the logical name now with the command:
ALTER DATABASE A
MODIFY FILE (NAME = 'GMF', NEWNAME = 'A')
GO
Thank you so much,
pat
June 6, 2011 at 6:26 am
Run
restore filelistonly from disk='D:\SQLData\A.bak'
Watch results.
Is there logical names "A" and "A_log" or names is different?
Ups, I'm late with answer.... 🙂
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply