Backing up SQL Server express db that runs in user instance?

Hi,
I want to do a backup of my database that runs in User Instance. The
problem is that due to connection pooling the file is constantly locked
so I can't copy the file or perform a backup in Management Studio.
Does anyone have any tips on how to do it?
I assume I'm not the only one that has these problems.
Thanks in advance
/Mats
How about just executing the TSQL BACKUP DATABASE command?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Mats Lycken" <MatsL@.newsgroups.nospam> wrote in message
news:uDBGGEYJGHA.3912@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I want to do a backup of my database that runs in User Instance. The problem is that due to
> connection pooling the file is constantly locked so I can't copy the file or perform a backup in
> Management Studio.
> Does anyone have any tips on how to do it?
> I assume I'm not the only one that has these problems.
> Thanks in advance
> /Mats
|||Tibor Karaszi wrote:
> How about just executing the TSQL BACKUP DATABASE command?
>
Thanks for the reply.
I'm still not getting it to work tho, actually I'm not sure how to
select the database to do the backup on.
My database instance is a .mdf file which I want to do a backup of.
How do I write the backup statement to perform the backup?
I've tried this in sqlcmd.
1> BACKUP DATABASE 'C:\Inetpub\wwwroot\SlottsCMS\App_Data\CMSDB.MDF' TO
DISK = 'C:\backup.dat'
2> GO
But it complains about the reference to the database.
How do I tell it that I want to do a backup of that file?
|||Mats Lycken wrote:
> Tibor Karaszi wrote:
> Thanks for the reply.
> I'm still not getting it to work tho, actually I'm not sure how to
> select the database to do the backup on.
> My database instance is a .mdf file which I want to do a backup of.
> How do I write the backup statement to perform the backup?
> I've tried this in sqlcmd.
> 1> BACKUP DATABASE 'C:\Inetpub\wwwroot\SlottsCMS\App_Data\CMSDB.MDF' TO
> DISK = 'C:\backup.dat'
> 2> GO
> But it complains about the reference to the database.
> How do I tell it that I want to do a backup of that file?
The correct syntax is
BACKUP DATABASE CMSDB TO DISK = 'C:\backup.dat'
(Assuming your database is named CMSDB)
You can look up the BACKUP DATABASE syntax in Books On Line. Here you
can also find all the various options you can use.
Regards
STeen
|||Use instances are special. You can get the name of the pipe from the parent instance and then use
that name to connect from your app where you want to execute the backup command. This is explained
in
http://msdn.microsoft.com/sql/expres...xpuserinst.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Mats Lycken" <MatsL@.newsgroups.nospam> wrote in message
news:%23FBYdFaJGHA.524@.TK2MSFTNGP09.phx.gbl...
> Tibor Karaszi wrote:
> Thanks for the reply.
> I'm still not getting it to work tho, actually I'm not sure how to select the database to do the
> backup on.
> My database instance is a .mdf file which I want to do a backup of.
> How do I write the backup statement to perform the backup?
> I've tried this in sqlcmd.
> 1> BACKUP DATABASE 'C:\Inetpub\wwwroot\SlottsCMS\App_Data\CMSDB.MDF' TO DISK = 'C:\backup.dat'
> 2> GO
> But it complains about the reference to the database.
> How do I tell it that I want to do a backup of that file?
|||The database name is not a string. Try doing a "select name from
sys.databases" in sseutil to see what the database name is. Chances are the
backup command would be:
1> BACKUP DATABASE [C:\Inetpub\wwwroot\SlottsCMS\App_Data\CMSDB.MD F] TO
DISK = 'C:\backup.dat'
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Mats Lycken" <MatsL@.newsgroups.nospam> wrote in message
news:%23FBYdFaJGHA.524@.TK2MSFTNGP09.phx.gbl...
> Tibor Karaszi wrote:
> Thanks for the reply.
> I'm still not getting it to work tho, actually I'm not sure how to select
> the database to do the backup on.
> My database instance is a .mdf file which I want to do a backup of.
> How do I write the backup statement to perform the backup?
> I've tried this in sqlcmd.
> 1> BACKUP DATABASE 'C:\Inetpub\wwwroot\SlottsCMS\App_Data\CMSDB.MDF' TO
> DISK = 'C:\backup.dat'
> 2> GO
> But it complains about the reference to the database.
> How do I tell it that I want to do a backup of that file?

Backing up SQL Server express db that runs in user instance?

