Backup SQL Server DB on Network Drive Big Problem. Please help

Hi All,
I want to using T-SQL execute BACKUP DATABASE command job on the SQL
Server Agent. But, it occured the following error:
My Job Owner is "sa", Category is Database Maintenance
My execute command is ..
BACKUP DATABASE NorthWind TO DISK =
'\\servername\Backup\NorthWind.BAK'
The job failed. The Job was invoked by User Machine\Administrator.
The last step to run was step 1 (DBt Backup).
Please advise.Hi
Make sure that your SQL Server Services are running under a Domain Account
and that the user has correct permissions to the share.
To see the error message, run the T-SQL from Query Analyser.
Regards
Mike
"Kelvin" wrote:

> Hi All,
> I want to using T-SQL execute BACKUP DATABASE command job on the SQL
> Server Agent. But, it occured the following error:
> My Job Owner is "sa", Category is Database Maintenance
> My execute command is ..
> BACKUP DATABASE NorthWind TO DISK =
> '\\servername\Backup\NorthWind.BAK'
> The job failed. The Job was invoked by User Machine\Administrator.
> The last step to run was step 1 (DBt Backup).
> Please advise.
>|||Hi Mike,
How to check SQL Server Services are running under a Domain Account and
the user has correct permissions to the share. ' Please advise
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||1. On the SQL Server, go to Control Panel/Administrative Tools/Services
and make sure the MSSQLServer service is running under an account
"Domain\User" instead of Local Service.
2. Go to the share on the network drive and look at Sharing and Security to
make sure that same user has read/write permissions on the share. You can
probably have your network group do this for you.
"Tai Kelvin" <kelvinweb@.gmail.com> wrote in message
news:#OWSbgn7EHA.3696@.TK2MSFTNGP10.phx.gbl...
> Hi Mike,
>
> How to check SQL Server Services are running under a Domain Account and
> the user has correct permissions to the share. ' Please advise
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!|||Hi Mike,
I checked that MSSQLServer service is running under Local Administrator
Account, and I also change the job owner to Local Administrator Account,
but it still occurs following error:
The job failed. The Job was invoked by User LocalMachine\Administrator.
The last step to run was step 1 (MyTesting Backup).
If the Backup Network Drive of PC just only running on Workgroup. How to
using T-SQL Backup Network Drive ?
Please advise.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||"Tai Kelvin" <kelvinweb@.gmail.com> wrote in message
news:eJsI9wu7EHA.2180@.TK2MSFTNGP12.phx.gbl...
> Hi Mike,
> I checked that MSSQLServer service is running under Local Administrator
> Account, and I also change the job owner to Local Administrator Account,
> but it still occurs following error:
> The job failed. The Job was invoked by User LocalMachine\Administrator.
Right. It can NOT run under a Local Administrator account.
It needs to be a domain account.

> The last step to run was step 1 (MyTesting Backup).
> If the Backup Network Drive of PC just only running on Workgroup. How to
> using T-SQL Backup Network Drive ?
You can TRY to give it a username and password that the computer containing
the network drive also has, but I'm not sure it would would work.

> Please advise.
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!|||Hi,
When I change the owner to "Domain Administrator" to execute the T-SQL
to backup database to the network drive.
It display the following error:
The job failed. Unable to determine if the owner (Domain\Administrator)
of job Test Backup has server access (reason: Could not obtain
information about Windows NT group/user 'Domain\Administrator'.
[SQLSTATE 42000] (Error 8198)).
But the Shared Network Drive PC haven't connect Domain, runing on
workgroup only. I can't directly to access that PC. Please advise.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||Try to change the job owner to sa.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Tai Kelvin" <kelvinweb@.gmail.com> wrote in message news:ORQOrBy7EHA.3696@.TK2MSFTNGP10.phx.g
bl...
> Hi,
> When I change the owner to "Domain Administrator" to execute the T-SQL
> to backup database to the network drive.
> It display the following error:
> The job failed. Unable to determine if the owner (Domain\Administrator)
> of job Test Backup has server access (reason: Could not obtain
> information about Windows NT group/user 'Domain\Administrator'.
> [SQLSTATE 42000] (Error 8198)).
> But the Shared Network Drive PC haven't connect Domain, runing on
> workgroup only. I can't directly to access that PC. Please advise.
>
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!|||Hi.. I try to using sa, but it is same result.
Please advise
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||Did you get the exact same error message after changing job owner to "sa". T
hat would be strange, as
the error messages you posted referred to "Domain\Administrator"...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Tai Kelvin" <kelvinweb@.gmail.com> wrote in message news:uEl5LwC8EHA.3236@.TK2MSFTNGP15.phx.g
bl...
>
> Hi.. I try to using sa, but it is same result.
> Please advise
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

Backup SQL Server DB on Network Drive Big Problem. Please help

Hi All,
I want to using T-SQL execute BACKUP DATABASE command job on the SQL
Server Agent. But, it occured the following error:
My Job Owner is "sa", Category is Database Maintenance
My execute command is ..
BACKUP DATABASE NorthWind TO DISK =
'\\servername\Backup\NorthWind.BAK'
The job failed. The Job was invoked by User Machine\Administrator.
The last step to run was step 1 (DBt Backup).
Please advise.
Hi
Make sure that your SQL Server Services are running under a Domain Account
and that the user has correct permissions to the share.
To see the error message, run the T-SQL from Query Analyser.
Regards
Mike
"Kelvin" wrote:

> Hi All,
> I want to using T-SQL execute BACKUP DATABASE command job on the SQL
> Server Agent. But, it occured the following error:
> My Job Owner is "sa", Category is Database Maintenance
> My execute command is ..
> BACKUP DATABASE NorthWind TO DISK =
> '\\servername\Backup\NorthWind.BAK'
> The job failed. The Job was invoked by User Machine\Administrator.
> The last step to run was step 1 (DBt Backup).
> Please advise.
>
|||Hi Mike,
How to check SQL Server Services are running under a Domain Account and
the user has correct permissions to the share. ? Please advise
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||1. On the SQL Server, go to Control Panel/Administrative Tools/Services
and make sure the MSSQLServer service is running under an account
"Domain\User" instead of Local Service.
2. Go to the share on the network drive and look at Sharing and Security to
make sure that same user has read/write permissions on the share. You can
probably have your network group do this for you.
"Tai Kelvin" <kelvinweb@.gmail.com> wrote in message
news:#OWSbgn7EHA.3696@.TK2MSFTNGP10.phx.gbl...
> Hi Mike,
>
> How to check SQL Server Services are running under a Domain Account and
> the user has correct permissions to the share. ? Please advise
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||Hi Mike,
I checked that MSSQLServer service is running under Local Administrator
Account, and I also change the job owner to Local Administrator Account,
but it still occurs following error:
The job failed. The Job was invoked by User LocalMachine\Administrator.
The last step to run was step 1 (MyTesting Backup).
If the Backup Network Drive of PC just only running on Workgroup. How to
using T-SQL Backup Network Drive ?
Please advise.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||"Tai Kelvin" <kelvinweb@.gmail.com> wrote in message
news:eJsI9wu7EHA.2180@.TK2MSFTNGP12.phx.gbl...
> Hi Mike,
> I checked that MSSQLServer service is running under Local Administrator
> Account, and I also change the job owner to Local Administrator Account,
> but it still occurs following error:
> The job failed. The Job was invoked by User LocalMachine\Administrator.
Right. It can NOT run under a Local Administrator account.
It needs to be a domain account.

> The last step to run was step 1 (MyTesting Backup).
> If the Backup Network Drive of PC just only running on Workgroup. How to
> using T-SQL Backup Network Drive ?
You can TRY to give it a username and password that the computer containing
the network drive also has, but I'm not sure it would would work.

