Click here to Skip to main content
15,892,746 members

SqlServer Database Backup

Revision 3
I want to take backup of my server database into my local system.so i have used the following sql query
SQL
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 20:14pm by shibashish mohanty.
Tags: