Backup Error:Set property Devices to accomplish this action.

I am getting an error, "Set property Devices to accomplish this action." when doing a backup with SQL Server 2005 Express when I try to do a full (or Differential) backup through the SQL Server Management Studio. I'm using the backup defaults in the GUI and was able to back up the database a couple of days ago without any problem. Today (second time) it just comes up with this error and I have been able to find anything that talks about what it might be.

I tired to look at the script by using the "Script action to new query, file, clipboard, etc." but it fails before it creates the script in any destination. I've rebooted the server and that didn't help.

I found one similar post on this forum but it had no resolution. Any direction on this problem would be greatly appreciated.

Thank you,

Steve

Some components cannot be installed on an alternative location and have to be installed in the %programfiles% directory. If this one is located on the system drive you will not be able to install it on an alternative drive.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

I did specify the data directory to be in a location other than the program's default but all the code is in the program directory. I will uninstall and reinstall everything in it's default location if that is the problem.

Steve

|||

What do you mean by "all the code" ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

In the SQL setup you are given the option to store the SQL application ("The Code") in one location and the Data in another. I let the application install in the default location in the program directory and changed the data to be stored in a non-standard directory (ie. c:\data\mssql).

sr

|||

That should work for you.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

I uninstalled everything that had to do with SQL Server 2005 Express and reinstalled. The backup works now but what a way to fix a problem, huh?

Thanks for the replies,

Steve

|||

I spoke too soon... I was able to do the backup a couple of times and now the same error is back!!

Have any more suggestions?

Steve

Backup Error:Set property Devices to accomplish this action.

I am getting an error, "Set property Devices to accomplish this action." when doing a backup with SQL Server 2005 Express when I try to do a full (or Differential) backup through the SQL Server Management Studio. I'm using the backup defaults in the GUI and was able to back up the database a couple of days ago without any problem. Today (second time) it just comes up with this error and I have been able to find anything that talks about what it might be.

I tired to look at the script by using the "Script action to new query, file, clipboard, etc." but it fails before it creates the script in any destination. I've rebooted the server and that didn't help.

I found one similar post on this forum but it had no resolution. Any direction on this problem would be greatly appreciated.

Thank you,

Steve

Some components cannot be installed on an alternative location and have to be installed in the %programfiles% directory. If this one is located on the system drive you will not be able to install it on an alternative drive.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

I did specify the data directory to be in a location other than the program's default but all the code is in the program directory. I will uninstall and reinstall everything in it's default location if that is the problem.

Steve

|||

What do you mean by "all the code" ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

In the SQL setup you are given the option to store the SQL application ("The Code") in one location and the Data in another. I let the application install in the default location in the program directory and changed the data to be stored in a non-standard directory (ie. c:\data\mssql).

sr

|||

That should work for you.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

I uninstalled everything that had to do with SQL Server 2005 Express and reinstalled. The backup works now but what a way to fix a problem, huh?

Thanks for the replies,

Steve

|||

I spoke too soon... I was able to do the backup a couple of times and now the same error is back!!

Have any more suggestions?

Steve

backup devices export

Hi, I am updagrading database server to new datbase server.
I want to transfer the backup devices info from old server to new
server.
is there any way so that I can script backup devices on old server
and deploy them to new server.
This will save me lot of time.
Thanks in advance
Hi DKR,
I don't think you can script the backup devices from any of the management
tools.
SQL DMO has a Backup Device object with a Script method but that means you
will need to write procedural code for that.
See
http://msdn.microsoft.com/library/de...f_m_s_8wz6.asp
The backup devices are stored in the "sysdevices" table in the master
database.
Restoring the master DB will restore all backup devices but it will have
many more implications that you usually don't want to mess with.
Since each device is stored as a simple single row in sysdevices, Perhaps
the easiest way will be to simply export the data from the table and import
it back on your new installation.
The sysdevices table is a "stand alone" table with no reference to any other
tables so it should be pretty straight forward.
You will need to configure the server to allow updates to system tables
using sp_configure:
EXEC sp_configure 'Show Advnaced Options',1
RECONFIGURE
EXEC sp_configure 'Allow Updates',1
RECONFIGURE WITH OVERRIDE
INSERT INTO master..sysdevices
SELECT * FROM <previous_sysdevices> WHERE cntrltype > 0
-- 0 is used for the system DB files
EXEC sp_configure 'Allow Updates',0
RECONFIGURE
* WARNING - Messing with system tables is not recommended and not supported
by MS.
HTH
Ami
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:%23RoyzSIeFHA.1400@.TK2MSFTNGP15.phx.gbl...
> Hi, I am updagrading database server to new datbase server.
> I want to transfer the backup devices info from old server to new
> server.
> is there any way so that I can script backup devices on old server
> and deploy them to new server.
> This will save me lot of time.
> Thanks in advance
>
|||Hi,
You could write a script with system stored procedure sp_addumpdevice based
on MASTER..SYSDEVICES table.
Thanks
Hari
SQL Server MVP
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:%23RoyzSIeFHA.1400@.TK2MSFTNGP15.phx.gbl...
> Hi, I am updagrading database server to new datbase server.
> I want to transfer the backup devices info from old server to new
> server.
> is there any way so that I can script backup devices on old server
> and deploy them to new server.
> This will save me lot of time.
> Thanks in advance
>
|||Thank you very much.
"Ami Levin" <XXX__NO_SPAM__XXX__Levin_Ami@.Yahoo.com> wrote in message
news:OL$SDEJeFHA.2420@.TK2MSFTNGP15.phx.gbl...
> Hi DKR,
> I don't think you can script the backup devices from any of the management
> tools.
> SQL DMO has a Backup Device object with a Script method but that means you
> will need to write procedural code for that.
> See
>
http://msdn.microsoft.com/library/de...f_m_s_8wz6.asp
> The backup devices are stored in the "sysdevices" table in the master
> database.
> Restoring the master DB will restore all backup devices but it will have
> many more implications that you usually don't want to mess with.
> Since each device is stored as a simple single row in sysdevices, Perhaps
> the easiest way will be to simply export the data from the table and
import
> it back on your new installation.
> The sysdevices table is a "stand alone" table with no reference to any
other
> tables so it should be pretty straight forward.
> You will need to configure the server to allow updates to system tables
> using sp_configure:
> EXEC sp_configure 'Show Advnaced Options',1
> RECONFIGURE
> EXEC sp_configure 'Allow Updates',1
> RECONFIGURE WITH OVERRIDE
> INSERT INTO master..sysdevices
> SELECT * FROM <previous_sysdevices> WHERE cntrltype > 0
> -- 0 is used for the system DB files
> EXEC sp_configure 'Allow Updates',0
> RECONFIGURE
>
> * WARNING - Messing with system tables is not recommended and not
supported
> by MS.
> HTH
> Ami
> "DKRReddy" <dkrreddy@.hotmail.com> wrote in message
> news:%23RoyzSIeFHA.1400@.TK2MSFTNGP15.phx.gbl...
>

backup devices export

Hi, I am updagrading database server to new datbase server.
I want to transfer the backup devices info from old server to new
server.
is there any way so that I can script backup devices on old server
and deploy them to new server.
This will save me lot of time.
Thanks in advanceHi DKR,
I don't think you can script the backup devices from any of the management
tools.
SQL DMO has a Backup Device object with a Script method but that means you
will need to write procedural code for that.
See
http://msdn.microsoft.com/library/d...r />
_8wz6.asp
The backup devices are stored in the "sysdevices" table in the master
database.
Restoring the master DB will restore all backup devices but it will have
many more implications that you usually don't want to mess with.
Since each device is stored as a simple single row in sysdevices, Perhaps
the easiest way will be to simply export the data from the table and import
it back on your new installation.
The sysdevices table is a "stand alone" table with no reference to any other
tables so it should be pretty straight forward.
You will need to configure the server to allow updates to system tables
using sp_configure:
EXEC sp_configure 'Show Advnaced Options',1
RECONFIGURE
EXEC sp_configure 'Allow Updates',1
RECONFIGURE WITH OVERRIDE
INSERT INTO master..sysdevices
SELECT * FROM <previous_sysdevices> WHERE cntrltype > 0
-- 0 is used for the system DB files
EXEC sp_configure 'Allow Updates',0
RECONFIGURE
* WARNING - Messing with system tables is not recommended and not supported
by MS.
HTH
Ami
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:%23RoyzSIeFHA.1400@.TK2MSFTNGP15.phx.gbl...
> Hi, I am updagrading database server to new datbase server.
> I want to transfer the backup devices info from old server to new
> server.
> is there any way so that I can script backup devices on old server
> and deploy them to new server.
> This will save me lot of time.
> Thanks in advance
>|||Hi,
You could write a script with system stored procedure sp_addumpdevice based
on MASTER..SYSDEVICES table.
Thanks
Hari
SQL Server MVP
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:%23RoyzSIeFHA.1400@.TK2MSFTNGP15.phx.gbl...
> Hi, I am updagrading database server to new datbase server.
> I want to transfer the backup devices info from old server to new
> server.
> is there any way so that I can script backup devices on old server
> and deploy them to new server.
> This will save me lot of time.
> Thanks in advance
>|||Thank you very much.
"Ami Levin" <XXX__NO_SPAM__XXX__Levin_Ami@.Yahoo.com> wrote in message
news:OL$SDEJeFHA.2420@.TK2MSFTNGP15.phx.gbl...
> Hi DKR,
> I don't think you can script the backup devices from any of the management
> tools.
> SQL DMO has a Backup Device object with a Script method but that means you
> will need to write procedural code for that.
> See
>
http://msdn.microsoft.com/library/d...ef_m_s_8wz6.asp[
vbcol=seagreen]
> The backup devices are stored in the "sysdevices" table in the master
> database.
> Restoring the master DB will restore all backup devices but it will have
> many more implications that you usually don't want to mess with.
> Since each device is stored as a simple single row in sysdevices, Perhaps
> the easiest way will be to simply export the data from the table and[/vbcol]
import
> it back on your new installation.
> The sysdevices table is a "stand alone" table with no reference to any
other
> tables so it should be pretty straight forward.
> You will need to configure the server to allow updates to system tables
> using sp_configure:
> EXEC sp_configure 'Show Advnaced Options',1
> RECONFIGURE
> EXEC sp_configure 'Allow Updates',1
> RECONFIGURE WITH OVERRIDE
> INSERT INTO master..sysdevices
> SELECT * FROM <previous_sysdevices> WHERE cntrltype > 0
> -- 0 is used for the system DB files
> EXEC sp_configure 'Allow Updates',0
> RECONFIGURE
>
> * WARNING - Messing with system tables is not recommended and not
supported
> by MS.
> HTH
> Ami
> "DKRReddy" <dkrreddy@.hotmail.com> wrote in message
> news:%23RoyzSIeFHA.1400@.TK2MSFTNGP15.phx.gbl...
>