Hi,
I want to do a backup of my database that runs in User Instance. The
problem is that due to connection pooling the file is constantly locked
so I can't copy the file or perform a backup in Management Studio.
Does anyone have any tips on how to do it?
I assume I'm not the only one that has these problems.
Thanks in advance
/MatsHow about just executing the TSQL BACKUP DATABASE command?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Mats Lycken" <MatsL@.newsgroups.nospam> wrote in message
news:uDBGGEYJGHA.3912@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I want to do a backup of my database that runs in User Instance. The problem is that due to
> connection pooling the file is constantly locked so I can't copy the file or perform a backup in
> Management Studio.
> Does anyone have any tips on how to do it?
> I assume I'm not the only one that has these problems.
> Thanks in advance
> /Mats|||Tibor Karaszi wrote:
> How about just executing the TSQL BACKUP DATABASE command?
>
Thanks for the reply.
I'm still not getting it to work tho, actually I'm not sure how to
select the database to do the backup on.
My database instance is a .mdf file which I want to do a backup of.
How do I write the backup statement to perform the backup?
I've tried this in sqlcmd.
1> BACKUP DATABASE 'C:\Inetpub\wwwroot\SlottsCMS\App_Data\CMSDB.MDF' TO
DISK = 'C:\backup.dat'
2> GO
But it complains about the reference to the database.
How do I tell it that I want to do a backup of that file?|||Mats Lycken wrote:
> Tibor Karaszi wrote:
>> How about just executing the TSQL BACKUP DATABASE command?
> Thanks for the reply.
> I'm still not getting it to work tho, actually I'm not sure how to
> select the database to do the backup on.
> My database instance is a .mdf file which I want to do a backup of.
> How do I write the backup statement to perform the backup?
> I've tried this in sqlcmd.
> 1> BACKUP DATABASE 'C:\Inetpub\wwwroot\SlottsCMS\App_Data\CMSDB.MDF' TO
> DISK = 'C:\backup.dat'
> 2> GO
> But it complains about the reference to the database.
> How do I tell it that I want to do a backup of that file?
The correct syntax is
BACKUP DATABASE CMSDB TO DISK = 'C:\backup.dat'
(Assuming your database is named CMSDB)
You can look up the BACKUP DATABASE syntax in Books On Line. Here you
can also find all the various options you can use.
Regards
STeen|||Use instances are special. You can get the name of the pipe from the parent instance and then use
that name to connect from your app where you want to execute the backup command. This is explained
in
http://msdn.microsoft.com/sql/express/default.aspx?pull=/library/en-us/dnsse/html/sqlexpuserinst.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Mats Lycken" <MatsL@.newsgroups.nospam> wrote in message
news:%23FBYdFaJGHA.524@.TK2MSFTNGP09.phx.gbl...
> Tibor Karaszi wrote:
>> How about just executing the TSQL BACKUP DATABASE command?
> Thanks for the reply.
> I'm still not getting it to work tho, actually I'm not sure how to select the database to do the
> backup on.
> My database instance is a .mdf file which I want to do a backup of.
> How do I write the backup statement to perform the backup?
> I've tried this in sqlcmd.
> 1> BACKUP DATABASE 'C:\Inetpub\wwwroot\SlottsCMS\App_Data\CMSDB.MDF' TO DISK = 'C:\backup.dat'
> 2> GO
> But it complains about the reference to the database.
> How do I tell it that I want to do a backup of that file?|||The database name is not a string. Try doing a "select name from
sys.databases" in sseutil to see what the database name is. Chances are the
backup command would be:
1> BACKUP DATABASE [C:\Inetpub\wwwroot\SlottsCMS\App_Data\CMSDB.MDF] TO
DISK = 'C:\backup.dat'
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Mats Lycken" <MatsL@.newsgroups.nospam> wrote in message
news:%23FBYdFaJGHA.524@.TK2MSFTNGP09.phx.gbl...
> Tibor Karaszi wrote:
>> How about just executing the TSQL BACKUP DATABASE command?
> Thanks for the reply.
> I'm still not getting it to work tho, actually I'm not sure how to select
> the database to do the backup on.
> My database instance is a .mdf file which I want to do a backup of.
> How do I write the backup statement to perform the backup?
> I've tried this in sqlcmd.
> 1> BACKUP DATABASE 'C:\Inetpub\wwwroot\SlottsCMS\App_Data\CMSDB.MDF' TO
> DISK = 'C:\backup.dat'
> 2> GO
> But it complains about the reference to the database.
> How do I tell it that I want to do a backup of that file?

Backing up SQL Server express db that runs in user instance?

Hi,
I want to do a backup of my database that runs in User Instance. The
problem is that due to connection pooling the file is constantly locked
so I can't copy the file or perform a backup in Management Studio.
Does anyone have any tips on how to do it?
I assume I'm not the only one that has these problems.
Thanks in advance
/MatsHow about just executing the TSQL BACKUP DATABASE command?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Mats Lycken" <MatsL@.newsgroups.nospam> wrote in message
news:uDBGGEYJGHA.3912@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I want to do a backup of my database that runs in User Instance. The probl
em is that due to
> connection pooling the file is constantly locked so I can't copy the file
or perform a backup in
> Management Studio.
> Does anyone have any tips on how to do it?
> I assume I'm not the only one that has these problems.
> Thanks in advance
> /Mats|||Tibor Karaszi wrote:
> How about just executing the TSQL BACKUP DATABASE command?
>
Thanks for the reply.
I'm still not getting it to work tho, actually I'm not sure how to
select the database to do the backup on.
My database instance is a .mdf file which I want to do a backup of.
How do I write the backup statement to perform the backup?
I've tried this in sqlcmd.
1> BACKUP DATABASE 'C:\Inetpub\wwwroot\SlottsCMS\App_Data\C
MSDB.MDF' TO
DISK = 'C:\backup.dat'
2> GO
But it complains about the reference to the database.
How do I tell it that I want to do a backup of that file?|||Mats Lycken wrote:
> Tibor Karaszi wrote:
> Thanks for the reply.
> I'm still not getting it to work tho, actually I'm not sure how to
> select the database to do the backup on.
> My database instance is a .mdf file which I want to do a backup of.
> How do I write the backup statement to perform the backup?
> I've tried this in sqlcmd.
> 1> BACKUP DATABASE 'C:\Inetpub\wwwroot\SlottsCMS\App_Data\C
MSDB.MDF' TO
> DISK = 'C:\backup.dat'
> 2> GO
> But it complains about the reference to the database.
> How do I tell it that I want to do a backup of that file?
The correct syntax is
BACKUP DATABASE CMSDB TO DISK = 'C:\backup.dat'
(Assuming your database is named CMSDB)
You can look up the BACKUP DATABASE syntax in Books On Line. Here you
can also find all the various options you can use.
Regards
STeen|||Use instances are special. You can get the name of the pipe from the parent
instance and then use
that name to connect from your app where you want to execute the backup comm
and. This is explained
in
sqlexpuserinst.asp" target="_blank">http://msdn.microsoft.com/sql/expre...expuserinst.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Mats Lycken" <MatsL@.newsgroups.nospam> wrote in message
news:%23FBYdFaJGHA.524@.TK2MSFTNGP09.phx.gbl...
> Tibor Karaszi wrote:
> Thanks for the reply.
> I'm still not getting it to work tho, actually I'm not sure how to select
the database to do the
> backup on.
> My database instance is a .mdf file which I want to do a backup of.
> How do I write the backup statement to perform the backup?
> I've tried this in sqlcmd.
> 1> BACKUP DATABASE 'C:\Inetpub\wwwroot\SlottsCMS\App_Data\C
MSDB.MDF' TO DI
SK = 'C:\backup.dat'
> 2> GO
> But it complains about the reference to the database.
> How do I tell it that I want to do a backup of that file?|||The database name is not a string. Try doing a "select name from
sys.databases" in sseutil to see what the database name is. Chances are the
backup command would be:
1> BACKUP DATABASE & #91;C:\Inetpub\wwwroot\SlottsCMS\App_Dat
a\CMSDB.MDF] TO
DISK = 'C:\backup.dat'
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Mats Lycken" <MatsL@.newsgroups.nospam> wrote in message
news:%23FBYdFaJGHA.524@.TK2MSFTNGP09.phx.gbl...
> Tibor Karaszi wrote:
> Thanks for the reply.
> I'm still not getting it to work tho, actually I'm not sure how to select
> the database to do the backup on.
> My database instance is a .mdf file which I want to do a backup of.
> How do I write the backup statement to perform the backup?
> I've tried this in sqlcmd.
> 1> BACKUP DATABASE 'C:\Inetpub\wwwroot\SlottsCMS\App_Data\C
MSDB.MDF' TO
> DISK = 'C:\backup.dat'
> 2> GO
> But it complains about the reference to the database.
> How do I tell it that I want to do a backup of that file?sql

Backing Up SQL

Please Help.......
New to my place of employment and new to SQL. Due to funding issues, we would like to use NT Backup (2000 Server) and not use a third party software such as Veritas or ArcServe. Will NT Server 2k backup successfully back up SQL? Are there any tricks I sho
uld be aware of?
I am also new to SQL, any info would be greatly appreciated.
Yout best bet is to use native SQL backups to backup your databases to disk
and then simply backup these files using NTBackup. You will not be able to
backup the actual database files themselves when SQL Server is running
because they will be in use. Have a look in Books On Line for BACKUP
DATABASE and BACKUP LOG. The easisest way to create a backup job for your
databases would be to create a Database Maintenance Plan. Again, there are
details of this in BOL
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"davidpkrok" <davidpkrok@.discussions.microsoft.com> wrote in message
news:88EF9E31-76FE-471B-BCA8-BC634054B6FD@.microsoft.com...
> Please Help.......
> New to my place of employment and new to SQL. Due to funding issues, we
would like to use NT Backup (2000 Server) and not use a third party software
such as Veritas or ArcServe. Will NT Server 2k backup successfully back up
SQL? Are there any tricks I should be aware of?
> I am also new to SQL, any info would be greatly appreciated.

Backing Up SQL

Please Help.......
New to my place of employment and new to SQL. Due to funding issues, we woul
d like to use NT Backup (2000 Server) and not use a third party software suc
h as Veritas or ArcServe. Will NT Server 2k backup successfully back up SQL?
Are there any tricks I sho
uld be aware of?
I am also new to SQL, any info would be greatly appreciated.Yout best bet is to use native SQL backups to backup your databases to disk
and then simply backup these files using NTBackup. You will not be able to
backup the actual database files themselves when SQL Server is running
because they will be in use. Have a look in Books On Line for BACKUP
DATABASE and BACKUP LOG. The easisest way to create a backup job for your
databases would be to create a Database Maintenance Plan. Again, there are
details of this in BOL
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"davidpkrok" <davidpkrok@.discussions.microsoft.com> wrote in message
news:88EF9E31-76FE-471B-BCA8-BC634054B6FD@.microsoft.com...
> Please Help.......
> New to my place of employment and new to SQL. Due to funding issues, we
would like to use NT Backup (2000 Server) and not use a third party software
such as Veritas or ArcServe. Will NT Server 2k backup successfully back up
SQL? Are there any tricks I should be aware of?
> I am also new to SQL, any info would be greatly appreciated.

Back Up question

I currently have my database backed up to the same physical drive where the data resides. In the event of drive failure, I would lose everything. Due to space considerations on a tape, I was wondering if I can copy only the database backup file (.BAK) and not the transaction log backup (.TRN). Can I go ahead with this approach if I do not want point-in-time recovery and I am satisfied with only what resides on .BAK file? Will I able to restore the database from the tape if the tape contains only the (.BAK) file ?

Thanks.Sure...

How often do you dump the transaction log?

But I don't think it's advisable...

How big a database are we talking about...

Ever consider compression?|||I often dump my backups across the network to a share on another server. It won't work with very large databases very well, but I've had pretty good luck so far. I find that writing backups to tape can be extremely slow.

As for not keeping the transaction logs, I have to agree with Brett; I would prefer to keep mine.

Regards,

hmscott|||Do you dump across a network, or locally the copy?

I've always thought a dump over a network could corrupt the dump

Never seen it...just always believed it could happen...|||I had all kinds of problems with it in 6.5 and 7.0. I've never seen any problems with it in 2000 though. We do it all the time. All of our servers are dumped to a central repository. We even do restores from there, including log shipping restores. I've never had any corruption doing it this way. Of course, we do have a dual 16gb core with fiber to all servers. We're upgrading to dual 64gb. A lot has changed over the years I guess.

I would definitely consider compression if I were that strapped on space and bandwidth. Many times SQL Backup files will shrink to 70-80 percent of the original size.

Back Issue

Following is my log file for transaction log backup via DB maintainance plan.
The step 2 of the process fails due to the reason mentioned.
I can try omitting the verify backup step in the plan, however wanted to
know if anybody has come accross this before and found a solution.
thanks
Starting maintenance plan 'SDS2 Database' on 9/20/2005 4:00:01 PM
[1] Database sds2: Transaction Log Backup...
Destination: [\\epsql09sa\e$\backup\epsql12sa\sds2_tlog_2005092 01600.TRN]
** Execution Time: 0 hrs, 0 mins, 32 secs **
[2] Database sds2: Verifying Backup...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3203: [Microsoft][ODBC SQL
Server Driver][SQL Server]Read on
'\\epsql09sa\e$\backup\epsql12sa\sds2_tlog_2005092 01600.TRN' failed, status =
1450. See the SQL Server error log for more details.
[Microsoft][ODBC SQL Server Driver][SQL Server]VERIFY DATABASE is
terminating abnormally.
Deleting old text reports... 0 file(s) deleted.
End of maintenance plan 'SDS2 Database' on 9/20/2005 4:00:33 PM
SQLMAINT.EXE Process Exit Code: 1 (Failed)
Hi
Looks like you are having network problems during the verify.
Have you tried backup up the file to a local disk and then verifying it?
Backup up across a network is rather risky as you are relying on your
backups to be 100%, but might be working on an un-reliable network.
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/
"b_dba" <b_dba@.discussions.microsoft.com> wrote in message
news:B669C041-0235-4673-94C1-DC0002998145@.microsoft.com...
> Following is my log file for transaction log backup via DB maintainance
> plan.
> The step 2 of the process fails due to the reason mentioned.
> I can try omitting the verify backup step in the plan, however wanted to
> know if anybody has come accross this before and found a solution.
> thanks
>
> Starting maintenance plan 'SDS2 Database' on 9/20/2005 4:00:01 PM
> [1] Database sds2: Transaction Log Backup...
> Destination:
> [\\epsql09sa\e$\backup\epsql12sa\sds2_tlog_2005092 01600.TRN]
> ** Execution Time: 0 hrs, 0 mins, 32 secs **
> [2] Database sds2: Verifying Backup...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3203: [Microsoft][ODBC
> SQL
> Server Driver][SQL Server]Read on
> '\\epsql09sa\e$\backup\epsql12sa\sds2_tlog_2005092 01600.TRN' failed,
> status =
> 1450. See the SQL Server error log for more details.
> [Microsoft][ODBC SQL Server Driver][SQL Server]VERIFY DATABASE is
> terminating abnormally.
> Deleting old text reports... 0 file(s) deleted.
> End of maintenance plan 'SDS2 Database' on 9/20/2005 4:00:33 PM
> SQLMAINT.EXE Process Exit Code: 1 (Failed)

Back Issue

Following is my log file for transaction log backup via DB maintainance plan.
The step 2 of the process fails due to the reason mentioned.
I can try omitting the verify backup step in the plan, however wanted to
know if anybody has come accross this before and found a solution.
thanks
Starting maintenance plan 'SDS2 Database' on 9/20/2005 4:00:01 PM
[1] Database sds2: Transaction Log Backup...
Destination: [\\epsql09sa\e$\backup\epsql12sa\sds2_tlog_200509201600.TRN]
** Execution Time: 0 hrs, 0 mins, 32 secs **
[2] Database sds2: Verifying Backup...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3203: [Microsoft][ODBC SQL
Server Driver][SQL Server]Read on
'\\epsql09sa\e$\backup\epsql12sa\sds2_tlog_200509201600.TRN' failed, status = 1450. See the SQL Server error log for more details.
[Microsoft][ODBC SQL Server Driver][SQL Server]VERIFY DATABASE is
terminating abnormally.
Deleting old text reports... 0 file(s) deleted.
End of maintenance plan 'SDS2 Database' on 9/20/2005 4:00:33 PM
SQLMAINT.EXE Process Exit Code: 1 (Failed)Hi
Looks like you are having network problems during the verify.
Have you tried backup up the file to a local disk and then verifying it?
Backup up across a network is rather risky as you are relying on your
backups to be 100%, but might be working on an un-reliable network.
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/
"b_dba" <b_dba@.discussions.microsoft.com> wrote in message
news:B669C041-0235-4673-94C1-DC0002998145@.microsoft.com...
> Following is my log file for transaction log backup via DB maintainance
> plan.
> The step 2 of the process fails due to the reason mentioned.
> I can try omitting the verify backup step in the plan, however wanted to
> know if anybody has come accross this before and found a solution.
> thanks
>
> Starting maintenance plan 'SDS2 Database' on 9/20/2005 4:00:01 PM
> [1] Database sds2: Transaction Log Backup...
> Destination:
> [\\epsql09sa\e$\backup\epsql12sa\sds2_tlog_200509201600.TRN]
> ** Execution Time: 0 hrs, 0 mins, 32 secs **
> [2] Database sds2: Verifying Backup...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3203: [Microsoft][ODBC
> SQL
> Server Driver][SQL Server]Read on
> '\\epsql09sa\e$\backup\epsql12sa\sds2_tlog_200509201600.TRN' failed,
> status => 1450. See the SQL Server error log for more details.
> [Microsoft][ODBC SQL Server Driver][SQL Server]VERIFY DATABASE is
> terminating abnormally.
> Deleting old text reports... 0 file(s) deleted.
> End of maintenance plan 'SDS2 Database' on 9/20/2005 4:00:33 PM
> SQLMAINT.EXE Process Exit Code: 1 (Failed)

Back Issue

Following is my log file for transaction log backup via DB maintainance plan
.
The step 2 of the process fails due to the reason mentioned.
I can try omitting the verify backup step in the plan, however wanted to
know if anybody has come accross this before and found a solution.
thanks
Starting maintenance plan 'SDS2 Database' on 9/20/2005 4:00:01 PM
[1] Database sds2: Transaction Log Backup...
Destination: & #91;\\epsql09sa\e$\backup\epsql12sa\sds2
_tlog_200509201600.TRN
]
** Execution Time: 0 hrs, 0 mins, 32 secs **
[2] Database sds2: Verifying Backup...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3203: [Microsoft]&#
91;ODBC SQL
Server Driver][SQL Server]Read on
'\\epsql09sa\e$\backup\epsql12sa\sds2_tl
og_200509201600.TRN' failed, status
=
1450. See the SQL Server error log for more details.
[Microsoft][ODBC SQL Server Driver][SQL Server]VERIFY DATABASE i
s
terminating abnormally.
Deleting old text reports... 0 file(s) deleted.
End of maintenance plan 'SDS2 Database' on 9/20/2005 4:00:33 PM
SQLMAINT.EXE Process Exit Code: 1 (Failed)Hi
Looks like you are having network problems during the verify.
Have you tried backup up the file to a local disk and then verifying it?
Backup up across a network is rather risky as you are relying on your
backups to be 100%, but might be working on an un-reliable network.
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/
"b_dba" <b_dba@.discussions.microsoft.com> wrote in message
news:B669C041-0235-4673-94C1-DC0002998145@.microsoft.com...
> Following is my log file for transaction log backup via DB maintainance
> plan.
> The step 2 of the process fails due to the reason mentioned.
> I can try omitting the verify backup step in the plan, however wanted to
> know if anybody has come accross this before and found a solution.
> thanks
>
> Starting maintenance plan 'SDS2 Database' on 9/20/2005 4:00:01 PM
> [1] Database sds2: Transaction Log Backup...
> Destination:
> & #91;\\epsql09sa\e$\backup\epsql12sa\sds2
_tlog_200509201600.TRN]
> ** Execution Time: 0 hrs, 0 mins, 32 secs **
> [2] Database sds2: Verifying Backup...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3203: [Microsoft]
[ODBC
> SQL
> Server Driver][SQL Server]Read on
> '\\epsql09sa\e$\backup\epsql12sa\sds2_tl
og_200509201600.TRN' failed,
> status =
> 1450. See the SQL Server error log for more details.
> [Microsoft][ODBC SQL Server Driver][SQL Server]VERIFY DATABASE
is
> terminating abnormally.
> Deleting old text reports... 0 file(s) deleted.
> End of maintenance plan 'SDS2 Database' on 9/20/2005 4:00:33 PM
> SQLMAINT.EXE Process Exit Code: 1 (Failed)

avoid cursor question

hi how do i avoid using a cursor in this situation.
i want all the payment installment_nos to be ordered by their due dates.
only 'unpaid' payments can be ordered.
/*
drop table payment1
create TABLE payment1
(installment_id integer IDENTITY(1,1), amount float, installment_no int,
due_date datetime, payment_status_code varchar(100))
insert into payment1 (amount, installment_no, due_date,
payment_status_code)
values(1,1,'1-1-05', 'paid')
insert into payment1 (amount, installment_no, due_date,
payment_status_code)
values(1,1,'1-2-05', 'unpaid')
insert into payment1 (amount, installment_no, due_date,
payment_status_code)
values(1,1,'1-3-05', 'unpaid')
insert into payment1 (amount, installment_no, due_date,
payment_status_code)
values(1,1,'1-4-05', 'unpaid')
*/
DECLARE @.liPaymentId integer
DECLARE @.lipaymentCntr integer
/*REORDER installments*/
SET @.lipaymentCntr =1
SELECT @.lipaymentCntr = MAX(installment_no) +1 FROM payment1
WHERE payment_status_code = 'PAID'
IF @.lipaymentCntr IS NULL
SET @.lipaymentCntr =1
DECLARE UpdateCursor CURSOR FOR
SELECT installment_id
FROM payment1
WHERE payment_status_code = 'unpaid'
ORDER by due_date
open UpdateCursor
FETCH NEXT FROM UpdateCursor
INTO @.liPaymentId
WHILE @.@.FETCH_STATUS = 0
BEGIN
UPDATE payment1
SET installment_no = @.lipaymentCntr
WHERE installment_id = @.lipaymentId
SET @.lipaymentCntr = @.lipaymentCntr + 1
FETCH NEXT FROM UpdateCursor
INTO @.lipaymentId
END
CLOSE UpdateCursor
DEALLOCATE UpdateCursor
select * from payment1"ichor" <ichor@.hotmail.com> wrote in message
news:OFkwLY$4FHA.1028@.TK2MSFTNGP11.phx.gbl...
> hi how do i avoid using a cursor in this situation.
> i want all the payment installment_nos to be ordered by their due dates.
> only 'unpaid' payments can be ordered.
> /*
> drop table payment1
> create TABLE payment1
> (installment_id integer IDENTITY(1,1), amount float, installment_no int,
> due_date datetime, payment_status_code varchar(100))
> insert into payment1 (amount, installment_no, due_date,
> payment_status_code)
> values(1,1,'1-1-05', 'paid')
> insert into payment1 (amount, installment_no, due_date,
> payment_status_code)
> values(1,1,'1-2-05', 'unpaid')
> insert into payment1 (amount, installment_no, due_date,
> payment_status_code)
> values(1,1,'1-3-05', 'unpaid')
> insert into payment1 (amount, installment_no, due_date,
> payment_status_code)
> values(1,1,'1-4-05', 'unpaid')
> */
> DECLARE @.liPaymentId integer
> DECLARE @.lipaymentCntr integer
> /*REORDER installments*/
> SET @.lipaymentCntr =1
> SELECT @.lipaymentCntr = MAX(installment_no) +1 FROM payment1
> WHERE payment_status_code = 'PAID'
> IF @.lipaymentCntr IS NULL
> SET @.lipaymentCntr =1
> DECLARE UpdateCursor CURSOR FOR
> SELECT installment_id
> FROM payment1
> WHERE payment_status_code = 'unpaid'
> ORDER by due_date
> open UpdateCursor
> FETCH NEXT FROM UpdateCursor
> INTO @.liPaymentId
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> UPDATE payment1
> SET installment_no = @.lipaymentCntr
> WHERE installment_id = @.lipaymentId
> SET @.lipaymentCntr = @.lipaymentCntr + 1
> FETCH NEXT FROM UpdateCursor
> INTO @.lipaymentId
> END
> CLOSE UpdateCursor
> DEALLOCATE UpdateCursor
>
> select * from payment1
>
Try:
UPDATE payment1
SET installment_no =
(SELECT COUNT(*)
FROM payment1 AS P
WHERE P.due_date <= payment1.due_date
AND P.payment_status_code = 'unpaid')
WHERE payment_status_code = 'unpaid' ;
I'm assuming due_date is unique even though you didn't specify. If not, then
please explain what defines the sequence.
Even better perhaps, avoid putting the redundant installment_no column in
the table at all:
SELECT installment_id, amount,
(SELECT COUNT(*)
FROM payment1 AS P
WHERE P.due_date <=
payment1.due_date
AND P.payment_status_code = 'unpaid') AS installment_no,
due_date, payment_status_code
FROM payment1 ;
David Portas
SQL Server MVP
--|||this is what i actually want
thanks
update payment1
set installment_no =
(select installment_no from payment1 P
where payment_status_code = 'paid'
) +
(select count(*)
FROM payment1 AS P
WHERE P.due_date <= payment1.due_date
AND P.payment_status_code = 'unpaid')
From payment1
WHERE payment_status_code = 'unpaid'
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:xJudnT64sNDwcPLeRVnyvw@.giganews.com...
> "ichor" <ichor@.hotmail.com> wrote in message
> news:OFkwLY$4FHA.1028@.TK2MSFTNGP11.phx.gbl...
> Try:
> UPDATE payment1
> SET installment_no =
> (SELECT COUNT(*)
> FROM payment1 AS P
> WHERE P.due_date <= payment1.due_date
> AND P.payment_status_code = 'unpaid')
> WHERE payment_status_code = 'unpaid' ;
> I'm assuming due_date is unique even though you didn't specify. If not,
> then please explain what defines the sequence.
> Even better perhaps, avoid putting the redundant installment_no column in
> the table at all:
> SELECT installment_id, amount,
> (SELECT COUNT(*)
> FROM payment1 AS P
> WHERE P.due_date <=
> payment1.due_date
> AND P.payment_status_code = 'unpaid') AS installment_no,
> due_date, payment_status_code
> FROM payment1 ;
> --
> David Portas
> SQL Server MVP
> --
>

AutoNumber Primary Key

We are testing the upload of an Access 2002 data database to SQL Server
2000. Some of the tables had AutoNumber fields that were Random (due to it
being a replicated table in Access). The upload created triggers similar to
the example below. However, Access applications fail on insert because the
primary key field is not populated until the trigger runs. Any help is
appreciated.
CREATE TRIGGER T_AssessmentDocs_ITrig ON dbo.AssessmentDocs FOR INSERT AS
SET NOCOUNT ON
DECLARE @.randc int, @.newc int /* FOR AUTONUMBER-EMULATION CODE */
/* * RANDOM AUTONUMBER EMULATION CODE FOR FIELD 'DocID' */
SELECT @.randc = (SELECT convert(int, rand() * power(2, 30)))
SELECT @.newc = (SELECT DocID FROM inserted)
UPDATE AssessmentDocs SET DocID = @.randc WHERE DocID = @.newc
DavidDavid C wrote:
> We are testing the upload of an Access 2002 data database to SQL
> Server 2000. Some of the tables had AutoNumber fields that were
> Random (due to it being a replicated table in Access). The upload
> created triggers similar to the example below. However, Access
> applications fail on insert because the primary key field is not
> populated until the trigger runs. Any help is appreciated.
> CREATE TRIGGER T_AssessmentDocs_ITrig ON dbo.AssessmentDocs FOR
> INSERT AS SET NOCOUNT ON
> DECLARE @.randc int, @.newc int /* FOR AUTONUMBER-EMULATION CODE */
> /* * RANDOM AUTONUMBER EMULATION CODE FOR FIELD 'DocID' */
> SELECT @.randc = (SELECT convert(int, rand() * power(2, 30)))
> SELECT @.newc = (SELECT DocID FROM inserted)
> UPDATE AssessmentDocs SET DocID = @.randc WHERE DocID = @.newc
> David
Change the column to an IDENTITY value. SQL Server automates the
generation of the next value. You may have to seed the identity value
using the MAX(ID) + 1 in the table.
You can pull back the newly inserted identity value into the application
using the SCOPE_IDENTITY() function.
David Gugick
Imceda Software
www.imceda.com|||David Gugick wrote:
> David C wrote:
>> We are testing the upload of an Access 2002 data database to SQL
>> Server 2000. Some of the tables had AutoNumber fields that were
>> Random (due to it being a replicated table in Access). The upload
>> created triggers similar to the example below. However, Access
>> applications fail on insert because the primary key field is not
>> populated until the trigger runs. Any help is appreciated.
>> CREATE TRIGGER T_AssessmentDocs_ITrig ON dbo.AssessmentDocs FOR
>> INSERT AS SET NOCOUNT ON
>> DECLARE @.randc int, @.newc int /* FOR AUTONUMBER-EMULATION CODE */
>> /* * RANDOM AUTONUMBER EMULATION CODE FOR FIELD 'DocID' */
>> SELECT @.randc = (SELECT convert(int, rand() * power(2, 30)))
>> SELECT @.newc = (SELECT DocID FROM inserted)
>> UPDATE AssessmentDocs SET DocID = @.randc WHERE DocID = @.newc
>> David
> Change the column to an IDENTITY value. SQL Server automates the
> generation of the next value. You may have to seed the identity value
> using the MAX(ID) + 1 in the table.
Or by using DBCC CHECKIDENT, which should do the same job.

AutoNumber Primary Key

We are testing the upload of an Access 2002 data database to SQL Server
2000. Some of the tables had AutoNumber fields that were Random (due to it
being a replicated table in Access). The upload created triggers similar to
the example below. However, Access applications fail on insert because the
primary key field is not populated until the trigger runs. Any help is
appreciated.
CREATE TRIGGER T_AssessmentDocs_ITrig ON dbo.AssessmentDocs FOR INSERT AS
SET NOCOUNT ON
DECLARE @.randc int, @.newc int /* FOR AUTONUMBER-EMULATION CODE */
/* * RANDOM AUTONUMBER EMULATION CODE FOR FIELD 'DocID' */
SELECT @.randc = (SELECT convert(int, rand() * power(2, 30)))
SELECT @.newc = (SELECT DocID FROM inserted)
UPDATE AssessmentDocs SET DocID = @.randc WHERE DocID = @.newc
David
David C wrote:
> We are testing the upload of an Access 2002 data database to SQL
> Server 2000. Some of the tables had AutoNumber fields that were
> Random (due to it being a replicated table in Access). The upload
> created triggers similar to the example below. However, Access
> applications fail on insert because the primary key field is not
> populated until the trigger runs. Any help is appreciated.
> CREATE TRIGGER T_AssessmentDocs_ITrig ON dbo.AssessmentDocs FOR
> INSERT AS SET NOCOUNT ON
> DECLARE @.randc int, @.newc int /* FOR AUTONUMBER-EMULATION CODE */
> /* * RANDOM AUTONUMBER EMULATION CODE FOR FIELD 'DocID' */
> SELECT @.randc = (SELECT convert(int, rand() * power(2, 30)))
> SELECT @.newc = (SELECT DocID FROM inserted)
> UPDATE AssessmentDocs SET DocID = @.randc WHERE DocID = @.newc
> David
Change the column to an IDENTITY value. SQL Server automates the
generation of the next value. You may have to seed the identity value
using the MAX(ID) + 1 in the table.
You can pull back the newly inserted identity value into the application
using the SCOPE_IDENTITY() function.
David Gugick
Imceda Software
www.imceda.com
|||David Gugick wrote:
> David C wrote:
> Change the column to an IDENTITY value. SQL Server automates the
> generation of the next value. You may have to seed the identity value
> using the MAX(ID) + 1 in the table.
Or by using DBCC CHECKIDENT, which should do the same job.

AutoNumber Primary Key

We are testing the upload of an Access 2002 data database to SQL Server
2000. Some of the tables had AutoNumber fields that were Random (due to it
being a replicated table in Access). The upload created triggers similar to
the example below. However, Access applications fail on insert because the
primary key field is not populated until the trigger runs. Any help is
appreciated.
CREATE TRIGGER T_AssessmentDocs_ITrig ON dbo.AssessmentDocs FOR INSERT AS
SET NOCOUNT ON
DECLARE @.randc int, @.newc int /* FOR AUTONUMBER-EMULATION CODE */
/* * RANDOM AUTONUMBER EMULATION CODE FOR FIELD 'DocID' */
SELECT @.randc = (SELECT convert(int, rand() * power(2, 30)))
SELECT @.newc = (SELECT DocID FROM inserted)
UPDATE AssessmentDocs SET DocID = @.randc WHERE DocID = @.newc
DavidDavid C wrote:
> We are testing the upload of an Access 2002 data database to SQL
> Server 2000. Some of the tables had AutoNumber fields that were
> Random (due to it being a replicated table in Access). The upload
> created triggers similar to the example below. However, Access
> applications fail on insert because the primary key field is not
> populated until the trigger runs. Any help is appreciated.
> CREATE TRIGGER T_AssessmentDocs_ITrig ON dbo.AssessmentDocs FOR
> INSERT AS SET NOCOUNT ON
> DECLARE @.randc int, @.newc int /* FOR AUTONUMBER-EMULATION CODE */
> /* * RANDOM AUTONUMBER EMULATION CODE FOR FIELD 'DocID' */
> SELECT @.randc = (SELECT convert(int, rand() * power(2, 30)))
> SELECT @.newc = (SELECT DocID FROM inserted)
> UPDATE AssessmentDocs SET DocID = @.randc WHERE DocID = @.newc
> David
Change the column to an IDENTITY value. SQL Server automates the
generation of the next value. You may have to seed the identity value
using the MAX(ID) + 1 in the table.
You can pull back the newly inserted identity value into the application
using the SCOPE_IDENTITY() function.
David Gugick
Imceda Software
www.imceda.com|||David Gugick wrote:
> David C wrote:
> Change the column to an IDENTITY value. SQL Server automates the
> generation of the next value. You may have to seed the identity value
> using the MAX(ID) + 1 in the table.
Or by using DBCC CHECKIDENT, which should do the same job.