BackupDiskFile::CreateMedia: Backup device failed to create

Hi,
In SQL Query Analyzer, I run the following command.
Backup database sharepoint_webspace To sharepoint_webspace with init,
name='sharepoint_webspace_full'.
The result was,
Server: Msg 3201, Level 16, State 1, Line 1
Cannot open backup device 'sharepoint_webspace'. Device error or device
off-line. See the SQL Server error log for more details.
Server: Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
SQL server log shows:
=== BACKUP failed to complete the command Backup database SharePoint_WebSpace To
sharepoint_webspace with init, name='SharePoint_WebSpace_full'
BackupDiskFile::CreateMedia: Backup device
'z:\data\MYSQL\BACKUP\sharepoint_webspace.BAK' failed to create. Operating
system error=32 (The process cannot access the file because it is being used
by another process.)
===
The backup disk file z:\data\MYSQL\BACKUP\sharepoint_webspace.BAK has some
old backed up stuff. It's over 11G right now.
Question I have:
How should I know what process is using the sharepoint_webspace.BAK file? I
checked Process Info in EM, there are four processes associated with the
sharepoint_webspace database, but three of them are connections from the IIS
web server (Network Library: TCP/IP). The other one is the Backup command
I'm running in SQL Query Analyzer (Network Library).
How should I get the problem fixed?
Thanks much in advance,
BingNever mind. I've figured it out. Another process is trying to write the
backup disk file to tapes but no more tapes available.
Bing
"bing" wrote:
> Hi,
> In SQL Query Analyzer, I run the following command.
> Backup database sharepoint_webspace To sharepoint_webspace with init,
> name='sharepoint_webspace_full'.
> The result was,
> Server: Msg 3201, Level 16, State 1, Line 1
> Cannot open backup device 'sharepoint_webspace'. Device error or device
> off-line. See the SQL Server error log for more details.
> Server: Msg 3013, Level 16, State 1, Line 1
> BACKUP DATABASE is terminating abnormally.
> SQL server log shows:
> ===> BACKUP failed to complete the command Backup database SharePoint_WebSpace To
> sharepoint_webspace with init, name='SharePoint_WebSpace_full'
> BackupDiskFile::CreateMedia: Backup device
> 'z:\data\MYSQL\BACKUP\sharepoint_webspace.BAK' failed to create. Operating
> system error=32 (The process cannot access the file because it is being used
> by another process.)
> ===> The backup disk file z:\data\MYSQL\BACKUP\sharepoint_webspace.BAK has some
> old backed up stuff. It's over 11G right now.
> Question I have:
> How should I know what process is using the sharepoint_webspace.BAK file? I
> checked Process Info in EM, there are four processes associated with the
> sharepoint_webspace database, but three of them are connections from the IIS
> web server (Network Library: TCP/IP). The other one is the Backup command
> I'm running in SQL Query Analyzer (Network Library).
> How should I get the problem fixed?
> Thanks much in advance,
> Bing

Backupcommands, explanation

Does anybody know where I can find an explanation of the
backupcommands: WITH INIT, NOUNLOAD, NOSKIP, STATS=10 and
NOFORMAT.
Look in BOL under the topic Backing up and Restoring Databases. I suggest
reading the entire section since backups are the first and most important
responsibility of a DBA.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Terje" <anonymous@.discussions.microsoft.com> wrote in message
news:854001c4320d$0c53f910$a001280a@.phx.gbl...
> Does anybody know where I can find an explanation of the
> backupcommands: WITH INIT, NOUNLOAD, NOSKIP, STATS=10 and
> NOFORMAT.
|||Terje,
BooksOnLine.
Dinesh
SQL Server MVP
--
SQL Server FAQ at
http://www.tkdinesh.com
"Terje" <anonymous@.discussions.microsoft.com> wrote in message
news:854001c4320d$0c53f910$a001280a@.phx.gbl...
> Does anybody know where I can find an explanation of the
> backupcommands: WITH INIT, NOUNLOAD, NOSKIP, STATS=10 and
> NOFORMAT.
|||Thanks, I found it!

>--Original Message--
>Terje,
>BooksOnLine.
>--
>Dinesh
>SQL Server MVP
>--
>--
>SQL Server FAQ at
>http://www.tkdinesh.com
>"Terje" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:854001c4320d$0c53f910$a001280a@.phx.gbl...
and
>
>.
>
|||Thanks, I found it!

>--Original Message--
>Look in BOL under the topic Backing up and Restoring
Databases. I suggest
>reading the entire section since backups are the first
and most important
>responsibility of a DBA.
>--
>Geoff N. Hiten
>Microsoft SQL Server MVP
>Senior Database Administrator
>Careerbuilder.com
>I support the Professional Association for SQL Server
>www.sqlpass.org
>"Terje" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:854001c4320d$0c53f910$a001280a@.phx.gbl...
and
>
>.
>

Backupcommands, explanation

Does anybody know where I can find an explanation of the
backupcommands: WITH INIT, NOUNLOAD, NOSKIP, STATS=10 and
NOFORMAT.Look in BOL under the topic Backing up and Restoring Databases. I suggest
reading the entire section since backups are the first and most important
responsibility of a DBA.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Terje" <anonymous@.discussions.microsoft.com> wrote in message
news:854001c4320d$0c53f910$a001280a@.phx.gbl...
> Does anybody know where I can find an explanation of the
> backupcommands: WITH INIT, NOUNLOAD, NOSKIP, STATS=10 and
> NOFORMAT.|||Terje,
BooksOnLine.
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Terje" <anonymous@.discussions.microsoft.com> wrote in message
news:854001c4320d$0c53f910$a001280a@.phx.gbl...
> Does anybody know where I can find an explanation of the
> backupcommands: WITH INIT, NOUNLOAD, NOSKIP, STATS=10 and
> NOFORMAT.|||Thanks, I found it!

>--Original Message--
>Terje,
>BooksOnLine.
>--
>Dinesh
>SQL Server MVP
>--
>--
>SQL Server FAQ at
>http://www.tkdinesh.com
>"Terje" <anonymous@.discussions.microsoft.com> wrote in
message
>news:854001c4320d$0c53f910$a001280a@.phx.gbl...
and[vbcol=seagreen]
>
>.
>|||Thanks, I found it!

>--Original Message--
>Look in BOL under the topic Backing up and Restoring
Databases. I suggest
>reading the entire section since backups are the first
and most important
>responsibility of a DBA.
>--
>Geoff N. Hiten
>Microsoft SQL Server MVP
>Senior Database Administrator
>Careerbuilder.com
>I support the Professional Association for SQL Server
>www.sqlpass.org
>"Terje" <anonymous@.discussions.microsoft.com> wrote in
message
>news:854001c4320d$0c53f910$a001280a@.phx.gbl...
and[vbcol=seagreen]
>
>.
>

Backupcommands, explanation

Does anybody know where I can find an explanation of the
backupcommands: WITH INIT, NOUNLOAD, NOSKIP, STATS=10 and
NOFORMAT.Look in BOL under the topic Backing up and Restoring Databases. I suggest
reading the entire section since backups are the first and most important
responsibility of a DBA.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Terje" <anonymous@.discussions.microsoft.com> wrote in message
news:854001c4320d$0c53f910$a001280a@.phx.gbl...
> Does anybody know where I can find an explanation of the
> backupcommands: WITH INIT, NOUNLOAD, NOSKIP, STATS=10 and
> NOFORMAT.|||Terje,
BooksOnLine.
--
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Terje" <anonymous@.discussions.microsoft.com> wrote in message
news:854001c4320d$0c53f910$a001280a@.phx.gbl...
> Does anybody know where I can find an explanation of the
> backupcommands: WITH INIT, NOUNLOAD, NOSKIP, STATS=10 and
> NOFORMAT.|||Thanks, I found it!
>--Original Message--
>Terje,
>BooksOnLine.
>--
>Dinesh
>SQL Server MVP
>--
>--
>SQL Server FAQ at
>http://www.tkdinesh.com
>"Terje" <anonymous@.discussions.microsoft.com> wrote in
message
>news:854001c4320d$0c53f910$a001280a@.phx.gbl...
>> Does anybody know where I can find an explanation of the
>> backupcommands: WITH INIT, NOUNLOAD, NOSKIP, STATS=10
and
>> NOFORMAT.
>
>.
>|||Thanks, I found it!
>--Original Message--
>Look in BOL under the topic Backing up and Restoring
Databases. I suggest
>reading the entire section since backups are the first
and most important
>responsibility of a DBA.
>--
>Geoff N. Hiten
>Microsoft SQL Server MVP
>Senior Database Administrator
>Careerbuilder.com
>I support the Professional Association for SQL Server
>www.sqlpass.org
>"Terje" <anonymous@.discussions.microsoft.com> wrote in
message
>news:854001c4320d$0c53f910$a001280a@.phx.gbl...
>> Does anybody know where I can find an explanation of the
>> backupcommands: WITH INIT, NOUNLOAD, NOSKIP, STATS=10
and
>> NOFORMAT.
>
>.
>

Backup transaction logs

Currently my backup plan for my databases has them going to file backup every
day and then to tape. My DB backup is set with the option of "with init" to
allow for backup to be overwritten.
For my transaction logs they get backed up to disk every hour and then once
a day the backup file gets backed up to disk. The problem is on these they
are set to use "with noinit" so these backup files are growing.
If I switch the init option my transaction log backups will get overwritten
every hour.
Would this work if I had to do a a full restore? I would have the full DB
backup from the night before and the trans backup from the latest hour. But I
would not have the backup of the other transactions (hopefully that makes
sense) between that latest hour and the when the full DB backup was done.
The problem I have now is that with my trans bakcups being appended is that
there is backups of transactions that are older that the current day. I
don't need to keep these as a do a full DB backup every day...
Thanks.
Two options come to mind:
You could modify your T-Log backup job to issue a WITH INIT before you issue
your nightly database backup.
You could modify your hourly T-Log backups to backup WITH INIT. You will
also need to add a job step which renames the files and includes a datetime
identifier in the file name. Something like this:
DECLARE @.str varchar(1000),
@.dte varchar(100)
SELECT @.dte = CONVERT(varchar(8),getdate(),112) + '.' +
CONVERT(varchar(2),datepart(hh,getdate()))
SELECT @.str = 'master..xp_cmdshell ''rename x:\testfoo.txt testfoo.' +@.dte +
'.txt'''
SELECT @.str /*if you want to see the command that will be executed*/
EXEC (@.str) /*performs the file rename*/
Keith
"Tyler" <Tyler@.discussions.microsoft.com> wrote in message
news:CB42C0B9-D402-4275-ACC0-9FAE0384A7EC@.microsoft.com...
> Currently my backup plan for my databases has them going to file backup
every
> day and then to tape. My DB backup is set with the option of "with init"
to
> allow for backup to be overwritten.
> For my transaction logs they get backed up to disk every hour and then
once
> a day the backup file gets backed up to disk. The problem is on these
they
> are set to use "with noinit" so these backup files are growing.
> If I switch the init option my transaction log backups will get
overwritten
> every hour.
> Would this work if I had to do a a full restore? I would have the full DB
> backup from the night before and the trans backup from the latest hour.
But I
> would not have the backup of the other transactions (hopefully that makes
> sense) between that latest hour and the when the full DB backup was done.
> The problem I have now is that with my trans bakcups being appended is
that
> there is backups of transactions that are older that the current day. I
> don't need to keep these as a do a full DB backup every day...
> Thanks.
>
|||I had a similar problem. I have created 8 backup devices (for each hour
between 9 AM and 5 pm) with a name convention (transDump9, transDump10,
transDump11, transDump12, transDump13...transDump17). I also have a job to
dump the transaction log WITH INIT every hour...
Sasan Saidi, MSc in CS
Senior DBA
Brascan Business Services
"I saw it work in a cartoon once so I am pretty sure I can do it."
"Keith Kratochvil" wrote:

> Two options come to mind:
> You could modify your T-Log backup job to issue a WITH INIT before you issue
> your nightly database backup.
> You could modify your hourly T-Log backups to backup WITH INIT. You will
> also need to add a job step which renames the files and includes a datetime
> identifier in the file name. Something like this:
> DECLARE @.str varchar(1000),
> @.dte varchar(100)
> SELECT @.dte = CONVERT(varchar(8),getdate(),112) + '.' +
> CONVERT(varchar(2),datepart(hh,getdate()))
>
> SELECT @.str = 'master..xp_cmdshell ''rename x:\testfoo.txt testfoo.' +@.dte +
> '.txt'''
> SELECT @.str /*if you want to see the command that will be executed*/
> EXEC (@.str) /*performs the file rename*/
>
> --
> Keith
>
> "Tyler" <Tyler@.discussions.microsoft.com> wrote in message
> news:CB42C0B9-D402-4275-ACC0-9FAE0384A7EC@.microsoft.com...
> every
> to
> once
> they
> overwritten
> But I
> that
>
|||Yep, that solution will work but now you probably have
n number of jobs and n number of backup devices that you have to
maintain/monitor.
SQL Server does not have to backup to a device. It can simply backup to a
file.
BACKUP DATABASE foo TO DISK = 'x:\foo.bak' WITH INIT
(replace foo and x with appropriate values).
So, it is easy enough to do this all within one job that is scheduled to run
at whatever interval during the day that is needed. If it needs to run more
often that hourly it is possible to append the minute to the file name as
well. All that is needed is to increase the frequency of the job execution
and to include the minute within the file rename.
Keith
"Sasan Saidi" <SasanSaidi@.discussions.microsoft.com> wrote in message
news:2C8D3FFA-02B7-4C80-AA2F-DC965AAD0C71@.microsoft.com...[vbcol=seagreen]
> I had a similar problem. I have created 8 backup devices (for each hour
> between 9 AM and 5 pm) with a name convention (transDump9, transDump10,
> transDump11, transDump12, transDump13...transDump17). I also have a job to
> dump the transaction log WITH INIT every hour...
> --
> Sasan Saidi, MSc in CS
> Senior DBA
> Brascan Business Services
> "I saw it work in a cartoon once so I am pretty sure I can do it."
> "Keith Kratochvil" wrote:
issue[vbcol=seagreen]
will[vbcol=seagreen]
datetime[vbcol=seagreen]
+@.dte +[vbcol=seagreen]
backup[vbcol=seagreen]
init"[vbcol=seagreen]
full DB[vbcol=seagreen]
hour.[vbcol=seagreen]
makes[vbcol=seagreen]
done.[vbcol=seagreen]
I[vbcol=seagreen]
|||why not just use a db maintenance plan to backup the databases? the
backup files created from it will have the backup time as part of the
filename, hence they are unique filenames. use your tape backup to
backup the folders that contain the backup files. use the delete backup
files option to remove the older backup files from disk.
Tyler wrote:
> Currently my backup plan for my databases has them going to file backup every
> day and then to tape. My DB backup is set with the option of "with init" to
> allow for backup to be overwritten.
> For my transaction logs they get backed up to disk every hour and then once
> a day the backup file gets backed up to disk. The problem is on these they
> are set to use "with noinit" so these backup files are growing.
> If I switch the init option my transaction log backups will get overwritten
> every hour.
> Would this work if I had to do a a full restore? I would have the full DB
> backup from the night before and the trans backup from the latest hour. But I
> would not have the backup of the other transactions (hopefully that makes
> sense) between that latest hour and the when the full DB backup was done.
> The problem I have now is that with my trans bakcups being appended is that
> there is backups of transactions that are older that the current day. I
> don't need to keep these as a do a full DB backup every day...
> Thanks.

Backup transaction logs

Currently my backup plan for my databases has them going to file backup ever
y
day and then to tape. My DB backup is set with the option of "with init" to
allow for backup to be overwritten.
For my transaction logs they get backed up to disk every hour and then once
a day the backup file gets backed up to disk. The problem is on these they
are set to use "with noinit" so these backup files are growing.
If I switch the init option my transaction log backups will get overwritten
every hour.
Would this work if I had to do a a full restore? I would have the full DB
backup from the night before and the trans backup from the latest hour. But
I
would not have the backup of the other transactions (hopefully that makes
sense) between that latest hour and the when the full DB backup was done.
The problem I have now is that with my trans bakcups being appended is that
there is backups of transactions that are older that the current day. I
don't need to keep these as a do a full DB backup every day...
Thanks.Two options come to mind:
You could modify your T-Log backup job to issue a WITH INIT before you issue
your nightly database backup.
You could modify your hourly T-Log backups to backup WITH INIT. You will
also need to add a job step which renames the files and includes a datetime
identifier in the file name. Something like this:
DECLARE @.str varchar(1000),
@.dte varchar(100)
SELECT @.dte = CONVERT(varchar(8),getdate(),112) + '.' +
CONVERT(varchar(2),datepart(hh,getdate()
))
SELECT @.str = 'master..xp_cmdshell ''rename x:\testfoo.txt testfoo.' +@.dte +
'.txt'''
SELECT @.str /*if you want to see the command that will be executed*/
EXEC (@.str) /*performs the file rename*/
Keith
"Tyler" <Tyler@.discussions.microsoft.com> wrote in message
news:CB42C0B9-D402-4275-ACC0-9FAE0384A7EC@.microsoft.com...
> Currently my backup plan for my databases has them going to file backup
every
> day and then to tape. My DB backup is set with the option of "with init"
to
> allow for backup to be overwritten.
> For my transaction logs they get backed up to disk every hour and then
once
> a day the backup file gets backed up to disk. The problem is on these
they
> are set to use "with noinit" so these backup files are growing.
> If I switch the init option my transaction log backups will get
overwritten
> every hour.
> Would this work if I had to do a a full restore? I would have the full DB
> backup from the night before and the trans backup from the latest hour.
But I
> would not have the backup of the other transactions (hopefully that makes
> sense) between that latest hour and the when the full DB backup was done.
> The problem I have now is that with my trans bakcups being appended is
that
> there is backups of transactions that are older that the current day. I
> don't need to keep these as a do a full DB backup every day...
> Thanks.
>|||I had a similar problem. I have created 8 backup devices (for each hour
between 9 AM and 5 pm) with a name convention (transDump9, transDump10,
transDump11, transDump12, transDump13...transDump17). I also have a job to
dump the transaction log WITH INIT every hour...
--
Sasan Saidi, MSc in CS
Senior DBA
Brascan Business Services
"I saw it work in a cartoon once so I am pretty sure I can do it."
"Keith Kratochvil" wrote:

> Two options come to mind:
> You could modify your T-Log backup job to issue a WITH INIT before you iss
ue
> your nightly database backup.
> You could modify your hourly T-Log backups to backup WITH INIT. You will
> also need to add a job step which renames the files and includes a datetim
e
> identifier in the file name. Something like this:
> DECLARE @.str varchar(1000),
> @.dte varchar(100)
> SELECT @.dte = CONVERT(varchar(8),getdate(),112) + '.' +
> CONVERT(varchar(2),datepart(hh,getdate()
))
>
> SELECT @.str = 'master..xp_cmdshell ''rename x:\testfoo.txt testfoo.' +@.dte
+
> '.txt'''
> SELECT @.str /*if you want to see the command that will be executed*/
> EXEC (@.str) /*performs the file rename*/
>
> --
> Keith
>
> "Tyler" <Tyler@.discussions.microsoft.com> wrote in message
> news:CB42C0B9-D402-4275-ACC0-9FAE0384A7EC@.microsoft.com...
> every
> to
> once
> they
> overwritten
> But I
> that
>|||Yep, that solution will work but now you probably have
n number of jobs and n number of backup devices that you have to
maintain/monitor.
SQL Server does not have to backup to a device. It can simply backup to a
file.
BACKUP DATABASE foo TO DISK = 'x:\foo.bak' WITH INIT
(replace foo and x with appropriate values).
So, it is easy enough to do this all within one job that is scheduled to run
at whatever interval during the day that is needed. If it needs to run more
often that hourly it is possible to append the minute to the file name as
well. All that is needed is to increase the frequency of the job execution
and to include the minute within the file rename.
Keith
"Sasan Saidi" <SasanSaidi@.discussions.microsoft.com> wrote in message
news:2C8D3FFA-02B7-4C80-AA2F-DC965AAD0C71@.microsoft.com...[vbcol=seagreen]
> I had a similar problem. I have created 8 backup devices (for each hour
> between 9 AM and 5 pm) with a name convention (transDump9, transDump10,
> transDump11, transDump12, transDump13...transDump17). I also have a job to
> dump the transaction log WITH INIT every hour...
> --
> Sasan Saidi, MSc in CS
> Senior DBA
> Brascan Business Services
> "I saw it work in a cartoon once so I am pretty sure I can do it."
> "Keith Kratochvil" wrote:
>
issue[vbcol=seagreen]
will[vbcol=seagreen]
datetime[vbcol=seagreen]
+@.dte +[vbcol=seagreen]
backup[vbcol=seagreen]
init"[vbcol=seagreen]
full DB[vbcol=seagreen]
hour.[vbcol=seagreen]
makes[vbcol=seagreen]
done.[vbcol=seagreen]
I[vbcol=seagreen]|||why not just use a db maintenance plan to backup the databases? the
backup files created from it will have the backup time as part of the
filename, hence they are unique filenames. use your tape backup to
backup the folders that contain the backup files. use the delete backup
files option to remove the older backup files from disk.
Tyler wrote:
> Currently my backup plan for my databases has them going to file backup ev
ery
> day and then to tape. My DB backup is set with the option of "with init"
to
> allow for backup to be overwritten.
> For my transaction logs they get backed up to disk every hour and then onc
e
> a day the backup file gets backed up to disk. The problem is on these the
y
> are set to use "with noinit" so these backup files are growing.
> If I switch the init option my transaction log backups will get overwritte
n
> every hour.
> Would this work if I had to do a a full restore? I would have the full DB
> backup from the night before and the trans backup from the latest hour. Bu
t I
> would not have the backup of the other transactions (hopefully that makes
> sense) between that latest hour and the when the full DB backup was done.
> The problem I have now is that with my trans bakcups being appended is tha
t
> there is backups of transactions that are older that the current day. I
> don't need to keep these as a do a full DB backup every day...
> Thanks.