Database become inaccessible

Hi,
log file of my database become inaccessible after some time.
After restarting mssqlserver, few hours it is working fine and then it happe
n again.
In the serverlog I can see:
2004-01-28 04:18:21.65 spid55 udopen: Operating system error 32(The proce
ss cannot access the file because it is being used by another process.) duri
ng the creation/opening of physical device C:\Program Files\Microsoft SQL Se
rver\MSSQL\Data\Replication
DB_log.LDF.
2004-01-28 04:18:21.67 spid55 FCB::Open failed: Could not open device C:\
Program Files\Microsoft SQL Server\MSSQL\Data\ReplicationDB_log.LDF for virt
ual device number (VDN) 2.
2004-01-28 04:18:21.78 spid55 Attempting to rebuild primary log file for
database ReplicationDB.
2004-01-28 04:18:21.78 spid55 FCB::CreateFile() failed with error 80 for
file C:\Program Files\Microsoft SQL Server\MSSQL\Data\ReplicationDB_log.LDF.
regards
janCheck eventlog etc for OS and HW errors. Also, check if you have autoclose o
n, that can result in
the file being closed that the file is used by some other program (anti-viru
s, defrag etc).
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ls
erver
"Jan Zatko" <jan.zatko@.frequentis.com> wrote in message
news:855E0933-1BE8-4B7F-94D1-BA214CBADDB7@.microsoft.com...

quote:

---

> Hi,
> log file of my database become inaccessible after some time.
> After restarting mssqlserver, few hours it is working fine and then it hap
pen again.
> In the serverlog I can see:
> 2004-01-28 04:18:21.65 spid55 udopen: Operating system error 32(The process cannot acce
ss the

---

file because it is being used by another process.) during the creation/openi
ng of physical device
C:\Program Files\Microsoft SQL Server\MSSQL\Data\ReplicationDB_log.LDF.

quote:

---

> 2004-01-28 04:18:21.67 spid55 FCB::Open failed: Could not open device C:\Program[/color
]

---

Files\Microsoft SQL Server\MSSQL\Data\ReplicationDB_log.LDF for virtual device number (VDN) 2.[QUO
TE]
> 2004-01-28 04:18:21.78 spid55 Attempting to rebuild primary log file for database[/colo
r][/QUOTE]
ReplicationDB.

quote:

---

> 2004-01-28 04:18:21.78 spid55 FCB::CreateFile() failed with error 80 for file C:\Progra
m

---

Files\Microsoft SQL Server\MSSQL\Data\ReplicationDB_log.LDF.

quote:

---

> regards
> jan

---

Database become inaccessible

Hi
log file of my database become inaccessible after some time.
After restarting mssqlserver, few hours it is working fine and then it happen again
In the serverlog I can see
2004-01-28 04:18:21.65 spid55 udopen: Operating system error 32(The process cannot access the file because it is being used by another process.) during the creation/opening of physical device C:\Program Files\Microsoft SQL Server\MSSQL\Data\ReplicationDB_log.LDF
2004-01-28 04:18:21.67 spid55 FCB::Open failed: Could not open device C:\Program Files\Microsoft SQL Server\MSSQL\Data\ReplicationDB_log.LDF for virtual device number (VDN) 2
2004-01-28 04:18:21.78 spid55 Attempting to rebuild primary log file for database ReplicationDB.
2004-01-28 04:18:21.78 spid55 FCB::CreateFile() failed with error 80 for file C:\Program Files\Microsoft SQL Server\MSSQL\Data\ReplicationDB_log.LDF.
regard
janCheck eventlog etc for OS and HW errors. Also, check if you have autoclose on, that can result in
the file being closed that the file is used by some other program (anti-virus, defrag etc).
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Jan Zatko" <jan.zatko@.frequentis.com> wrote in message
news:855E0933-1BE8-4B7F-94D1-BA214CBADDB7@.microsoft.com...
> Hi,
> log file of my database become inaccessible after some time.
> After restarting mssqlserver, few hours it is working fine and then it happen again.
> In the serverlog I can see:
> 2004-01-28 04:18:21.65 spid55 udopen: Operating system error 32(The process cannot access the
file because it is being used by another process.) during the creation/opening of physical device
C:\Program Files\Microsoft SQL Server\MSSQL\Data\ReplicationDB_log.LDF.
> 2004-01-28 04:18:21.67 spid55 FCB::Open failed: Could not open device C:\Program
Files\Microsoft SQL Server\MSSQL\Data\ReplicationDB_log.LDF for virtual device number (VDN) 2.
> 2004-01-28 04:18:21.78 spid55 Attempting to rebuild primary log file for database
ReplicationDB.
> 2004-01-28 04:18:21.78 spid55 FCB::CreateFile() failed with error 80 for file C:\Program
Files\Microsoft SQL Server\MSSQL\Data\ReplicationDB_log.LDF.
> regards
> jansql

database backups