backup devices export

Hi, I am updagrading database server to new datbase server.
I want to transfer the backup devices info from old server to new
server.
is there any way so that I can script backup devices on old server
and deploy them to new server.
This will save me lot of time.
Thanks in advanceHi DKR,
I don't think you can script the backup devices from any of the management
tools.
SQL DMO has a Backup Device object with a Script method but that means you
will need to write procedural code for that.
See
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqldmo/dmoref_m_s_8wz6.asp
The backup devices are stored in the "sysdevices" table in the master
database.
Restoring the master DB will restore all backup devices but it will have
many more implications that you usually don't want to mess with.
Since each device is stored as a simple single row in sysdevices, Perhaps
the easiest way will be to simply export the data from the table and import
it back on your new installation.
The sysdevices table is a "stand alone" table with no reference to any other
tables so it should be pretty straight forward.
You will need to configure the server to allow updates to system tables
using sp_configure:
EXEC sp_configure 'Show Advnaced Options',1
RECONFIGURE
EXEC sp_configure 'Allow Updates',1
RECONFIGURE WITH OVERRIDE
INSERT INTO master..sysdevices
SELECT * FROM <previous_sysdevices> WHERE cntrltype > 0
-- 0 is used for the system DB files
EXEC sp_configure 'Allow Updates',0
RECONFIGURE
* WARNING - Messing with system tables is not recommended and not supported
by MS.
HTH
Ami
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:%23RoyzSIeFHA.1400@.TK2MSFTNGP15.phx.gbl...
> Hi, I am updagrading database server to new datbase server.
> I want to transfer the backup devices info from old server to new
> server.
> is there any way so that I can script backup devices on old server
> and deploy them to new server.
> This will save me lot of time.
> Thanks in advance
>|||Hi,
You could write a script with system stored procedure sp_addumpdevice based
on MASTER..SYSDEVICES table.
Thanks
Hari
SQL Server MVP
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:%23RoyzSIeFHA.1400@.TK2MSFTNGP15.phx.gbl...
> Hi, I am updagrading database server to new datbase server.
> I want to transfer the backup devices info from old server to new
> server.
> is there any way so that I can script backup devices on old server
> and deploy them to new server.
> This will save me lot of time.
> Thanks in advance
>|||Thank you very much.
"Ami Levin" <XXX__NO_SPAM__XXX__Levin_Ami@.Yahoo.com> wrote in message
news:OL$SDEJeFHA.2420@.TK2MSFTNGP15.phx.gbl...
> Hi DKR,
> I don't think you can script the backup devices from any of the management
> tools.
> SQL DMO has a Backup Device object with a Script method but that means you
> will need to write procedural code for that.
> See
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqldmo/dmoref_m_s_8wz6.asp
> The backup devices are stored in the "sysdevices" table in the master
> database.
> Restoring the master DB will restore all backup devices but it will have
> many more implications that you usually don't want to mess with.
> Since each device is stored as a simple single row in sysdevices, Perhaps
> the easiest way will be to simply export the data from the table and
import
> it back on your new installation.
> The sysdevices table is a "stand alone" table with no reference to any
other
> tables so it should be pretty straight forward.
> You will need to configure the server to allow updates to system tables
> using sp_configure:
> EXEC sp_configure 'Show Advnaced Options',1
> RECONFIGURE
> EXEC sp_configure 'Allow Updates',1
> RECONFIGURE WITH OVERRIDE
> INSERT INTO master..sysdevices
> SELECT * FROM <previous_sysdevices> WHERE cntrltype > 0
> -- 0 is used for the system DB files
> EXEC sp_configure 'Allow Updates',0
> RECONFIGURE
>
> * WARNING - Messing with system tables is not recommended and not
supported
> by MS.
> HTH
> Ami
> "DKRReddy" <dkrreddy@.hotmail.com> wrote in message
> news:%23RoyzSIeFHA.1400@.TK2MSFTNGP15.phx.gbl...
> > Hi, I am updagrading database server to new datbase server.
> > I want to transfer the backup devices info from old server to new
> > server.
> > is there any way so that I can script backup devices on old server
> > and deploy them to new server.
> > This will save me lot of time.
> >
> > Thanks in advance
> >
> >
>

