DIFFERENTIAL BACKUP - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

DIFFERENTIAL BACKUP

Author

Topic

GaneshRamanan
Starting Member

40 Posts

Posted - 2011-01-24 : 02:00:33

Hi,

I am taking Diff backup in express edition, when I am running this SP
executed properly but it doesnt take backup(.BAK). By the same Query I have taken Full Backup. But it doesnt work it only for DIFFERENTAIL.

ALTER PROCEDURE [dbo].[FULLBACKUP]
AS

declare @mybackup nvarchar(1000);

SET @MYBACKUP='D:BACKUP'+'HIS'+CONVERT(VARCHAR(16),GETDATE(),120)+'.BAK'

BACKUP DATABASE [HIS] TO DISK = @ WITH DIFFERENTIAL, NOFORMAT, NOINIT,
NAME = N'HIS Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

Thanks in Advance,
Ganesh

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-01-24 : 03:12:19

What do you mean 'doesn't work'?

Error?
Runs fine but no backup created?
Backup created but not restorable?
Something else?

--
Gail Shaw
SQL Server MVP

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-01-24 : 03:14:37

You might want to try

BACKUP DATABASE [HIS] TO DISK = @MYBACKUP WITH DIFFERENTIAL, NOINIT,
NAME = N'HIS Diff Database Backup', SKIP, STATS = 10

Since you're not using the variable declared.

--
Gail Shaw
SQL Server MVP

chris_cs
Posting Yak Master

223 Posts

Posted - 2011-01-24 : 06:17:06

The issue is probably the filename you're trying to create as you can't use "/" in the file name.

You'll need to use a replace to get rid of these. I use something like the following in my SP:



SET @BackupFileName = '\\PATH_' + REPLACE(REPLACE(REPLACE(CONVERT(varchar,GETDATE(), 20),'-',''),':',''),' ','') + '.BAK'

GaneshRamanan
Starting Member

40 Posts

Posted - 2011-01-24 : 11:46:56

quote:
Originally posted by GilaMonster

What do you mean 'doesn't work'?

Error?
Runs fine but no backup created?
Backup created but not restorable?
Something else?

--
Gail Shaw
SQL Server MVP

Yes , Backup runs fine but i couldnt Restore it.

In the specified path file has been created but i couldnt Restore it.
I dont know the format of the file it is not created as .bak

Thanks
Ganesh Ramanan

GaneshRamanan
Starting Member

40 Posts

Posted - 2011-01-24 : 13:10:15

Really it helps me.

Thanks much for your support.

Thanks one and all.

GaneshRamanan
Starting Member

40 Posts

Posted - 2011-01-24 : 13:12:47

quote:
Originally posted by chris_cs

The issue is probably the filename you're trying to create as you can't use "/" in the file name.

You'll need to use a replace to get rid of these. I use something like the following in my SP:

SET @BackupFileName = '\\PATH_' + REPLACE(REPLACE(REPLACE(CONVERT(varchar,GETDATE(), 20),'-',''),':',''),' ','') + '.BAK'

Thank you so much for your help.

Ganesh :-)

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-01-24 : 13:23:28

quote:
Originally posted by GaneshRamanan

Yes , Backup runs fine but i couldnt Restore it.

In the specified path file has been created but i couldnt Restore it.
I dont know the format of the file it is not created as .bak

If it was created by BACKUP DATABASE, it's a valid backup. What error did you get trying to restore?

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources