UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Pass - Through Query To Linked SQL Database, Access 2016    
 
   
marinko888
post May 22 2019, 08:04 AM
Post#1



Posts: 58
Joined: 9-April 19



Hi,
I have made a pass-through Query for SQL Database Backup file succesfully but I have a problem with Restore Query. I am passing 5 Parameters with Query:

CODE
EXEC [dbo].[DBRestore] 'Farm21052019184404','Farm','C:\Backup\','C:\Data\','F:\Data\'


to a SQL Procedure:

CODE
USE [Farm]
GO

CREATE PROCEDURE [dbo].[DBRestore] (@name VARCHAR(MAX), @DirectoryData VARCHAR(MAX),@DirectoryLog VARCHAR(MAX),@OldDBName VARCHAR(MAX),@FileNameOrgBackup VARCHAR(MAX))  
AS

BEGIN
DECLARE @PathOrgignalBackup VARCHAR(MAX) -- Path of backup file to restore

SET @FileNameOrgBackup = @PathOrgignalBackup+@name+'.bak'

DECLARE @SQL VARCHAR(MAX)=
'RESTORE DATABASE ['+@name+'] FROM DISK =N'''+@FileNameOrgBackup+' WITH FILE= 1,
MOVE N'''+@name+''' TO N'''+@DirectoryData+@name+'.mdf'',
MOVE N'''+@name+'_log'' TO N'''+@DirectoryLog+@name+'.ldf'',
NOUNLOAD, REPLACE, STATS= 10'''

EXECUTE(@SQL)
END


and nothing happens. There is no error and Database is also not restored. What am I doing wrong?

Thanks for your answers!
This post has been edited by marinko888: May 22 2019, 08:45 AM
Go to the top of the page
 
jleach
post May 22 2019, 09:15 AM
Post#2


UtterAccess Editor
Posts: 10,011
Joined: 7-December 09
From: St Augustine, FL


Does your user that you're connecting with have restore permissions on the server?

--------------------
Go to the top of the page
 
marinko888
post May 22 2019, 09:28 AM
Post#3



Posts: 58
Joined: 9-April 19



I am using Trusted Connection. How can I grant restore permission on the server?
Go to the top of the page
 
jleach
post May 22 2019, 09:31 AM
Post#4


UtterAccess Editor
Posts: 10,011
Joined: 7-December 09
From: St Augustine, FL


You need to find out which user you're connected as (trusted connection (I think) logs you in using your Windows AD username), then log into the server and check the permissions on that user.

--------------------
Go to the top of the page
 
MadPiet
post May 22 2019, 09:32 AM
Post#5



Posts: 3,136
Joined: 27-February 09



Can't you use EXECUTE AS to do that?
Go to the top of the page
 
jleach
post May 22 2019, 09:33 AM
Post#6


UtterAccess Editor
Posts: 10,011
Joined: 7-December 09
From: St Augustine, FL


You can probably get away from building the dynamic SQL and using EXECUTE: you're using a passthrough, which you're essentially building by hand anyway (e.g., you're already dynamically building the whole thing), so no particular need to further complicate matters by executing yet another piece of dynamic SQL in the query itself. It could be a simple syntax error (extra/missing quote, etc) with the error being hidden due to the EXEC function.

Another option might to wrap in a TRY/CATCH and see if you can force error output.

--------------------
Go to the top of the page
 
marinko888
post May 22 2019, 10:00 AM
Post#7



Posts: 58
Joined: 9-April 19



I have granted permission to Create any database and nothing changes.. I am not sure that I can do TRY/CATCH here in Access Query:

Attached File  Capture.PNG ( 7.27K )Number of downloads: 0


That is something from VB.NET
Still searching for solution...
Go to the top of the page
 
GroverParkGeorge
post May 22 2019, 10:18 AM
Post#8


UA Admin
Posts: 35,177
Joined: 20-June 02
From: Newcastle, WA


I don't know if you've tried this yet, but one good trouble-shooting step might be to execute the stored procedure directly on the Server, via SSMS.

You'd have to supply the parameters to it in the Query Designer in SSMS. That ought to give you a better look at any errors returned.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
marinko888
post May 22 2019, 10:33 AM
Post#9



Posts: 58
Joined: 9-April 19



I have done suggested error checking via SSMS and I am getting this:

Attached File  Capture1.PNG ( 1.75K )Number of downloads: 0


and of course nothing has happened again. It must be some error in the Procedure but I don't know where is it.
Go to the top of the page
 
jleach
post May 22 2019, 10:41 AM
Post#10


UtterAccess Editor
Posts: 10,011
Joined: 7-December 09
From: St Augustine, FL


I meant TRY/CATCH in the query (it's a T-SQL construct).

Also, you have your own custom restore procedure you're calling (EXEC dbo.DBRestore - that's not the built-in one). Why not call RESTORE DATABASE directly? Perhaps something in the dbo.DBRestore procedure is failing.

>> Commands completed successfully <<

Is there something in dbo.DBRestore that is burying errors? Can you post that sproc for us? I think it may be indicating that it called your procedure correctly, but if your procedure is for some reason not returning errors, the caller may never know.

Also, how are you verifying the restore of this db (e.g., how do you know it didn't in fact restore as intended?)

--------------------
Go to the top of the page
 
cheekybuddha
post May 22 2019, 10:50 AM
Post#11


UtterAccess VIP
Posts: 11,329
Joined: 6-December 03
From: Telegraph Hill


@Jack,

>> Can you post that sproc for us? <<

It's in the OP.

d

--------------------


Regards,

David Marten
Go to the top of the page
 
marinko888
post May 22 2019, 11:03 AM
Post#12



Posts: 58
Joined: 9-April 19



I can make a valid Backup with Pass-Through Query so I am slightly changing Database Table and Restoring from that Backup. That's the way I can confirm that restoring fails. I will try with RESTORE DATABASE but still I have to costumize it due to Parameters I have to pass through.

This post has been edited by marinko888: May 22 2019, 11:23 AM
Go to the top of the page
 
marinko888
post May 22 2019, 11:57 AM
Post#13



Posts: 58
Joined: 9-April 19



I have succeded to restore Database from within SSMS:

CODE
ALTER DATABASE Farm
----Make Database to single user Mode
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE

----Restore Database
RESTORE DATABASE Farm
FROM DISK = 'C:\Backup\Farm21052019184404.bak'
WITH MOVE 'Farm_data' TO 'C:\Data\Farm21052019184404.mdf',
MOVE 'Farm_log' TO 'C:\Data\Farm21052019184404.ldf'

ALTER DATABASE [Farm] SET MULTI_USER
GO


Only have to make working pass-through query from MS Access front end. I hope this code will help someone...

Thank you for your help!
This post has been edited by marinko888: May 22 2019, 11:58 AM
Go to the top of the page
 
jleach
post May 22 2019, 07:48 PM
Post#14


UtterAccess Editor
Posts: 10,011
Joined: 7-December 09
From: St Augustine, FL


>> >> >> Can you post that sproc for us? <<

It's in the OP. <<

Oops, my bad, sorry.

At least it's working through SSMS now, which rules out a whole lot of potential issues.

Good luck,

--------------------
Go to the top of the page
 
marinko888
post May 23 2019, 06:20 AM
Post#15



Posts: 58
Joined: 9-April 19



Hi,
due to the complexity of the whole process, I would not recommend anyone to implement SQL Restore from MS Access front end, but if you really need it:

1. you have to make a pass-through Query which looks like:

CODE
EXEC [dbo].[DBRestore]


2. then you have to create the procedure in SQL Database on the master database to avoid potential issues:

CODE
USE [master]
GO
/****** Object:  StoredProcedure [dbo].[DBRestore]    Script Date: 23.5.2019. 10:45:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[DBRestore]
AS
BEGIN
ALTER DATABASE Farm
----Make Database to single user Mode
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE

----without this line you will get an error:
RESTORE FILELISTONLY FROM BackupDevice

----Restore Database
RESTORE DATABASE Farm
FROM DISK = 'C:\Backup\Farm_backup.bak'
WITH MOVE 'Farm' TO 'C:\Data\Farm_backup.mdf',
MOVE 'Farm_log' TO 'C:\Data\Farm_backup.ldf'

----Go back to multi user Mode
ALTER DATABASE [Farm] SET MULTI_USER
END


3. the part in the procedure - RESTORE FILELISTONLY - refers to device you have to make on your disk (otherwise you will get an error):

CODE
USE master;  
GO  
EXEC sp_addumpdevice 'disk', 'BackupDevice', 'C:\Backup\Farm_backup.bak';



4. then on the button CallBack you have to attach something like this:

CODE
DoCmd.SetWarnings False
DoCmd.OpenQuery "QueryDBRestore"
DoCmd.SetWarnings True


And that would be it. I hope that this will help somebody...
Thanks for the help you have provided so far!
This post has been edited by marinko888: May 23 2019, 06:35 AM
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    25th June 2019 - 10:54 PM