How Do I Backup Database Using Sql Statement As .Bak File On Hard Disk ?

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

i want to do backup for database as .bak file where i want the file on hard disk i used this code n the output is succsefull but i didnt find any file on the path:
use try2

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 = 'd:\Backup\' 
set @name='try2'
 
-- specify filename format
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) + '_' + REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),':','')


SET @fileName = @path + @name + '_' + @fileDate + '.BAK'  
       BACKUP DATABASE @name TO DISK = @fileName  --WITH  RETAINDAYS = 30, NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD,  STATS = 10

--

the result is

Processed 304 pages for database 'try2', file 'Council_Ministers_Meetings' on file 1.
Processed 1 pages for database 'try2', file 'Council_Ministers_Meetings_log' on file 1.
BACKUP DATABASE successfully processed 305 pages in 0.344 seconds (6.926 MB/sec).

note : Council_Ministers_Meetings is database name i didnt use where i want to do backup for try2 database

Posted 3-Aug-14 22:24pm

zainab77

Updated 3-Aug-14 22:27pm

CPallini

v2

Add a Solution

Comments

Dilan Shaminda 4-Aug-14 4:37am

it works fine for me. i tested it with my test DB. and the result is

Processed 248 pages for database 'test', file 'test' on file 1.
Processed 1 pages for database 'test', file 'test_log' on file 1.
BACKUP DATABASE successfully processed 249 pages in 0.270 seconds (7.204 MB/sec).

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

OriginalGriff · Answer 1 · 2014-08-03T22:42:00

Check your permissions, and that the instance of SQL server is on a machine that has a D: drive.

When I run that code here, on my development SLQ 2008R2 the file is created ok - so you need to look at disk related reasons why it isn't there. If you server is on a machine with a D: drive, then look here: Backing up an SQL Database in C#[^] under "So, It'll Work Now?" and check the access permissions for teh folder / user combination.

zainab77 · Answer 2 · 2014-08-05T00:52:00

Solution 2

it works after i checked my permission as OriginalGriff said
many great thanx

Posted 5-Aug-14 0:52am