> Please advise.
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||Hi,
When I change the owner to "Domain Administrator" to execute the T-SQL
to backup database to the network drive.
It display the following error:
The job failed. Unable to determine if the owner (Domain\Administrator)
of job Test Backup has server access (reason: Could not obtain
information about Windows NT group/user 'Domain\Administrator'.
[SQLSTATE 42000] (Error 8198)).
But the Shared Network Drive PC haven't connect Domain, runing on
workgroup only. I can't directly to access that PC. Please advise.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Try to change the job owner to sa.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Tai Kelvin" <kelvinweb@.gmail.com> wrote in message news:ORQOrBy7EHA.3696@.TK2MSFTNGP10.phx.gbl...
> Hi,
> When I change the owner to "Domain Administrator" to execute the T-SQL
> to backup database to the network drive.
> It display the following error:
> The job failed. Unable to determine if the owner (Domain\Administrator)
> of job Test Backup has server access (reason: Could not obtain
> information about Windows NT group/user 'Domain\Administrator'.
> [SQLSTATE 42000] (Error 8198)).
> But the Shared Network Drive PC haven't connect Domain, runing on
> workgroup only. I can't directly to access that PC. Please advise.
>
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||Hi.. I try to using sa, but it is same result.
Please advise
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Did you get the exact same error message after changing job owner to "sa". That would be strange, as
the error messages you posted referred to "Domain\Administrator"...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Tai Kelvin" <kelvinweb@.gmail.com> wrote in message news:uEl5LwC8EHA.3236@.TK2MSFTNGP15.phx.gbl...
>
> Hi.. I try to using sa, but it is same result.
> Please advise
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

Backup SQL Server DB on Network Drive Big Problem. Please help

Hi All,
I want to using T-SQL execute BACKUP DATABASE command job on the SQL
Server Agent. But, it occured the following error:
My Job Owner is "sa", Category is Database Maintenance
My execute command is ..
BACKUP DATABASE NorthWind TO DISK = '\\servername\Backup\NorthWind.BAK'
The job failed. The Job was invoked by User Machine\Administrator.
The last step to run was step 1 (DBt Backup).
Please advise.Hi
Make sure that your SQL Server Services are running under a Domain Account
and that the user has correct permissions to the share.
To see the error message, run the T-SQL from Query Analyser.
Regards
Mike
"Kelvin" wrote:
> Hi All,
> I want to using T-SQL execute BACKUP DATABASE command job on the SQL
> Server Agent. But, it occured the following error:
> My Job Owner is "sa", Category is Database Maintenance
> My execute command is ..
> BACKUP DATABASE NorthWind TO DISK => '\\servername\Backup\NorthWind.BAK'
> The job failed. The Job was invoked by User Machine\Administrator.
> The last step to run was step 1 (DBt Backup).
> Please advise.
>sql

Backup script

Hi,

I'm currently working on a backup script for my MSSQL databases. I have managed to make a working backup command but I have one problem.
I need every new backup file to have a uniqe filename, for example backup_UNIQE.bak. How can I make a variable that can do this?

Alexander

Here's the basis of one that can be used which uses the date as a unique identifier:

Code Snippet

declare @.filename nvarchar(2000)

set @.filename = 'c:\yourbackupdirectory\dbname_bk_' + convert(varchar,getdate(),112) + '.bak'

backup database northwind
to disk = @.filename

HTH!

|||

Hi,

Can you stick a date on the end of it? See below and then pass the filename into you script somehow.

declare @.datet varchar(10), @.filename varchar(20)

set @.datet = convert(varchar(10),getdate(),112)

set @.filename = 'backup'+@.datet+'.bak'

print @.filename

will give you

backup20070824.bak

You could always add the time to it :

set @.datet = replace(convert(varchar(8),getdate(),114),':', '')

Any help to you?

Cheers

Matt

|||Thank you both! This worked like a charm!

Matt Tolhurst:

Can you please explain this one: set @.datet = replace(convert(varchar(8),getdate(),114),':', '') ?

I'm not TSQL coder yet, and I would appricitate it very much!

- Alexander
|||

Alexander Davidsen wrote:

Can you please explain this one: set @.datet = replace(convert(varchar(8),getdate(),114),':', '') ?

Hi,

That bit is simply taking the time part of the getdate function and removing the colons, as you don't really want to put colons in the file name.

declare @.datet2 datetime, @.datet varchar(10), @.filename varchar(20)

set @.datet2 = getdate()

print @.datet2 --

Aug 24 2007 11:07AM

set @.datet = convert(varchar(10),@.datet2,112)

print @.datet

20070824

here is the time part:

set @.datet = convert(varchar(8),@.datet2,114)

print @.datet

11:07:44

set @.datet = replace(convert(varchar(8),@.datet2,114),':', '')

print @.datet

110744

Any clearer?

Matt

|||Thanks Matt

This made it alot clearer!