Hi All
I creatred a service that backups all my databases to a file on the server.
My backup software will backup the files at night. I'm having problems
backing up databases who's names are all numbers (1234, 1211, .. etc). I can
backup the databases manually but my script will always fail. Do I have to
do anything special in T-sql to access a database whos name is all numbers?
Thanks
PhilTry enclosing the database name in brackets:
BACKUP DATABASE [1234]
TO DISK='\\SomeServer\SomeShare\1234.bak'
WITH INIT
BTW, you can make things easier by having database names confirm to the
rules for regular identifiers as described in the Books Online
<acdata.chm::/ac_8_con_03_6e9e.htm>.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Phillip D Edwards" <phil@.benesysinc.com> wrote in message
news:%23FuNKzseDHA.2464@.TK2MSFTNGP09.phx.gbl...
> Hi All
> I creatred a service that backups all my databases to a file on the
server.
> My backup software will backup the files at night. I'm having problems
> backing up databases who's names are all numbers (1234, 1211, .. etc).
I can
> backup the databases manually but my script will always fail. Do I
have to
> do anything special in T-sql to access a database whos name is all
numbers?
>
> Thanks
> Phil
>
>

Database backups

When I backup one of the SQL server databases, the size of the backup file
varies from 1.2, 3.6 or 9.8MB. All these backups were made w/in 30-min of
each other with only me doing testing, no other users. This makes me feel a
bit uneasy, like I'm not sure if the backups are valid. Is this normal?
How can test a backup to make sure it's complete? I mean w/o deleting the
database and doing a restore.> When I backup one of the SQL server databases, the size of the backup file
> varies from 1.2, 3.6 or 9.8MB. All these backups were made w/in 30-min of
> each other with only me doing testing, no other users. This makes me feel
a
> bit uneasy, like I'm not sure if the backups are valid. Is this normal?
Did you append backups to the same file? Have you done always the same
backups or do you have full database, transaction log and differential
backups?
> How can test a backup to make sure it's complete? I mean w/o deleting the
> database and doing a restore.
You can always restore to a new database, so this is not a problem. Check
the "How to restore a database backup (Enterprise Manager)" topic in Books
OnLine.
--
Dejan Sarka, SQL Server MVP
Please reply only to the newsgroups.

Database Backup-Restore Case

I have a database in SQL Server 2000 A. It has 2 data device and 2 log
device.
I have backup the database into a file, and try to restore it into another
SQL Server 2000.
The new database has only 1 data device and 1 log.
However, when I try to restore it, in Option page, it has 2 data device and
2 log device and I have no way to restore
how can I restore that database?
Ivan
Hi,
All the 4 files will be created during the database restore automatically.
No need to create the database before the restore.
So say in the source server you have a database called Finance with 4 files
( 2 MDF and 2 LDF). When you perform a backup all the
4 files will be stored in the backup file (FINANCE.BAK). Copy the
FINANCE.BAK to destination server to c:\backup folder (any folder)
Steps After that. (ALL in destination server)
1. Execute the below statement to get the associated file details
Restore filelistonly from disk='c:\backup\Finance.bak'
This will give you the details of all MDF and LDF files with Logical and
physical names of MDF and LDF files
2. Execute the below command to create and restore the database
Restore database NEWDBNAME from disk='c:\backup\Finance.bak'
with 'Logical_MDF_name' to 'c:\mssql\data\filename.mdf',
with 'Logical_NDF_name' to 'c:\mssql\data\filename.ndf',
with 'Logical_LDF_name' to 'c:\mssql\data\filename.ldf',
with 'Logical_LDF_name' to 'c:\mssql\data\filename.ldf'
Note:
You must give the exact "logical name " based on the out from FILELISTONLY
command (Step-1). Physical name and directory can be anything.
Thanks
Hari
MCDBA
"Ivan Kan" <ivankan@.no-ip.com> wrote in message
news:#anORPGREHA.2716@.tk2msftngp13.phx.gbl...
> I have a database in SQL Server 2000 A. It has 2 data device and 2 log
> device.
> I have backup the database into a file, and try to restore it into another
> SQL Server 2000.
> The new database has only 1 data device and 1 log.
> However, when I try to restore it, in Option page, it has 2 data device
and
> 2 log device and I have no way to restore
> how can I restore that database?
> Ivan
>
|||thx. It works!
Ivan
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:efsxaqGREHA.1388@.TK2MSFTNGP09.phx.gbl...
> Hi,
> All the 4 files will be created during the database restore automatically.
> No need to create the database before the restore.
> So say in the source server you have a database called Finance with 4
files[vbcol=seagreen]
> ( 2 MDF and 2 LDF). When you perform a backup all the
> 4 files will be stored in the backup file (FINANCE.BAK). Copy the
> FINANCE.BAK to destination server to c:\backup folder (any folder)
> Steps After that. (ALL in destination server)
> 1. Execute the below statement to get the associated file details
> Restore filelistonly from disk='c:\backup\Finance.bak'
> This will give you the details of all MDF and LDF files with Logical and
> physical names of MDF and LDF files
> 2. Execute the below command to create and restore the database
> Restore database NEWDBNAME from disk='c:\backup\Finance.bak'
> with 'Logical_MDF_name' to 'c:\mssql\data\filename.mdf',
> with 'Logical_NDF_name' to 'c:\mssql\data\filename.ndf',
> with 'Logical_LDF_name' to 'c:\mssql\data\filename.ldf',
> with 'Logical_LDF_name' to 'c:\mssql\data\filename.ldf'
> Note:
> You must give the exact "logical name " based on the out from FILELISTONLY
> command (Step-1). Physical name and directory can be anything.
> Thanks
> Hari
> MCDBA
>
>
>
>
> "Ivan Kan" <ivankan@.no-ip.com> wrote in message
> news:#anORPGREHA.2716@.tk2msftngp13.phx.gbl...
another
> and
>

Database Backup-Restore Case

I have a database in SQL Server 2000 A. It has 2 data device and 2 log
device.
I have backup the database into a file, and try to restore it into another
SQL Server 2000.
The new database has only 1 data device and 1 log.
However, when I try to restore it, in Option page, it has 2 data device and
2 log device and I have no way to restore
how can I restore that database?
IvanHi,
All the 4 files will be created during the database restore automatically.
No need to create the database before the restore.
So say in the source server you have a database called Finance with 4 files
( 2 MDF and 2 LDF). When you perform a backup all the
4 files will be stored in the backup file (FINANCE.BAK). Copy the
FINANCE.BAK to destination server to c:\backup folder (any folder)
Steps After that. (ALL in destination server)
1. Execute the below statement to get the associated file details
Restore filelistonly from disk='c:\backup\Finance.bak'
This will give you the details of all MDF and LDF files with Logical and
physical names of MDF and LDF files
2. Execute the below command to create and restore the database
Restore database NEWDBNAME from disk='c:\backup\Finance.bak'
with 'Logical_MDF_name' to 'c:\mssql\data\filename.mdf',
with 'Logical_NDF_name' to 'c:\mssql\data\filename.ndf',
with 'Logical_LDF_name' to 'c:\mssql\data\filename.ldf',
with 'Logical_LDF_name' to 'c:\mssql\data\filename.ldf'
Note:
You must give the exact "logical name " based on the out from FILELISTONLY
command (Step-1). Physical name and directory can be anything.
Thanks
Hari
MCDBA
"Ivan Kan" <ivankan@.no-ip.com> wrote in message
news:#anORPGREHA.2716@.tk2msftngp13.phx.gbl...
> I have a database in SQL Server 2000 A. It has 2 data device and 2 log
> device.
> I have backup the database into a file, and try to restore it into another
> SQL Server 2000.
> The new database has only 1 data device and 1 log.
> However, when I try to restore it, in Option page, it has 2 data device
and
> 2 log device and I have no way to restore
> how can I restore that database?
> Ivan
>|||thx. It works!
Ivan
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:efsxaqGREHA.1388@.TK2MSFTNGP09.phx.gbl...
> Hi,
> All the 4 files will be created during the database restore automatically.
> No need to create the database before the restore.
> So say in the source server you have a database called Finance with 4
files
> ( 2 MDF and 2 LDF). When you perform a backup all the
> 4 files will be stored in the backup file (FINANCE.BAK). Copy the
> FINANCE.BAK to destination server to c:\backup folder (any folder)
> Steps After that. (ALL in destination server)
> 1. Execute the below statement to get the associated file details
> Restore filelistonly from disk='c:\backup\Finance.bak'
> This will give you the details of all MDF and LDF files with Logical and
> physical names of MDF and LDF files
> 2. Execute the below command to create and restore the database
> Restore database NEWDBNAME from disk='c:\backup\Finance.bak'
> with 'Logical_MDF_name' to 'c:\mssql\data\filename.mdf',
> with 'Logical_NDF_name' to 'c:\mssql\data\filename.ndf',
> with 'Logical_LDF_name' to 'c:\mssql\data\filename.ldf',
> with 'Logical_LDF_name' to 'c:\mssql\data\filename.ldf'
> Note:
> You must give the exact "logical name " based on the out from FILELISTONLY
> command (Step-1). Physical name and directory can be anything.
> Thanks
> Hari
> MCDBA
>
>
>
>
> "Ivan Kan" <ivankan@.no-ip.com> wrote in message
> news:#anORPGREHA.2716@.tk2msftngp13.phx.gbl...
> > I have a database in SQL Server 2000 A. It has 2 data device and 2 log
> > device.
> >
> > I have backup the database into a file, and try to restore it into
another
> > SQL Server 2000.
> > The new database has only 1 data device and 1 log.
> > However, when I try to restore it, in Option page, it has 2 data device
> and
> > 2 log device and I have no way to restore
> >
> > how can I restore that database?
> >
> > Ivan
> >
> >
>sql

Database Backup-Restore Case

I have a database in SQL Server 2000 A. It has 2 data device and 2 log
device.
I have backup the database into a file, and try to restore it into another
SQL Server 2000.
The new database has only 1 data device and 1 log.
However, when I try to restore it, in Option page, it has 2 data device and
2 log device and I have no way to restore
how can I restore that database?
IvanHi,
All the 4 files will be created during the database restore automatically.
No need to create the database before the restore.
So say in the source server you have a database called Finance with 4 files
( 2 MDF and 2 LDF). When you perform a backup all the
4 files will be stored in the backup file (FINANCE.BAK). Copy the
FINANCE.BAK to destination server to c:\backup folder (any folder)
Steps After that. (ALL in destination server)
1. Execute the below statement to get the associated file details
Restore filelistonly from disk='c:\backup\Finance.bak'
This will give you the details of all MDF and LDF files with Logical and
physical names of MDF and LDF files
2. Execute the below command to create and restore the database
Restore database NEWDBNAME from disk='c:\backup\Finance.bak'
with 'Logical_MDF_name' to 'c:\mssql\data\filename.mdf',
with 'Logical_NDF_name' to 'c:\mssql\data\filename.ndf',
with 'Logical_LDF_name' to 'c:\mssql\data\filename.ldf',
with 'Logical_LDF_name' to 'c:\mssql\data\filename.ldf'
Note:
You must give the exact "logical name " based on the out from FILELISTONLY
command (Step-1). Physical name and directory can be anything.
Thanks
Hari
MCDBA
"Ivan Kan" <ivankan@.no-ip.com> wrote in message
news:#anORPGREHA.2716@.tk2msftngp13.phx.gbl...
> I have a database in SQL Server 2000 A. It has 2 data device and 2 log
> device.
> I have backup the database into a file, and try to restore it into another
> SQL Server 2000.
> The new database has only 1 data device and 1 log.
> However, when I try to restore it, in Option page, it has 2 data device
and
> 2 log device and I have no way to restore
> how can I restore that database?
> Ivan
>|||thx. It works!
Ivan
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:efsxaqGREHA.1388@.TK2MSFTNGP09.phx.gbl...
> Hi,
> All the 4 files will be created during the database restore automatically.
> No need to create the database before the restore.
> So say in the source server you have a database called Finance with 4
files
> ( 2 MDF and 2 LDF). When you perform a backup all the
> 4 files will be stored in the backup file (FINANCE.BAK). Copy the
> FINANCE.BAK to destination server to c:\backup folder (any folder)
> Steps After that. (ALL in destination server)
> 1. Execute the below statement to get the associated file details
> Restore filelistonly from disk='c:\backup\Finance.bak'
> This will give you the details of all MDF and LDF files with Logical and
> physical names of MDF and LDF files
> 2. Execute the below command to create and restore the database
> Restore database NEWDBNAME from disk='c:\backup\Finance.bak'
> with 'Logical_MDF_name' to 'c:\mssql\data\filename.mdf',
> with 'Logical_NDF_name' to 'c:\mssql\data\filename.ndf',
> with 'Logical_LDF_name' to 'c:\mssql\data\filename.ldf',
> with 'Logical_LDF_name' to 'c:\mssql\data\filename.ldf'
> Note:
> You must give the exact "logical name " based on the out from FILELISTONLY
> command (Step-1). Physical name and directory can be anything.
> Thanks
> Hari
> MCDBA
>
>
>
>
> "Ivan Kan" <ivankan@.no-ip.com> wrote in message
> news:#anORPGREHA.2716@.tk2msftngp13.phx.gbl...
another[vbcol=seagreen]
> and
>

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...
>
>
|||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:

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:
>

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.
>>

Database backup strategy questions....

I have been reading about the SQL Server transaction log and I'm still
confused about a lot of things. First of all, what is this file
actually used for? In the past when backing up my database, I only
backup and restore the actual database and skip the log file and this
has worked just fine in getting all my data back. Why do I have to
backup the log file and when would I ever use it?
I am having a problem now becuase my log file is over 4 GB for a 50 MB
database and I'm not sure why. My application is a POS system where
hundreds of transactions are entered daily and backups are done each
night of the database but not the log. I can restore the database just
fine without the log file, what is the advantage of backing up the log
file also? I can't backup that much data so I'll need to make that
much smaller if I did back it up. Based on my needs what is the best
backup strategy and what would I lose by not backing up the log?
Thanks.Ray,
When you restore the database file from a Full backup it actually restores
the log file along with it. The Log file holds the transactional
information to ensure consistency within the db and can not be used without
it. The log backups allow you to potentially restore to a particular point
in time under thright conditions. If all you care about is being able to
restore to the last Full backup (potentially loosing all transactions since
that last backup) you should place the database in SIMPLE recovery mode and
it will automatically truncate the log and not grow. See here for more
details:
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
Server 7.0 Tran Log
http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
Andrew J. Kelly SQL MVP
"Ray Lavelle" <bostonpartykid@.yahoo.com> wrote in message
news:d8653140.0401131950.120fda92@.posting.google.com...
> I have been reading about the SQL Server transaction log and I'm still
> confused about a lot of things. First of all, what is this file
> actually used for? In the past when backing up my database, I only
> backup and restore the actual database and skip the log file and this
> has worked just fine in getting all my data back. Why do I have to
> backup the log file and when would I ever use it?
> I am having a problem now becuase my log file is over 4 GB for a 50 MB
> database and I'm not sure why. My application is a POS system where
> hundreds of transactions are entered daily and backups are done each
> night of the database but not the log. I can restore the database just
> fine without the log file, what is the advantage of backing up the log
> file also? I can't backup that much data so I'll need to make that
> much smaller if I did back it up. Based on my needs what is the best
> backup strategy and what would I lose by not backing up the log?
> Thanks.

database backup question

For my database backup is there a way to only keep the last 5 backups? Currently my backup is creating a new .bak file with every backup. I only want a .BAK for the last 5 days. So I only want the last 5 .bak files at any given time.
Is there an option to set this or do I need to create a job of sort to delete them manually?
You didn't mention what SQL Server version you use, however you can set up a maintanence plan to delete old files older than ... days.
Ekrem nsoy
"Mike" <Mike@.community.nospam.com> wrote in message news:ufTkFc5JIHA.4584@.TK2MSFTNGP03.phx.gbl...
For my database backup is there a way to only keep the last 5 backups? Currently my backup is creating a new .bak file with every backup. I only want a .BAK for the last 5 days. So I only want the last 5 .bak files at any given time.
Is there an option to set this or do I need to create a job of sort to delete them manually?
|||I'm using SQL 2005.
Where in the maintance plan can I set that?
"Ekrem nsoy" <ekrem@.btegitim.com> wrote in message news:563B7A98-DAA7-4015-818D-7840A3B55015@.microsoft.com...
You didn't mention what SQL Server version you use, however you can set up a maintanence plan to delete old files older than ... days.
Ekrem nsoy
"Mike" <Mike@.community.nospam.com> wrote in message news:ufTkFc5JIHA.4584@.TK2MSFTNGP03.phx.gbl...
For my database backup is there a way to only keep the last 5 backups? Currently my backup is creating a new .bak file with every backup. I only want a .BAK for the last 5 days. So I only want the last 5 .bak files at any given time.
Is there an option to set this or do I need to create a job of sort to delete them manually?
|||the 'maintenance cleanup task' wants an actual folder to 'clean' all of my
..bak files are in sub folders for each db, how will that work, or do i have
to create a clean up plan for each DB?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uP97Nz5JIHA.3940@.TK2MSFTNGP05.phx.gbl...
> In the "maintenance cleanup task".
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Mike" <Mike@.community.nospam.com> wrote in message
> news:Oh5D$w5JIHA.5116@.TK2MSFTNGP03.phx.gbl...
> I'm using SQL 2005.
> Where in the maintance plan can I set that?
> "Ekrem nsoy" <ekrem@.btegitim.com> wrote in message
> news:563B7A98-DAA7-4015-818D-7840A3B55015@.microsoft.com...
> You didn't mention what SQL Server version you use, however you can set
> up a maintanence plan to delete old files older than ... days.
> --
> Ekrem nsoy
>
> "Mike" <Mike@.community.nospam.com> wrote in message
> news:ufTkFc5JIHA.4584@.TK2MSFTNGP03.phx.gbl...
> For my database backup is there a way to only keep the last 5 backups?
> Currently my backup is creating a new .bak file with every backup. I only
> want a .BAK for the last 5 days. So I only want the last 5 .bak files at
> any given time.
> Is there an option to set this or do I need to create a job of sort to
> delete them manually?
>
|||I figured it out. thanks
"Mike" <Mike@.community.nospam.com> wrote in message
news:%23Wi18W6JIHA.3356@.TK2MSFTNGP02.phx.gbl...
> the 'maintenance cleanup task' wants an actual folder to 'clean' all of my
> .bak files are in sub folders for each db, how will that work, or do i
> have to create a clean up plan for each DB?
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:uP97Nz5JIHA.3940@.TK2MSFTNGP05.phx.gbl...
>
|||The idea is, you'd have a Backup folder and you'd store your *.bak files in
that specific folder. When you set up a Cleanup Task, you'll point to that
folder and the Task will determine the old files (according to your setting)
and delete them. Also, you can check that sub folder option if there is a
granular backup folder hierarchy. So you'll not have to set up different
Cleanup Tasks for each db you backup under Backup folder.
Ekrem nsoy
"Mike" <Mike@.community.nospam.com> wrote in message
news:%23Wi18W6JIHA.3356@.TK2MSFTNGP02.phx.gbl...
> the 'maintenance cleanup task' wants an actual folder to 'clean' all of my
> .bak files are in sub folders for each db, how will that work, or do i
> have to create a clean up plan for each DB?
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:uP97Nz5JIHA.3940@.TK2MSFTNGP05.phx.gbl...
>

database backup question

