backup file much larger than the actual working database file

Hi All,
I am having a problem with the backup file (approx 70+ GB) that is much larger than the original working database (approx 4GB). This doesn't seems logical to me, anyone knows what causes this strange situations ?
following is my database backup setting
- Full Database Backup
- Overwrite Media
- Backup to physical disc on the same server
Many thanks in advance.
Bernard Goh
I would guess there is more than 1 backup set in that file. If you were to
run the RESTORE HEADERONLY command on the backup file, how many entries do
you see?
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Try MiniSQLBackup
"SummerCoke" <SummerCoke@.discussions.microsoft.com> wrote in message
news:A150B725-F523-4152-9E53-D1DC71815A94@.microsoft.com...
> Hi All,
> I am having a problem with the backup file (approx 70+ GB) that is much
larger than the original working database (approx 4GB). This doesn't seems
logical to me, anyone knows what causes this strange situations ?
> following is my database backup setting
> - Full Database Backup
> - Overwrite Media
> - Backup to physical disc on the same server
> Many thanks in advance.
> --
> Bernard Goh
|||I would guess there is more than 1 backup set in that file. If you were to
run the RESTORE HEADERONLY command on the backup file, how many entries do
you see?
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Try MiniSQLBackup
"SummerCoke" <SummerCoke@.discussions.microsoft.com> wrote in message
news:A150B725-F523-4152-9E53-D1DC71815A94@.microsoft.com...
> Hi All,
> I am having a problem with the backup file (approx 70+ GB) that is much
larger than the original working database (approx 4GB). This doesn't seems
logical to me, anyone knows what causes this strange situations ?
> following is my database backup setting
> - Full Database Backup
> - Overwrite Media
> - Backup to physical disc on the same server
> Many thanks in advance.
> --
> Bernard Goh
|||Hi,
Can you execute the below command to check the number of backup sets you
have in your backup file:-
RESTORE headeronly from disk='c:\FILENAME.BAK'
This will show the number of backup files in the backfile. Since you
selected the "Overwrite media", there should be only one file.
To identify the exact backup file size, Execute the below command from Query
Analyzer:-
BACKUP DATABASE <dbname> to disk='d:\backup\dbname.bak' with
,stats=10 -- INIT will overwrite the backup file
Give the drive letter and folder based on ur availability , after the
execution check the file size.
Can you check the transaction log size as well. How to check the log size
DBCC SQLPERF(LOGSPACE)
If you have a big log file then backup the transaction log file and shrink
the trasnaction log file.
How to shrink the transaction log file if your database is FULL recovery
model.
1. Backup the Transaction Log using (Backup log command)
2. Use DBCC SHRINKFILE('logical_tran_log_name','Truncateonly')
How to shrink the transaction log file if your database is Simple recovery
model.
1. Truncate the Transaction Log using (Backup log dbname with truncate_only
command)
2. Use DBCC SHRINKFILE('logical_tran_log_name','Truncateonly')
How to shrink the data files
1. Check any open transactions or there using DBCC OPENTRAN(DBname)
2. If not, Run DBCC SHRINKFILE('logical_datafile_name',size)
Thanks
Hari
MCDBA
"SummerCoke" <SummerCoke@.discussions.microsoft.com> wrote in message
news:A150B725-F523-4152-9E53-D1DC71815A94@.microsoft.com...
> Hi All,
> I am having a problem with the backup file (approx 70+ GB) that is much
larger than the original working database (approx 4GB). This doesn't seems
logical to me, anyone knows what causes this strange situations ?
> following is my database backup setting
> - Full Database Backup
> - Overwrite Media
> - Backup to physical disc on the same server
> Many thanks in advance.
> --
> Bernard Goh
|||Hi,
Can you execute the below command to check the number of backup sets you
have in your backup file:-
RESTORE headeronly from disk='c:\FILENAME.BAK'
This will show the number of backup files in the backfile. Since you
selected the "Overwrite media", there should be only one file.
To identify the exact backup file size, Execute the below command from Query
Analyzer:-
BACKUP DATABASE <dbname> to disk='d:\backup\dbname.bak' with
,stats=10 -- INIT will overwrite the backup file
Give the drive letter and folder based on ur availability , after the
execution check the file size.
Can you check the transaction log size as well. How to check the log size
DBCC SQLPERF(LOGSPACE)
If you have a big log file then backup the transaction log file and shrink
the trasnaction log file.
How to shrink the transaction log file if your database is FULL recovery
model.
1. Backup the Transaction Log using (Backup log command)
2. Use DBCC SHRINKFILE('logical_tran_log_name','Truncateonly')
How to shrink the transaction log file if your database is Simple recovery
model.
1. Truncate the Transaction Log using (Backup log dbname with truncate_only
command)
2. Use DBCC SHRINKFILE('logical_tran_log_name','Truncateonly')
How to shrink the data files
1. Check any open transactions or there using DBCC OPENTRAN(DBname)
2. If not, Run DBCC SHRINKFILE('logical_datafile_name',size)
Thanks
Hari
MCDBA
"SummerCoke" <SummerCoke@.discussions.microsoft.com> wrote in message
news:A150B725-F523-4152-9E53-D1DC71815A94@.microsoft.com...
> Hi All,
> I am having a problem with the backup file (approx 70+ GB) that is much
larger than the original working database (approx 4GB). This doesn't seems
logical to me, anyone knows what causes this strange situations ?
> following is my database backup setting
> - Full Database Backup
> - Overwrite Media
> - Backup to physical disc on the same server
> Many thanks in advance.
> --
> Bernard Goh

backup file much larger than the actual working database file

Hi All,
I am having a problem with the backup file (approx 70+ GB) that is much larg
er than the original working database (approx 4GB). This doesn't seems logic
al to me, anyone knows what causes this strange situations ?
following is my database backup setting
- Full Database Backup
- Overwrite Media
- Backup to physical disc on the same server
Many thanks in advance.
--
Bernard GohI would guess there is more than 1 backup set in that file. If you were to
run the RESTORE HEADERONLY command on the backup file, how many entries do
you see?
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Try MiniSQLBackup
"SummerCoke" <SummerCoke@.discussions.microsoft.com> wrote in message
news:A150B725-F523-4152-9E53-D1DC71815A94@.microsoft.com...
> Hi All,
> I am having a problem with the backup file (approx 70+ GB) that is much
larger than the original working database (approx 4GB). This doesn't seems
logical to me, anyone knows what causes this strange situations ?
> following is my database backup setting
> - Full Database Backup
> - Overwrite Media
> - Backup to physical disc on the same server
> Many thanks in advance.
> --
> Bernard Goh|||Hi,
Can you execute the below command to check the number of backup sets you
have in your backup file:-
RESTORE headeronly from disk='c:\FILENAME.BAK'
This will show the number of backup files in the backfile. Since you
selected the "Overwrite media", there should be only one file.
To identify the exact backup file size, Execute the below command from Query
Analyzer:-
BACKUP DATABASE <dbname> to disk='d:\backup\dbname.bak' with
,stats=10 -- INIT will overwrite the backup file
Give the drive letter and folder based on ur availability , after the
execution check the file size.
----
--
Can you check the transaction log size as well. How to check the log size
DBCC SQLPERF(LOGSPACE)
If you have a big log file then backup the transaction log file and shrink
the trasnaction log file.
How to shrink the transaction log file if your database is FULL recovery
model.
1. Backup the Transaction Log using (Backup log command)
2. Use DBCC SHRINKFILE('logical_tran_log_name','Trun
cateonly')
How to shrink the transaction log file if your database is Simple recovery
model.
1. Truncate the Transaction Log using (Backup log dbname with truncate_only
command)
2. Use DBCC SHRINKFILE('logical_tran_log_name','Trun
cateonly')
How to shrink the data files
1. Check any open transactions or there using DBCC OPENTRAN(DBname)
2. If not, Run DBCC SHRINKFILE('logical_datafile_name',size)
Thanks
Hari
MCDBA
"SummerCoke" <SummerCoke@.discussions.microsoft.com> wrote in message
news:A150B725-F523-4152-9E53-D1DC71815A94@.microsoft.com...
> Hi All,
> I am having a problem with the backup file (approx 70+ GB) that is much
larger than the original working database (approx 4GB). This doesn't seems
logical to me, anyone knows what causes this strange situations ?
> following is my database backup setting
> - Full Database Backup
> - Overwrite Media
> - Backup to physical disc on the same server
> Many thanks in advance.
> --
> Bernard Goh

backup file much larger than the actual working database file

Hi All,
I am having a problem with the backup file (approx 70+ GB) that is much larger than the original working database (approx 4GB). This doesn't seems logical to me, anyone knows what causes this strange situations ?
following is my database backup setting
- Full Database Backup
- Overwrite Media
- Backup to physical disc on the same server
Many thanks in advance.
--
Bernard GohI would guess there is more than 1 backup set in that file. If you were to
run the RESTORE HEADERONLY command on the backup file, how many entries do
you see?
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Try MiniSQLBackup
"SummerCoke" <SummerCoke@.discussions.microsoft.com> wrote in message
news:A150B725-F523-4152-9E53-D1DC71815A94@.microsoft.com...
> Hi All,
> I am having a problem with the backup file (approx 70+ GB) that is much
larger than the original working database (approx 4GB). This doesn't seems
logical to me, anyone knows what causes this strange situations ?
> following is my database backup setting
> - Full Database Backup
> - Overwrite Media
> - Backup to physical disc on the same server
> Many thanks in advance.
> --
> Bernard Goh|||Hi,
Can you execute the below command to check the number of backup sets you
have in your backup file:-
RESTORE headeronly from disk='c:\FILENAME.BAK'
This will show the number of backup files in the backfile. Since you
selected the "Overwrite media", there should be only one file.
To identify the exact backup file size, Execute the below command from Query
Analyzer:-
BACKUP DATABASE <dbname> to disk='d:\backup\dbname.bak' with
,stats=10 -- INIT will overwrite the backup file
Give the drive letter and folder based on ur availability , after the
execution check the file size.
----
--
Can you check the transaction log size as well. How to check the log size
DBCC SQLPERF(LOGSPACE)
If you have a big log file then backup the transaction log file and shrink
the trasnaction log file.
How to shrink the transaction log file if your database is FULL recovery
model.
1. Backup the Transaction Log using (Backup log command)
2. Use DBCC SHRINKFILE('logical_tran_log_name','Truncateonly')
How to shrink the transaction log file if your database is Simple recovery
model.
1. Truncate the Transaction Log using (Backup log dbname with truncate_only
command)
2. Use DBCC SHRINKFILE('logical_tran_log_name','Truncateonly')
How to shrink the data files
1. Check any open transactions or there using DBCC OPENTRAN(DBname)
2. If not, Run DBCC SHRINKFILE('logical_datafile_name',size)
--
Thanks
Hari
MCDBA
"SummerCoke" <SummerCoke@.discussions.microsoft.com> wrote in message
news:A150B725-F523-4152-9E53-D1DC71815A94@.microsoft.com...
> Hi All,
> I am having a problem with the backup file (approx 70+ GB) that is much
larger than the original working database (approx 4GB). This doesn't seems
logical to me, anyone knows what causes this strange situations ?
> following is my database backup setting
> - Full Database Backup
> - Overwrite Media
> - Backup to physical disc on the same server
> Many thanks in advance.
> --
> Bernard Gohsql

Backup File Deletions not Working

Hi:
We configured a backup job in the database maintenance plan for a database
for both "Complete Backup" and the "Transaction Log". We told the job to
delete files after one day. It has not been deleting these files, though.
Why is that?
John
Hi
Files are deleted after the next backup completes successfully.
If you se a backup to delete after 1 day, and you don't run the actual
backup job for 2 days, the backup will remain on disk for 2 days, until
after the backup is successful.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:6E533D8D-A308-47F9-AC62-282987DBF849@.microsoft.com...
> Hi:
> We configured a backup job in the database maintenance plan for a database
> for both "Complete Backup" and the "Transaction Log". We told the job to
> delete files after one day. It has not been deleting these files, though.
> Why is that?
> John
|||So, does that mean that the backups have not been successful?
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> Files are deleted after the next backup completes successfully.
> If you se a backup to delete after 1 day, and you don't run the actual
> backup job for 2 days, the backup will remain on disk for 2 days, until
> after the backup is successful.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
> message news:6E533D8D-A308-47F9-AC62-282987DBF849@.microsoft.com...
>
>
|||See this old post:-
http://groups.google.co.in/group/mic...141cc02159d2bc
Thanks
Hari
SQL Server MVP
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:262CAB61-803A-46BF-AA4A-CF5EC4AF8672@.microsoft.com...[vbcol=seagreen]
> So, does that mean that the backups have not been successful?
> "Mike Epprecht (SQL MVP)" wrote:
|||Hi
No. Look at the backup job logs to see what is going on.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:262CAB61-803A-46BF-AA4A-CF5EC4AF8672@.microsoft.com...[vbcol=seagreen]
> So, does that mean that the backups have not been successful?
> "Mike Epprecht (SQL MVP)" wrote:

Backup File Deletions not Working

Hi:
We configured a backup job in the database maintenance plan for a database
for both "Complete Backup" and the "Transaction Log". We told the job to
delete files after one day. It has not been deleting these files, though.
Why is that?
JohnHi
Files are deleted after the next backup completes successfully.
If you se a backup to delete after 1 day, and you don't run the actual
backup job for 2 days, the backup will remain on disk for 2 days, until
after the backup is successful.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:6E533D8D-A308-47F9-AC62-282987DBF849@.microsoft.com...
> Hi:
> We configured a backup job in the database maintenance plan for a database
> for both "Complete Backup" and the "Transaction Log". We told the job to
> delete files after one day. It has not been deleting these files, though.
> Why is that?
> John|||So, does that mean that the backups have not been successful?
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> Files are deleted after the next backup completes successfully.
> If you se a backup to delete after 1 day, and you don't run the actual
> backup job for 2 days, the backup will remain on disk for 2 days, until
> after the backup is successful.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
> message news:6E533D8D-A308-47F9-AC62-282987DBF849@.microsoft.com...
>
>|||See this old post:-
http://groups.google.co.in/group/mi...4141cc02159d2bc
Thanks
Hari
SQL Server MVP
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:262CAB61-803A-46BF-AA4A-CF5EC4AF8672@.microsoft.com...[vbcol=seagreen]
> So, does that mean that the backups have not been successful?
> "Mike Epprecht (SQL MVP)" wrote:
>|||Hi
No. Look at the backup job logs to see what is going on.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:262CAB61-803A-46BF-AA4A-CF5EC4AF8672@.microsoft.com...[vbcol=seagreen]
> So, does that mean that the backups have not been successful?
> "Mike Epprecht (SQL MVP)" wrote:
>

Backup File Deletions not Working

Hi:
We configured a backup job in the database maintenance plan for a database
for both "Complete Backup" and the "Transaction Log". We told the job to
delete files after one day. It has not been deleting these files, though.
Why is that?
JohnHi
Files are deleted after the next backup completes successfully.
If you se a backup to delete after 1 day, and you don't run the actual
backup job for 2 days, the backup will remain on disk for 2 days, until
after the backup is successful.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:6E533D8D-A308-47F9-AC62-282987DBF849@.microsoft.com...
> Hi:
> We configured a backup job in the database maintenance plan for a database
> for both "Complete Backup" and the "Transaction Log". We told the job to
> delete files after one day. It has not been deleting these files, though.
> Why is that?
> John|||So, does that mean that the backups have not been successful?
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> Files are deleted after the next backup completes successfully.
> If you se a backup to delete after 1 day, and you don't run the actual
> backup job for 2 days, the backup will remain on disk for 2 days, until
> after the backup is successful.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
> message news:6E533D8D-A308-47F9-AC62-282987DBF849@.microsoft.com...
> > Hi:
> >
> > We configured a backup job in the database maintenance plan for a database
> > for both "Complete Backup" and the "Transaction Log". We told the job to
> > delete files after one day. It has not been deleting these files, though.
> >
> > Why is that?
> >
> > John
>
>|||See this old post:-
http://groups.google.co.in/group/microsoft.public.sqlserver.server/browse_thread/thread/f24fa621edb472b/04141cc02159d2bc?lnk=st&q=maintenance+plan+not+deleting+old+files&rnum=4&hl=en#04141cc02159d2bc
Thanks
Hari
SQL Server MVP
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:262CAB61-803A-46BF-AA4A-CF5EC4AF8672@.microsoft.com...
> So, does that mean that the backups have not been successful?
> "Mike Epprecht (SQL MVP)" wrote:
>> Hi
>> Files are deleted after the next backup completes successfully.
>> If you se a backup to delete after 1 day, and you don't run the actual
>> backup job for 2 days, the backup will remain on disk for 2 days, until
>> after the backup is successful.
>> Regards
>> --
>> Mike Epprecht, Microsoft SQL Server MVP
>> Zurich, Switzerland
>> IM: mike@.epprecht.net
>> MVP Program: http://www.microsoft.com/mvp
>> Blog: http://www.msmvps.com/epprecht/
>> "childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
>> message news:6E533D8D-A308-47F9-AC62-282987DBF849@.microsoft.com...
>> > Hi:
>> >
>> > We configured a backup job in the database maintenance plan for a
>> > database
>> > for both "Complete Backup" and the "Transaction Log". We told the job
>> > to
>> > delete files after one day. It has not been deleting these files,
>> > though.
>> >
>> > Why is that?
>> >
>> > John
>>|||Hi
No. Look at the backup job logs to see what is going on.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:262CAB61-803A-46BF-AA4A-CF5EC4AF8672@.microsoft.com...
> So, does that mean that the backups have not been successful?
> "Mike Epprecht (SQL MVP)" wrote:
>> Hi
>> Files are deleted after the next backup completes successfully.
>> If you se a backup to delete after 1 day, and you don't run the actual
>> backup job for 2 days, the backup will remain on disk for 2 days, until
>> after the backup is successful.
>> Regards
>> --
>> Mike Epprecht, Microsoft SQL Server MVP
>> Zurich, Switzerland
>> IM: mike@.epprecht.net
>> MVP Program: http://www.microsoft.com/mvp
>> Blog: http://www.msmvps.com/epprecht/
>> "childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
>> message news:6E533D8D-A308-47F9-AC62-282987DBF849@.microsoft.com...
>> > Hi:
>> >
>> > We configured a backup job in the database maintenance plan for a
>> > database
>> > for both "Complete Backup" and the "Transaction Log". We told the job
>> > to
>> > delete files after one day. It has not been deleting these files,
>> > though.
>> >
>> > Why is that?
>> >
>> > John
>>

Backup fails with A nonrecoverable I/O error occurred for large DB

Hi,
We have just installed an Windows 2003 x64 server with SQL 2005 to replace
our old server. Everything seems to be working fine but the backup.
When trying to backup databases larger than 16MB or so the backup fails with
a System.Data.SqlClient.SqlError: A nonrecoverable I/O error occurred on file
"S:\SQLData\OLAPLogging.mdf:" 1(Incorrect function.).
(Microsoft.SqlServer.Smo) message:
Smaller DBs backup fine (master, model, etc). even if I create a new DB it
backups fine until it grows bigger than about 16MB, then it gives the same
error message.
In the eventlog I get 18210 events:
BackupIoRequest::WaitForIoCompletion: read failure on backup device
'S:\SQLData\OLAPLogging.mdf'. Operating system error 1(Incorrect function.).
Windows has latest hotfixes, latest disk drivers, tried disk caching
enabled/disabled, SQL 2005 without service pack as apps guy said no.
Hardware is DL585, 32GB of RAM, 1.4TB HP RA4100 external disk array.
Any suggestions what to try?
Thanks,
GergelySince you are getting a I/O error on drive S. Have you tried the same
backups on two or more different drives?
Ben Nevarez, MCDBA, OCP
Database Administrator
"G.Gardonyi" wrote:
> Hi,
> We have just installed an Windows 2003 x64 server with SQL 2005 to replace
> our old server. Everything seems to be working fine but the backup.
> When trying to backup databases larger than 16MB or so the backup fails with
> a System.Data.SqlClient.SqlError: A nonrecoverable I/O error occurred on file
> "S:\SQLData\OLAPLogging.mdf:" 1(Incorrect function.).
> (Microsoft.SqlServer.Smo) message:
> Smaller DBs backup fine (master, model, etc). even if I create a new DB it
> backups fine until it grows bigger than about 16MB, then it gives the same
> error message.
> In the eventlog I get 18210 events:
> BackupIoRequest::WaitForIoCompletion: read failure on backup device
> 'S:\SQLData\OLAPLogging.mdf'. Operating system error 1(Incorrect function.).
> Windows has latest hotfixes, latest disk drivers, tried disk caching
> enabled/disabled, SQL 2005 without service pack as apps guy said no.
> Hardware is DL585, 32GB of RAM, 1.4TB HP RA4100 external disk array.
> Any suggestions what to try?
> Thanks,
> Gergely|||Yes, same problem. Mind you it's the same external storage unit so can't rule
out some kind of HW problem. It's just weird that 'normal' operations are
running fine but when starting a backup it throws an error instantly.
"Ben Nevarez" wrote:
> Since you are getting a I/O error on drive S. Have you tried the same
> backups on two or more different drives?
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "G.Gardonyi" wrote:
> > Hi,
> >
> > We have just installed an Windows 2003 x64 server with SQL 2005 to replace
> > our old server. Everything seems to be working fine but the backup.
> > When trying to backup databases larger than 16MB or so the backup fails with
> > a System.Data.SqlClient.SqlError: A nonrecoverable I/O error occurred on file
> > "S:\SQLData\OLAPLogging.mdf:" 1(Incorrect function.).
> > (Microsoft.SqlServer.Smo) message:
> >
> > Smaller DBs backup fine (master, model, etc). even if I create a new DB it
> > backups fine until it grows bigger than about 16MB, then it gives the same
> > error message.
> >
> > In the eventlog I get 18210 events:
> > BackupIoRequest::WaitForIoCompletion: read failure on backup device
> > 'S:\SQLData\OLAPLogging.mdf'. Operating system error 1(Incorrect function.).
> >
> > Windows has latest hotfixes, latest disk drivers, tried disk caching
> > enabled/disabled, SQL 2005 without service pack as apps guy said no.
> > Hardware is DL585, 32GB of RAM, 1.4TB HP RA4100 external disk array.
> >
> > Any suggestions what to try?
> >
> > Thanks,
> > Gergely

Backup fails with A nonrecoverable I/O error occurred for large DB

Hi,
We have just installed an Windows 2003 x64 server with SQL 2005 to replace
our old server. Everything seems to be working fine but the backup.
When trying to backup databases larger than 16MB or so the backup fails with
a System.Data.SqlClient.SqlError: A nonrecoverable I/O error occurred on fil
e
"S:\SQLData\OLAPLogging.mdf:" 1(Incorrect function.).
(Microsoft.SqlServer.Smo) message:
Smaller DBs backup fine (master, model, etc). even if I create a new DB it
backups fine until it grows bigger than about 16MB, then it gives the same
error message.
In the eventlog I get 18210 events:
BackupIoRequest::WaitForIoCompletion: read failure on backup device
'S:\SQLData\OLAPLogging.mdf'. Operating system error 1(Incorrect function.).
Windows has latest hotfixes, latest disk drivers, tried disk caching
enabled/disabled, SQL 2005 without service pack as apps guy said no.
Hardware is DL585, 32GB of RAM, 1.4TB HP RA4100 external disk array.
Any suggestions what to try?
Thanks,
GergelySince you are getting a I/O error on drive S. Have you tried the same
backups on two or more different drives?
Ben Nevarez, MCDBA, OCP
Database Administrator
"G.Gardonyi" wrote:

> Hi,
> We have just installed an Windows 2003 x64 server with SQL 2005 to replace
> our old server. Everything seems to be working fine but the backup.
> When trying to backup databases larger than 16MB or so the backup fails wi
th
> a System.Data.SqlClient.SqlError: A nonrecoverable I/O error occurred on f
ile
> "S:\SQLData\OLAPLogging.mdf:" 1(Incorrect function.).
> (Microsoft.SqlServer.Smo) message:
> Smaller DBs backup fine (master, model, etc). even if I create a new DB it
> backups fine until it grows bigger than about 16MB, then it gives the same
> error message.
> In the eventlog I get 18210 events:
> BackupIoRequest::WaitForIoCompletion: read failure on backup device
> 'S:\SQLData\OLAPLogging.mdf'. Operating system error 1(Incorrect function.
).
> Windows has latest hotfixes, latest disk drivers, tried disk caching
> enabled/disabled, SQL 2005 without service pack as apps guy said no.
> hardware is DL585, 32GB of RAM, 1.4TB HP RA4100 external disk array.
> Any suggestions what to try?
> Thanks,
> Gergely

backup exec fails (though having the sql addon)

Hi group
Working with backup exec 10, sql agent and advanced open file options
included to backup our databases. We have 11 SQL databases varying in size
from 200 to 5000mb. There is one new dbase which is the database for a
website that is now not being backed up (size, approx 1.5 GB), this has
never worked since it was created last week.
(for now we are doing a full backup dump in non production hours to a
location that is backed up)
Additional info:
-Backupexec runs under a domain admin account
-SQL Server runs under another account
-owner for databases is domain admin
-2003 server for SQL (2000/sp3a) and the webserver (IIS6.0)
-backupexec shows fileskipped messages without specifying the file
-no usable info in eventvwr
-there are jobs running on this database (although not when the backup is
run)
-no locks/active connections are shown in the enterprise manager
Are there any issues with sql databases that are used by websites?
Has anybody have experienced similar problems and have a sollution available
that might work for me?
thx.
PerryPerry,
When you view the history for the backup job with BackupExec what error
message is recorded, if any?
> (for now we are doing a full backup dump in non production hours to a
> location that is backed up)
Using? Are you using Enterprise Manager? If not, does that work? Any
errors in the SQL errorlog for the backup failing?
HTH
Jerry
"Perry" <reply@.to.ng> wrote in message
news:ejIiKnUxFHA.1032@.TK2MSFTNGP12.phx.gbl...
> Hi group
> Working with backup exec 10, sql agent and advanced open file options
> included to backup our databases. We have 11 SQL databases varying in size
> from 200 to 5000mb. There is one new dbase which is the database for a
> website that is now not being backed up (size, approx 1.5 GB), this has
> never worked since it was created last week.
> (for now we are doing a full backup dump in non production hours to a
> location that is backed up)
> Additional info:
> -Backupexec runs under a domain admin account
> -SQL Server runs under another account
> -owner for databases is domain admin
> -2003 server for SQL (2000/sp3a) and the webserver (IIS6.0)
> -backupexec shows fileskipped messages without specifying the file
> -no usable info in eventvwr
> -there are jobs running on this database (although not when the backup is
> run)
> -no locks/active connections are shown in the enterprise manager
> Are there any issues with sql databases that are used by websites?
> Has anybody have experienced similar problems and have a sollution
> available that might work for me?
> thx.
> Perry
>
>|||Hi
Never use open file options against a SQL Server database as the DB and
Transaction log will never be restorable as they will not be in sync at a
point in time.
Backup the database to disk from within SQL Server (see BACKUP command in
BOL), or use the SQL Server maintenance wizard.
Backup the backup file files that SQL Server generated.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Perry" <reply@.to.ng> wrote in message
news:ejIiKnUxFHA.1032@.TK2MSFTNGP12.phx.gbl...
> Hi group
> Working with backup exec 10, sql agent and advanced open file options
> included to backup our databases. We have 11 SQL databases varying in size
> from 200 to 5000mb. There is one new dbase which is the database for a
> website that is now not being backed up (size, approx 1.5 GB), this has
> never worked since it was created last week.
> (for now we are doing a full backup dump in non production hours to a
> location that is backed up)
> Additional info:
> -Backupexec runs under a domain admin account
> -SQL Server runs under another account
> -owner for databases is domain admin
> -2003 server for SQL (2000/sp3a) and the webserver (IIS6.0)
> -backupexec shows fileskipped messages without specifying the file
> -no usable info in eventvwr
> -there are jobs running on this database (although not when the backup is
> run)
> -no locks/active connections are shown in the enterprise manager
> Are there any issues with sql databases that are used by websites?
> Has anybody have experienced similar problems and have a sollution
> available that might work for me?
> thx.
> Perry
>
>|||Mike,
I have this same argument with my IT director. It's been one of those he
won't budge...I won't budge thing so...Veritas does have an agent for SQL
backups. We have the license but I've chosen not to use it (yet) not
because it doesn't work but because it is a bit more challenging and
involved. I do backup the databases using EM (jobs) then I use Veritas
BackupExec to backup the backup files to tape for off-site storage.
HTH
Jerry
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:ObMOEeVxFHA.3756@.tk2msftngp13.phx.gbl...
> Hi
> Never use open file options against a SQL Server database as the DB and
> Transaction log will never be restorable as they will not be in sync at a
> point in time.
> Backup the database to disk from within SQL Server (see BACKUP command in
> BOL), or use the SQL Server maintenance wizard.
> Backup the backup file files that SQL Server generated.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Perry" <reply@.to.ng> wrote in message
> news:ejIiKnUxFHA.1032@.TK2MSFTNGP12.phx.gbl...
>> Hi group
>> Working with backup exec 10, sql agent and advanced open file options
>> included to backup our databases. We have 11 SQL databases varying in
>> size from 200 to 5000mb. There is one new dbase which is the database for
>> a website that is now not being backed up (size, approx 1.5 GB), this has
>> never worked since it was created last week.
>> (for now we are doing a full backup dump in non production hours to a
>> location that is backed up)
>> Additional info:
>> -Backupexec runs under a domain admin account
>> -SQL Server runs under another account
>> -owner for databases is domain admin
>> -2003 server for SQL (2000/sp3a) and the webserver (IIS6.0)
>> -backupexec shows fileskipped messages without specifying the file
>> -no usable info in eventvwr
>> -there are jobs running on this database (although not when the backup is
>> run)
>> -no locks/active connections are shown in the enterprise manager
>> Are there any issues with sql databases that are used by websites?
>> Has anybody have experienced similar problems and have a sollution
>> available that might work for me?
>> thx.
>> Perry
>>
>>
>|||Jerry,
Below is what the log says, the servername is DC4, the databasename is
SYNERGY:
Job Completion Status
Job ended: Wednesday, September 28, 2005 at 8:36:41 PM
Completed status: Completed with exceptions
Errors
Click an error below to locate it in the job log
Backup- DC4 Database ?SYNERGY was not found, or could not be accessed.
Exceptions
Click an exception below to locate it in the job log
Backup- DC4 The item DC4\SYNERGY in use - skipped.Perry
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:u4j16tUxFHA.3740@.tk2msftngp13.phx.gbl...
> Perry,
> When you view the history for the backup job with BackupExec what error
> message is recorded, if any?
>> (for now we are doing a full backup dump in non production hours to a
>> location that is backed up)
> Using? Are you using Enterprise Manager? If not, does that work? Any
> errors in the SQL errorlog for the backup failing?
> HTH
> Jerry
> "Perry" <reply@.to.ng> wrote in message
> news:ejIiKnUxFHA.1032@.TK2MSFTNGP12.phx.gbl...
>> Hi group
>> Working with backup exec 10, sql agent and advanced open file options
>> included to backup our databases. We have 11 SQL databases varying in
>> size from 200 to 5000mb. There is one new dbase which is the database for
>> a website that is now not being backed up (size, approx 1.5 GB), this has
>> never worked since it was created last week.
>> (for now we are doing a full backup dump in non production hours to a
>> location that is backed up)
>> Additional info:
>> -Backupexec runs under a domain admin account
>> -SQL Server runs under another account
>> -owner for databases is domain admin
>> -2003 server for SQL (2000/sp3a) and the webserver (IIS6.0)
>> -backupexec shows fileskipped messages without specifying the file
>> -no usable info in eventvwr
>> -there are jobs running on this database (although not when the backup is
>> run)
>> -no locks/active connections are shown in the enterprise manager
>> Are there any issues with sql databases that are used by websites?
>> Has anybody have experienced similar problems and have a sollution
>> available that might work for me?
>> thx.
>> Perry
>>
>>
>

Backup Error - Operating system error 1450

Hi,
Backing up 70Gb database to the network drive on the backup server. Was
working for long time until now. I think the database size is growing and
the OS cannot handle it. Error messages are as follows,
"Operating system error 1450(Insufficient system resources exist to complete
the requested service."
Looking at MS KB - http://support.microsoft.com/default.aspx/kb/304101
Not sure which server should add the registry key PoolUsageMaximum ? (On
backup server or SQL server ?)
Had set the registry key PoolUsageMaximum to 40 on backup server but no good.
Please advise !in general, I think it's better to backup to a local disk. that way if the
network goes down during the backup, you still have a backup. when the
backup succeeds, you can copy it to the network share then delete the local
copy.
if you backup to the share only, you are toast if the network hiccups. then
you have data loss and are soon looking for a new job. :)
--
http://elsasoft.org
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:9BB1A66C-170D-49DE-9667-2AF306E75996@.microsoft.com...
> Hi,
> Backing up 70Gb database to the network drive on the backup server. Was
> working for long time until now. I think the database size is growing and
> the OS cannot handle it. Error messages are as follows,
> "Operating system error 1450(Insufficient system resources exist to
> complete
> the requested service."
> Looking at MS KB - http://support.microsoft.com/default.aspx/kb/304101
> Not sure which server should add the registry key PoolUsageMaximum ? (On
> backup server or SQL server ?)
> Had set the registry key PoolUsageMaximum to 40 on backup server but no
> good.
> Please advise !
>
>
>
>|||I know it is highly recommended to backup SQL databases on local drive. This
working place is gradually changing the practice to do the right things but
takes times.
I really need to fix this issues right now.
Any solutions ?
"Jesse Hersch" wrote:
> in general, I think it's better to backup to a local disk. that way if the
> network goes down during the backup, you still have a backup. when the
> backup succeeds, you can copy it to the network share then delete the local
> copy.
> if you backup to the share only, you are toast if the network hiccups. then
> you have data loss and are soon looking for a new job. :)
> --
> http://elsasoft.org
>
> "Johnny" <Johnny@.discussions.microsoft.com> wrote in message
> news:9BB1A66C-170D-49DE-9667-2AF306E75996@.microsoft.com...
> > Hi,
> >
> > Backing up 70Gb database to the network drive on the backup server. Was
> > working for long time until now. I think the database size is growing and
> > the OS cannot handle it. Error messages are as follows,
> >
> > "Operating system error 1450(Insufficient system resources exist to
> > complete
> > the requested service."
> >
> > Looking at MS KB - http://support.microsoft.com/default.aspx/kb/304101
> >
> > Not sure which server should add the registry key PoolUsageMaximum ? (On
> > backup server or SQL server ?)
> > Had set the registry key PoolUsageMaximum to 40 on backup server but no
> > good.
> >
> > Please advise !
> >
> >
> >
> >
> >
> >
> >
>
>

Backup database over network not working.

The database server is on computer P4.
Computer P3 has a shared folder with full access.
Computer P5 on which I have my Backup Device and will execute the Backup Database Command using Query Analyzer.

On P5 I created the Backup Device:

USE master
EXEC sp_addumpdevice 'disk', 'myDevice',
'\\P3\temp\test.bak'

Then I executed the following on P5 Query Analyzer:

Backup Database myDatabase TO myDevice

But I get this error:

Server: Msg 3201, Level 16, State 1, Line 1
Cannot open backup device 'myDevice'. Device error or device off-line. See the SQL Server error log for more details.
Server: Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

Any idea? Thanks and regards.Originally posted by juniper
The database server is on computer P4.
Computer P3 has a shared folder with full access.
Computer P5 on which I have my Backup Device and will execute the Backup Database Command using Query Analyzer.

On P5 I created the Backup Device:

USE master
EXEC sp_addumpdevice 'disk', 'myDevice',
'\\P3\temp\test.bak'

Then I executed the following on P5 Query Analyzer:

Backup Database myDatabase TO myDevice

But I get this error:

Server: Msg 3201, Level 16, State 1, Line 1
Cannot open backup device 'myDevice'. Device error or device off-line. See the SQL Server error log for more details.
Server: Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

Any idea? Thanks and regards.

