tsql - SQL Server Management Studio - trouble restoring from bak and trn -
i trying restore database .bak
, trn
files. not able see .bak
, .trn
files through sql server management studio. when go folder see them. used t-sql says access denied. sysadmin on server. can please me it. script:
restore database [xyz] disk = n'r:\mssql10_50.mssqlserver\mssql\restore\xyz_full.bak' file = 1
go
error: msg 3201, level 16, state 2, line 3 cannot open backup device 'r:..."operating system error 5(access denied.).
make sure account (windows or sql server) in ssms has right backup/restore, sysadmin, db_backoperator, etc.
the backup , restore processes runs under sql server (engine) service account since might running ssms on laptop working files on server.
it doesn't matter logged in as, service account needs access directory , files.
is service account domain account or local service? use domain account can work files on unc path.
also, there 2 system stored procedures executed during browse dialog: master.dbo.xp_dirtree, master.dbo.xp_fileexist.
if return empty results query window, permission issue sql server service account.
profiler trace browse operation (adventure works).
declare @path nvarchar(255) declare @name nvarchar(255) select @path = n'c:\mssql\save me\backup\adventureworks2012' select @name = n'adventureworks2012_backup_2012_11_30_160723_2147507.bak' create table #filetmpfin (name nvarchar(255) not null, isfile bit null) if(@name null) begin create table #filetmp (name nvarchar(255) not null, depth int not null, isfile bit null ) insert #filetmp execute master.dbo.xp_dirtree @path, 1, 1 insert #filetmpfin select name, isfile #filetmp f drop table #filetmp end if(not @name null) begin declare @fullname nvarchar(300) if(@path null) select @fullname = @name else select @fullname = @path + '\' + @name create table #filetmp2 ( exist bit not null, isdir bit not null, direxist bit null ) insert #filetmp2 execute master.dbo.xp_fileexist @fullname insert #filetmpfin select @name, 1-isdir #filetmp2 exist = 1 or isdir = 1 drop table #filetmp2 end select name [name], isfile [isfile] #filetmpfin order [isfile] asc,[name] asc drop table #filetmpfin
Comments
Post a Comment