Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

error on t-sql script

Posted on 2016-08-24
3
Medium Priority
?
34 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

719 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