Have a nice weekend!

- Alexander

Backup Question

What effect does using the FORMAT command have when backing up to disk? BOL
only addresses tapes, so I'm not sure.It allow you to split up a media set, and also overwrite a device earlier
then what you specified in RETAINDAYS or EXPIREDATE.
--
Tibor Karaszi
"John Hamilton" <jhamil@.nowhere.com> wrote in message
news:utjmnvhoDHA.2312@.TK2MSFTNGP12.phx.gbl...
> What effect does using the FORMAT command have when backing up to disk?
BOL
> only addresses tapes, so I'm not sure.
>|||So, does that mean it does not wipe out the entire directory on a hard
drive?
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:kvupb.36249$dP1.120988@.newsc.telia.net...
> It allow you to split up a media set, and also overwrite a device earlier
> then what you specified in RETAINDAYS or EXPIREDATE.
> --
> Tibor Karaszi
>
> "John Hamilton" <jhamil@.nowhere.com> wrote in message
> news:utjmnvhoDHA.2312@.TK2MSFTNGP12.phx.gbl...
> > What effect does using the FORMAT command have when backing up to disk?
> BOL
> > only addresses tapes, so I'm not sure.
> >
> >
>|||No. it doesn't wipe out the whole directory. It has
nothing to do with the NT FORMAT command.
When backing up to disk, I use the FORMAT option primarily
for reusing a backup file that was part of a backup stripe
set or for reusing a file in a new backup stripe set.
Other than that, I don't use the FORMAT option at all when
backing up to disk.
Linchi
>--Original Message--
>So, does that mean it does not wipe out the entire
directory on a hard
>drive?
>"Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
>wrote in message news:kvupb.36249
$dP1.120988@.newsc.telia.net...
>> It allow you to split up a media set, and also
overwrite a device earlier
>> then what you specified in RETAINDAYS or EXPIREDATE.
>> --
>> Tibor Karaszi
>>
>> "John Hamilton" <jhamil@.nowhere.com> wrote in message
>> news:utjmnvhoDHA.2312@.TK2MSFTNGP12.phx.gbl...
>> > What effect does using the FORMAT command have when
backing up to disk?
>> BOL
>> > only addresses tapes, so I'm not sure.
>> >
>> >
>>
>
>.
>sql

Backup Log using Maintenance Plan

Is there a means to look at the code that is generated from a 'Backup Log'
command in a Maintenance Plan in SQL Server 2000? I would like to verify the
exact syntax of the 'Backup Log' command when the Maintenance Plan executes.
Thank you,
PALDBARun a Profiler trace while the job is executing.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"paldba" <paldba@.discussions.microsoft.com> wrote in message
news:8E0DA0A0-ECB3-46E0-9D8C-73AB63B24B04@.microsoft.com...
> Is there a means to look at the code that is generated from a 'Backup Log'
> command in a Maintenance Plan in SQL Server 2000? I would like to verify the
> exact syntax of the 'Backup Log' command when the Maintenance Plan executes.
> Thank you,
> PALDBA

backup job step from remote server

I want to run a backup of a database against a remote server through a SQL
Agent job.
So I have the following TSQL Command in my job step. I know it works, but is
there something better ?
xp_cmdshell 'sqlcmd -S ServerA -Q "backup database test to disk =
''M:\backups\test.bak'' with init "'
You could just do an OS cmd step and skip the xp_cmdshell. Or a linked
server with the proper permissions. I am not sure if those are much better
though...
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
"Hassan" <hassan@.test.com> wrote in message
news:epMGCADRIHA.4684@.TK2MSFTNGP02.phx.gbl...
>I want to run a backup of a database against a remote server through a SQL
>Agent job.
> So I have the following TSQL Command in my job step. I know it works, but
> is there something better ?
> xp_cmdshell 'sqlcmd -S ServerA -Q "backup database test to disk =
> ''M:\backups\test.bak'' with init "'

backup job step from remote server