Backup devices and expired backups

Hello, I am having a problem with backup devices, and
hope someone knows a solution.
I have created a backup device on my 100GB D: drive
called Mustang_Backup. The file is located at
D:\MSSQL\BACKUP\Mustang_Backup.BAK.
My maintenance plan is a full backup on Sunday and a
differential on all other days. These are working
correctly.
My server also has a 14 day retention setting.
My problem is that the backup device is not removing the
expired backups. I now have a 75GB backup file for a 3GB
database. I am concerned that the backups are going to
fill the entire drive and then start to fail.
Does anyone know of a way to delete or remove the expired
backups in a device?
Thanks for your help,
SteveYou can't keep the different backups in one device if you want to remove
some of them after a certain time. It's all or nothing in a single device.
If your using the MP then have it create different files for each backup and
it should work as expected.
--
Andrew J. Kelly
SQL Server MVP
"steve" <sgent001@.hotmail.com> wrote in message
news:055b01c35556$78041df0$a301280a@.phx.gbl...
> Hello, I am having a problem with backup devices, and
> hope someone knows a solution.
> I have created a backup device on my 100GB D: drive
> called Mustang_Backup. The file is located at
> D:\MSSQL\BACKUP\Mustang_Backup.BAK.
> My maintenance plan is a full backup on Sunday and a
> differential on all other days. These are working
> correctly.
> My server also has a 14 day retention setting.
> My problem is that the backup device is not removing the
> expired backups. I now have a 75GB backup file for a 3GB
> database. I am concerned that the backups are going to
> fill the entire drive and then start to fail.
> Does anyone know of a way to delete or remove the expired
> backups in a device?
> Thanks for your help,
> Stevesql

Backup devices and backup expire

We have a third party application that backs up a sql server database as
part of its overall backup plan. This is hardwired into the application, so
that it must have a backup device with a specific name. I would like to drop
backup sets from the device after a week, so that the backup device does not
grow without bounds. I have played with expiredate/retaindays but this is
not the answer.
Any suggestions would be most appreciated.
Thanks,
John
Unfortunately, you cannot remove part of a backup device, it is all or nothing. An option can be to copy the
physical file at regular intervals over to a new file name and then delete these files as they get too old.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"John" <jkraeck@.NOprincetonSPAM.edu> wrote in message news:uol5hQWIEHA.308@.tk2msftngp13.phx.gbl...
> We have a third party application that backs up a sql server database as
> part of its overall backup plan. This is hardwired into the application, so
> that it must have a backup device with a specific name. I would like to drop
> backup sets from the device after a week, so that the backup device does not
> grow without bounds. I have played with expiredate/retaindays but this is
> not the answer.
> Any suggestions would be most appreciated.
> Thanks,
> John
>
|||Tibor,
Thanks for the response. Alas, I thought that might be the case.
I will look into alternate methods of working around the issue. There is
always some way out.
Cheers,
John
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23FnpQhWIEHA.2688@.tk2msftngp13.phx.gbl...
> Unfortunately, you cannot remove part of a backup device, it is all or
nothing. An option can be to copy the
> physical file at regular intervals over to a new file name and then delete
these files as they get too old.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "John" <jkraeck@.NOprincetonSPAM.edu> wrote in message
news:uol5hQWIEHA.308@.tk2msftngp13.phx.gbl...[color=darkblue]
so[color=darkblue]
drop[color=darkblue]
not[color=darkblue]
is
>

Backup devices and backup expire

