Solved

error on t-sql script

Posted on 2016-08-24
3
21 Views
Last Modified: 2016-08-25
Hello,

I try to build a backup command but the default backup directory is specified :
DECLARE @FILE VARCHAR(65)
DECLARE @DUMPFILE VARCHAR(150)

SELECT CONVERT(VARCHAR(10),GETDATE(),112)
SELECT CONVERT(VARCHAR,GETDATE(),108)
SELECT REPLACE(CONVERT(VARCHAR,GETDATE(),108),':','')

SET @FILE = CONVERT(VARCHAR(10),GETDATE(),112) + '_' + REPLACE(CONVERT(VARCHAR,GETDATE(),108),':','')
SELECT @FILE
SET @DUMPFILE = '''' + 'd:\backup' + @FILE + '.bak' + ''''
SELECT @DUMPFILE



BACKUP database model 
to disk=@DUMPFILE
with compression, copy_only

Open in new window


DECLARE @FILE VARCHAR(65)
DECLARE @DUMPFILE VARCHAR(150)

SELECT CONVERT(VARCHAR(10),GETDATE(),112)
SELECT CONVERT(VARCHAR,GETDATE(),108)
SELECT REPLACE(CONVERT(VARCHAR,GETDATE(),108),':','')

SET @FILE = CONVERT(VARCHAR(10),GETDATE(),112) + '_' + REPLACE(CONVERT(VARCHAR,GETDATE(),108),':','')
SELECT @FILE
SET @DUMPFILE = '''' + 'd:\backup' + @FILE + '.bak' + ''''
SELECT @DUMPFILE



BACKUP database test
to disk=@DUMPFILE
with compression, copy_only

Msg 3201, Level 16, State 1, Line 15
Cannot open backup device 'E:\MSSQLSQL01\BACKUP\'d:\backup20160824_180424.bak''. Operating system error 123(failed to retrieve text for this error. Reason: 15105).
Msg 3013, Level 16, State 1, Line 15
BACKUP DATABASE is terminating abnormally.


Why?

Thanks

Regards
0
Comment
Question by:bibi92
  • 2
3 Comments
 
LVL 1

Expert Comment

by:Helen Ramsden
ID: 41769113
Hi

Please could you try removing two apostrophes from the beginning and end. e.g.

SET @DUMPFILE = '' + 'd:\backup' + @FILE + '.bak' + ''

Thanks,
Helen
0
 
LVL 1

Accepted Solution

by:
Helen Ramsden earned 500 total points
ID: 41769142
Also, if you want the backup to be saved to the backup folder, add an oblique to the directory e.g.

SET @DUMPFILE = '' + 'd:\backup\' + @FILE + '.bak' + ''
0
 

Author Comment

by:bibi92
ID: 41771313
Thanks
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question