I want to run a backup of a database against a remote server through a SQL
Agent job.
So I have the following TSQL Command in my job step. I know it works, but is
there something better ?
xp_cmdshell 'sqlcmd -S ServerA -Q "backup database test to disk =
''M:\backups\test.bak'' with init "'You could just do an OS cmd step and skip the xp_cmdshell. Or a linked
server with the proper permissions. I am not sure if those are much better
though...
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
"Hassan" <hassan@.test.com> wrote in message
news:epMGCADRIHA.4684@.TK2MSFTNGP02.phx.gbl...
>I want to run a backup of a database against a remote server through a SQL
>Agent job.
> So I have the following TSQL Command in my job step. I know it works, but
> is there something better ?
> xp_cmdshell 'sqlcmd -S ServerA -Q "backup database test to disk =
> ''M:\backups\test.bak'' with init "'

backup job step from remote server

I want to run a backup of a database against a remote server through a SQL
Agent job.
So I have the following TSQL Command in my job step. I know it works, but is
there something better ?
xp_cmdshell 'sqlcmd -S ServerA -Q "backup database test to disk = ''M:\backups\test.bak'' with init "'You could just do an OS cmd step and skip the xp_cmdshell. Or a linked
server with the proper permissions. I am not sure if those are much better
though...
--
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
"Hassan" <hassan@.test.com> wrote in message
news:epMGCADRIHA.4684@.TK2MSFTNGP02.phx.gbl...
>I want to run a backup of a database against a remote server through a SQL
>Agent job.
> So I have the following TSQL Command in my job step. I know it works, but
> is there something better ?
> xp_cmdshell 'sqlcmd -S ServerA -Q "backup database test to disk => ''M:\backups\test.bak'' with init "'

Backup in a file and retention

Hi,
I backup a database SQL 2005 Express Edition, with this command :
BACKUP DATABASE [mSuite] TO DISK = N'E:\BackupSQL\Backup-mSuite.bak' WITH
RETAINDAYS = 21, NOFORMAT, NOINIT, NAME = N'mSuite-Full Database Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
I would like to know if one option can erase old backup (expired backup ) in
the backup file ?
Thanks in advance,
--
Gilles> I would like to know if one option can erase old backup (expired backup )
> in
> the backup file ?
You can overwrite the entire backup file by specifying INIT instead of
NOINIT. You will also need to specify FORMAT instead of NOFORMAT if the
target backup file contains unexpired backups.
If you objective is to keep a rolling set of the most recent backups, you'll
need create a new backup each day and delete the oldest backup file.
Hope this helps.
Dan Guzman
SQL Server MVP
"Gilles" <Gilles@.discussions.microsoft.com> wrote in message
news:139F95E2-6005-47BF-974D-56612F8110BF@.microsoft.com...
> Hi,
> I backup a database SQL 2005 Express Edition, with this command :
> BACKUP DATABASE [mSuite] TO DISK = N'E:\BackupSQL\Backup-mSuite.bak' WITH
> RETAINDAYS = 21, NOFORMAT, NOINIT, NAME = N'mSuite-Full Database Backup',
> SKIP, NOREWIND, NOUNLOAD, STATS = 10
> I would like to know if one option can erase old backup (expired backup )
> in
> the backup file ?
> Thanks in advance,
> --
> Gilles|||Hi Dan,
Thanks for this information.
--
Gilles
"Dan Guzman" wrote:
> > I would like to know if one option can erase old backup (expired backup )
> > in
> > the backup file ?
> You can overwrite the entire backup file by specifying INIT instead of
> NOINIT. You will also need to specify FORMAT instead of NOFORMAT if the
> target backup file contains unexpired backups.
> If you objective is to keep a rolling set of the most recent backups, you'll
> need create a new backup each day and delete the oldest backup file.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Gilles" <Gilles@.discussions.microsoft.com> wrote in message
> news:139F95E2-6005-47BF-974D-56612F8110BF@.microsoft.com...
> > Hi,
> >
> > I backup a database SQL 2005 Express Edition, with this command :
> >
> > BACKUP DATABASE [mSuite] TO DISK = N'E:\BackupSQL\Backup-mSuite.bak' WITH
> > RETAINDAYS = 21, NOFORMAT, NOINIT, NAME = N'mSuite-Full Database Backup',
> > SKIP, NOREWIND, NOUNLOAD, STATS = 10
> >
> > I would like to know if one option can erase old backup (expired backup )
> > in
> > the backup file ?
> >
> > Thanks in advance,
> >
> > --
> > Gilles
>

Backup in a file and retention

Hi,
I backup a database SQL 2005 Express Edition, with this command :
BACKUP DATABASE [mSuite] TO DISK = N'E:\BackupSQL\Backup-mSuite.bak' WI
TH
RETAINDAYS = 21, NOFORMAT, NOINIT, NAME = N'mSuite-Full Database Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
I would like to know if one option can erase old backup (expired backup ) in
the backup file ?
Thanks in advance,
Gilles> I would like to know if one option can erase old backup (expired backup )
> in
> the backup file ?
You can overwrite the entire backup file by specifying INIT instead of
NOINIT. You will also need to specify FORMAT instead of NOFORMAT if the
target backup file contains unexpired backups.
If you objective is to keep a rolling set of the most recent backups, you'll
need create a new backup each day and delete the oldest backup file.
Hope this helps.
Dan Guzman
SQL Server MVP
"Gilles" <Gilles@.discussions.microsoft.com> wrote in message
news:139F95E2-6005-47BF-974D-56612F8110BF@.microsoft.com...
> Hi,
> I backup a database SQL 2005 Express Edition, with this command :
> BACKUP DATABASE [mSuite] TO DISK = N'E:\BackupSQL\Backup-mSuite.bak'
WITH
> RETAINDAYS = 21, NOFORMAT, NOINIT, NAME = N'mSuite-Full Database Backup',
> SKIP, NOREWIND, NOUNLOAD, STATS = 10
> I would like to know if one option can erase old backup (expired backup )
> in
> the backup file ?
> Thanks in advance,
> --
> Gilles|||Hi Dan,
Thanks for this information.
Gilles
"Dan Guzman" wrote:

> You can overwrite the entire backup file by specifying INIT instead of
> NOINIT. You will also need to specify FORMAT instead of NOFORMAT if the
> target backup file contains unexpired backups.
> If you objective is to keep a rolling set of the most recent backups, you'
ll
> need create a new backup each day and delete the oldest backup file.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Gilles" <Gilles@.discussions.microsoft.com> wrote in message
> news:139F95E2-6005-47BF-974D-56612F8110BF@.microsoft.com...
>

Backup in a file and retention

Hi,
I backup a database SQL 2005 Express Edition, with this command :
BACKUP DATABASE [mSuite] TO DISK = N'E:\BackupSQL\Backup-mSuite.bak' WITH
RETAINDAYS = 21, NOFORMAT, NOINIT, NAME = N'mSuite-Full Database Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
I would like to know if one option can erase old backup (expired backup ) in
the backup file ?
Thanks in advance,
Gilles
> I would like to know if one option can erase old backup (expired backup )
> in
> the backup file ?
You can overwrite the entire backup file by specifying INIT instead of
NOINIT. You will also need to specify FORMAT instead of NOFORMAT if the
target backup file contains unexpired backups.
If you objective is to keep a rolling set of the most recent backups, you'll
need create a new backup each day and delete the oldest backup file.
Hope this helps.
Dan Guzman
SQL Server MVP
"Gilles" <Gilles@.discussions.microsoft.com> wrote in message
news:139F95E2-6005-47BF-974D-56612F8110BF@.microsoft.com...
> Hi,
> I backup a database SQL 2005 Express Edition, with this command :
> BACKUP DATABASE [mSuite] TO DISK = N'E:\BackupSQL\Backup-mSuite.bak' WITH
> RETAINDAYS = 21, NOFORMAT, NOINIT, NAME = N'mSuite-Full Database Backup',
> SKIP, NOREWIND, NOUNLOAD, STATS = 10
> I would like to know if one option can erase old backup (expired backup )
> in
> the backup file ?
> Thanks in advance,
> --
> Gilles
|||Hi Dan,
Thanks for this information.
Gilles
"Dan Guzman" wrote:

> You can overwrite the entire backup file by specifying INIT instead of
> NOINIT. You will also need to specify FORMAT instead of NOFORMAT if the
> target backup file contains unexpired backups.
> If you objective is to keep a rolling set of the most recent backups, you'll
> need create a new backup each day and delete the oldest backup file.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Gilles" <Gilles@.discussions.microsoft.com> wrote in message
> news:139F95E2-6005-47BF-974D-56612F8110BF@.microsoft.com...
>