For my database backup is there a way to only keep the last 5 backups? Curre
ntly my backup is creating a new .bak file with every backup. I only want a
.BAK for the last 5 days. So I only want the last 5 .bak files at any given
time.
Is there an option to set this or do I need to create a job of sort to delet
e them manually?You didn't mention what SQL Server version you use, however you can set up a
maintanence plan to delete old files older than ... days.
--
Ekrem nsoy
"Mike" <Mike@.community.nospam.com> wrote in message news:ufTkFc5JIHA.4584@.TK
2MSFTNGP03.phx.gbl...
For my database backup is there a way to only keep the last 5 backups? Curre
ntly my backup is creating a new .bak file with every backup. I only want a
.BAK for the last 5 days. So I only want the last 5 .bak files at any given
time.
Is there an option to set this or do I need to create a job of sort to delet
e them manually?|||I'm using SQL 2005.
Where in the maintance plan can I set that?
"Ekrem nsoy" <ekrem@.btegitim.com> wrote in message news:563B7A98-DAA7-4015-
818D-7840A3B55015@.microsoft.com...
You didn't mention what SQL Server version you use, however you can set up a
maintanence plan to delete old files older than ... days.
--
Ekrem nsoy
"Mike" <Mike@.community.nospam.com> wrote in message news:ufTkFc5JIHA.4584@.TK
2MSFTNGP03.phx.gbl...
For my database backup is there a way to only keep the last 5 backups? Curre
ntly my backup is creating a new .bak file with every backup. I only want a
.BAK for the last 5 days. So I only want the last 5 .bak files at any given
time.
Is there an option to set this or do I need to create a job of sort to delet
e them manually?|||In the "maintenance cleanup task".
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Mike" <Mike@.community.nospam.com> wrote in message news:Oh5D$w5JIHA.5116@.TK
2MSFTNGP03.phx.gbl...
I'm using SQL 2005.
Where in the maintance plan can I set that?
"Ekrem nsoy" <ekrem@.btegitim.com> wrote in message
news:563B7A98-DAA7-4015-818D-7840A3B55015@.microsoft.com...
You didn't mention what SQL Server version you use, however you can set up a
maintanence plan to
delete old files older than ... days.
Ekrem nsoy
"Mike" <Mike@.community.nospam.com> wrote in message
news:ufTkFc5JIHA.4584@.TK2MSFTNGP03.phx.gbl...
For my database backup is there a way to only keep the last 5 backups? Curre
ntly my backup is
creating a new .bak file with every backup. I only want a .BAK for the last
5 days. So I only want
the last 5 .bak files at any given time.
Is there an option to set this or do I need to create a job of sort to delet
e them manually?|||the 'maintenance cleanup task' wants an actual folder to 'clean' all of my
.bak files are in sub folders for each db, how will that work, or do i have
to create a clean up plan for each DB?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uP97Nz5JIHA.3940@.TK2MSFTNGP05.phx.gbl...
> In the "maintenance cleanup task".
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Mike" <Mike@.community.nospam.com> wrote in message
> news:Oh5D$w5JIHA.5116@.TK2MSFTNGP03.phx.gbl...
> I'm using SQL 2005.
> Where in the maintance plan can I set that?
> "Ekrem nsoy" <ekrem@.btegitim.com> wrote in message
> news:563B7A98-DAA7-4015-818D-7840A3B55015@.microsoft.com...
> You didn't mention what SQL Server version you use, however you can set
> up a maintanence plan to delete old files older than ... days.
> --
> Ekrem nsoy
>
> "Mike" <Mike@.community.nospam.com> wrote in message
> news:ufTkFc5JIHA.4584@.TK2MSFTNGP03.phx.gbl...
> For my database backup is there a way to only keep the last 5 backups?
> Currently my backup is creating a new .bak file with every backup. I only
> want a .BAK for the last 5 days. So I only want the last 5 .bak files at
> any given time.
> Is there an option to set this or do I need to create a job of sort to
> delete them manually?
>|||I figured it out. thanks
"Mike" <Mike@.community.nospam.com> wrote in message
news:%23Wi18W6JIHA.3356@.TK2MSFTNGP02.phx.gbl...
> the 'maintenance cleanup task' wants an actual folder to 'clean' all of my
> .bak files are in sub folders for each db, how will that work, or do i
> have to create a clean up plan for each DB?
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:uP97Nz5JIHA.3940@.TK2MSFTNGP05.phx.gbl...
>|||The idea is, you'd have a Backup folder and you'd store your *.bak files in
that specific folder. When you set up a Cleanup Task, you'll point to that
folder and the Task will determine the old files (according to your setting)
and delete them. Also, you can check that sub folder option if there is a
granular backup folder hierarchy. So you'll not have to set up different
Cleanup Tasks for each db you backup under Backup folder.
Ekrem nsoy
"Mike" <Mike@.community.nospam.com> wrote in message
news:%23Wi18W6JIHA.3356@.TK2MSFTNGP02.phx.gbl...
> the 'maintenance cleanup task' wants an actual folder to 'clean' all of my
> .bak files are in sub folders for each db, how will that work, or do i
> have to create a clean up plan for each DB?
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:uP97Nz5JIHA.3940@.TK2MSFTNGP05.phx.gbl...
>sql

database backup problem

I got error message, Error: 9002, Severity: 17, State: 6 The log file for database 'test' is full. Anybody can help me? why happen, how to fix, thanks in advance.I got that yesterday when trying to change a column width! Turned out the log file was set to a fixed size. As soon as I changed it to automatically grow the problem went away.|||i don't know if this'll work, but if the database is still operational, i think you could expand the database size. this should cancel out the "Full" error message. It allows you to do it 6.5 and 7, but i can't remember off the top of my head how to do it in 2000 (if that's what you're running)|||Grahamt: can you describe the step of how to I changed it to automatically grow . I appreciate it. Thanks|||Cruickshanks: i am running on sql server 2000|||Right-click the DB in Enterprise Mangler
Click the Transaction Log Tab at the top
Make sure the "Automatically Grow file" and "Unrestricted file growth" are both checked.

