June 19, 2015 at 6:30 am
Ok I am writing an automated process that moves a database to a link server and restores it. This could happen multiple times on a single instance so I need to change the name of the DB and its physical files. The code here is a mock-up of the restore. It works! The database shows up and seems to query ok. I don't SEE anything missing. however I get a success message and a failure message. Any clues would be helpful:
Here is the code:
USE master
GO
DECLARE @RestoreStatement as nvarchar(4000)
SET @RestoreStatement = 'RESTORE DATABASE ETL_New3 FROM DISK = '
SET @RestoreStatement = @RestoreStatement + '''\\adas16.clients.advance.local\wip$\ETL_780.bak'''
SET @RestoreStatement = @RestoreStatement + ' WITH RECOVERY, MOVE '
SET @RestoreStatement = @RestoreStatement + '''JWETL'''
SET @RestoreStatement = @RestoreStatement + ' TO '
SET @RestoreStatement = @RestoreStatement + '''G:\MSSQL11.MSSQLSERVER\MSSQL\Data\JWETLnew3.mdf'''
SET @RestoreStatement = @RestoreStatement + ', MOVE '
SET @RestoreStatement = @RestoreStatement + '''JWETL_log'''
SET @RestoreStatement = @RestoreStatement + ' TO '
SET @RestoreStatement = @RestoreStatement + '''L:\MSSQL11.MSSQLSERVER\MSSQL\Data\JWETL_lognew3.ldf'''
PRINT cast(@RestoreStatement as varchar(1000))
EXEC adasdb.master.sys.sp_executesql @RestoreStatement
Now as I said the DB shows up on this other server and seems to be healthy. Here is the output message I get in SSMS:
RESTORE DATABASE ETL_New3 FROM DISK = '\\adas16.clients.advance.local\wip$\ETL_780.bak' WITH RECOVERY, MOVE 'JWETL' TO 'G:\MSSQL11.MSSQLSERVER\MSSQL\Data\JWETLnew3.mdf', MOVE 'JWETL_log' TO 'L:\MSSQL11.MSSQLSERVER\MSSQL\Data\JWETL_lognew3.ldf'
Processed 69928 pages for database 'ETL_New3', file 'JWETL' on file 1.
Processed 3 pages for database 'ETL_New3', file 'JWETL_log' on file 1.
RESTORE DATABASE successfully processed 69931 pages in 1.740 seconds (313.983 MB/sec).
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Msg 3159, Level 16, State 1, Line 1
The tail of the log for the database "ETL_New3" has not been backed up. Use BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE statement to just overwrite the contents of the log.
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
June 19, 2015 at 6:50 am
I like your tag-line for one.
Secondly what other information would help you help me? I explained the issue, supplied my code and the output.
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
June 19, 2015 at 6:53 am
Jeffery Williams (6/19/2015)
I explained the issue, supplied my code and the output.
Did you read the link he provided? The one in his post, not the ones that are part of his signature.
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
June 19, 2015 at 7:01 am
I just read it. And basically my question is already in this format. My question I believe is pretty detailed. I tell what I'm trying to do, the code I've used to accomplish my task, the result that I get from the code and the bizarre message received from SQL, or conflicting I should say.
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
June 19, 2015 at 7:04 am
Jeffery Williams (6/19/2015)
I just read it. And basically my question is already in this format.
Err... The link in his post, not the one in his signature
Phil Parkin (6/19/2015)
A brief search suggested >this<.
I don't know why you're getting a failure then a success, but the link Phil provided does explain why that error comes up.
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
June 19, 2015 at 7:08 am
You have been given the answer to your problem in both the error message you are getting, and in the post that Phil provided.
Use WITH REPLACE in your restore statement.
To see how that may work, here is the MSDN link for for the RESTORE command:
https://msdn.microsoft.com/en-us/library/ms186858.aspx
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
June 19, 2015 at 7:09 am
Good grief guys! Look I pulled a late one, running on about two hours sleep. You know i didn't even see the link) until now). Time for more coffee!
And thanks I'll try this.
(shaking head in disbelief)
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
June 19, 2015 at 7:11 am
Jeffery Williams (6/19/2015)
Time for more coffee!
Always!
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply