June 22, 2006 at 8:35 am
I have 100 DB, I want to restore in new server, what scripts shall I use, I don't know the data and log file location.
When I did:
RESTORE DATABASE testing
FROM DISK = 'D:\Microsoft SQL Server\test1'
with Move 'test_Data' tO 'D:\Microsoft SQL Server\Data\testing_Data.MDF',
MOVE 'test_log' TO 'D:\Microsoft SQL Server\log\testing_log.ldf'
I got this following error
Server: Msg 3234, Level 16, State 2, Line 1
Logical file 'test_Data' is not part of database 'testing'. Use RESTORE FILELISTONLY to list the logical file names.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
June 22, 2006 at 9:45 am
You do need to use that restore command to get the logical file names. They are returned in a result set, so you can store that in a temp table or table variable and then use it to do the restore.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
June 22, 2006 at 10:26 am
Steve,
Thanks for replying me back, I just have backup file, which I want to restore in one of the server,without knowing there data and log file how should I restore it. I can do it from enterprise manager but I have 100 DB. I want to do from script.
Nita
June 23, 2006 at 12:00 pm
use
restore
filelistonly from disk = 'D:\Microsoft SQL Server\test1'
to see the logical and physical filenames that are included in the backup file
June 23, 2006 at 2:20 pm
Also, note that the default logical filenames for database 'testing' are testing_data and testing_log, not test_data and test_log, no matter what the backup file is called. Changing your script build to use the database name, and not the file name, when restoring would cure 90% of your issues.
Also, if this is a rename from test to testing, and test already exists, you will need to include the REPLACE option on your restore options, and you should (IMO) also include the RECOVERY option. In this case, your WITH would be:
...
WITH RECOVERY, REPLACE, MOVE....
June 24, 2006 at 1:42 am
You can get the file locations from the following command.
sp_msforeachdb "use ? ;select name,filename from sysfiles "
------------
Prakash Sawant
http://psawant.blogspot.com
June 24, 2006 at 3:27 pm
If you can't figure out the script, then you can use this little trick I've been doing sometimes. I recently started using SQL 2005 on my desktop to manage my SQL 2000 SQL servers. It has a handy "script" function when you're in a GUI. That allows you to use the GUI to set up all the restore parameters, but instead of pressing "GO" in the GUI, you can create a script of it, or a job directly, and then cancel your GUI and run the job.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply