Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-Server
I want to take backup of my server database into my local system.so i have used the following sql query
DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name
 
 
-- specify database backup directory
SET @path = 'C:\Backup\'
 
 
-- specify filename format
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
 
 
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name IN ('Sutra') -- exclude these databases
 
 
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
 
 
WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
BACKUP DATABASE @name TO DISK = @fileName
 
 
FETCH NEXT FROM db_cursor INTO @name
END
 
 
CLOSE db_cursor
DEALLOCATE db_cursor
 
i have created the Backup folder in C: drive.but it returns the following error

Msg 3201, Level 16, State 1, Line 28
Cannot open backup device 'C:\Backup\Sutra_20121116.BAK'. Operating system error 3(The system cannot find the path specified.).
Msg 3013, Level 16, State 1, Line 28
BACKUP DATABASE is terminating abnormally.

Please solve it.i have tried with different location.still it is not working.please help me.Thanks in advance
shibashish mohanty
Posted 15-Nov-12 21:14pm
Edited 15-Nov-12 23:29pm
v3
Comments
Tadit Dash at 16-Nov-12 3:46am
   
Have you tried by placing the backup directory in other drives ?
Is it working ?

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Backup drive must exits on sql server. If you want to take a backup on local hard-drive than you have to create a map drive on server with your network shared folder.
 
Thanks
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 195
1 ProgramFOX 130
2 Maciej Los 105
3 Afzaal Ahmad Zeeshan 82
4 Sergey Alexandrovich Kryukov 75
0 OriginalGriff 6,564
1 Sergey Alexandrovich Kryukov 6,048
2 DamithSL 5,228
3 Manas Bhardwaj 4,717
4 Maciej Los 4,150


Advertise | Privacy | Mobile
Web04 | 2.8.1411022.1 | Last Updated 16 Nov 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100