Hi just wondering when you set up a scheluled database backup, does the old
backup file get written over each time the scheduled backup takes place as
this is what I would like it to do?
thanks.
Paul G
Software engineer.
The BACKUP command does not overwrite by default. Defile is NOINIT which is append. To overwrite,
specify INIT. If you schedule using some type if GUI instead of typing the command, we need to know
more about that GUI and what checkboxes etc you have checked.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:D8C9F159-8933-4EF8-85E8-2873E9815B22@.microsoft.com...
> Hi just wondering when you set up a scheluled database backup, does the old
> backup file get written over each time the scheduled backup takes place as
> this is what I would like it to do?
> thanks.
> --
> Paul G
> Software engineer.
|||Paul,
The default is NOINIT which appends the backup to the device. You can use
WITH INIT to overwrite the contents of the backup device.
See BACKUP in the SQL BOL for more details.
HTH
Jerry
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:D8C9F159-8933-4EF8-85E8-2873E9815B22@.microsoft.com...
> Hi just wondering when you set up a scheluled database backup, does the
> old
> backup file get written over each time the scheduled backup takes place as
> this is what I would like it to do?
> thanks.
> --
> Paul G
> Software engineer.
|||ok thanks did use the gui to set up the schedule.
Paul G
Software engineer.
"Jerry Spivey" wrote:
> Paul,
> The default is NOINIT which appends the backup to the device. You can use
> WITH INIT to overwrite the contents of the backup device.
> See BACKUP in the SQL BOL for more details.
> HTH
> Jerry
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:D8C9F159-8933-4EF8-85E8-2873E9815B22@.microsoft.com...
>
>
|||looks like the gui has a selection do not keep longer than 4 weeks which will
work for what I needed. I tried to start the server agent service and get
the following error,
error 5-access is denied. I am able to access everything on the dbase
server so not sure why I am getting this error. Do you know where I need to
check the permissions? thanks.
Paul G
Software engineer.
"Tibor Karaszi" wrote:
> The BACKUP command does not overwrite by default. Defile is NOINIT which is append. To overwrite,
> specify INIT. If you schedule using some type if GUI instead of typing the command, we need to know
> more about that GUI and what checkboxes etc you have checked.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:D8C9F159-8933-4EF8-85E8-2873E9815B22@.microsoft.com...
>
|||By GUI, I assume you mean the Maintenance Plan Wizard. Yes, it has an option to delete old backup
files. It creates a new file each time you do backup, including a time stamp in the file name. As
for the access denied errors, we need more information. First, understand that the service account
for SQL Server Agent and for the SQL Server service need permissions on whatever you need to do
(like accessing the backup share). I suggest you define a report file for your maint plan, as that
will probably give more details information about exactly what it is that access is denied for etc.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:4AD647D2-C767-4B8B-B02D-F1752D96D0C6@.microsoft.com...[vbcol=seagreen]
> looks like the gui has a selection do not keep longer than 4 weeks which will
> work for what I needed. I tried to start the server agent service and get
> the following error,
> error 5-access is denied. I am able to access everything on the dbase
> server so not sure why I am getting this error. Do you know where I need to
> check the permissions? thanks.
> --
> Paul G
> Software engineer.
>
> "Tibor Karaszi" wrote:
Showing posts with label written. Show all posts
Showing posts with label written. Show all posts
Sunday, March 25, 2012
database backup-overwrites old backupfile?
Hi just wondering when you set up a scheluled database backup, does the old
backup file get written over each time the scheduled backup takes place as
this is what I would like it to do?
thanks.
--
Paul G
Software engineer.The BACKUP command does not overwrite by default. Defile is NOINIT which is
append. To overwrite,
specify INIT. If you schedule using some type if GUI instead of typing the c
ommand, we need to know
more about that GUI and what checkboxes etc you have checked.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:D8C9F159-8933-4EF8-85E8-2873E9815B22@.microsoft.com...
> Hi just wondering when you set up a scheluled database backup, does the ol
d
> backup file get written over each time the scheduled backup takes place as
> this is what I would like it to do?
> thanks.
> --
> Paul G
> Software engineer.|||Paul,
The default is NOINIT which appends the backup to the device. You can use
WITH INIT to overwrite the contents of the backup device.
See BACKUP in the SQL BOL for more details.
HTH
Jerry
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:D8C9F159-8933-4EF8-85E8-2873E9815B22@.microsoft.com...
> Hi just wondering when you set up a scheluled database backup, does the
> old
> backup file get written over each time the scheduled backup takes place as
> this is what I would like it to do?
> thanks.
> --
> Paul G
> Software engineer.|||ok thanks did use the gui to set up the schedule.
--
Paul G
Software engineer.
"Jerry Spivey" wrote:
> Paul,
> The default is NOINIT which appends the backup to the device. You can use
> WITH INIT to overwrite the contents of the backup device.
> See BACKUP in the SQL BOL for more details.
> HTH
> Jerry
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:D8C9F159-8933-4EF8-85E8-2873E9815B22@.microsoft.com...
>
>|||looks like the gui has a selection do not keep longer than 4 weeks which wil
l
work for what I needed. I tried to start the server agent service and get
the following error,
error 5-access is denied. I am able to access everything on the dbase
server so not sure why I am getting this error. Do you know where I need to
check the permissions? thanks.
--
Paul G
Software engineer.
"Tibor Karaszi" wrote:
> The BACKUP command does not overwrite by default. Defile is NOINIT which i
s append. To overwrite,
> specify INIT. If you schedule using some type if GUI instead of typing the
command, we need to know
> more about that GUI and what checkboxes etc you have checked.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:D8C9F159-8933-4EF8-85E8-2873E9815B22@.microsoft.com...
>|||By GUI, I assume you mean the Maintenance Plan Wizard. Yes, it has an option
to delete old backup
files. It creates a new file each time you do backup, including a time stamp
in the file name. As
for the access denied errors, we need more information. First, understand th
at the service account
for SQL Server Agent and for the SQL Server service need permissions on what
ever you need to do
(like accessing the backup share). I suggest you define a report file for yo
ur maint plan, as that
will probably give more details information about exactly what it is that ac
cess is denied for etc.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:4AD647D2-C767-4B8B-B02D-F1752D96D0C6@.microsoft.com...[vbcol=seagreen]
> looks like the gui has a selection do not keep longer than 4 weeks which w
ill
> work for what I needed. I tried to start the server agent service and get
> the following error,
> error 5-access is denied. I am able to access everything on the dbase
> server so not sure why I am getting this error. Do you know where I need
to
> check the permissions? thanks.
> --
> Paul G
> Software engineer.
>
> "Tibor Karaszi" wrote:
>
backup file get written over each time the scheduled backup takes place as
this is what I would like it to do?
thanks.
--
Paul G
Software engineer.The BACKUP command does not overwrite by default. Defile is NOINIT which is
append. To overwrite,
specify INIT. If you schedule using some type if GUI instead of typing the c
ommand, we need to know
more about that GUI and what checkboxes etc you have checked.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:D8C9F159-8933-4EF8-85E8-2873E9815B22@.microsoft.com...
> Hi just wondering when you set up a scheluled database backup, does the ol
d
> backup file get written over each time the scheduled backup takes place as
> this is what I would like it to do?
> thanks.
> --
> Paul G
> Software engineer.|||Paul,
The default is NOINIT which appends the backup to the device. You can use
WITH INIT to overwrite the contents of the backup device.
See BACKUP in the SQL BOL for more details.
HTH
Jerry
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:D8C9F159-8933-4EF8-85E8-2873E9815B22@.microsoft.com...
> Hi just wondering when you set up a scheluled database backup, does the
> old
> backup file get written over each time the scheduled backup takes place as
> this is what I would like it to do?
> thanks.
> --
> Paul G
> Software engineer.|||ok thanks did use the gui to set up the schedule.
--
Paul G
Software engineer.
"Jerry Spivey" wrote:
> Paul,
> The default is NOINIT which appends the backup to the device. You can use
> WITH INIT to overwrite the contents of the backup device.
> See BACKUP in the SQL BOL for more details.
> HTH
> Jerry
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:D8C9F159-8933-4EF8-85E8-2873E9815B22@.microsoft.com...
>
>|||looks like the gui has a selection do not keep longer than 4 weeks which wil
l
work for what I needed. I tried to start the server agent service and get
the following error,
error 5-access is denied. I am able to access everything on the dbase
server so not sure why I am getting this error. Do you know where I need to
check the permissions? thanks.
--
Paul G
Software engineer.
"Tibor Karaszi" wrote:
> The BACKUP command does not overwrite by default. Defile is NOINIT which i
s append. To overwrite,
> specify INIT. If you schedule using some type if GUI instead of typing the
command, we need to know
> more about that GUI and what checkboxes etc you have checked.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:D8C9F159-8933-4EF8-85E8-2873E9815B22@.microsoft.com...
>|||By GUI, I assume you mean the Maintenance Plan Wizard. Yes, it has an option
to delete old backup
files. It creates a new file each time you do backup, including a time stamp
in the file name. As
for the access denied errors, we need more information. First, understand th
at the service account
for SQL Server Agent and for the SQL Server service need permissions on what
ever you need to do
(like accessing the backup share). I suggest you define a report file for yo
ur maint plan, as that
will probably give more details information about exactly what it is that ac
cess is denied for etc.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:4AD647D2-C767-4B8B-B02D-F1752D96D0C6@.microsoft.com...[vbcol=seagreen]
> looks like the gui has a selection do not keep longer than 4 weeks which w
ill
> work for what I needed. I tried to start the server agent service and get
> the following error,
> error 5-access is denied. I am able to access everything on the dbase
> server so not sure why I am getting this error. Do you know where I need
to
> check the permissions? thanks.
> --
> Paul G
> Software engineer.
>
> "Tibor Karaszi" wrote:
>
database backup-overwrites old backupfile?
Hi just wondering when you set up a scheluled database backup, does the old
backup file get written over each time the scheduled backup takes place as
this is what I would like it to do?
thanks.
--
Paul G
Software engineer.The BACKUP command does not overwrite by default. Defile is NOINIT which is append. To overwrite,
specify INIT. If you schedule using some type if GUI instead of typing the command, we need to know
more about that GUI and what checkboxes etc you have checked.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:D8C9F159-8933-4EF8-85E8-2873E9815B22@.microsoft.com...
> Hi just wondering when you set up a scheluled database backup, does the old
> backup file get written over each time the scheduled backup takes place as
> this is what I would like it to do?
> thanks.
> --
> Paul G
> Software engineer.|||Paul,
The default is NOINIT which appends the backup to the device. You can use
WITH INIT to overwrite the contents of the backup device.
See BACKUP in the SQL BOL for more details.
HTH
Jerry
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:D8C9F159-8933-4EF8-85E8-2873E9815B22@.microsoft.com...
> Hi just wondering when you set up a scheluled database backup, does the
> old
> backup file get written over each time the scheduled backup takes place as
> this is what I would like it to do?
> thanks.
> --
> Paul G
> Software engineer.|||ok thanks did use the gui to set up the schedule.
--
Paul G
Software engineer.
"Jerry Spivey" wrote:
> Paul,
> The default is NOINIT which appends the backup to the device. You can use
> WITH INIT to overwrite the contents of the backup device.
> See BACKUP in the SQL BOL for more details.
> HTH
> Jerry
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:D8C9F159-8933-4EF8-85E8-2873E9815B22@.microsoft.com...
> > Hi just wondering when you set up a scheluled database backup, does the
> > old
> > backup file get written over each time the scheduled backup takes place as
> > this is what I would like it to do?
> > thanks.
> > --
> > Paul G
> > Software engineer.
>
>|||looks like the gui has a selection do not keep longer than 4 weeks which will
work for what I needed. I tried to start the server agent service and get
the following error,
error 5-access is denied. I am able to access everything on the dbase
server so not sure why I am getting this error. Do you know where I need to
check the permissions? thanks.
--
Paul G
Software engineer.
"Tibor Karaszi" wrote:
> The BACKUP command does not overwrite by default. Defile is NOINIT which is append. To overwrite,
> specify INIT. If you schedule using some type if GUI instead of typing the command, we need to know
> more about that GUI and what checkboxes etc you have checked.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:D8C9F159-8933-4EF8-85E8-2873E9815B22@.microsoft.com...
> > Hi just wondering when you set up a scheluled database backup, does the old
> > backup file get written over each time the scheduled backup takes place as
> > this is what I would like it to do?
> > thanks.
> > --
> > Paul G
> > Software engineer.
>|||By GUI, I assume you mean the Maintenance Plan Wizard. Yes, it has an option to delete old backup
files. It creates a new file each time you do backup, including a time stamp in the file name. As
for the access denied errors, we need more information. First, understand that the service account
for SQL Server Agent and for the SQL Server service need permissions on whatever you need to do
(like accessing the backup share). I suggest you define a report file for your maint plan, as that
will probably give more details information about exactly what it is that access is denied for etc.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:4AD647D2-C767-4B8B-B02D-F1752D96D0C6@.microsoft.com...
> looks like the gui has a selection do not keep longer than 4 weeks which will
> work for what I needed. I tried to start the server agent service and get
> the following error,
> error 5-access is denied. I am able to access everything on the dbase
> server so not sure why I am getting this error. Do you know where I need to
> check the permissions? thanks.
> --
> Paul G
> Software engineer.
>
> "Tibor Karaszi" wrote:
>> The BACKUP command does not overwrite by default. Defile is NOINIT which is append. To overwrite,
>> specify INIT. If you schedule using some type if GUI instead of typing the command, we need to
>> know
>> more about that GUI and what checkboxes etc you have checked.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Paul" <Paul@.discussions.microsoft.com> wrote in message
>> news:D8C9F159-8933-4EF8-85E8-2873E9815B22@.microsoft.com...
>> > Hi just wondering when you set up a scheluled database backup, does the old
>> > backup file get written over each time the scheduled backup takes place as
>> > this is what I would like it to do?
>> > thanks.
>> > --
>> > Paul G
>> > Software engineer.
>>
backup file get written over each time the scheduled backup takes place as
this is what I would like it to do?
thanks.
--
Paul G
Software engineer.The BACKUP command does not overwrite by default. Defile is NOINIT which is append. To overwrite,
specify INIT. If you schedule using some type if GUI instead of typing the command, we need to know
more about that GUI and what checkboxes etc you have checked.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:D8C9F159-8933-4EF8-85E8-2873E9815B22@.microsoft.com...
> Hi just wondering when you set up a scheluled database backup, does the old
> backup file get written over each time the scheduled backup takes place as
> this is what I would like it to do?
> thanks.
> --
> Paul G
> Software engineer.|||Paul,
The default is NOINIT which appends the backup to the device. You can use
WITH INIT to overwrite the contents of the backup device.
See BACKUP in the SQL BOL for more details.
HTH
Jerry
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:D8C9F159-8933-4EF8-85E8-2873E9815B22@.microsoft.com...
> Hi just wondering when you set up a scheluled database backup, does the
> old
> backup file get written over each time the scheduled backup takes place as
> this is what I would like it to do?
> thanks.
> --
> Paul G
> Software engineer.|||ok thanks did use the gui to set up the schedule.
--
Paul G
Software engineer.
"Jerry Spivey" wrote:
> Paul,
> The default is NOINIT which appends the backup to the device. You can use
> WITH INIT to overwrite the contents of the backup device.
> See BACKUP in the SQL BOL for more details.
> HTH
> Jerry
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:D8C9F159-8933-4EF8-85E8-2873E9815B22@.microsoft.com...
> > Hi just wondering when you set up a scheluled database backup, does the
> > old
> > backup file get written over each time the scheduled backup takes place as
> > this is what I would like it to do?
> > thanks.
> > --
> > Paul G
> > Software engineer.
>
>|||looks like the gui has a selection do not keep longer than 4 weeks which will
work for what I needed. I tried to start the server agent service and get
the following error,
error 5-access is denied. I am able to access everything on the dbase
server so not sure why I am getting this error. Do you know where I need to
check the permissions? thanks.
--
Paul G
Software engineer.
"Tibor Karaszi" wrote:
> The BACKUP command does not overwrite by default. Defile is NOINIT which is append. To overwrite,
> specify INIT. If you schedule using some type if GUI instead of typing the command, we need to know
> more about that GUI and what checkboxes etc you have checked.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:D8C9F159-8933-4EF8-85E8-2873E9815B22@.microsoft.com...
> > Hi just wondering when you set up a scheluled database backup, does the old
> > backup file get written over each time the scheduled backup takes place as
> > this is what I would like it to do?
> > thanks.
> > --
> > Paul G
> > Software engineer.
>|||By GUI, I assume you mean the Maintenance Plan Wizard. Yes, it has an option to delete old backup
files. It creates a new file each time you do backup, including a time stamp in the file name. As
for the access denied errors, we need more information. First, understand that the service account
for SQL Server Agent and for the SQL Server service need permissions on whatever you need to do
(like accessing the backup share). I suggest you define a report file for your maint plan, as that
will probably give more details information about exactly what it is that access is denied for etc.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:4AD647D2-C767-4B8B-B02D-F1752D96D0C6@.microsoft.com...
> looks like the gui has a selection do not keep longer than 4 weeks which will
> work for what I needed. I tried to start the server agent service and get
> the following error,
> error 5-access is denied. I am able to access everything on the dbase
> server so not sure why I am getting this error. Do you know where I need to
> check the permissions? thanks.
> --
> Paul G
> Software engineer.
>
> "Tibor Karaszi" wrote:
>> The BACKUP command does not overwrite by default. Defile is NOINIT which is append. To overwrite,
>> specify INIT. If you schedule using some type if GUI instead of typing the command, we need to
>> know
>> more about that GUI and what checkboxes etc you have checked.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Paul" <Paul@.discussions.microsoft.com> wrote in message
>> news:D8C9F159-8933-4EF8-85E8-2873E9815B22@.microsoft.com...
>> > Hi just wondering when you set up a scheluled database backup, does the old
>> > backup file get written over each time the scheduled backup takes place as
>> > this is what I would like it to do?
>> > thanks.
>> > --
>> > Paul G
>> > Software engineer.
>>
Friday, February 17, 2012
Data Type and Length in SQL Server
Hello folks!
I have written a stored proc that selects data from this table:
A1 AA
A2 BB
B1 AAA
B2 BBB
and puts it another table in this manner:
A1 AA BB
A2 AA BB
B1 AAA BBB
B2 AAA BBB
In short, I wanted to concatenate data in the second column.
My question is for some reason, SQL Server is limiting the length of the string to only 256 characters, even though I have defined the local variables in the storeds proc as varchar(1000) and the table has that field defined as text.
Any ideas how I can get around this problem?
Thanks!
ParulVChances are, it is not limiting the string to 256 characters. This is probably due to the default 256 character result set width in Query Analyzer. Go through Query Analyzer's options and bump it up to a higher value.
I have written a stored proc that selects data from this table:
A1 AA
A2 BB
B1 AAA
B2 BBB
and puts it another table in this manner:
A1 AA BB
A2 AA BB
B1 AAA BBB
B2 AAA BBB
In short, I wanted to concatenate data in the second column.
My question is for some reason, SQL Server is limiting the length of the string to only 256 characters, even though I have defined the local variables in the storeds proc as varchar(1000) and the table has that field defined as text.
Any ideas how I can get around this problem?
Thanks!
ParulVChances are, it is not limiting the string to 256 characters. This is probably due to the default 256 character result set width in Query Analyzer. Go through Query Analyzer's options and bump it up to a higher value.
Subscribe to:
Posts (Atom)