See if that works. :)|||I check the transaction log, "Automatically Grow file" and "Unrestricted file growth" are both checked. What happen?|||Disk full?|||If the disk is full...just try truncating the log . in query analyzer execute

use test
go

back up log test with truncate_only .

This should resolve your problem. Also try to create a maintenance plan .|||Praveenvc: Why need to trunck the log file, is the log file locate in the management\SQL Sever Agent\SQL Sever Log, but that is not two big, only have 7 logfile there. Another thing, after you trunck the log file, how to do mantainance plan, can you give me more detail as i am a new with sql server . Thanks again!

database backup job quits for no apparent reason

I created a maintenance plan to back up a database as a file to another server. Full backup was specified. The job has been running for about 3 months, then quit last night (after the server was rebooted). Error is:

Message
Executed as user: xxxxx\Administrator. The command line parameters are invalid. The step failed.

not sure what to do to fix this - -

Do a profiler trace to see if it's a TSQL issue somewhere, maybe the plan was accidentally changed somehow.|||I turned on Profiler to trace database backups - now all the backups are running fine -|||This is typically caused by the account changing the password. If you change the password of the account you used to run the job, you won't see any problems until you reboot your server/restart SQL Server service as this is the only time that the new security credentials will take effect.|||the jobs were created by and are run under the domain master system administrator account|||

Please go the SQL server jobs and change user domain\Administrator to sa (SQL Administrators).

it should be resolve......

Database backup hardware

Can anyone suggest what types of hardware I should be using to backup my SQL database. I currently do a differential each night to a file share, which works well, and there is no concern for network usage. I wonder if using a tape or perhaps DVD is a bett
er method. Is a differential backup to DVD even possible?
Thanks.
I personally prefer to backup to a file share overnight and archive that to
tape during the day. I don't bother with differential backups, but that is
due more to my data flow than anything else. Differentials don't really
save me much. Your situation may be different.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"G Clark" <G Clark@.discussions.microsoft.com> wrote in message
news:EA640F28-A688-4A9C-B3C2-0F0C51470A76@.microsoft.com...
> Can anyone suggest what types of hardware I should be using to backup my
SQL database. I currently do a differential each night to a file share,
which works well, and there is no concern for network usage. I wonder if
using a tape or perhaps DVD is a better method. Is a differential backup to
DVD even possible?
> Thanks.
|||So I tend to be of the school of thought that the first, if not the second
and third, generations of backups should be on spinning disk somewhere -
high performance, cheap, simple and for more reliable than any removable
medium. In a multi-server environment it's best to have these on a device
that's independant of a server chassis (NAS or SAN). I consider tape for
disaster recovery from off-site resources _only_. TCO of tape is
astronomical compared to SATA NAS or SAN devices and way more problematic.
"G Clark" <G Clark@.discussions.microsoft.com> wrote in message
news:EA640F28-A688-4A9C-B3C2-0F0C51470A76@.microsoft.com...
> Can anyone suggest what types of hardware I should be using to backup my
SQL database. I currently do a differential each night to a file share,
which works well, and there is no concern for network usage. I wonder if
using a tape or perhaps DVD is a better method. Is a differential backup to
DVD even possible?
> Thanks.
|||G Clark,
I like to backup to disk first, then let a tape job run at night to
backup the sql backup files. When selecting backup media you should look
at the durability of the media. I'm not sure how durable DVD media is,
or if it is better than tape. There is certainly a capacity issue with
DVD for me.
Tape manufacturers recommend that a tape not be used more than 20 times
before being discarded. However this is seldom respected and I quite
often here stories of people with corrupt tapes - due I suspect to a
worn out tape.
A tape solution should be serviceable for you as long as your respect
the durability of the medium and adhere to manufacturers guidelines.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
G Clark wrote:
> Can anyone suggest what types of hardware I should be using to backup my SQL database. I currently do a differential each night to a file share, which works well, and there is no concern for network usage. I wonder if using a tape or perhaps DVD is a be
tter method. Is a differential backup to DVD even possible?
> Thanks.
sql

Database backup hardware