We have a third party application that backs up a sql server database as
part of its overall backup plan. This is hardwired into the application, so
that it must have a backup device with a specific name. I would like to drop
backup sets from the device after a week, so that the backup device does not
grow without bounds. I have played with expiredate/retaindays but this is
not the answer.
Any suggestions would be most appreciated.
Thanks,
JohnUnfortunately, you cannot remove part of a backup device, it is all or nothi
ng. An option can be to copy the
physical file at regular intervals over to a new file name and then delete t
hese files as they get too old.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"John" <jkraeck@.NOprincetonSPAM.edu> wrote in message news:uol5hQWIEHA.308@.tk2msftngp13.phx
.gbl...
> We have a third party application that backs up a sql server database as
> part of its overall backup plan. This is hardwired into the application, s
o
> that it must have a backup device with a specific name. I would like to dr
op
> backup sets from the device after a week, so that the backup device does n
ot
> grow without bounds. I have played with expiredate/retaindays but this is
> not the answer.
> Any suggestions would be most appreciated.
> Thanks,
> John
>|||Tibor,
Thanks for the response. Alas, I thought that might be the case.
I will look into alternate methods of working around the issue. There is
always some way out.
Cheers,
John
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23FnpQhWIEHA.2688@.tk2msftngp13.phx.gbl...
> Unfortunately, you cannot remove part of a backup device, it is all or
nothing. An option can be to copy the
> physical file at regular intervals over to a new file name and then delete
these files as they get too old.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "John" <jkraeck@.NOprincetonSPAM.edu> wrote in message
news:uol5hQWIEHA.308@.tk2msftngp13.phx.gbl...
so
drop
not
is
>

Backup devices and backup expire

We have a third party application that backs up a sql server database as
part of its overall backup plan. This is hardwired into the application, so
that it must have a backup device with a specific name. I would like to drop
backup sets from the device after a week, so that the backup device does not
grow without bounds. I have played with expiredate/retaindays but this is
not the answer.
Any suggestions would be most appreciated.
Thanks,
JohnUnfortunately, you cannot remove part of a backup device, it is all or nothing. An option can be to copy the
physical file at regular intervals over to a new file name and then delete these files as they get too old.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"John" <jkraeck@.NOprincetonSPAM.edu> wrote in message news:uol5hQWIEHA.308@.tk2msftngp13.phx.gbl...
> We have a third party application that backs up a sql server database as
> part of its overall backup plan. This is hardwired into the application, so
> that it must have a backup device with a specific name. I would like to drop
> backup sets from the device after a week, so that the backup device does not
> grow without bounds. I have played with expiredate/retaindays but this is
> not the answer.
> Any suggestions would be most appreciated.
> Thanks,
> John
>|||Tibor,
Thanks for the response. Alas, I thought that might be the case.
I will look into alternate methods of working around the issue. There is
always some way out.
Cheers,
John
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23FnpQhWIEHA.2688@.tk2msftngp13.phx.gbl...
> Unfortunately, you cannot remove part of a backup device, it is all or
nothing. An option can be to copy the
> physical file at regular intervals over to a new file name and then delete
these files as they get too old.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "John" <jkraeck@.NOprincetonSPAM.edu> wrote in message
news:uol5hQWIEHA.308@.tk2msftngp13.phx.gbl...
> > We have a third party application that backs up a sql server database as
> > part of its overall backup plan. This is hardwired into the application,
so
> > that it must have a backup device with a specific name. I would like to
drop
> > backup sets from the device after a week, so that the backup device does
not
> > grow without bounds. I have played with expiredate/retaindays but this
is
> > not the answer.
> >
> > Any suggestions would be most appreciated.
> >
> > Thanks,
> > John
> >
> >
>

Backup devices (named pipes)

I hope someone can at least point me in the right direction on this one.
I've written some procs and created appropriate tables to replicate logshipping without incurring the added expense of Enterprise, just for the GUI.
I'm now looking for ways to extend/enhance this functionality. Currently I use a commandline compression app (bzip2) to compress the tran log backup after it's been written to disk, and although it works, I was thinking it would be nice if I could capture the backup stream on the fly (before it get's written to disk) and compress it before writing to disk. I've found several products that do this, but almost no documentation on the actual process of capturing the backup output.
Through a little sleuthing, I was able to create a device with sp_addumpdevice using a named pipe, and a small .Net app that was to read from that pipe, but I'm running into a problem. For some reason, all I get is the header information, then SQL server seems to expect a response from me and if I don't respond within a certain timeframe (let alone that I have no idea what to send it) it closes the pipe and the backup operation fails with a device write error, listing OS error 232(pipe being closed) in the logs.
Any assistance that someone could provide would be greatly appreciated.
Thanks in advance for taking the time to read this gobbledy-gook!

You can write a VDI (Virtual Device Interface) application to get access to the backup data.

Named pipe backups have been deprecated since sql7, and are removed in sql2005.

You can download doc and samples for VDI here:

http://www.microsoft.com/downloads/details.aspx?FamilyID=416f8a51-65a3-4e8e-a4c8-adfe15e850fc&DisplayLang=en

|||Thanks very much for the quick reply Steve. Unfortunately, I won't be able to utilize sql2005 in a production environment for at least another year. We have many legacy systems that interface with SQL2000 via batch loads, DTS and a vagary of third party interfaces (Liant Relativity for example) and until testing is complete company wide, policy is that SQL2005 not be used.
That being the case, are there any resources relevant to this situation in SQL2000?
Also, I apologize if this is posted in the wrong forum, but as I wasn't able to find any SQL2000 forums, this seemed the most appropriate place to pose my question.
Thanks again for your help!
|||

You can use VDI since sql7, so that should do the trick for you.

The VDI has not changed since sql2000, so our newer 2005 spec is mostly just clarifications. Any VDI app written for sql2000 (or sql7) should work without changes on sql2000 or sql2005.

I strongly advise you against writing a named pipe backup application. The reason is that such an app will not be compatible with sql2005.

Of course you are free to pass the VDI stream on via your own named pipes if you really want to.

This forum is good for any sql questions.

Cheers,

backup devices

hey all,
i'm trying to do a full backup on my database. there is a disk device
currently assigned to do a differential backup on the db. how do i get my
backup without messing up the current device. because there have been a
couple of times where i ran the backup using osq and it still ended up
changing the device and i didn't want it to do that.
thanks,
rodchar
Hi,
Use Maintenance Plan in Enterprise manager / SQL Server Management Studio to
do the full database backup. This will automatically create file names based
on Database name
and date and time stamp. Maintenance plan have an option of archival
also...s
Thanks
Hari
"rodchar" <rodchar@.discussions.microsoft.com> wrote in message
news:2DC3B373-0006-4C19-BECC-A277913BC48D@.microsoft.com...
> hey all,
> i'm trying to do a full backup on my database. there is a disk device
> currently assigned to do a differential backup on the db. how do i get my
> backup without messing up the current device. because there have been a
> couple of times where i ran the backup using osq and it still ended up
> changing the device and i didn't want it to do that.
> thanks,
> rodchar
>
|||thank you for your help. rod.
"Hari Prasad" wrote:

> Hi,
> Use Maintenance Plan in Enterprise manager / SQL Server Management Studio to
> do the full database backup. This will automatically create file names based
> on Database name
> and date and time stamp. Maintenance plan have an option of archival
> also...s
> Thanks
> Hari
> "rodchar" <rodchar@.discussions.microsoft.com> wrote in message
> news:2DC3B373-0006-4C19-BECC-A277913BC48D@.microsoft.com...
>
>
sql

backup devices

hey all,
i'm trying to do a full backup on my database. there is a disk device
currently assigned to do a differential backup on the db. how do i get my
backup without messing up the current device. because there have been a
couple of times where i ran the backup using osq and it still ended up
changing the device and i didn't want it to do that.
thanks,
rodcharHi,
Use Maintenance Plan in Enterprise manager / SQL Server Management Studio to
do the full database backup. This will automatically create file names based
on Database name
and date and time stamp. Maintenance plan have an option of archival
also...s
Thanks
Hari
"rodchar" <rodchar@.discussions.microsoft.com> wrote in message
news:2DC3B373-0006-4C19-BECC-A277913BC48D@.microsoft.com...
> hey all,
> i'm trying to do a full backup on my database. there is a disk device
> currently assigned to do a differential backup on the db. how do i get my
> backup without messing up the current device. because there have been a
> couple of times where i ran the backup using osq and it still ended up
> changing the device and i didn't want it to do that.
> thanks,
> rodchar
>|||thank you for your help. rod.
"Hari Prasad" wrote:
> Hi,
> Use Maintenance Plan in Enterprise manager / SQL Server Management Studio to
> do the full database backup. This will automatically create file names based
> on Database name
> and date and time stamp. Maintenance plan have an option of archival
> also...s
> Thanks
> Hari
> "rodchar" <rodchar@.discussions.microsoft.com> wrote in message
> news:2DC3B373-0006-4C19-BECC-A277913BC48D@.microsoft.com...
> > hey all,
> > i'm trying to do a full backup on my database. there is a disk device
> > currently assigned to do a differential backup on the db. how do i get my
> > backup without messing up the current device. because there have been a
> > couple of times where i ran the backup using osq and it still ended up
> > changing the device and i didn't want it to do that.
> >
> > thanks,
> > rodchar
> >
>
>

backup devices

hey all,
i'm trying to do a full backup on my database. there is a disk device
currently assigned to do a differential backup on the db. how do i get my
backup without messing up the current device. because there have been a
couple of times where i ran the backup using osq and it still ended up
changing the device and i didn't want it to do that.
thanks,
rodcharHi,
Use Maintenance Plan in Enterprise manager / SQL Server Management Studio to
do the full database backup. This will automatically create file names based
on Database name
and date and time stamp. Maintenance plan have an option of archival
also...s
Thanks
Hari
"rodchar" <rodchar@.discussions.microsoft.com> wrote in message
news:2DC3B373-0006-4C19-BECC-A277913BC48D@.microsoft.com...
> hey all,
> i'm trying to do a full backup on my database. there is a disk device
> currently assigned to do a differential backup on the db. how do i get my
> backup without messing up the current device. because there have been a
> couple of times where i ran the backup using osq and it still ended up
> changing the device and i didn't want it to do that.
> thanks,
> rodchar
>|||thank you for your help. rod.
"Hari Prasad" wrote:

> Hi,
> Use Maintenance Plan in Enterprise manager / SQL Server Management Studio
to
> do the full database backup. This will automatically create file names bas
ed
> on Database name
> and date and time stamp. Maintenance plan have an option of archival
> also...s
> Thanks
> Hari
> "rodchar" <rodchar@.discussions.microsoft.com> wrote in message
> news:2DC3B373-0006-4C19-BECC-A277913BC48D@.microsoft.com...
>
>

Backup Device size

I am looking for a way to retrieve the size of my backup devices. When I run sp_helpdevice, the backup devices with a cntrltype = 2 (which happen to be the only ones I am interested in), all return a size = 0. Any idea how to get the size using T-SQL?
Message posted via http://www.sqlmonster.com
How about the file_size in the BackupFile system table?
Andrew J. Kelly SQL MVP
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:a46bfcae27494128849870935f2de9b3@.SQLMonster.c om...
>I am looking for a way to retrieve the size of my backup devices. When I
>run sp_helpdevice, the backup devices with a cntrltype = 2 (which happen to
>be the only ones I am interested in), all return a size = 0. Any idea how
>to get the size using T-SQL?
> --
> Message posted via http://www.sqlmonster.com
|||That does not provide the size of the backup files (as in *.bak), but instead provides the files that are being backed up. I am looking for the size of the *.bak files.
Message posted via http://www.sqlmonster.com
|||OK then how about Backup_Size in sysbackuphistory?
Andrew J. Kelly SQL MVP
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:5d56319a8f704287b2cbe0cc361395fc@.SQLMonster.c om...
> That does not provide the size of the backup files (as in *.bak), but
> instead provides the files that are being backed up. I am looking for the
> size of the *.bak files.
> --
> Message posted via http://www.sqlmonster.com
|||I am running SQL 2000 and do not seem to be able to find sysbackuphistory.
Message posted via http://www.sqlmonster.com
|||I am sorry I meant the BackupSet system table.
Andrew J. Kelly SQL MVP
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:13e45dbcb76d4537a615b957827d5d8a@.SQLMonster.c om...
>I am running SQL 2000 and do not seem to be able to find sysbackuphistory.
> --
> Message posted via http://www.sqlmonster.com
sql

Backup Device size

I am looking for a way to retrieve the size of my backup devices. When I run
sp_helpdevice, the backup devices with a cntrltype = 2 (which happen to be
the only ones I am interested in), all return a size = 0. Any idea how to ge
t the size using T-SQL?
Message posted via http://www.droptable.comHow about the file_size in the BackupFile system table?
Andrew J. Kelly SQL MVP
"Robert Richards via droptable.com" <forum@.droptable.com> wrote in message
news:a46bfcae27494128849870935f2de9b3@.SQ
droptable.com...
>I am looking for a way to retrieve the size of my backup devices. When I
>run sp_helpdevice, the backup devices with a cntrltype = 2 (which happen to
>be the only ones I am interested in), all return a size = 0. Any idea how
>to get the size using T-SQL?
> --
> Message posted via http://www.droptable.com|||That does not provide the size of the backup files (as in *.bak), but instea
d provides the files that are being backed up. I am looking for the size of
the *.bak files.
Message posted via http://www.droptable.com|||OK then how about Backup_Size in sysbackuphistory?
Andrew J. Kelly SQL MVP
"Robert Richards via droptable.com" <forum@.droptable.com> wrote in message
news:5d56319a8f704287b2cbe0cc361395fc@.SQ
droptable.com...
> That does not provide the size of the backup files (as in *.bak), but
> instead provides the files that are being backed up. I am looking for the
> size of the *.bak files.
> --
> Message posted via http://www.droptable.com|||I am running SQL 2000 and do not seem to be able to find sysbackuphistory.
Message posted via http://www.droptable.com|||I am sorry I meant the BackupSet system table.
Andrew J. Kelly SQL MVP
"Robert Richards via droptable.com" <forum@.droptable.com> wrote in message
news:13e45dbcb76d4537a615b957827d5d8a@.SQ
droptable.com...
>I am running SQL 2000 and do not seem to be able to find sysbackuphistory.
> --
> Message posted via http://www.droptable.com

Backup Device size

I am looking for a way to retrieve the size of my backup devices. When I run sp_helpdevice, the backup devices with a cntrltype = 2 (which happen to be the only ones I am interested in), all return a size = 0. Any idea how to get the size using T-SQL?
--
Message posted via http://www.sqlmonster.comHow about the file_size in the BackupFile system table?
--
Andrew J. Kelly SQL MVP
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:a46bfcae27494128849870935f2de9b3@.SQLMonster.com...
>I am looking for a way to retrieve the size of my backup devices. When I
>run sp_helpdevice, the backup devices with a cntrltype = 2 (which happen to
>be the only ones I am interested in), all return a size = 0. Any idea how
>to get the size using T-SQL?
> --
> Message posted via http://www.sqlmonster.com|||That does not provide the size of the backup files (as in *.bak), but instead provides the files that are being backed up. I am looking for the size of the *.bak files.
--
Message posted via http://www.sqlmonster.com|||OK then how about Backup_Size in sysbackuphistory?
--
Andrew J. Kelly SQL MVP
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:5d56319a8f704287b2cbe0cc361395fc@.SQLMonster.com...
> That does not provide the size of the backup files (as in *.bak), but
> instead provides the files that are being backed up. I am looking for the
> size of the *.bak files.
> --
> Message posted via http://www.sqlmonster.com|||I am running SQL 2000 and do not seem to be able to find sysbackuphistory.
--
Message posted via http://www.sqlmonster.com|||I am sorry I meant the BackupSet system table.
--
Andrew J. Kelly SQL MVP
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:13e45dbcb76d4537a615b957827d5d8a@.SQLMonster.com...
>I am running SQL 2000 and do not seem to be able to find sysbackuphistory.
> --
> Message posted via http://www.sqlmonster.com

backup database

Dear all,
I backup the master, msdb, and my database from sql server 2000 by the add a backup devices and backup all of these db into the backup devices..
Then it have one .bak file including the db's. Is it can restore by the sql server 2000 when i reinstall the windows server and sql 2000 server?
or can i restore it into a new hardware system with windows server and sql 2000server?
Hi;
This BAK file can be restored in the below scenarios:
1. Restore in the same SQL server with a different name.
2. Restore it in the SQL Server after installation of Windows and SQL Server
3. Restore it in a SQL server with the same database name in a new hardware
For all the above you can use "RESTORE database " command.
Note:
This BAK files can be used when there is a crash of database. Better copy
this BAK file to
a safe place preferably to a TAPE device.
Thanks
Hari
MCDBA
"ken" <anonymous@.discussions.microsoft.com> wrote in message
news:672AFD82-EF81-4080-96D2-6344C048DCA2@.microsoft.com...
> Dear all,
> I backup the master, msdb, and my database from sql server 2000 by the add
a backup devices and backup all of these db into the backup devices..
> Then it have one .bak file including the db's. Is it can restore by the
sql server 2000 when i reinstall the windows server and sql 2000 server?
> or can i restore it into a new hardware system with windows server and sql
2000server?
|||The SQL Server that you are restoring to has to have the same build number
as the one the backup came from.
This applies to the system databases, not the user databases.
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||What is the build number?
If i reinstall the windows server as well as the sql server, will the build number become a new number?

backup database

Dear all,
I backup the master, msdb, and my database from sql server 2000 by the add a
backup devices and backup all of these db into the backup devices..
Then it have one .bak file including the db's. Is it can restore by the sql
server 2000 when i reinstall the windows server and sql 2000 server?
or can i restore it into a new hardware system with windows server and sql 2
000server?Hi;
This BAK file can be restored in the below scenarios:
1. Restore in the same SQL server with a different name.
2. Restore it in the SQL Server after installation of Windows and SQL Server
3. Restore it in a SQL server with the same database name in a new hardware
For all the above you can use "RESTORE database " command.
Note:
This BAK files can be used when there is a crash of database. Better copy
this BAK file to
a safe place preferably to a TAPE device.
Thanks
Hari
MCDBA
"ken" <anonymous@.discussions.microsoft.com> wrote in message
news:672AFD82-EF81-4080-96D2-6344C048DCA2@.microsoft.com...
> Dear all,
> I backup the master, msdb, and my database from sql server 2000 by the add
a backup devices and backup all of these db into the backup devices..
> Then it have one .bak file including the db's. Is it can restore by the
sql server 2000 when i reinstall the windows server and sql 2000 server?
> or can i restore it into a new hardware system with windows server and sql
2000server?|||The SQL Server that you are restoring to has to have the same build number
as the one the backup came from.
This applies to the system databases, not the user databases.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||What is the build number?
If i reinstall the windows server as well as the sql server, will the build
number become a new number?