yeah, go into Enterprise Manager, right click on the db you are trying to backup and all tasks - bring online|||Better yet...don't dump across a network...

It'll be much slower and be at risk for blips in the network...

Dump local then copy...|||Hi guys, thanks for your advice. Brett, I am doing it the way you suggested. However, it would mean that the computer running the database server would have to have a folder permanently shared for my program to copy the file across the network.

Any workaround so that I do not need to share folders? The user might inadvertently unshare the folder and would create problems.

Thanks and regards.|||Ensure the account used to execute this task does have required privileges to access the share specified.

If you're scheduling this task then ensure SQLAgent account used does have admin privileges or a domain account to access so.

BackUp database

Hi all

I use this code to bak up database
and its working fine but i want run this code via ASP
can anyone write it for me in ASP format?

USE master
EXEC sp_addumpdevice 'disk', 'test_2',
'C:\test_2.dat'

USE master
EXEC sp_addumpdevice 'disk', 'testlog',
'C:\testlog.dat'

BACKUP DATABASE test TO test_2

BACKUP LOG test TO testlog



thanksAll you need to do is put each TSQL command as relevant ASP command.
In your case, you need to execute 4 commands so you would need
SQLStmt, SQLstmt1,2,3 and 4 lots of RS = Connection.Execute(SQLStmt)