Can anyone suggest what types of hardware I should be using to backup my SQL database. I currently do a differential each night to a file share, which works well, and there is no concern for network usage. I wonder if using a tape or perhaps DVD is a better method. Is a differential backup to DVD even possible?
Thanks.I personally prefer to backup to a file share overnight and archive that to
tape during the day. I don't bother with differential backups, but that is
due more to my data flow than anything else. Differentials don't really
save me much. Your situation may be different.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"G Clark" <G Clark@.discussions.microsoft.com> wrote in message
news:EA640F28-A688-4A9C-B3C2-0F0C51470A76@.microsoft.com...
> Can anyone suggest what types of hardware I should be using to backup my
SQL database. I currently do a differential each night to a file share,
which works well, and there is no concern for network usage. I wonder if
using a tape or perhaps DVD is a better method. Is a differential backup to
DVD even possible?
> Thanks.|||So I tend to be of the school of thought that the first, if not the second
and third, generations of backups should be on spinning disk somewhere -
high performance, cheap, simple and for more reliable than any removable
medium. In a multi-server environment it's best to have these on a device
that's independant of a server chassis (NAS or SAN). I consider tape for
disaster recovery from off-site resources _only_. TCO of tape is
astronomical compared to SATA NAS or SAN devices and way more problematic.
"G Clark" <G Clark@.discussions.microsoft.com> wrote in message
news:EA640F28-A688-4A9C-B3C2-0F0C51470A76@.microsoft.com...
> Can anyone suggest what types of hardware I should be using to backup my
SQL database. I currently do a differential each night to a file share,
which works well, and there is no concern for network usage. I wonder if
using a tape or perhaps DVD is a better method. Is a differential backup to
DVD even possible?
> Thanks.|||G Clark,
I like to backup to disk first, then let a tape job run at night to
backup the sql backup files. When selecting backup media you should look
at the durability of the media. I'm not sure how durable DVD media is,
or if it is better than tape. There is certainly a capacity issue with
DVD for me.
Tape manufacturers recommend that a tape not be used more than 20 times
before being discarded. However this is seldom respected and I quite
often here stories of people with corrupt tapes - due I suspect to a
worn out tape.
A tape solution should be serviceable for you as long as your respect
the durability of the medium and adhere to manufacturers guidelines.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
G Clark wrote:
> Can anyone suggest what types of hardware I should be using to backup my SQL database. I currently do a differential each night to a file share, which works well, and there is no concern for network usage. I wonder if using a tape or perhaps DVD is a better method. Is a differential backup to DVD even possible?
> Thanks.

Database backup hardware

Can anyone suggest what types of hardware I should be using to backup my SQL
database. I currently do a differential each night to a file share, which w
orks well, and there is no concern for network usage. I wonder if using a ta
pe or perhaps DVD is a bett
er method. Is a differential backup to DVD even possible?
Thanks.I personally prefer to backup to a file share overnight and archive that to
tape during the day. I don't bother with differential backups, but that is
due more to my data flow than anything else. Differentials don't really
save me much. Your situation may be different.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"G Clark" <G Clark@.discussions.microsoft.com> wrote in message
news:EA640F28-A688-4A9C-B3C2-0F0C51470A76@.microsoft.com...
> Can anyone suggest what types of hardware I should be using to backup my
SQL database. I currently do a differential each night to a file share,
which works well, and there is no concern for network usage. I wonder if
using a tape or perhaps DVD is a better method. Is a differential backup to
DVD even possible?
> Thanks.|||So I tend to be of the school of thought that the first, if not the second
and third, generations of backups should be on spinning disk somewhere -
high performance, cheap, simple and for more reliable than any removable
medium. In a multi-server environment it's best to have these on a device
that's independant of a server chassis (NAS or SAN). I consider tape for
disaster recovery from off-site resources _only_. TCO of tape is
astronomical compared to SATA NAS or SAN devices and way more problematic.
"G Clark" <G Clark@.discussions.microsoft.com> wrote in message
news:EA640F28-A688-4A9C-B3C2-0F0C51470A76@.microsoft.com...
> Can anyone suggest what types of hardware I should be using to backup my
SQL database. I currently do a differential each night to a file share,
which works well, and there is no concern for network usage. I wonder if
using a tape or perhaps DVD is a better method. Is a differential backup to
DVD even possible?
> Thanks.|||G Clark,
I like to backup to disk first, then let a tape job run at night to
backup the sql backup files. When selecting backup media you should look
at the durability of the media. I'm not sure how durable DVD media is,
or if it is better than tape. There is certainly a capacity issue with
DVD for me.
Tape manufacturers recommend that a tape not be used more than 20 times
before being discarded. However this is seldom respected and I quite
often here stories of people with corrupt tapes - due I suspect to a
worn out tape.
A tape solution should be serviceable for you as long as your respect
the durability of the medium and adhere to manufacturers guidelines.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
G Clark wrote:
> Can anyone suggest what types of hardware I should be using to backup my SQL datab
ase. I currently do a differential each night to a file share, which works well, and
there is no concern for network usage. I wonder if using a tape or perhaps DVD is a
be
tter method. Is a differential backup to DVD even possible?
> Thanks.

Database backup from ADP application

if it is possible, how would i go about creating routines (storedprocedures?
)
that can be invoked from a microsoft access ADP file that will backup (and
hopefully have the ability to also restore) as SQL Server database?
thanks for any and all help!
benYou would execute the TSQL BACKUP and RESTORE command from your Access appli
cation. As for how you
execute TSQL command from Access, I suggest you ask in the Access group(s).
:-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ben" <ben_1_ AT hotmail DOT com> wrote in message
news:3AC37D13-C777-462C-ABFD-13F82698A070@.microsoft.com...
> if it is possible, how would i go about creating routines (storedprocedure
s?)
> that can be invoked from a microsoft access ADP file that will backup (and
> hopefully have the ability to also restore) as SQL Server database?
> thanks for any and all help!
> ben

database backup file size

How does backup database command works? I don't see size of database backup file increasing while backup is in progress OR is it locked till the backup is finished.
Thanks.The size of the backup file (or files) is pre-calculated and the space is allocated before the actual backup starts. You will not see the size change while the backup is running.sql