Restore database by way of TSQL call to a linkserver database

Question

Restore database by way of TSQL call to a linkserver database

Jeffery Williams

SSCertifiable

Points: 6441
More actions
June 10, 2015 at 9:11 am

#298817

Ok This must be done in tsql, it is for a large overall project and this is a piece of it. So far I take a database on server A and back it up, I move the backup to a commonly accessible path and from there to a local drive path on the destination server.
From this point I need to make a call from Server A to restore the database on Server B. This will be when it is all said and done dynamic sql because server B could be any number of servers. For this example we will pretend that everything is statis.
I am running this:
EXEC sp_executesql ('RESTORE DATABASE ETLNewTest FROM DISK = ''g:\MSSQL11.MSSQLSERVER\MSSQL\ETL_663.bak''') AT [adasdb];
And getting this result:
Incorrect syntax near 'RESTORE DATABASE ETLNewTest FROM DISK g:\MSSQL11.MSSQLSERVER\MSSQL\ETL_663.bak'.
Can anyone help?
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

Try this:

EXEC adasdb.master.sys.sp_executesql 'RESTORE DATABASE ETLNewTest FROM DISK = ''g:\MSSQL11.MSSQLSERVER\MSSQL\ETL_663.bak''';

Any difference?

-- Gianluca Sartori

Jeffery Williams SSCertifiable Points: 6441 More actions · Answer 2

Good idea, when I read it...

I run this:

EXEC adasdb.master.sys.sp_executesql 'RESTORE DATABASE ETLNewTest FROM DISK = ''g:\MSSQL11.MSSQLSERVER\MSSQL\ETL_663.bak''';

Get this:

Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.

<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh

Jeffery Williams SSCertifiable Points: 6441 More actions · Answer 3

Thank you so much. You got me there. Took that error, parameterized the command and it worked.

Here is what I ran:

DECLARE @sql as nvarchar(4000)

SET @sql = 'RESTORE DATABASE ETLNewTest FROM DISK = ''g:\MSSQL11.MSSQLSERVER\MSSQL\ETL_663.bak'''

EXEC adasdb.master.sys.sp_executesql @sql

<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Or you could just

EXEC adasdb.master.sys.sp_executesql N'RESTORE DATABASE ETLNewTest FROM DISK = ''g:\MSSQL11.MSSQLSERVER\MSSQL\ETL_663.bak''';

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ed Wagner SSC Guru Points: 287026 More actions · Answer 5

Do you see the difference in what Gail did?

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

spaghettidba SSC Guru Points: 105732 More actions · Answer 6

Ed Wagner (6/10/2015)
Do you see the difference in what Gail did?

I do: she fixed my mistake 🙂

-- Gianluca Sartori

Jeffery Williams SSCertifiable Points: 6441 More actions · Answer 7

LOL. OR you could add the N..

Thanks Gail.

<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh