Backing Up System Databases...

I am rather new to the area of performing DBA type activities (so don't be
too hard on me).
When I create a maintenance plan, which of the system databases should I
choose to back up (if it is not necessary or recommended to back all of them
up)?
Thank you,
JasonBoth master and msdb should be done daily. You could do model every once in
a while. Generally, it doesn't change - unless you choose to add an object
that you want to appear in all future DB's.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Jason Richmeier" <JasonRichmeier@.discussions.microsoft.com> wrote in
message news:179C142F-CEBC-4E16-870D-FCC20881E5F3@.microsoft.com...
I am rather new to the area of performing DBA type activities (so don't be
too hard on me).
When I create a maintenance plan, which of the system databases should I
choose to back up (if it is not necessary or recommended to back all of them
up)?
Thank you,
Jason|||Or just include model for daily backup anyway since it's so tiny.
Linchi
"Tom Moreau" wrote:

> Both master and msdb should be done daily. You could do model every once
in
> a while. Generally, it doesn't change - unless you choose to add an objec
t
> that you want to appear in all future DB's.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Jason Richmeier" <JasonRichmeier@.discussions.microsoft.com> wrote in
> message news:179C142F-CEBC-4E16-870D-FCC20881E5F3@.microsoft.com...
> I am rather new to the area of performing DBA type activities (so don't be
> too hard on me).
> When I create a maintenance plan, which of the system databases should I
> choose to back up (if it is not necessary or recommended to back all of th
em
> up)?
> Thank you,
> Jason
>

Backing Up System Databases...

I am rather new to the area of performing DBA type activities (so don't be
too hard on me).
When I create a maintenance plan, which of the system databases should I
choose to back up (if it is not necessary or recommended to back all of them
up)?
Thank you,
JasonBoth master and msdb should be done daily. You could do model every once in
a while. Generally, it doesn't change - unless you choose to add an object
that you want to appear in all future DB's.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Jason Richmeier" <JasonRichmeier@.discussions.microsoft.com> wrote in
message news:179C142F-CEBC-4E16-870D-FCC20881E5F3@.microsoft.com...
I am rather new to the area of performing DBA type activities (so don't be
too hard on me).
When I create a maintenance plan, which of the system databases should I
choose to back up (if it is not necessary or recommended to back all of them
up)?
Thank you,
Jason|||Or just include model for daily backup anyway since it's so tiny.
Linchi
"Tom Moreau" wrote:
> Both master and msdb should be done daily. You could do model every once in
> a while. Generally, it doesn't change - unless you choose to add an object
> that you want to appear in all future DB's.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Jason Richmeier" <JasonRichmeier@.discussions.microsoft.com> wrote in
> message news:179C142F-CEBC-4E16-870D-FCC20881E5F3@.microsoft.com...
> I am rather new to the area of performing DBA type activities (so don't be
> too hard on me).
> When I create a maintenance plan, which of the system databases should I
> choose to back up (if it is not necessary or recommended to back all of them
> up)?
> Thank you,
> Jason
>sql

Backing up SQL Server MSDE and Express

We are attempting to backup using a maintenance plan a database to a network share.

The backup job works within a domain but fails within a workgroup?

Any thoughts?

thanks

hi Larry,

Larry Sitka wrote:

We are attempting to backup using a maintenance plan a database to a network share.

The backup job works within a domain but fails within a workgroup?

under which account does the maintenance plans run?

does the account running SQL Server have enought permissions on the remote network share? or the account on which the maintenance plan runs?

regards

Backing up databases in a mirror configuration

I have a user database maintance plan with subplans for full and
transaction log backups deployed to both the Principal and Mirror
servers. Each subplan has its own schedule.
Obviously, the principal server executes a successfully backup, while
the mirror copy fails. This generates errors that are picked up by
MOM and also from the jobs themselves.
In order to eliminate these errors, I emplyed a method that prevents
the jobs from running if the server is hosting the mirror copy (note
that all the databases on the server are mirrored):
1) Remove the schedule from the job that runs the SSIS package - job
name 'User Databases.Full Database Backup'
2) Create a new job called 'User Databases.Full Database Backup Status
Check' that checks for the read/write and online status of the
databases and executes the job in step one if they are online
3) Add a schedule to the job 'User Databases.Full Database Backup
Status Check' with the same details as that removed in step 1
I have a big problem with this approach: if any of the databases are
the mirror, then the entire backup subplan is not run - even if some
of the databases are the principal on the same server - it's an all or
nothing approach since the databases checked in step 2 may not
necessarily match the list of databases defined in the subplan.
Clearly, I'd like the checking step to only be affected by the
databases defined in the subplan, but I cannot think of a way of
interrogating the subplan details using SQL code to determine this.
Ideally, I would like to edit the maintenance plan's list of databases
to only include databases that are the principal on that server and to
do it each time the job is executed - maybe using the two job approach
as described above.
I would like some advice on a way to do this or whether I am barking
up the wrong tree and should try a different angle - perhaps defining
my own backup process (I really want to avoid doing this). Clearly,
if the maintenance plan could detect the fact that the database is a
mirror copy and just skip it, the whole use goes away, but it does
not.
Thanks> I would like some advice on a way to do this or whether I am barking
> up the wrong tree and should try a different angle - perhaps defining
> my own backup process (I really want to avoid doing this).
This is what you would have to do. Maint plans are for simpler installations
, and conditionally
handling a mirrored database *inside* a backup task is not available. Consid
er wishing this at
connect.microsoft.com.
Another options is to have one backup task per database, and have a TSQL tas
k before each which
check the mirroring catalog views and if a mirror, moves to the next databas
e instead. A bit messy,
but should be doable.
Having your own scripts for performing a backup isn't difficult, though. You
can still use the maint
plan to remove old backup files. (Google for how to generate the backup comm
and with a time stamp in
the file name and you will find examples...)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Viracochas" <warmtea@.hotmail.com> wrote in message
news:1179742488.051578.275990@.b40g2000prd.googlegroups.com...
>I have a user database maintance plan with subplans for full and
> transaction log backups deployed to both the Principal and Mirror
> servers. Each subplan has its own schedule.
> Obviously, the principal server executes a successfully backup, while
> the mirror copy fails. This generates errors that are picked up by
> MOM and also from the jobs themselves.
> In order to eliminate these errors, I emplyed a method that prevents
> the jobs from running if the server is hosting the mirror copy (note
> that all the databases on the server are mirrored):
> 1) Remove the schedule from the job that runs the SSIS package - job
> name 'User Databases.Full Database Backup'
> 2) Create a new job called 'User Databases.Full Database Backup Status
> Check' that checks for the read/write and online status of the
> databases and executes the job in step one if they are online
> 3) Add a schedule to the job 'User Databases.Full Database Backup
> Status Check' with the same details as that removed in step 1
> I have a big problem with this approach: if any of the databases are
> the mirror, then the entire backup subplan is not run - even if some
> of the databases are the principal on the same server - it's an all or
> nothing approach since the databases checked in step 2 may not
> necessarily match the list of databases defined in the subplan.
> Clearly, I'd like the checking step to only be affected by the
> databases defined in the subplan, but I cannot think of a way of
> interrogating the subplan details using SQL code to determine this.
> Ideally, I would like to edit the maintenance plan's list of databases
> to only include databases that are the principal on that server and to
> do it each time the job is executed - maybe using the two job approach
> as described above.
> I would like some advice on a way to do this or whether I am barking
> up the wrong tree and should try a different angle - perhaps defining
> my own backup process (I really want to avoid doing this). Clearly,
> if the maintenance plan could detect the fact that the database is a
> mirror copy and just skip it, the whole use goes away, but it does
> not.
> Thanks
>|||On 21 May, 13:43, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> This is what you would have to do. Maint plans are for simpler installatio
ns, and conditionally
> handling a mirrored database *inside* a backup task is not available. Cons
ider wishing this at
> connect.microsoft.com.
> Another options is to have one backup task per database, and have a TSQL t
ask before each which
> check the mirroring catalog views and if a mirror, moves to the next datab
ase instead. A bit messy,
> but should be doable.
> Having your own scripts for performing a backup isn't difficult, though. Y
ou can still use the maint
> plan to remove old backup files. (Google for how to generate the backup co
mmand with a time stamp in
> the file name and you will find examples...)
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asph
ttp://sqlblog.com/blogs/tibor_karaszi
> "Viracochas" <warm...@.hotmail.com> wrote in message
> news:1179742488.051578.275990@.b40g2000prd.googlegroups.com...
Thanks for the feedback. I feared there was no easy way, though it
won't be too difficult to set it up.

Backing up databases in a mirror configuration

I have a user database maintance plan with subplans for full and
transaction log backups deployed to both the Principal and Mirror
servers. Each subplan has its own schedule.
Obviously, the principal server executes a successfully backup, while
the mirror copy fails. This generates errors that are picked up by
MOM and also from the jobs themselves.
In order to eliminate these errors, I emplyed a method that prevents
the jobs from running if the server is hosting the mirror copy (note
that all the databases on the server are mirrored):
1) Remove the schedule from the job that runs the SSIS package - job
name 'User Databases.Full Database Backup'
2) Create a new job called 'User Databases.Full Database Backup Status
Check' that checks for the read/write and online status of the
databases and executes the job in step one if they are online
3) Add a schedule to the job 'User Databases.Full Database Backup
Status Check' with the same details as that removed in step 1
I have a big problem with this approach: if any of the databases are
the mirror, then the entire backup subplan is not run - even if some
of the databases are the principal on the same server - it's an all or
nothing approach since the databases checked in step 2 may not
necessarily match the list of databases defined in the subplan.
Clearly, I'd like the checking step to only be affected by the
databases defined in the subplan, but I cannot think of a way of
interrogating the subplan details using SQL code to determine this.
Ideally, I would like to edit the maintenance plan's list of databases
to only include databases that are the principal on that server and to
do it each time the job is executed - maybe using the two job approach
as described above.
I would like some advice on a way to do this or whether I am barking
up the wrong tree and should try a different angle - perhaps defining
my own backup process (I really want to avoid doing this). Clearly,
if the maintenance plan could detect the fact that the database is a
mirror copy and just skip it, the whole use goes away, but it does
not.
Thanks> I would like some advice on a way to do this or whether I am barking
> up the wrong tree and should try a different angle - perhaps defining
> my own backup process (I really want to avoid doing this).
This is what you would have to do. Maint plans are for simpler installations, and conditionally
handling a mirrored database *inside* a backup task is not available. Consider wishing this at
connect.microsoft.com.
Another options is to have one backup task per database, and have a TSQL task before each which
check the mirroring catalog views and if a mirror, moves to the next database instead. A bit messy,
but should be doable.
Having your own scripts for performing a backup isn't difficult, though. You can still use the maint
plan to remove old backup files. (Google for how to generate the backup command with a time stamp in
the file name and you will find examples...)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Viracochas" <warmtea@.hotmail.com> wrote in message
news:1179742488.051578.275990@.b40g2000prd.googlegroups.com...
>I have a user database maintance plan with subplans for full and
> transaction log backups deployed to both the Principal and Mirror
> servers. Each subplan has its own schedule.
> Obviously, the principal server executes a successfully backup, while
> the mirror copy fails. This generates errors that are picked up by
> MOM and also from the jobs themselves.
> In order to eliminate these errors, I emplyed a method that prevents
> the jobs from running if the server is hosting the mirror copy (note
> that all the databases on the server are mirrored):
> 1) Remove the schedule from the job that runs the SSIS package - job
> name 'User Databases.Full Database Backup'
> 2) Create a new job called 'User Databases.Full Database Backup Status
> Check' that checks for the read/write and online status of the
> databases and executes the job in step one if they are online
> 3) Add a schedule to the job 'User Databases.Full Database Backup
> Status Check' with the same details as that removed in step 1
> I have a big problem with this approach: if any of the databases are
> the mirror, then the entire backup subplan is not run - even if some
> of the databases are the principal on the same server - it's an all or
> nothing approach since the databases checked in step 2 may not
> necessarily match the list of databases defined in the subplan.
> Clearly, I'd like the checking step to only be affected by the
> databases defined in the subplan, but I cannot think of a way of
> interrogating the subplan details using SQL code to determine this.
> Ideally, I would like to edit the maintenance plan's list of databases
> to only include databases that are the principal on that server and to
> do it each time the job is executed - maybe using the two job approach
> as described above.
> I would like some advice on a way to do this or whether I am barking
> up the wrong tree and should try a different angle - perhaps defining
> my own backup process (I really want to avoid doing this). Clearly,
> if the maintenance plan could detect the fact that the database is a
> mirror copy and just skip it, the whole use goes away, but it does
> not.
> Thanks
>|||On 21 May, 13:43, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> > I would like some advice on a way to do this or whether I am barking
> > up the wrong tree and should try a different angle - perhaps defining
> > my own backup process (I really want to avoid doing this).
> This is what you would have to do. Maint plans are for simpler installations, and conditionally
> handling a mirrored database *inside* a backup task is not available. Consider wishing this at
> connect.microsoft.com.
> Another options is to have one backup task per database, and have a TSQL task before each which
> check the mirroring catalog views and if a mirror, moves to the next database instead. A bit messy,
> but should be doable.
> Having your own scripts for performing a backup isn't difficult, though. You can still use the maint
> plan to remove old backup files. (Google for how to generate the backup command with a time stamp in
> the file name and you will find examples...)
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> "Viracochas" <warm...@.hotmail.com> wrote in message
> news:1179742488.051578.275990@.b40g2000prd.googlegroups.com...
Thanks for the feedback. I feared there was no easy way, though it
won't be too difficult to set it up.

Backing Up A MDF

I plan on using a database file for my site, how would I back it up if I were to make changes to my site?

From the sql server management studio, you can choose a backup option from the menus. Right-click on the database entry, choose Tasks, then Backup.

You can restore it using the Restore command.

Alternatively, you can Detach the database file, make a copy using windows, then reattach.

|||

Can this be done on my site, if I use ftp, can I download the file?

|||

Are you using a hosting provider? If you are, you will probably have to use their backup utility.

Once you use it, it will build a file that you should be able to ftp.

|||

Using SQL DMO for automated Database Backup and Restore.

Or,

This code may help you:

public static void BackupDatabase() {string sConnect = Properties.Settings.Default.BackupConnectionString;string dbName;using (SqlConnection cnn =new SqlConnection(sConnect)) { cnn.Open(); dbName = cnn.Database.ToString(); ServerConnection sc =new ServerConnection(cnn); Server sv =new Server(sc);// Check that I'm connected to the user instance Console.WriteLine(sv.InstanceName.ToString());// Create backup device item for the backup BackupDeviceItem bdi =new BackupDeviceItem(@."C:\AppDataBackup\SampleBackup.bak", DeviceType.File);// Create the backup informaton Backup bk =new Backup(); bk.Devices.Add(bdi); bk.Action = BackupActionType.Database; bk.BackupSetDescription ="SQL Express is a great product!"; bk.BackupSetName ="SampleBackupSet"; bk.Database = dbName; bk.ExpirationDate =new DateTime(2007, 5, 1); bk.LogTruncation = BackupTruncateLogType.Truncate;// Run the backup bk.SqlBackup(sv); Console.WriteLine("Your backup is complete."); } }

|||

Sql server comes with the capability to do a batch job, so you can just tell it to run the backup program without having to write a program...

back up MP

I set up a Maintenance Plan through the wizard that backs up Tlogs every hour and does a full back up at 1:00 am. After the full back up the T-log back ups do not resume. I go in in the morning and reset it to start at the top of the next hour then it p
roceeds fine. How can I fix that?
Thanks
Niles,
Try breaking up the existing plan into 2 separate plans. 1 to do the
database backup and the other to do the tlog backups.
"Niles" <Niles@.discussions.microsoft.com> wrote in message
news:D6E8D0B6-3A2C-464D-A4CC-CE18E7476891@.microsoft.com...
> I set up a Maintenance Plan through the wizard that backs up Tlogs every
hour and does a full back up at 1:00 am. After the full back up the T-log
back ups do not resume. I go in in the morning and reset it to start at the
top of the next hour then it proceeds fine. How can I fix that?
> Thanks

back up MP

I set up a Maintenance Plan through the wizard that backs up Tlogs every hou
r and does a full back up at 1:00 am. After the full back up the T-log back
ups do not resume. I go in in the morning and reset it to start at the top
of the next hour then it p
roceeds fine. How can I fix that?
ThanksNiles,
Try breaking up the existing plan into 2 separate plans. 1 to do the
database backup and the other to do the tlog backups.
"Niles" <Niles@.discussions.microsoft.com> wrote in message
news:D6E8D0B6-3A2C-464D-A4CC-CE18E7476891@.microsoft.com...
> I set up a Maintenance Plan through the wizard that backs up Tlogs every
hour and does a full back up at 1:00 am. After the full back up the T-log
back ups do not resume. I go in in the morning and reset it to start at the
top of the next hour then it proceeds fine. How can I fix that?
> Thanks

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 reusing query plan..

Hi,

I'm trying to test some queries in SQL analyser without reusing the query plan (already cached). I know that there is a way to avoid that but I don't remember right now. Another option would be to restart MS SQL service but I don't want to do that.
Any thoughts...?

Thanks,

S.How about...

DBCC FREEPROCCACHE|||Does it also clear the Query cache ??|||Originally posted by Enigma
Does it also clear the Query cache ??

Not sure, never had the need..

Remarks
Use DBCC FREEPROCCACHE to clear the procedure cache. Freeing the procedure cache would cause, for example, an ad-hoc SQL statement to be recompiled rather than reused from the cache.|||Thanks guys,

Well, I hoped to find a way to clear the procedure cache and the buffer pages as well, but DBCC FREEPROCCACHE does only the first part of it.
Actually I was trying to optimize a stored proc. and examine the impact directly using SQL Analyser. When I execute the SP for the first time, it takes around 3 sec. to complete. Once the data pages buffered and execution plan reused, it takes around 1 sec. (FREEPROCCACHE doesn't alter it much)
So it seems that FREEPROCCACHE has the same effect as CREATE PROCEDURE WITH RECOMPILE but nothing more.
Thanks anyway,

S.|||Look up Buffer manage object in BOL...

Haven't seen a way to do what you're looking for...

but hey ...1 second ain't bad....

1 mississippi...

done|||Originally posted by dbadelphes
Thanks guys,

Well, I hoped to find a way to clear the procedure cache and the buffer pages as well, but DBCC FREEPROCCACHE does only the first part of it.
Actually I was trying to optimize a stored proc. and examine the impact directly using SQL Analyser. When I execute the SP for the first time, it takes around 3 sec. to complete. Once the data pages buffered and execution plan reused, it takes around 1 sec. (FREEPROCCACHE doesn't alter it much)
So it seems that FREEPROCCACHE has the same effect as CREATE PROCEDURE WITH RECOMPILE but nothing more.
Thanks anyway,

S.

Try DBCC DROPCLEANBUFFERS. This removes any cached data. I use this and dbcc freeproccache when doing any comparison testing.|||That's what I was looking for.
Thanks a lot...(go Leafs go.. :) )

S.

Originally posted by homer37
Try DBCC DROPCLEANBUFFERS. This removes any cached data. I use this and dbcc freeproccache when doing any comparison testing.

AutoNumber Column and ...

Hi *.*
I plan to crate a database and want to know if it's good to use autonumber
columns as my primary key or I should generate the numbers myself?
Also, If I want to replicate the DB, is it ok to use autonumber columns?
At last, Is there any way I can manage the autonumber start number? Means
autonumbering starts at 100 or 300000 instead of 0?
Thanks for your help,
HOA
Hoa wrote:

> Hi *.*
> I plan to crate a database and want to know if it's good to use autonumber
> columns as my primary key or I should generate the numbers myself?
> Also, If I want to replicate the DB, is it ok to use autonumber columns?
> At last, Is there any way I can manage the autonumber start number? Means
> autonumbering starts at 100 or 300000 instead of 0?
> Thanks for your help,
> HOA
>
I suggest you take a step back and spend a month researching all this
along with getting your database professionally designed. Every one of
those questions are so "depends" in nature that the very fact that you
asked them tells me you don't have a good enough grasp of the situation
to appreciate the answers.
But, quickly:
1) Depends.
2) Sorta. Depends.
3) Yes.
There is an entire section in the Books Online that is called
"Replication Data Considerations" with a topic "Using IDENTITY Values".
Have you not even bothered to read that section prior to posting your
very open ended question?
Zach
|||Identity columns are fine for PK... as long as you plan on keeping your identity values unique. PK values MUST be unique; Identity does not explicitly enforce uniqueness. Identity columns are good for PKs in the fact that they are INTEGER datatype. This d
atatype is small, so it works well w/ indexes (PK defaults to unique, clustered)
Identity columns may need to be set to 'NOT FOR REPLICATION' in certain situations. See Books Online: Replication topology & NOT FOR REPLICATION for more info.
Yes, you can start an Identity value at a number greater than 0 or 1... you declare it with the IDENTITY(10,5) clause (10 being the 'seed' value, 5 being the increment.
"Hoa" wrote:

> Hi *.*
> I plan to crate a database and want to know if it's good to use autonumber
> columns as my primary key or I should generate the numbers myself?
> Also, If I want to replicate the DB, is it ok to use autonumber columns?
> At last, Is there any way I can manage the autonumber start number? Means
> autonumbering starts at 100 or 300000 instead of 0?
> Thanks for your help,
> HOA
>
>
|||Oh boy. Books Online is your friend, don't let it be a stranger. These
issues are all covered in the documentation.
Your choice of a primary key depends on more factors than "it's good"... see
for a bit of my own opinion.
Using an IDENTITY in a replication scenario is okay, depending on what type
of replication. It will not work in merge replication, for example, since
IDENTITY can't guarantee uniqueness from two different database sources.
As for the identity, yes see the CREATE TABLE and DBCC CHECKIDENT tables in
Books Online. If the table hasn't been created:
CREATE TABLE Splunge
(sid INT IDENTITY(30000, 1))
INSERT Splunge DEFAULT VALUES
INSERT Splunge DEFAULT VALUES
SELECT * FROM Splunge
GO
DROP TABLE Splunge
Once again, please become familiar with Books Online. All of the questions
are answered, and then some, in the documentation you already have available
to you.
http://www.aspfaq.com/
(Reverse address to reply.)
"Hoa" <newpoorguy@.yahoo.com> wrote in message
news:eMTZ#DkeEHA.2804@.TK2MSFTNGP11.phx.gbl...
> Hi *.*
> I plan to crate a database and want to know if it's good to use
autonumber
> columns as my primary key or I should generate the numbers myself?
> Also, If I want to replicate the DB, is it ok to use autonumber columns?
> At last, Is there any way I can manage the autonumber start number? Means
> autonumbering starts at 100 or 300000 instead of 0?
> Thanks for your help,
> HOA
>
|||Using identity columns in replication is supported and as other posters have
indicated this is covered in BOL. The exact use of identities in replication
depends on hte type of replication you have selected, but if for example you
were to do a merge publication, another tab appears on the article
properties which allows you to have SQL Server assign ranges to publisher
and subscriber which ensures there is no overlap in assigned numbers - this
is enforced by check constraints. You can alternatively manage the identity
ranges yourself and Michael Hotek shows how to do this on his site
(http://www.mssqlserver.com/replicati...h_identity.asp).
HTH,
Paul Ibison