e.g. - example of one connection & execution :

Dim UID
Dim SQLStmt, SQLStmt1, SQLStmt2, SQLStmt3
Dim Connection,
Dim RS, RS1, RS2, RS3

Set Connection = Server.CreateObject("ADODB.Connection")

Connection.Open "PROVIDER=SQLOLEDB;DATA SOURCE=server1;UID=test;PWD=password;DATABASE=data base1"

SQLStmt = "SELECT * FROM TBL_Test where server_name = 'SERVER1' and job_run_time > (getdate() - 7)"

Set RS = Connection.Execute(SQLStmt)

Backup and restore databases and access rights

Hi,

I am creating a way of working in order to "copy" databases from a master SQL Server 2000 database to the developers local machines.

I want to create a master SQL Server at our office location. Whenever our developers works on site the master is used. To be able to work locally, a backup has been created of the master and is distributed to the developers. On their local machines, they restore the database to be able to develop locally.

I have created a backup on my laptop using on my domain account (not the account that created the database, but I can use it when developing). I moved the backup file to another computer and restored it using my domain account on that computer. I could not see all of the tables, users or stored procedures, only those which type are System, not User. When i log in to the local administrators account I can see them all. When I log back into my domain account I can see all of the tables.

Anyone knows why?

/M

SQL Enterprise Manager doesn't refresh objects very well. You actually have to force it to refresh sometimes by hitting F5 and even that doesn't always work. Something to try in this case is a quick select statement in query analyzer of a table that you know should be there. Typically it will either show the results or give you the error associated with permissions.

It also sounds like it could be db permissions problems. Typically, SEM connects to the local server via the sa login. So when you login as yourself, you are probably connecting to the server as yourself in SEM. Also keep in mind that objects are owned by their creators and unless permissions are specifically granted for other users/roles or the current user is an admin, you won't be able to work with these objects. So if you created objects under one login (such as sa) you may not be able to see them under another login (such as your domain account). sp_changeobjectowner is a way to modify the owner for each object. sp_MScheck_uid_owns_anything will help determine if the user owns anything; just use the id of the user as the only parameter.

Another option would be to grant all permissions to the public role for all objects as they are created. This is not advised because you are not using SQL Security like it should. But roles are a good thing in environments like this, but these take some setup and administration time.

Backup / Restore to a different server

Our main system has maintenance plans in place (including backups) and they
seem to be working.
I am taking a seperate specific complete database backup to restore on
another maching for testing.
I'm backing up to a specific file and have the backup verified.
I copy to file to another machine without error.
I restore it on that machine without error.
Yet here is data that is not there.
What am I doing wrong?
Kyle!What you say is simply impossible. I would make sure you are connecting to
the correct servers for both the backup and restore process.
Andrew J. Kelly SQL MVP
"Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
news:uOCKY6rlFHA.2444@.tk2msftngp13.phx.gbl...
> Our main system has maintenance plans in place (including backups) and
> they seem to be working.
> I am taking a seperate specific complete database backup to restore on
> another maching for testing.
> I'm backing up to a specific file and have the backup verified.
> I copy to file to another machine without error.
> I restore it on that machine without error.
> Yet here is data that is not there.
> What am I doing wrong?
> Kyle!
>|||How are you determining the data isn't there? Enterprise Mangler needs a
refresh before it will show newly restored databases. You may have
permission issues that are preventing your account from accessing particular
data within the database? Withoutknowing how you are accessing the data, I
really can't go any further.
Geoff N. Hiten
Senior Database Administrator
Microsoct SQL Server MVP
"Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
news:uOCKY6rlFHA.2444@.tk2msftngp13.phx.gbl...
> Our main system has maintenance plans in place (including backups) and
> they seem to be working.
> I am taking a seperate specific complete database backup to restore on
> another maching for testing.
> I'm backing up to a specific file and have the backup verified.
> I copy to file to another machine without error.
> I restore it on that machine without error.
> Yet here is data that is not there.
> What am I doing wrong?
> Kyle!
>|||Just stupidity on my part.
Was restoring an old backup. (Happened to be in a different directory but
have the same name.)
Thanks
Kyle!
"Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
news:uOCKY6rlFHA.2444@.tk2msftngp13.phx.gbl...
> Our main system has maintenance plans in place (including backups) and
> they seem to be working.
> I am taking a seperate specific complete database backup to restore on
> another maching for testing.
> I'm backing up to a specific file and have the backup verified.
> I copy to file to another machine without error.
> I restore it on that machine without error.
> Yet here is data that is not there.
> What am I doing wrong?
> Kyle!
>

Backup / Restore to a different server

Our main system has maintenance plans in place (including backups) and they
seem to be working.
I am taking a seperate specific complete database backup to restore on
another maching for testing.
I'm backing up to a specific file and have the backup verified.
I copy to file to another machine without error.
I restore it on that machine without error.
Yet here is data that is not there.
What am I doing wrong?
Kyle!What you say is simply impossible. I would make sure you are connecting to
the correct servers for both the backup and restore process.
--
Andrew J. Kelly SQL MVP
"Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
news:uOCKY6rlFHA.2444@.tk2msftngp13.phx.gbl...
> Our main system has maintenance plans in place (including backups) and
> they seem to be working.
> I am taking a seperate specific complete database backup to restore on
> another maching for testing.
> I'm backing up to a specific file and have the backup verified.
> I copy to file to another machine without error.
> I restore it on that machine without error.
> Yet here is data that is not there.
> What am I doing wrong?
> Kyle!
>|||How are you determining the data isn't there? Enterprise Mangler needs a
refresh before it will show newly restored databases. You may have
permission issues that are preventing your account from accessing particular
data within the database? Withoutknowing how you are accessing the data, I
really can't go any further.
Geoff N. Hiten
Senior Database Administrator
Microsoct SQL Server MVP
"Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
news:uOCKY6rlFHA.2444@.tk2msftngp13.phx.gbl...
> Our main system has maintenance plans in place (including backups) and
> they seem to be working.
> I am taking a seperate specific complete database backup to restore on
> another maching for testing.
> I'm backing up to a specific file and have the backup verified.
> I copy to file to another machine without error.
> I restore it on that machine without error.
> Yet here is data that is not there.
> What am I doing wrong?
> Kyle!
>|||Just stupidity on my part.
Was restoring an old backup. (Happened to be in a different directory but
have the same name.)
Thanks
Kyle!
"Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
news:uOCKY6rlFHA.2444@.tk2msftngp13.phx.gbl...
> Our main system has maintenance plans in place (including backups) and
> they seem to be working.
> I am taking a seperate specific complete database backup to restore on
> another maching for testing.
> I'm backing up to a specific file and have the backup verified.
> I copy to file to another machine without error.
> I restore it on that machine without error.
> Yet here is data that is not there.
> What am I doing wrong?
> Kyle!
>

Backup / Restore to a different server

Our main system has maintenance plans in place (including backups) and they
seem to be working.
I am taking a seperate specific complete database backup to restore on
another maching for testing.
I'm backing up to a specific file and have the backup verified.
I copy to file to another machine without error.
I restore it on that machine without error.
Yet here is data that is not there.
What am I doing wrong?
Kyle!
What you say is simply impossible. I would make sure you are connecting to
the correct servers for both the backup and restore process.
Andrew J. Kelly SQL MVP
"Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
news:uOCKY6rlFHA.2444@.tk2msftngp13.phx.gbl...
> Our main system has maintenance plans in place (including backups) and
> they seem to be working.
> I am taking a seperate specific complete database backup to restore on
> another maching for testing.
> I'm backing up to a specific file and have the backup verified.
> I copy to file to another machine without error.
> I restore it on that machine without error.
> Yet here is data that is not there.
> What am I doing wrong?
> Kyle!
>
|||How are you determining the data isn't there? Enterprise Mangler needs a
refresh before it will show newly restored databases. You may have
permission issues that are preventing your account from accessing particular
data within the database? Withoutknowing how you are accessing the data, I
really can't go any further.
Geoff N. Hiten
Senior Database Administrator
Microsoct SQL Server MVP
"Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
news:uOCKY6rlFHA.2444@.tk2msftngp13.phx.gbl...
> Our main system has maintenance plans in place (including backups) and
> they seem to be working.
> I am taking a seperate specific complete database backup to restore on
> another maching for testing.
> I'm backing up to a specific file and have the backup verified.
> I copy to file to another machine without error.
> I restore it on that machine without error.
> Yet here is data that is not there.
> What am I doing wrong?
> Kyle!
>
|||Just stupidity on my part.
Was restoring an old backup. (Happened to be in a different directory but
have the same name.)
Thanks
Kyle!
"Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
news:uOCKY6rlFHA.2444@.tk2msftngp13.phx.gbl...
> Our main system has maintenance plans in place (including backups) and
> they seem to be working.
> I am taking a seperate specific complete database backup to restore on
> another maching for testing.
> I'm backing up to a specific file and have the backup verified.
> I copy to file to another machine without error.
> I restore it on that machine without error.
> Yet here is data that is not there.
> What am I doing wrong?
> Kyle!
>

BACKUP - BCV

HI,
I am working with a DW and my costumer hired a service in
a colocation and we're using a STORAGE to store all data !
and he also contract a backup system called BCV that
connects to the storage that I'm using and then copy all
the information, but during this process he blocks all the
activity in the disk and syncronyze the information and
after that he releases the disk. I would like to know if
someone heard about it our work with it..
Thank's
Roberto CarrascoYes we use it. We use EMC and what basically happens is that it Freezes
your SQL Server IO and then thaws it. You will see this in the logs. We
use it on most of our systems and the process takes ~ 5 seconds. We have no
problems with it. it does not appear to affect replication or anything
(touch wood)
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Roberto Carrasco" <roberto_carrasco@.msn.com> wrote in message
news:09e201c37215$bfa001c0$a501280a@.phx.gbl...
> HI,
> I am working with a DW and my costumer hired a service in
> a colocation and we're using a STORAGE to store all data !
> and he also contract a backup system called BCV that
> connects to the storage that I'm using and then copy all
> the information, but during this process he blocks all the
> activity in the disk and syncronyze the information and
> after that he releases the disk. I would like to know if
> someone heard about it our work with it..
> Thank's
> Roberto Carrasco
>|||Nope you don't stop anything. The BCV/Symmetrix software handles
everything.
--
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Roberto Carrasco" <Roberto_carrasco@.msn.com> wrote in message
news:120f01c37255$06d513c0$a401280a@.phx.gbl...
> Hi,
> Thank's for your reply ! Is necessary to stop the SQL
> SERVICE - Do I need to do something ' If I need to
> restore ! Do I have to STOP SQL SERVER SERVICE .
> Please ANswer
> Thank's
> Roberto Carrasco
> >--Original Message--
> >Yes we use it. We use EMC and what basically happens is
> that it Freezes
> >your SQL Server IO and then thaws it. You will see this
> in the logs. We
> >use it on most of our systems and the process takes ~ 5
> seconds. We have no
> >problems with it. it does not appear to affect
> replication or anything
> >(touch wood)
> >
> >
> >
> >--
> >
> >--
> >Allan Mitchell (Microsoft SQL Server MVP)
> >MCSE,MCDBA
> >www.SQLDTS.com
> >I support PASS - the definitive, global community
> >for SQL Server professionals - http://www.sqlpass.org
> >
> >
> >
> >"Roberto Carrasco" <roberto_carrasco@.msn.com> wrote in
> message
> >news:09e201c37215$bfa001c0$a501280a@.phx.gbl...
> >> HI,
> >> I am working with a DW and my costumer hired a service
> in
> >> a colocation and we're using a STORAGE to store all
> data !
> >> and he also contract a backup system called BCV that
> >> connects to the storage that I'm using and then copy all
> >> the information, but during this process he blocks all
> the
> >> activity in the disk and syncronyze the information and
> >> after that he releases the disk. I would like to know if
> >> someone heard about it our work with it..
> >>
> >> Thank's
> >>
> >> Roberto Carrasco
> >>
> >
> >
> >.
> >

Backup

I'm working with a database server placed in a datacenter remotely placed fr
om my work location, and accessing the database through Enterprise Manager a
nd Quesry Analyser connected via Internet.
The backup is scheduled and log-shipping is implemented with another server
in the datacenter. But I want to take backup and store backup files in my lo
cal machine. Can I do this or I have to get the files from the database ser
ver through some FTP or oth
er protocol.
Thanks in advance but plz do help meSunil
BACKUP DATABASE DatabaseName TO DISK =
N'\\Server\backup\databasename.BAK'
"Sunil Dash" <anonymous@.discussions.microsoft.com> wrote in message
news:08D9D28D-E45A-4208-BF2C-6D5E15B9B1B6@.microsoft.com...

quote:

---

> I'm working with a database server placed in a datacenter remotely placed

---

from my work location, and accessing the database through Enterprise Manager
and Quesry Analyser connected via Internet.

quote:

---

> The backup is scheduled and log-shipping is implemented with another

---

server in the datacenter. But I want to take backup and store backup files
in my local machine. Can I do this or I have to get the files from the
database server through some FTP or other protocol.

quote:

---

>
> Thanks in advance but plz do help me

---

|||When I'm accessing the database server through the internet link so how can
I use \\ unc path.|||assumed you have SQL installed on your local machine or a machine on local
network and there is an FTP server at the remote network,
on the remote server you can use winrar (has more compression ratio then
winzip) to compress the backups, use DTS on to schedule an FTP job to
transfer the rar file to you local machine.
"Sunil Dash" <anonymous@.discussions.microsoft.com> wrote in message
news:76683E68-071B-42B1-9AFB-8E598E81FBF2@.microsoft.com...

quote:

---

> When I'm accessing the database server through the internet link so how

---

can I use \\ unc path.

Backup

I'm working with a database server placed in a datacenter remotely placed from my work location, and accessing the database through Enterprise Manager and Quesry Analyser connected via Internet.
The backup is scheduled and log-shipping is implemented with another server in the datacenter. But I want to take backup and store backup files in my local machine. Can I do this or I have to get the files from the database server through some FTP or other protocol.
Thanks in advance but plz do help meSunil
BACKUP DATABASE DatabaseName TO DISK = N'\\Server\backup\databasename.BAK'
"Sunil Dash" <anonymous@.discussions.microsoft.com> wrote in message
news:08D9D28D-E45A-4208-BF2C-6D5E15B9B1B6@.microsoft.com...
> I'm working with a database server placed in a datacenter remotely placed
from my work location, and accessing the database through Enterprise Manager
and Quesry Analyser connected via Internet.
> The backup is scheduled and log-shipping is implemented with another
server in the datacenter. But I want to take backup and store backup files
in my local machine. Can I do this or I have to get the files from the
database server through some FTP or other protocol.
>
> Thanks in advance but plz do help me|||When I'm accessing the database server through the internet link so how can I use \\ unc path.|||assumed you have SQL installed on your local machine or a machine on local
network and there is an FTP server at the remote network,
on the remote server you can use winrar (has more compression ratio then
winzip) to compress the backups, use DTS on to schedule an FTP job to
transfer the rar file to you local machine.
"Sunil Dash" <anonymous@.discussions.microsoft.com> wrote in message
news:76683E68-071B-42B1-9AFB-8E598E81FBF2@.microsoft.com...
> When I'm accessing the database server through the internet link so how
can I use \\ unc path.