Solved

error on t-sql script

Posted on 2016-08-24
3
24 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql query 8 51
Trying to get a Linked Server to Oracle DB working 21 69
Re-appearing SQL Server Agent jobs 7 30
query optimization 6 15
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

828 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