February 13, 2017 at 5:07 am
Hello,
My predecessor left this bit of code in an SSIS package and I can't understand how one restores a database using a text file.
It all works fine magically. The file exists with zero bytes. And the process will fail as it's meant to on the filename (don't delete this file).
RESTORE DATABASE [TESTDB]
FROM DISK = N'D:\SQL_Backups\DoNotDeleteThisFile.txt' WITH FILE = 1, MOVE N'DataFiles' TO N'D:\SQL_Data\TESTDB.mdf', MOVE N'LogFiles' TO N'D:\SQL_Logs\TESTDB.ldf', NOUNLOAD, REPLACE, STATS = 10
Any thoughts?
Thank you,
Vin
February 13, 2017 at 5:17 am
My guess is that the application / package renames the actual backup to DoNotDeleteThisFile.txt
😎
February 13, 2017 at 5:32 am
Vin
I can't reconcile your two statements "It all works fine magically" and "the process will fail as it's meant to". Why would anyone deliberately set up a restore operation to fail? Crucially, a database backup file doesn't have to have the extension .BAK - it can have any (or no) extension. But if the file is 0 bytes, I would expect the operation to fail with an error saying that the volume on the device is empty.
John
February 13, 2017 at 5:37 am
John Mitchell-245523 - Monday, February 13, 2017 5:32 AMVinI can't reconcile your two statements "It all works fine magically" and "the process will fail as it's meant to". Why would anyone deliberately set up a restore operation to fail? Crucially, a database backup file doesn't have to have the extension .BAK - it can have any (or no) extension. But if the file is 0 bytes, I would expect the operation to fail with an error saying that the volume on the device is empty.
John
John,
What I meant was, if I removed the said .txt file, then the SSIS package will fail. So it needs the empty text file there.
The Restore statement has got the line "FROM DISK = N'D:\SQL_Backups\DoNotDeleteThisFile.txt'"
Vin
February 13, 2017 at 5:42 am
Eirikur Eiriksson - Monday, February 13, 2017 5:17 AMMy guess is that the application / package renames the actual backup to DoNotDeleteThisFile.txt
😎
Eirikur,
If it's a variable or a parameter I would see a placeholder for that and a SSIS script which would replace the filename to the 'bak' file.
But there isn't anything like that. It's a direct Execute SQL Task under the SSIS package.
But you could be right, somewhere it replaces DoNotDeleteThisFile to something appropiate. Where does it do, I have no idea?
Vin
February 13, 2017 at 5:56 am
This topic is in the wrong section. I forgot to mention the database is on SQL 2008 R2.
I have asked webmaster to move the topic.
Thank you,
February 13, 2017 at 6:11 am
datsun - Monday, February 13, 2017 5:56 AMThis topic is in the wrong section. I forgot to mention the database is on SQL 2008 R2.I have asked webmaster to move the topic.
Thank you,
If you remove the text file and the job fails, what is the text of the error message you receive?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 13, 2017 at 10:42 am
Phil Parkin - Monday, February 13, 2017 6:11 AMdatsun - Monday, February 13, 2017 5:56 AMThis topic is in the wrong section. I forgot to mention the database is on SQL 2008 R2.I have asked webmaster to move the topic.
Thank you,
If you remove the text file and the job fails, what is the text of the error message you receive?
Phil,
First time it failed and I didn't check the SSIS log file.
Now when I removed the DoNotDeleteThisFile,txt blank file from the folder (source and destination) , it still continued to do Restore.
I think probably the blank file "DoNotDeleteThisFile.txt" doesn't serve any purpose in Restore Database, other than replacing the name with the source "..bak" file.
(which I have yet to find out where the replacement happens).
There must be a reason why the blank file exists in the first place.
Vin
February 15, 2017 at 6:48 am
The thing is, a backup file doesn't have to have any particular extension. You can run a command to backup to a .DOC file or .TXT or any other thing you want. Someone, for some obscure reason, chose to backup to a .TXT file. Not sure why you would do this, but that's what was done.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply