May 17, 2008 at 5:16 pm
Team,
I am taking a fresh backup on a primary server and copied it over to a failover server to restore.
I am running the following syntax:
restore database
FROM DISK = 'd:\mssql\backup\ _PSQL120080517.bak'
with NORECOVERY
MOVE ' _DATA.MDF',
MOVE ' _log.LDF'
Following is the error:
Msg 3634, Level 16, State 1, Line 1
The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'D:\ _DATA.MDF'.
Msg 3156, Level 16, State 5, Line 1
File ' _DATA.MDF'. Use WITH MOVE to identify a valid location for the file.
Msg 3634, Level 16, State 1, Line 1
The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'D:\ _log.LDF'.
Msg 3156, Level 16, State 5, Line 1
File ' _log.LDF'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 1
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Troubleshooting:
I checked that I was able to delete and create files
I checked the restore filelistonly to make sure I use the same logical names and give it my new physical location
The syntax parses so I can not see that the syntax is incorrect ( I even tried with recovery and norecovery)
I see alot of the web blogs are express edition realated
I tried to restore on multiple boxes and same error. I am thinking it has to be syntax. Can someone please find out what is driving me nuts.
May 17, 2008 at 11:43 pm
If the database already exist in the new server and if you wish to overwrite it then you can make use of the below command,
Restore database dbname from disk='Path\filename.bak' WITH REPLACE
You can make use of this command to restore the database,
(a) RESTORE FILELISTONLY FROM DISK='D:\YourBackupfilename.bak'
GO
(b) RESTORE DATABASE NewDatabaseName
FROM DISK='D:\YourBackupfilename.bak'
WITH MOVE'LOgiclaName of the SOurceDatafile' TO 'D:\NewDatabase.mdf',
MOVE 'Logicalname of sourcelogfile' TO'D:\NewDatabase_log.ldf'
Note : To get the logical file name run the step (a) and copy past the data and log file name. Change the script accordingly and run
[font="Verdana"]- Deepak[/font]
May 18, 2008 at 11:58 am
To generate a restore statement starting from a full backup, placing the new db-files at the instance default location, I use this script:
/* generate RESTORE DATABASE from backup file */
Declare @BU_DbName sysname
Declare @Restore_DbName sysname
Declare @BackupPath NVarchar(2000)
Select @BU_DbName = 'DServerInfo' --> MODIFY
, @Restore_DbName = '' --> MODIFY
, @BackupPath ='' --> MODIFY if not on default location
if @Restore_DbName = ''
begin
set @Restore_DbName = @BU_DbName + '_RESTORED'
end
Declare @DataPath NVarchar(2000)
Declare @LogPath NVarchar(2000)
/* Are there default Data/Log folders defined for this instance ? You realy should do this !*/
EXEC xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', @DataPath OUTPUT
EXEC xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', @LogPath OUTPUT
If @BackupPath =''
begin
EXEC xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'BackupDirectory', @BackupPath OUTPUT
end
Declare @Filename varchar(500)
Set @Filename = @BackupPath + '\' + @BU_DbName + 'Full.bak'
if 0 = 1
begin
print 'DataPath: [' + coalesce( @DataPath , '**UNKNOWN**') + ']'
print 'LogPath: [' + coalesce( @LogPath , '**UNKNOWN**') + ']'
print 'BackupPath: [' + coalesce( @BackupPath , '**UNKNOWN**') + ']'
print @Filename
end
create table #tmpFilelist -- generated from BOL Sep 2007
( LogicalName nvarchar(128) ,
PhysicalName nvarchar(260) ,
[Type] char(1) ,
FileGroupName nvarchar(128) ,
[Size] numeric(20,0) ,
[MaxSize] numeric(20,0) ,
FileID bigint ,
CreateLSN numeric(25,0) ,
DropLSN numeric(25,0) NULL ,
UniqueID uniqueidentifier ,
ReadOnlyLSN numeric(25,0) NULL ,
ReadWriteLSN numeric(25,0) NULL ,
BackupSizeInBytes bigint ,
SourceBlockSize int ,
FileGroupID int ,
LogGroupGUID uniqueidentifier NULL ,
DifferentialBaseLSN numeric(25,0) NULL ,
DifferentialBaseGUID uniqueidentifier ,
IsReadOnly bit ,
IsPresent bit)
Insert into #tmpFilelist
exec ('restore filelistonly from disk=N''' + @Filename + '''')
Select case FileID when 1 then ' Restore database [' + @Restore_DbName + ']
from disk=''' + @Filename + '''
with MOVE N'''+ LogicalName + ''' TO N''' + @DataPath + replace( substring( PhysicalName, (datalength(PhysicalName)/2) - charindex('\', reverse(PhysicalName)) + 1 , charindex('\', reverse(PhysicalName)) ), @BU_DbName, @Restore_DbName ) + ''''
else
', MOVE N'''+ LogicalName + ''' TO N''' + case [Type] when 'L' then @LogPath else @DataPath end + replace( substring( PhysicalName, (datalength(PhysicalName)/2) - charindex('\', reverse(PhysicalName)) + 1 , charindex('\', reverse(PhysicalName)) ), @BU_DbName, @Restore_DbName ) + ''''
end
from #tmpFilelist
order by FileID ;
drop table #tmpFilelist;
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 18, 2008 at 12:07 pm
u have to consider the following:
1- write the database name u wnat to restore on.
2- if the file exsits on the path u choose then u have to use overwrite.
3- if u wnat to change the file names then use with move option.
4- when using with move u have to write the fileoldname and the filenewname.
like old.mdf to new.mdf.
the easyist way is to use the GUI and script the click to new query window(SQL 2005), then apply some minner changes.
..>>..
MobashA
May 18, 2008 at 12:10 pm
sory.
and u only apply NORECOVERY if there is more backups u want to apply to the database like log backups.
..>>..
MobashA
May 18, 2008 at 3:17 pm
Sorry,
For some reason my syntax did not copy how I had it. I have the syntax you guys are trying to show me. I used RESTORE FILELISTONLY
FROM DISK = 'SOURCE PATH'
I get the following error:
Msg 3201, Level 16, State 2, Line 1
Cannot open backup device 'D:\MSSQL\EC_SunAmerica200805181408.bak'. Operating system error 5(Access is denied.).
Msg 3013, Level 16, State 1, Line 1
RESTORE FILELIST is terminating abnormally.
May 19, 2008 at 6:05 am
this means you sqlserver instance service account cannot access "'D:\MSSQL\EC_SunAmerica200805181408.bak"
btw : mentioning "failover" for most of us triggers "clustering".
With clustering, at failover time, shared disks are reconnected to the other node of the cluster.
With sql2005 you can also use "database mirroring", maybe can concept may serve you better.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 19, 2008 at 8:14 pm
And if mirroring is your goal, it works well I've found.
Perform the restore as above with NORECOVERY.
It must be in 'restoring' mode to activate mirroring. Then create your Endpoints and Alter database. That's it.
John
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply