August 13, 2008 at 1:05 pm
hi guys i am trying to restore a database from a .bak file to a different server but this database has two secondary data files, how do i specify when doing the restore that i need to put each secondary file in different drives? should i just make a copy and paste of the primary, sencondary files and transaction log and then in the new server use a attach DB instead of using a .bak file to restore? and if i do a copy of paste of the data files the original DB has to be offline correct?
August 13, 2008 at 1:43 pm
--Restore the headers so you can get the logical file names
--from the original DB. You'll need these later.
RESTORE FILELISTONLY
FROM DISK='Your backup file/path here'
--Use the logical file names from above in the MOVE clause below.
--add more MOVE statements if your restore of the file headers
--shows that there are multiple data or log files. You'll need a
--MOVE statement for each file in the backup.
RESTORE DATABASE YourDBNameHere
FROM DISK='Your backup file/path here'
WITH MOVE 'Logical DataFile Name' TO 'Path/file name for new MDF location',
MOVE 'Logical Log File Name' TO 'Path/file name for new LDF location',
STATS
--,REPLACE --Uncomment this if restoring over an existing DB
August 13, 2008 at 2:07 pm
thank you!! i saw that i could do the same in the OPTIONS page when doing the restore, what is the difference between the logical and the file name. which one is the name that resides in the drive? Also, After i do a restore, all logins from the DB are also restored?
August 13, 2008 at 4:01 pm
logical_file_name is a name used to refer to the file in all Transact-SQL
statements.
For example:
Alter Database
DBCC SHRINKFILE
FILEPROPERTY()
RESTORE ... MOVE
FILE_ID()
FILE_IDEX()
filename(Physical filename) is the one that resides under drives.
After restoration of database from another server only users gets created automatically, you need to map them to the logins by using
sp_change_users_login stored procedure. You might have to create logins too if they are not already existing on the destination server.
MJ
August 14, 2008 at 7:46 am
I have all the users and logins can you please tell me how to do the sp_change_users_login sp please i am having some issues conecting from the application side.
August 14, 2008 at 11:25 am
Just chck its syntax in books online and you will be able to use it.
report and update_one are two parameters that will help you in mapping the users to logins and displaying orphan logins(created after restoration).
MJ
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply