January 23, 2017 at 2:15 am
Hi everybody,
I have a script to restore a database with move. I do not have a database yet for this backup I want to restore so I want to create it with the restore. I keep getting a "RESTORE DATABASE is terminating abnormally." message. Can someone please help!
RESTORE DATABASE N'MyDb' FROM DISK = N'C:\SomeFolder\SomeBackup.bak'
WITH FILE = 1,
MOVE N'MyDb_dat' TO N'C:\SomeFolder\MyDb.mdf',
MOVE N'MyDb_log' TO N'C:\SomeFolder\MyDb_log.ldf,
NOUNLOAD, NORECOVERY
:-PManie Verster
Developer
Johannesburg
South Africa
I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
January 23, 2017 at 2:25 am
Manie Verster - Monday, January 23, 2017 2:15 AMHi everybody,
I have a script to restore a database with move. I do not have a database yet for this backup I want to restore so I want to create it with the restore. I keep getting a "RESTORE DATABASE is terminating abnormally." message. Can someone please help!
RESTORE DATABASE N'MyDb' FROM DISK = N'C:\SomeFolder\SomeBackup.bak'
WITH FILE = 1,
MOVE N'MyDb_dat' TO N'C:\SomeFolder\MyDb.mdf',
MOVE N'MyDb_log' TO N'C:\SomeFolder\MyDb_log.ldf,
NOUNLOAD, NORECOVERY
Can you post the full error message please?
😎
First thought would be that either the "MyDB" or the actual files ( C:\SomeFolder\MyDb.mdf or C:\SomeFolder\MyDb_log.ldf ) already exists on the server, another possibility would be that the path C:\SomeFolder doesn't exist.
January 23, 2017 at 2:27 am
Is the backup file good ?
Can you try
RESTORE VERIFYONLY FROM DISK = N'C:\SomeFolder\SomeBackup.bak'?
January 23, 2017 at 2:29 am
Eirikur Eiriksson - Monday, January 23, 2017 2:25 AMManie Verster - Monday, January 23, 2017 2:15 AMHi everybody,
I have a script to restore a database with move. I do not have a database yet for this backup I want to restore so I want to create it with the restore. I keep getting a "RESTORE DATABASE is terminating abnormally." message. Can someone please help!
RESTORE DATABASE N'MyDb' FROM DISK = N'C:\SomeFolder\SomeBackup.bak'
WITH FILE = 1,
MOVE N'MyDb_dat' TO N'C:\SomeFolder\MyDb.mdf',
MOVE N'MyDb_log' TO N'C:\SomeFolder\MyDb_log.ldf,
NOUNLOAD, NORECOVERYCan you post the full error message please?
😎First thought would be that either the "MyDB" or the actual files ( C:\SomeFolder\MyDb.mdf or C:\SomeFolder\MyDb_log.ldf ) already exists on the server, another possibility would be that the path C:\SomeFolder doesn't exist.
Msg 50000, Level 16, State 1, Line 74
RESTORE DATABASE is terminating abnormally.
:-PManie Verster
Developer
Johannesburg
South Africa
I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
January 23, 2017 at 2:31 am
joeroshan - Monday, January 23, 2017 2:27 AMIs the backup file good ?
Can you try
RESTORE VERIFYONLY FROM DISK = N'C:\SomeFolder\SomeBackup.bak'?
I will, thank you. Keep you posted.
:-PManie Verster
Developer
Johannesburg
South Africa
I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
January 23, 2017 at 2:36 am
Quick question, is there enough free space on the drive for the database?
😎
January 23, 2017 at 2:43 am
Eirikur Eiriksson - Monday, January 23, 2017 2:36 AMQuick question, is there enough free space on the drive for the database?
😎
Yes, way enough!
:-PManie Verster
Developer
Johannesburg
South Africa
I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
January 23, 2017 at 2:51 am
joeroshan - Monday, January 23, 2017 2:27 AMIs the backup file good ?
Can you try
RESTORE VERIFYONLY FROM DISK = N'C:\SomeFolder\SomeBackup.bak'?
I did run the verify script and got the following message back:
Attempting to restore this backup may encounter storage space problems. Subsequent messages will provide details.
The path specified by "C:\SQL\MSSQL11.ORISYS\MSSQL\DATA\MyDb.mdf" is not in a valid directory.
Directory lookup for the file "C:\SQL\MSSQL11.ORISYS\MSSQL\DATA\MyDb_log.ldf" failed with the operating system error 3(The system cannot find the path specified.).
The backup set on file 1 is valid.
I do not understand the concern for storage space. I have enough disk space but maybe it refers to something else? I did copy the backup from the server hence the path not found. This is the first time I am actually restoring a database I T-SQL but I have done this many times in SSMS. Why would it be different here?
:-PManie Verster
Developer
Johannesburg
South Africa
I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
January 23, 2017 at 7:40 am
Manie Verster - Monday, January 23, 2017 2:51 AMjoeroshan - Monday, January 23, 2017 2:27 AMIs the backup file good ?
Can you try
RESTORE VERIFYONLY FROM DISK = N'C:\SomeFolder\SomeBackup.bak'?I did run the verify script and got the following message back:
Attempting to restore this backup may encounter storage space problems. Subsequent messages will provide details.
The path specified by "C:\SQL\MSSQL11.ORISYS\MSSQL\DATA\MyDb.mdf" is not in a valid directory.
Directory lookup for the file "C:\SQL\MSSQL11.ORISYS\MSSQL\DATA\MyDb_log.ldf" failed with the operating system error 3(The system cannot find the path specified.).
The backup set on file 1 is valid.
I do not understand the concern for storage space. I have enough disk space but maybe it refers to something else? I did copy the backup from the server hence the path not found. This is the first time I am actually restoring a database I T-SQL but I have done this many times in SSMS. Why would it be different here?
Add the move clause to the restore, otherwise the original paths are used.
😎
RESTORE VERIFYONLY
FROM DISK = N'C:\SomeFolder\SomeBackup.bak'
WITH FILE = 1,
MOVE N'MyDb_dat' TO N'C:\SomeFolder\MyDb.mdf',
MOVE N'MyDb_log' TO N'C:\SomeFolder\MyDb_log.ldf'
January 24, 2017 at 12:05 am
Eirikur Eiriksson - Monday, January 23, 2017 7:40 AMManie Verster - Monday, January 23, 2017 2:51 AMjoeroshan - Monday, January 23, 2017 2:27 AMIs the backup file good ?
Can you try
RESTORE VERIFYONLY FROM DISK = N'C:\SomeFolder\SomeBackup.bak'?I did run the verify script and got the following message back:
Attempting to restore this backup may encounter storage space problems. Subsequent messages will provide details.
The path specified by "C:\SQL\MSSQL11.ORISYS\MSSQL\DATA\MyDb.mdf" is not in a valid directory.
Directory lookup for the file "C:\SQL\MSSQL11.ORISYS\MSSQL\DATA\MyDb_log.ldf" failed with the operating system error 3(The system cannot find the path specified.).
The backup set on file 1 is valid.
I do not understand the concern for storage space. I have enough disk space but maybe it refers to something else? I did copy the backup from the server hence the path not found. This is the first time I am actually restoring a database I T-SQL but I have done this many times in SSMS. Why would it be different here?
Add the move clause to the restore, otherwise the original paths are used.
😎
RESTORE VERIFYONLY
FROM DISK = N'C:\SomeFolder\SomeBackup.bak'
WITH FILE = 1,
MOVE N'MyDb_dat' TO N'C:\SomeFolder\MyDb.mdf',
MOVE N'MyDb_log' TO N'C:\SomeFolder\MyDb_log.ldf'
Thank you for this. I did run it and the message: The backup set on file 1 is valid. Sadly though, my restore still give me the same error. Eirikur, would you mind if I sent you an attachment in a private message?
:-PManie Verster
Developer
Johannesburg
South Africa
I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
January 24, 2017 at 1:18 am
Manie Verster - Tuesday, January 24, 2017 12:05 AMEirikur Eiriksson - Monday, January 23, 2017 7:40 AMManie Verster - Monday, January 23, 2017 2:51 AMjoeroshan - Monday, January 23, 2017 2:27 AMIs the backup file good ?
Can you try
RESTORE VERIFYONLY FROM DISK = N'C:\SomeFolder\SomeBackup.bak'?I did run the verify script and got the following message back:
Attempting to restore this backup may encounter storage space problems. Subsequent messages will provide details.
The path specified by "C:\SQL\MSSQL11.ORISYS\MSSQL\DATA\MyDb.mdf" is not in a valid directory.
Directory lookup for the file "C:\SQL\MSSQL11.ORISYS\MSSQL\DATA\MyDb_log.ldf" failed with the operating system error 3(The system cannot find the path specified.).
The backup set on file 1 is valid.
I do not understand the concern for storage space. I have enough disk space but maybe it refers to something else? I did copy the backup from the server hence the path not found. This is the first time I am actually restoring a database I T-SQL but I have done this many times in SSMS. Why would it be different here?
Add the move clause to the restore, otherwise the original paths are used.
😎
RESTORE VERIFYONLY
FROM DISK = N'C:\SomeFolder\SomeBackup.bak'
WITH FILE = 1,
MOVE N'MyDb_dat' TO N'C:\SomeFolder\MyDb.mdf',
MOVE N'MyDb_log' TO N'C:\SomeFolder\MyDb_log.ldf'Thank you for this. I did run it and the message: The backup set on file 1 is valid. Sadly though, my restore still give me the same error. Eirikur, would you mind if I sent you an attachment in a private message?
No problem
😎
January 26, 2017 at 6:39 am
--This is basically a copy of my original t-sql file that I just changed to remove real names. I am not sure if I declared
--all the variables correctly. If you can just check it because if I do not use variables but hardcoding in the restore script
--it works fine.
--I have a plan to create a stored proc to do a point in time restore but must still do the rest. If I can just come past this part
DECLARE @basepath nvarchar(1000) -- parameter in sp
DECLARE @fullbak nvarchar(4000), @errmes nvarchar(4000)
DECLARE @datalogicname sysname = N'MyDb_dat' -- parameter in sp
DECLARE @loglogicname sysname = N'MyDb_log' --
DECLARE @dbname sysname = N'MyDb' -- parameter in sp
DECLARE @dblogname sysname = N'MyDb_log' -- parameter in sp
DECLARE @dbdatafile nvarchar(1000) = N'C:\DB\MyDb.mdf' -- parameter in sp
DECLARE @dblogfile nvarchar(1000) = N'C:\DB\MyDb.ldf' -- parameter in sp
BEGIN TRANSACTION
BEGIN TRY
--these sets will be replaced with actual stored proc parameters
SET @basepath = N'C:\DB\Backup\'
SET @fullbak = @basepath+N'MyDb_backup_2017_01_18_170005_1286319.bak'
SET @datalogicname = N'MyDb_dat'
SET @loglogicname = N'MyDb_log'
SET @dbname = N'MyDb'
SET @dblogname = N'MyDb_log'
SET @dbdatafile = N'C:\DB\MyDb.mdf'
SET @dblogfile = N'C:\DB\MyDb.ldf'
RESTORE DATABASE @dbname FROM DISK = @fullbak
WITH
FILE = 1,
MOVE @datalogicname TO @dbdatafile,
MOVE @loglogicname TO @dblogfile,
NORECOVERY
--RESTORE VERIFYONLY
--FROM DISK = @fullbak
--WITH FILE = 1,
--MOVE @datalogicname TO @dbdatafile,
--MOVE @loglogicname TO @dblogfile
END TRY
BEGIN CATCH
set @errmes = 'Failed: ' + ERROR_MESSAGE()
RAISERROR(@errmes,16,1)
ROLLBACK TRANSACTION
RETURN
END CATCH
COMMIT TRANSACTION
I will appreciate any help I can get and will mention anyone that helped in the code.
:-PManie Verster
Developer
Johannesburg
South Africa
I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
January 26, 2017 at 9:38 am
Manie Verster - Monday, January 23, 2017 2:51 AMjoeroshan - Monday, January 23, 2017 2:27 AMIs the backup file good ?
Can you try
RESTORE VERIFYONLY FROM DISK = N'C:\SomeFolder\SomeBackup.bak'?I did run the verify script and got the following message back:
Attempting to restore this backup may encounter storage space problems. Subsequent messages will provide details.
The path specified by "C:\SQL\MSSQL11.ORISYS\MSSQL\DATA\MyDb.mdf" is not in a valid directory.
Directory lookup for the file "C:\SQL\MSSQL11.ORISYS\MSSQL\DATA\MyDb_log.ldf" failed with the operating system error 3(The system cannot find the path specified.).
The backup set on file 1 is valid.
I do not understand the concern for storage space. I have enough disk space but maybe it refers to something else? I did copy the backup from the server hence the path not found. This is the first time I am actually restoring a database I T-SQL but I have done this many times in SSMS. Why would it be different here?
I think the issue isn't space....I believe that may just be a warning for you to check based on the size of the backup file. More concerning is the actual error. The system cannot find the path specified.
That can mean one of two things (that I can think of at least).
1. The path is wrong. Check this by opening a Run window (Start>Run) and typing in the path (with double slants \\ before the path). If it can't open it, there's your issue.
2. Permissions. It is possible the process doesn't have the proper permissions to access the path. I would expect an error showing Access Denied, but sometimes I have seen the "cannot find path specified" if there is a permissions issue.
Even if you can restore a database using SSMS's GUI, doesn't mean it will work via code. You could have introduced a typo when you typed the path. Also, SSMS does the restore using a different login (I believe it uses the service account that SQL Agent runs as), whereas running it by code it is running with your access.
-SQLBill
January 29, 2017 at 11:54 pm
SQLBill - Thursday, January 26, 2017 9:38 AMManie Verster - Monday, January 23, 2017 2:51 AMI did run the verify script and got the following message back:
Attempting to restore this backup may encounter storage space problems. Subsequent messages will provide details.
The path specified by "C:\SQL\MSSQL11.ORISYS\MSSQL\DATA\MyDb.mdf" is not in a valid directory.
Directory lookup for the file "C:\SQL\MSSQL11.ORISYS\MSSQL\DATA\MyDb_log.ldf" failed with the operating system error 3(The system cannot find the path specified.).
The backup set on file 1 is valid.
I do not understand the concern for storage space. I have enough disk space but maybe it refers to something else? I did copy the backup from the server hence the path not found. This is the first time I am actually restoring a database I T-SQL but I have done this many times in SSMS. Why would it be different here?
I think the issue isn't space....I believe that may just be a warning for you to check based on the size of the backup file. More concerning is the actual error. The system cannot find the path specified.
That can mean one of two things (that I can think of at least).
1. The path is wrong. Check this by opening a Run window (Start>Run) and typing in the path (with double slants \\ before the path). If it can't open it, there's your issue.
2. Permissions. It is possible the process doesn't have the proper permissions to access the path. I would expect an error showing Access Denied, but sometimes I have seen the "cannot find path specified" if there is a permissions issue.Even if you can restore a database using SSMS's GUI, doesn't mean it will work via code. You could have introduced a typo when you typed the path. Also, SSMS does the restore using a different login (I believe it uses the service account that SQL Agent runs as), whereas running it by code it is running with your access.
-SQLBill
SQL Bill,
First of all thank you for your post. The error message you saw was from my first restore verify only but I was helped right by Eirikur and told to use the with move and then my backup was verified good. I thought at first it might be access rights but I hardcoded everything and then ran the script and everything was fine. My path is definitely correct as well al the backup file name. I checked everything and could not find a fault but I thought maybe I did not declare my variables correctly meaning the data types. The other thing is the backup was made on the server where the original database also is and I am trying to restore it to my local computer.
Manie Verster
Developer
Johannesburg
South Africa
I am happy because I choose to be happy.
I just love my job!!!
January 31, 2017 at 1:29 am
Manie Verster - Thursday, January 26, 2017 6:39 AMHere is my script as I created it with variables. This, as I said before, is a learning curve for me and maybe I am trying to reinvent the wheel here but I am trying.
--This is basically a copy of my original t-sql file that I just changed to remove real names. I am not sure if I declared
--all the variables correctly. If you can just check it because if I do not use variables but hardcoding in the restore script
--it works fine.--I have a plan to create a stored proc to do a point in time restore but must still do the rest. If I can just come past this part
DECLARE @basepath nvarchar(1000) -- parameter in sp
DECLARE @fullbak nvarchar(4000), @errmes nvarchar(4000)
DECLARE @datalogicname sysname = N'MyDb_dat' -- parameter in sp
DECLARE @loglogicname sysname = N'MyDb_log' --
DECLARE @dbname sysname = N'MyDb' -- parameter in sp
DECLARE @dblogname sysname = N'MyDb_log' -- parameter in sp
DECLARE @dbdatafile nvarchar(1000) = N'C:\DB\MyDb.mdf' -- parameter in sp
DECLARE @dblogfile nvarchar(1000) = N'C:\DB\MyDb.ldf' -- parameter in spBEGIN TRANSACTION
BEGIN TRY
--these sets will be replaced with actual stored proc parameters
SET @basepath = N'C:\DB\Backup\'
SET @fullbak = @basepath+N'MyDb_backup_2017_01_18_170005_1286319.bak'
SET @datalogicname = N'MyDb_dat'
SET @loglogicname = N'MyDb_log'
SET @dbname = N'MyDb'
SET @dblogname = N'MyDb_log'
SET @dbdatafile = N'C:\DB\MyDb.mdf'
SET @dblogfile = N'C:\DB\MyDb.ldf'RESTORE DATABASE @dbname FROM DISK = @fullbak
WITH
FILE = 1,
MOVE @datalogicname TO @dbdatafile,
MOVE @loglogicname TO @dblogfile,
NORECOVERY--RESTORE VERIFYONLY
--FROM DISK = @fullbak
--WITH FILE = 1,
--MOVE @datalogicname TO @dbdatafile,
--MOVE @loglogicname TO @dblogfileEND TRY
BEGIN CATCH
set @errmes = 'Failed: ' + ERROR_MESSAGE()
RAISERROR(@errmes,16,1)
ROLLBACK TRANSACTION
RETURN
END CATCH
COMMIT TRANSACTIONI will appreciate any help I can get and will mention anyone that helped in the code.
Hi guys,
I found the answer and just want to say thank you for those who tried to assist. A restore cannot be done within a transaction so the begin, rollback and commit transaction statements must be removed. I am currently running my script and it is going!
:-PManie Verster
Developer
Johannesburg
South Africa
I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply