Autonumber field: databases

Showing posts with label databases. Show all posts

Thursday, March 29, 2012

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 Sys Databases

I currently back up the Master and MSDB sys databases, why do some people say
to back up Model as well? I though this was a standard non enhanceable
database. What are the reprocussions of not backing this database up?
Also what is the best way to back up transaction logs? I am doing an hourly
back up of a production database that has a lot changes through out the day.
I do a full back up once each night. The tran log seems to grow bigger than
the data file when I choose the append option for the log. Can I create
seperate tran log back ups or should I choose overwrite instead of append?
Thanks for your help
-ChrisHi Chris
Model is not non-enhanceable. Model is used as the template whenever a new
database is created, so you can put users, stored procedures, permissions,
datatypes, etc, into model, as well as setting the database properties of
model with ALTER DATABASE, and every new database will inherit those objects
and settings.
The tempdb database is built from model every time your SQL Server starts,
so if you want objects, users, user defined datatypes or special permissions
in tempdb, put them in model.
And then, if you do put any of these objects in model, you can backup model
to make it faster to recreate it in case of system failure.
In the future, I would suggest two different messages for different
questions like you have here. It will make it much easier for everybody, you
and anyone trying to help, to keep track of the discussion.
Do you really mean that your tran log is bigger than the data file, or that
the tran log backup file is bigger than the database backup file? Or
something else?
For a heavily updated database and a day's worth of log backups, it could
happen. But without knowing the size of the db, the rate of changes, the
types of changes and the size of the rows changing, the recovery model of
the database, and probably a few other things, there is no way to tell if it
is TOO big.
To restore from backups to the point of a failure, you need ALL the log
backups since the last full backup. If you use the overwrite option when
backing up the log, you will only have the most recent log back, not the
whole set, and you will be unable to recover anything after the last full
backup. Please read about transaction log backups in the Books Online.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:01280324-7A0E-4EAD-B9A7-567FD9480FFE@.microsoft.com...
>I currently back up the Master and MSDB sys databases, why do some people
>say
> to back up Model as well? I though this was a standard non enhanceable
> database. What are the reprocussions of not backing this database up?
> Also what is the best way to back up transaction logs? I am doing an
> hourly
> back up of a production database that has a lot changes through out the
> day.
> I do a full back up once each night. The tran log seems to grow bigger
> than
> the data file when I choose the append option for the log. Can I create
> seperate tran log back ups or should I choose overwrite instead of append?
> Thanks for your help
> -Chris

Backing Up Sys Databases

I currently back up the Master and MSDB sys databases, why do some people sa
y
to back up Model as well? I though this was a standard non enhanceable
database. What are the reprocussions of not backing this database up?
Also what is the best way to back up transaction logs? I am doing an hourly
back up of a production database that has a lot changes through out the day.
I do a full back up once each night. The tran log seems to grow bigger than
the data file when I choose the append option for the log. Can I create
seperate tran log back ups or should I choose overwrite instead of append?
Thanks for your help
-ChrisHi Chris
Model is not non-enhanceable. Model is used as the template whenever a new
database is created, so you can put users, stored procedures, permissions,
datatypes, etc, into model, as well as setting the database properties of
model with ALTER DATABASE, and every new database will inherit those objects
and settings.
The tempdb database is built from model every time your SQL Server starts,
so if you want objects, users, user defined datatypes or special permissions
in tempdb, put them in model.
And then, if you do put any of these objects in model, you can backup model
to make it faster to recreate it in case of system failure.
In the future, I would suggest two different messages for different
questions like you have here. It will make it much easier for everybody, you
and anyone trying to help, to keep track of the discussion.
Do you really mean that your tran log is bigger than the data file, or that
the tran log backup file is bigger than the database backup file? Or
something else?
For a heavily updated database and a day's worth of log backups, it could
happen. But without knowing the size of the db, the rate of changes, the
types of changes and the size of the rows changing, the recovery model of
the database, and probably a few other things, there is no way to tell if it
is TOO big.
To restore from backups to the point of a failure, you need ALL the log
backups since the last full backup. If you use the overwrite option when
backing up the log, you will only have the most recent log back, not the
whole set, and you will be unable to recover anything after the last full
backup. Please read about transaction log backups in the Books Online.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:01280324-7A0E-4EAD-B9A7-567FD9480FFE@.microsoft.com...
>I currently back up the Master and MSDB sys databases, why do some people
>say
> to back up Model as well? I though this was a standard non enhanceable
> database. What are the reprocussions of not backing this database up?
> Also what is the best way to back up transaction logs? I am doing an
> hourly
> back up of a production database that has a lot changes through out the
> day.
> I do a full back up once each night. The tran log seems to grow bigger
> than
> the data file when I choose the append option for the log. Can I create
> seperate tran log back ups or should I choose overwrite instead of append?
> Thanks for your help
> -Chris

Backing up SSRS objects

I'm using sqlserver 2005 developer edition.
Unlike databases, there appears to be no way, in Management Studio, to back
up ReportSever.
How is it done?
--
Regards,
Gary BlakelyHi Gary,
Thank you for your posting!
You could backup the ReportServer database as you backup other databases.
In the databases, right-click ReportServer and then click Back up. Then you
could backup the Reporty Server Database.
Here is an article for your reference:
Back Up Database (General Page)
http://msdn2.microsoft.com/en-us/library/ms183383(d=ide).aspx
Hope this will be helpful!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Backing up SQL to a different server

I am trying to send backups of SQL databases and
transaction logs to a different server. I thought it
would work if the UNC name and path were used. So, first
of all, I don't if this is correct. I think it's trying
to do the backup, but the error I am getting
is "BackupDeviceFile::Backup device "\\erlfs1
\sqlbackups\..." failed to create. Operating system error
=5 (Access is denied.)" I have given the Everyone group
full control, and I still get the same error. Does anyone
have recommendations for sending backups to a different
machine?Make sure you are running SQL under a domain-level account and that the
account has permissions to create and write files on the remote location.
Logging in to the console of the SQL server as the service account and
copying files is a good way to test remote file access.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"teresa" <anonymous@.discussions.microsoft.com> wrote in message
news:1454601c3c33c$58da9540$a601280a@.phx.gbl...
> I am trying to send backups of SQL databases and
> transaction logs to a different server. I thought it
> would work if the UNC name and path were used. So, first
> of all, I don't if this is correct. I think it's trying
> to do the backup, but the error I am getting
> is "BackupDeviceFile::Backup device "\\erlfs1
> \sqlbackups\..." failed to create. Operating system error
> =5 (Access is denied.)" I have given the Everyone group
> full control, and I still get the same error. Does anyone
> have recommendations for sending backups to a different
> machine?|||The SQL server is logged in under the domain admin account
and the domain admin has full control on the remote folder
that SQL is trying to back up to. Also, SQL Server Agent
service is started under the domain admin account and the
particular job is owned by the domain admin. I was able
to copy a file from the console to the backup server.
Looking at the event viewer, after a job has failed, one
error says "operating system eror = 5 (access is denied),"
another one says that the job failed and that it was
invoked by user 'sa'. I mention this because I don't
if 'sa' trying to invoke the job is the problem.
>--Original Message--
>Make sure you are running SQL under a domain-level
account and that the
>account has permissions to create and write files on the
remote location.
>Logging in to the console of the SQL server as the
service account and
>copying files is a good way to test remote file access.
>--
>Geoff N. Hiten
>Microsoft SQL Server MVP
>Senior Database Administrator
>Careerbuilder.com
>
>"teresa" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1454601c3c33c$58da9540$a601280a@.phx.gbl...
>> I am trying to send backups of SQL databases and
>> transaction logs to a different server. I thought it
>> would work if the UNC name and path were used. So,
first
>> of all, I don't if this is correct. I think it's trying
>> to do the backup, but the error I am getting
>> is "BackupDeviceFile::Backup device "\\erlfs1
>> \sqlbackups\..." failed to create. Operating system
error
>> =5 (Access is denied.)" I have given the Everyone group
>> full control, and I still get the same error. Does
anyone
>> have recommendations for sending backups to a different
>> machine?
>
>.
>|||Ping the second server from the first using UNC (ping
Servername). If you can not ping, use the IP address (To
find IP...Ipconfig).
Second Check the the 'Domain Admins' group is added
to 'Administrators' group of the second server.
>--Original Message--
>The SQL server is logged in under the domain admin
account
>and the domain admin has full control on the remote
folder
>that SQL is trying to back up to. Also, SQL Server Agent
>service is started under the domain admin account and the
>particular job is owned by the domain admin. I was able
>to copy a file from the console to the backup server.
>Looking at the event viewer, after a job has failed, one
>error says "operating system eror = 5 (access is
denied),"
>another one says that the job failed and that it was
>invoked by user 'sa'. I mention this because I don't
>if 'sa' trying to invoke the job is the problem.
>>--Original Message--
>>Make sure you are running SQL under a domain-level
>account and that the
>>account has permissions to create and write files on the
>remote location.
>>Logging in to the console of the SQL server as the
>service account and
>>copying files is a good way to test remote file access.
>>--
>>Geoff N. Hiten
>>Microsoft SQL Server MVP
>>Senior Database Administrator
>>Careerbuilder.com
>>
>>"teresa" <anonymous@.discussions.microsoft.com> wrote in
>message
>>news:1454601c3c33c$58da9540$a601280a@.phx.gbl...
>> I am trying to send backups of SQL databases and
>> transaction logs to a different server. I thought it
>> would work if the UNC name and path were used. So,
>first
>> of all, I don't if this is correct. I think it's
trying
>> to do the backup, but the error I am getting
>> is "BackupDeviceFile::Backup device "\\erlfs1
>> \sqlbackups\..." failed to create. Operating system
>error
>> =5 (Access is denied.)" I have given the Everyone
group
>> full control, and I still get the same error. Does
>anyone
>> have recommendations for sending backups to a different
>> machine?
>>
>>.
>.
>sql

Backing up SQL Server: VDI or SQLDMO?

I am in the process of writing a backup software package, and I posted a
question here a while ago about how to best backup SQL Server databases, and
the preferred approach for implementing backup from a program written in C++
.
Louis Davidson recommended SQLDMO, but since then I've found documentation
for the "Virtual Device Interface (VDI) for Backup".
So now I'm wondering what the pros and cons are of these two approaches. To
judge by the documentation, it seems that VDI would be the preferred approac
h
since it provides the "the highest online backup throughput with minimal
degradation to the transaction workload".
Thanks,
PeterVDI and DMO are two completely different and are targeting two different
things
VDI is a virtual device interface for ISV's to develop SQL Server Backup
software so you can backup you SQL Server database and tlogs to for example
compressed files, remote locations by backing up over a pipe etc.
DMO is a management interface, abstracting programmers to learn or issue
TSQL commands directly.
Are you writing an application that tells SQL to perform a BACKUP or are you
planning on implementing a backup device?
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"jpstewart" <jpstewart@.discussions.microsoft.com> wrote in message
news:44B5C821-78C9-4C25-8951-9E4892825397@.microsoft.com...
>I am in the process of writing a backup software package, and I posted a
> question here a while ago about how to best backup SQL Server databases,
> and
> the preferred approach for implementing backup from a program written in
> C++.
> Louis Davidson recommended SQLDMO, but since then I've found documentation
> for the "Virtual Device Interface (VDI) for Backup".
> So now I'm wondering what the pros and cons are of these two approaches.
> To
> judge by the documentation, it seems that VDI would be the preferred
> approach
> since it provides the "the highest online backup throughput with minimal
> degradation to the transaction workload".
> Thanks,
> Peter
>|||Thanks for your reply. I'm writing an application that should be able to bac
k
up SQL server in the most convenient, automatic and efficient way possible.
In so far as the backup device resides on the internet and uses our
proprietary compressed and encrypted file format, I guess you could also say
that it's an implementation of a backup device.
You wouldn't happen to know the answer to my other question about SQL-DMO
and ATL style smart pointers?
Thanks,
Peter
"Gert E.R. Drapers" wrote:

> VDI and DMO are two completely different and are targeting two different
> things
> VDI is a virtual device interface for ISV's to develop SQL Server Backup
> software so you can backup you SQL Server database and tlogs to for exampl
e
> compressed files, remote locations by backing up over a pipe etc.
> DMO is a management interface, abstracting programmers to learn or issue
> TSQL commands directly.
> Are you writing an application that tells SQL to perform a BACKUP or are y
ou
> planning on implementing a backup device?
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> You assume all risk for your use.
> Copyright ? SQLDev.Net 1991-2005 All rights reserved.
> "jpstewart" <jpstewart@.discussions.microsoft.com> wrote in message
> news:44B5C821-78C9-4C25-8951-9E4892825397@.microsoft.com...
>
>|||Sounds like a VDI solution to me. I will dig up my ATL example, it is
possible, forgot how I did it though.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"jpstewart" <jpstewart@.discussions.microsoft.com> wrote in message
news:35978FC2-FB22-47DC-A3A7-0E90AC09741D@.microsoft.com...
> Thanks for your reply. I'm writing an application that should be able to
> back
> up SQL server in the most convenient, automatic and efficient way
> possible.
> In so far as the backup device resides on the internet and uses our
> proprietary compressed and encrypted file format, I guess you could also
> say
> that it's an implementation of a backup device.
> You wouldn't happen to know the answer to my other question about SQL-DMO
> and ATL style smart pointers?
> Thanks,
> Peter
> "Gert E.R. Drapers" wrote:
>|||Thanks, I would appreciate it!
-Peter
"Gert E.R. Drapers" wrote:

> Sounds like a VDI solution to me. I will dig up my ATL example, it is
> possible, forgot how I did it though.
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> You assume all risk for your use.
> Copyright ? SQLDev.Net 1991-2005 All rights reserved.
> "jpstewart" <jpstewart@.discussions.microsoft.com> wrote in message
> news:35978FC2-FB22-47DC-A3A7-0E90AC09741D@.microsoft.com...
>
>

Backing up SQL Server

Hi,
I backup my database in SQL Server. Do I need to also backup the master
database or any other databases?
Thanks,
SamYes, you need to backup master, msdb, and possibly model if it has modified
default configurations.
"Sam" <sam@.globalwebcentral.com> wrote in message
news:e0F8f%23L9EHA.2180@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I backup my database in SQL Server. Do I need to also backup the master
> database or any other databases?
> Thanks,
> Sam
>|||Just to be safe, I always do full backups of my master and msdb
databases daily. Model only needs to be backed up if you change
something in the database.
-Zach
Sam wrote:
> Hi,
> I backup my database in SQL Server. Do I need to also backup the master
> database or any other databases?
> Thanks,
> Sam
>

Tuesday, March 27, 2012

backing up SQL qhile it it running

Hello:
Is it possible to backup SQL databases whiel SQL is running?
I'm not concerned with the means of backing up. I just want to know if the
databases can be backed up whil the SQL Server Service is running.
Thanks!
childofthe1980s
You can back up SQL Server databases with the native back up while SQL
Server is running. You _can't_ backup the database files with a file backup
program while SQL Server is running. Well, technically you can, but in 99%
of the cases your backups will be unusable.
Jacco Schalkwijk
SQL Server MVP
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:0E595127-3A63-4172-80C0-EA1E746DF466@.microsoft.com...
> Hello:
> Is it possible to backup SQL databases whiel SQL is running?
> I'm not concerned with the means of backing up. I just want to know if
> the
> databases can be backed up whil the SQL Server Service is running.
> Thanks!
> childofthe1980s
|||Look at the commands BACKUP and RESTORE in books online.
En Enterprise Manager, right click on the DB and select Backup.
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/
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid > wrote
in message news:#rhbdlK3EHA.1404@.TK2MSFTNGP11.phx.gbl...
> You can back up SQL Server databases with the native back up while SQL
> Server is running. You _can't_ backup the database files with a file
backup
> program while SQL Server is running. Well, technically you can, but in 99%
> of the cases your backups will be unusable.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
> message news:0E595127-3A63-4172-80C0-EA1E746DF466@.microsoft.com...
>
|||"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid > wrote
in message news:%23rhbdlK3EHA.1404@.TK2MSFTNGP11.phx.gbl...
> You can back up SQL Server databases with the native back up while SQL
> Server is running. You _can't_ backup the database files with a file
backup
> program while SQL Server is running. Well, technically you can, but in 99%
> of the cases your backups will be unusable.
Unless you use specific "agents" designed for SQL Server.
But generally the native tools are far easier to use.
sql

backing up SQL qhile it it running

Hello:
Is it possible to backup SQL databases whiel SQL is running?
I'm not concerned with the means of backing up. I just want to know if the
databases can be backed up whil the SQL Server Service is running.
Thanks!
childofthe1980sYou can back up SQL Server databases with the native back up while SQL
Server is running. You _can't_ backup the database files with a file backup
program while SQL Server is running. Well, technically you can, but in 99%
of the cases your backups will be unusable.
--
Jacco Schalkwijk
SQL Server MVP
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:0E595127-3A63-4172-80C0-EA1E746DF466@.microsoft.com...
> Hello:
> Is it possible to backup SQL databases whiel SQL is running?
> I'm not concerned with the means of backing up. I just want to know if
> the
> databases can be backed up whil the SQL Server Service is running.
> Thanks!
> childofthe1980s|||Look at the commands BACKUP and RESTORE in books online.
En Enterprise Manager, right click on the DB and select Backup.
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/
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:#rhbdlK3EHA.1404@.TK2MSFTNGP11.phx.gbl...
> You can back up SQL Server databases with the native back up while SQL
> Server is running. You _can't_ backup the database files with a file
backup
> program while SQL Server is running. Well, technically you can, but in 99%
> of the cases your backups will be unusable.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
> message news:0E595127-3A63-4172-80C0-EA1E746DF466@.microsoft.com...
> > Hello:
> >
> > Is it possible to backup SQL databases whiel SQL is running?
> >
> > I'm not concerned with the means of backing up. I just want to know if
> > the
> > databases can be backed up whil the SQL Server Service is running.
> >
> > Thanks!
> >
> > childofthe1980s
>|||"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:%23rhbdlK3EHA.1404@.TK2MSFTNGP11.phx.gbl...
> You can back up SQL Server databases with the native back up while SQL
> Server is running. You _can't_ backup the database files with a file
backup
> program while SQL Server is running. Well, technically you can, but in 99%
> of the cases your backups will be unusable.
Unless you use specific "agents" designed for SQL Server.
But generally the native tools are far easier to use.

Backing Up SQL DBs With Retrospecct

I have SBS2003 and am using Dantz Retrospect to back up the SQL databases to
a NAS box outside of our building. I have never been sucessful doing this
because Retrospect throws an error saying that it cant read the database due
to "an unknown Windows error". I have tried talking to the folks at Dantz
and they said that they have done all they can and that I should call the SQL
tech support.
Any ideas? TIA
Scott
I don't know what Retrospect is but it is most likely trying to just backup
the sql files directly. That is never a good way to back up a database.
You need to either use the native SQL Server BACKUP command to dump the
backup file to disk (or tape if directly attached) and then you can back
that file up with your backup software. There are some 3rd party backup
tools that have a Plug-In for Sql Server that does the equivalent of a
native backup but I doubt that Retrospect has that.
Andrew J. Kelly SQL MVP
"scottdog129" <scottdog129@.discussions.microsoft.com> wrote in message
news:5D4CDEFB-3390-4AC4-B554-BB3427CE3A21@.microsoft.com...
>I have SBS2003 and am using Dantz Retrospect to back up the SQL databases
>to
> a NAS box outside of our building. I have never been sucessful doing this
> because Retrospect throws an error saying that it cant read the database
> due
> to "an unknown Windows error". I have tried talking to the folks at Dantz
> and they said that they have done all they can and that I should call the
> SQL
> tech support.
> Any ideas? TIA
> Scott
|||Retrospect does (or is supposed to do) the same thing as the Backup Database
option in EM, however, it doesnt work and I have to manually do the backup
every day.
"Andrew J. Kelly" wrote:

> I don't know what Retrospect is but it is most likely trying to just backup
> the sql files directly. That is never a good way to back up a database.
> You need to either use the native SQL Server BACKUP command to dump the
> backup file to disk (or tape if directly attached) and then you can back
> that file up with your backup software. There are some 3rd party backup
> tools that have a Plug-In for Sql Server that does the equivalent of a
> native backup but I doubt that Retrospect has that.
> --
> Andrew J. Kelly SQL MVP
>
> "scottdog129" <scottdog129@.discussions.microsoft.com> wrote in message
> news:5D4CDEFB-3390-4AC4-B554-BB3427CE3A21@.microsoft.com...
>
>
|||Well it's hard to say what this is doing. If you can do a native backup
then it must be their code. It's pretty lame that they can't help you.
Andrew J. Kelly SQL MVP
"scottdog129" <scottdog129@.discussions.microsoft.com> wrote in message
news:0A5D2BBA-12DD-4819-B4E2-6737CC59C5F7@.microsoft.com...[vbcol=seagreen]
> Retrospect does (or is supposed to do) the same thing as the Backup
> Database
> option in EM, however, it doesnt work and I have to manually do the
> backup
> every day.
> "Andrew J. Kelly" wrote:
sql

Backing up SQL databases

Backing up SQL Databases

Hello:
How do you change the scheduled database backups in SQL Server 2000?
We have a scheduled backup of a production database that is run once a day.
We need to change it to update once a week; however, no matter what we do, we
can't remove or edit the existing backup schedule.
In the SQL Server Backup dialog box, regardless of whether the Destination
is removed and/or a new one is created, or if the Schedule is changed, none
of these new settings are ever saved. Why is this? What am I missing to get
around this?
Thanks,
Troy
Check your SQL Server agent jobs.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"STroy" <STroy@.discussions.microsoft.com> wrote in message
news:4AADB0A7-E440-470F-BF1E-2B5134AB1899@.microsoft.com...
> Hello:
> How do you change the scheduled database backups in SQL Server 2000?
> We have a scheduled backup of a production database that is run once a day.
> We need to change it to update once a week; however, no matter what we do, we
> can't remove or edit the existing backup schedule.
> In the SQL Server Backup dialog box, regardless of whether the Destination
> is removed and/or a new one is created, or if the Schedule is changed, none
> of these new settings are ever saved. Why is this? What am I missing to get
> around this?
> Thanks,
> Troy
|||Hello,
Do you have enough previlages to update the job. Try login as SA and update
the job schedule using enterprise manager...
Thanks
Hari
"STroy" <STroy@.discussions.microsoft.com> wrote in message
news:4AADB0A7-E440-470F-BF1E-2B5134AB1899@.microsoft.com...
> Hello:
> How do you change the scheduled database backups in SQL Server 2000?
> We have a scheduled backup of a production database that is run once a
> day.
> We need to change it to update once a week; however, no matter what we do,
> we
> can't remove or edit the existing backup schedule.
> In the SQL Server Backup dialog box, regardless of whether the Destination
> is removed and/or a new one is created, or if the Schedule is changed,
> none
> of these new settings are ever saved. Why is this? What am I missing to
> get
> around this?
> Thanks,
> Troy
|||Hello:
I am logged in under the SA account and trying to update the job in
enterprise manager. Is there something else I should be looking at or
resetting?
Thanks,
STroy
"Hari Prasad" wrote:

> Hello,
> Do you have enough previlages to update the job. Try login as SA and update
> the job schedule using enterprise manager...
> Thanks
> Hari
> "STroy" <STroy@.discussions.microsoft.com> wrote in message
> news:4AADB0A7-E440-470F-BF1E-2B5134AB1899@.microsoft.com...
>
>

Backing up SQL Databases

Hello:
How do you change the scheduled database backups in SQL Server 2000?
We have a scheduled backup of a production database that is run once a day.
We need to change it to update once a week; however, no matter what we do, we
can't remove or edit the existing backup schedule.
In the SQL Server Backup dialog box, regardless of whether the Destination
is removed and/or a new one is created, or if the Schedule is changed, none
of these new settings are ever saved. Why is this? What am I missing to get
around this?
Thanks,
TroyCheck your SQL Server agent jobs.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"STroy" <STroy@.discussions.microsoft.com> wrote in message
news:4AADB0A7-E440-470F-BF1E-2B5134AB1899@.microsoft.com...
> Hello:
> How do you change the scheduled database backups in SQL Server 2000?
> We have a scheduled backup of a production database that is run once a day.
> We need to change it to update once a week; however, no matter what we do, we
> can't remove or edit the existing backup schedule.
> In the SQL Server Backup dialog box, regardless of whether the Destination
> is removed and/or a new one is created, or if the Schedule is changed, none
> of these new settings are ever saved. Why is this? What am I missing to get
> around this?
> Thanks,
> Troy|||Hello,
Do you have enough previlages to update the job. Try login as SA and update
the job schedule using enterprise manager...
Thanks
Hari
"STroy" <STroy@.discussions.microsoft.com> wrote in message
news:4AADB0A7-E440-470F-BF1E-2B5134AB1899@.microsoft.com...
> Hello:
> How do you change the scheduled database backups in SQL Server 2000?
> We have a scheduled backup of a production database that is run once a
> day.
> We need to change it to update once a week; however, no matter what we do,
> we
> can't remove or edit the existing backup schedule.
> In the SQL Server Backup dialog box, regardless of whether the Destination
> is removed and/or a new one is created, or if the Schedule is changed,
> none
> of these new settings are ever saved. Why is this? What am I missing to
> get
> around this?
> Thanks,
> Troy|||Hello:
I am logged in under the SA account and trying to update the job in
enterprise manager. Is there something else I should be looking at or
resetting?
Thanks,
STroy
"Hari Prasad" wrote:
> Hello,
> Do you have enough previlages to update the job. Try login as SA and update
> the job schedule using enterprise manager...
> Thanks
> Hari
> "STroy" <STroy@.discussions.microsoft.com> wrote in message
> news:4AADB0A7-E440-470F-BF1E-2B5134AB1899@.microsoft.com...
> > Hello:
> >
> > How do you change the scheduled database backups in SQL Server 2000?
> >
> > We have a scheduled backup of a production database that is run once a
> > day.
> > We need to change it to update once a week; however, no matter what we do,
> > we
> > can't remove or edit the existing backup schedule.
> >
> > In the SQL Server Backup dialog box, regardless of whether the Destination
> > is removed and/or a new one is created, or if the Schedule is changed,
> > none
> > of these new settings are ever saved. Why is this? What am I missing to
> > get
> > around this?
> >
> > Thanks,
> > Troy
>
>

Backing up SQL databases

I am backing up three databases being used for a
inventory program. I have scheduled these databases to
be backed up at night using enterprise manager. If I
needed to restore any of these databases would I have to
restore all three? I do not back up transaction logs
because they do not need to restore to a specific point
(they could restore back to the previous night when the
databases were being backed up). Also do I have to
backup the other default databases that came with the SQl
2000 server in order to restore the three databases
specific to the inventory program?> If I
> needed to restore any of these databases would I have to
> restore all three?
Technically, each database can be restored independently. However, your
inventory application might need the data in all three databases to be in
sync. It is probably safest to restore all three unless you know enough
about the app to be certain restoring a single database isn't a problem.
> Also do I have to
> backup the other default databases that came with the SQl
> 2000 server in order to restore the three databases
> specific to the inventory program?
You should backup the master and msdb system databases as part of your
backup plan. These system databases only need to be restored if they are
lost or corrupted. You don't need to restore them before your inventory
databases.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Andy" <anonymous@.discussions.microsoft.com> wrote in message
news:451701c4800c$cb3dbbc0$a301280a@.phx.gbl...
> I am backing up three databases being used for a
> inventory program. I have scheduled these databases to
> be backed up at night using enterprise manager. If I
> needed to restore any of these databases would I have to
> restore all three? I do not back up transaction logs
> because they do not need to restore to a specific point
> (they could restore back to the previous night when the
> databases were being backed up). Also do I have to
> backup the other default databases that came with the SQl
> 2000 server in order to restore the three databases
> specific to the inventory program?|||OK thanks. Most of that makes sense to me. However the
last part you said referring to the default
databases, "you don't need to restore them before the
inventory databases." Do you mean if I had to restore
the inventory databases I would not have to restore these
default databases? Also if these default databases
became corrupt like you said would they not be able to be
replaced without having a backup of them. I mean if they
are default databases do they ever change? Are they
(default databases) unique to my particular SQL server
installation and are needed inclusively to run my
inventory app?
Lastly if I backup these three databases at night at
separate times wihtin a couple of hours of each when no
one is accessing the inventory program at all would they
still be in sync if restored all at once. If not could I
schedule all three databases to be backed up at the same
time to the server hard drive(I later back the database
backup files to tape).
>--Original Message--
>> needed to restore any of these databases would I have
to
>> restore all three?
>Technically, each database can be restored
independently. However, your
>inventory application might need the data in all three
databases to be in
>sync. It is probably safest to restore all three unless
you know enough
>about the app to be certain restoring a single database
isn't a problem.
>> Also do I have to
>> backup the other default databases that came with the
SQl
>> 2000 server in order to restore the three databases
>> specific to the inventory program?
>You should backup the master and msdb system databases
as part of your
>backup plan. These system databases only need to be
restored if they are
>lost or corrupted. You don't need to restore them
before your inventory
>databases.
>--
>Hope this helps.
>Dan Guzman
>SQL Server MVP
>"Andy" <anonymous@.discussions.microsoft.com> wrote in
message
>news:451701c4800c$cb3dbbc0$a301280a@.phx.gbl...
>> I am backing up three databases being used for a
>> inventory program. I have scheduled these databases to
>> be backed up at night using enterprise manager. If I
>> needed to restore any of these databases would I have
to
>> restore all three? I do not back up transaction logs
>> because they do not need to restore to a specific point
>> (they could restore back to the previous night when the
>> databases were being backed up). Also do I have to
>> backup the other default databases that came with the
SQl
>> 2000 server in order to restore the three databases
>> specific to the inventory program?
>
>.
>|||Hi,
Answers to your queries:-
1. Do you mean if I had to restore the inventory databases I would not
have to restore these
default databases?
No need to restore the default (system) databases while restoring Inventory
databases. System database keeps tract of database, logins, backups , jobs
... information. But you need to backup and keep these datases in a safe
location, which will be helpful during a crash or corruption.
2. I mean if they are default databases do they ever change?
Yes, they will change. Especially MASTER and MSDB database.
Master database will change while you add a new database, add a new login,
change parameters ...
MSDB database will change when you add a new Job (task), operator, backup,
restore ....
SO it is necessory to restore these backup during problem sitation.
3. Are they (default databases) unique to my particular SQL server
installation and are needed inclusively to run my inventory app?
Uniqueness will be there. Because those databases store the Server name,
logins, system informations...
4. If not could I schedule all three databases to be backed up at the same
time to the server hard drive?
You could backup these databases simulateneosly or one by one. One by one
is also fine, Because changes to these database will happen only during the
above mentioned time (answer to my second question)
Thanks
Hari
MCDBA
"Andy" <anonymous@.discussions.microsoft.com> wrote in message
news:4b1f01c4801a$65fef810$a401280a@.phx.gbl...
> OK thanks. Most of that makes sense to me. However the
> last part you said referring to the default
> databases, "you don't need to restore them before the
> inventory databases." Do you mean if I had to restore
> the inventory databases I would not have to restore these
> default databases? Also if these default databases
> became corrupt like you said would they not be able to be
> replaced without having a backup of them. I mean if they
> are default databases do they ever change? Are they
> (default databases) unique to my particular SQL server
> installation and are needed inclusively to run my
> inventory app?
> Lastly if I backup these three databases at night at
> separate times wihtin a couple of hours of each when no
> one is accessing the inventory program at all would they
> still be in sync if restored all at once. If not could I
> schedule all three databases to be backed up at the same
> time to the server hard drive(I later back the database
> backup files to tape).
> >--Original Message--
> >> needed to restore any of these databases would I have
> to
> >> restore all three?
> >
> >Technically, each database can be restored
> independently. However, your
> >inventory application might need the data in all three
> databases to be in
> >sync. It is probably safest to restore all three unless
> you know enough
> >about the app to be certain restoring a single database
> isn't a problem.
> >
> >> Also do I have to
> >> backup the other default databases that came with the
> SQl
> >> 2000 server in order to restore the three databases
> >> specific to the inventory program?
> >
> >You should backup the master and msdb system databases
> as part of your
> >backup plan. These system databases only need to be
> restored if they are
> >lost or corrupted. You don't need to restore them
> before your inventory
> >databases.
> >
> >--
> >Hope this helps.
> >
> >Dan Guzman
> >SQL Server MVP
> >
> >"Andy" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:451701c4800c$cb3dbbc0$a301280a@.phx.gbl...
> >> I am backing up three databases being used for a
> >> inventory program. I have scheduled these databases to
> >> be backed up at night using enterprise manager. If I
> >> needed to restore any of these databases would I have
> to
> >> restore all three? I do not back up transaction logs
> >> because they do not need to restore to a specific point
> >> (they could restore back to the previous night when the
> >> databases were being backed up). Also do I have to
> >> backup the other default databases that came with the
> SQl
> >> 2000 server in order to restore the three databases
> >> specific to the inventory program?
> >
> >
> >.
> >|||>> Lastly if I backup these three databases at night at
>> separate times wihtin a couple of hours of each when no
>> one is accessing the inventory program at all would they
>>still be in sync if restored all at once.
If there is no activity at night, you should not have a consistency problem.
However, if there's activity at night, and if a user adds/modifies data to
database A and B, but not to C, you might hit a consistency issue since the
backups of each database will end at different times. In this case, you
will need to backup the transaction logs and restore to a point-in-time for
all 3 databases to guarantee consistency (or at least close to it, since
there's still a chance a trx is committed on database A, but not yet on B
and C at that time).
--
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Use MiniSQLBackup Lite, free!
"Andy" <anonymous@.discussions.microsoft.com> wrote in message
news:4b1f01c4801a$65fef810$a401280a@.phx.gbl...
> OK thanks. Most of that makes sense to me. However the
> last part you said referring to the default
> databases, "you don't need to restore them before the
> inventory databases." Do you mean if I had to restore
> the inventory databases I would not have to restore these
> default databases? Also if these default databases
> became corrupt like you said would they not be able to be
> replaced without having a backup of them. I mean if they
> are default databases do they ever change? Are they
> (default databases) unique to my particular SQL server
> installation and are needed inclusively to run my
> inventory app?
> Lastly if I backup these three databases at night at
> separate times wihtin a couple of hours of each when no
> one is accessing the inventory program at all would they
> still be in sync if restored all at once. If not could I
> schedule all three databases to be backed up at the same
> time to the server hard drive(I later back the database
> backup files to tape).
>|||Can I also schedule backup of nondefault databases(our
inventory program) simultaneously?
>--Original Message--
>Hi,
>Answers to your queries:-
>1. Do you mean if I had to restore the inventory
databases I would not
>have to restore these
>default databases?
>No need to restore the default (system) databases while
restoring Inventory
>databases. System database keeps tract of database,
logins, backups , jobs
>... information. But you need to backup and keep these
datases in a safe
>location, which will be helpful during a crash or
corruption.
>2. I mean if they are default databases do they ever
change?
>Yes, they will change. Especially MASTER and MSDB
database.
>Master database will change while you add a new database,
add a new login,
>change parameters ...
>MSDB database will change when you add a new Job (task),
operator, backup,
>restore ....
>SO it is necessory to restore these backup during problem
sitation.
>3. Are they (default databases) unique to my particular
SQL server
>installation and are needed inclusively to run my
inventory app?
> Uniqueness will be there. Because those databases
store the Server name,
>logins, system informations...
>
>4. If not could I schedule all three databases to be
backed up at the same
>time to the server hard drive?
> You could backup these databases simulateneosly or one
by one. One by one
>is also fine, Because changes to these database will
happen only during the
> above mentioned time (answer to my second question)
>Thanks
>Hari
>MCDBA
>
>"Andy" <anonymous@.discussions.microsoft.com> wrote in
message
>news:4b1f01c4801a$65fef810$a401280a@.phx.gbl...
>> OK thanks. Most of that makes sense to me. However the
>> last part you said referring to the default
>> databases, "you don't need to restore them before the
>> inventory databases." Do you mean if I had to restore
>> the inventory databases I would not have to restore
these
>> default databases? Also if these default databases
>> became corrupt like you said would they not be able to
be
>> replaced without having a backup of them. I mean if
they
>> are default databases do they ever change? Are they
>> (default databases) unique to my particular SQL server
>> installation and are needed inclusively to run my
>> inventory app?
>> Lastly if I backup these three databases at night at
>> separate times wihtin a couple of hours of each when no
>> one is accessing the inventory program at all would they
>> still be in sync if restored all at once. If not could
I
>> schedule all three databases to be backed up at the same
>> time to the server hard drive(I later back the database
>> backup files to tape).
>> >--Original Message--
>> >> needed to restore any of these databases would I have
>> to
>> >> restore all three?
>> >
>> >Technically, each database can be restored
>> independently. However, your
>> >inventory application might need the data in all three
>> databases to be in
>> >sync. It is probably safest to restore all three
unless
>> you know enough
>> >about the app to be certain restoring a single database
>> isn't a problem.
>> >
>> >> Also do I have to
>> >> backup the other default databases that came with the
>> SQl
>> >> 2000 server in order to restore the three databases
>> >> specific to the inventory program?
>> >
>> >You should backup the master and msdb system databases
>> as part of your
>> >backup plan. These system databases only need to be
>> restored if they are
>> >lost or corrupted. You don't need to restore them
>> before your inventory
>> >databases.
>> >
>> >--
>> >Hope this helps.
>> >
>> >Dan Guzman
>> >SQL Server MVP
>> >
>> >"Andy" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:451701c4800c$cb3dbbc0$a301280a@.phx.gbl...
>> >> I am backing up three databases being used for a
>> >> inventory program. I have scheduled these databases
to
>> >> be backed up at night using enterprise manager. If I
>> >> needed to restore any of these databases would I have
>> to
>> >> restore all three? I do not back up transaction logs
>> >> because they do not need to restore to a specific
point
>> >> (they could restore back to the previous night when
the
>> >> databases were being backed up). Also do I have to
>> >> backup the other default databases that came with the
>> SQl
>> >> 2000 server in order to restore the three databases
>> >> specific to the inventory program?
>> >
>> >
>> >.
>> >
>
>.
>sql

Backing up SQL databases

Backing up SQL Databases

Hello:
How do you change the scheduled database backups in SQL Server 2000?
We have a scheduled backup of a production database that is run once a day.
We need to change it to update once a week; however, no matter what we do, w
e
can't remove or edit the existing backup schedule.
In the SQL Server Backup dialog box, regardless of whether the Destination
is removed and/or a new one is created, or if the Schedule is changed, none
of these new settings are ever saved. Why is this? What am I missing to get
around this?
Thanks,
TroyCheck your SQL Server agent jobs.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"STroy" <STroy@.discussions.microsoft.com> wrote in message
news:4AADB0A7-E440-470F-BF1E-2B5134AB1899@.microsoft.com...
> Hello:
> How do you change the scheduled database backups in SQL Server 2000?
> We have a scheduled backup of a production database that is run once a day
.
> We need to change it to update once a week; however, no matter what we do,
we
> can't remove or edit the existing backup schedule.
> In the SQL Server Backup dialog box, regardless of whether the Destination
> is removed and/or a new one is created, or if the Schedule is changed, non
e
> of these new settings are ever saved. Why is this? What am I missing to ge
t
> around this?
> Thanks,
> Troy|||Hello,
Do you have enough previlages to update the job. Try login as SA and update
the job schedule using enterprise manager...
Thanks
Hari
"STroy" <STroy@.discussions.microsoft.com> wrote in message
news:4AADB0A7-E440-470F-BF1E-2B5134AB1899@.microsoft.com...
> Hello:
> How do you change the scheduled database backups in SQL Server 2000?
> We have a scheduled backup of a production database that is run once a
> day.
> We need to change it to update once a week; however, no matter what we do,
> we
> can't remove or edit the existing backup schedule.
> In the SQL Server Backup dialog box, regardless of whether the Destination
> is removed and/or a new one is created, or if the Schedule is changed,
> none
> of these new settings are ever saved. Why is this? What am I missing to
> get
> around this?
> Thanks,
> Troy|||Hello:
I am logged in under the SA account and trying to update the job in
enterprise manager. Is there something else I should be looking at or
resetting?
Thanks,
STroy
"Hari Prasad" wrote:

> Hello,
> Do you have enough previlages to update the job. Try login as SA and updat
e
> the job schedule using enterprise manager...
> Thanks
> Hari
> "STroy" <STroy@.discussions.microsoft.com> wrote in message
> news:4AADB0A7-E440-470F-BF1E-2B5134AB1899@.microsoft.com...
>
>

Backing up SQL 2000

Hello!'
New to the admin aspect of SQL 2000, wondering if there
are any advantages/disadvantaged of backing the databases
using the NT backup utility found in Windows 2000 server.
Is there anything that should be excluded similar to,like,
the M drive on Exchange 2000?
Thanks !
CarlosThe NT backup will only copy files. You don't want a file level backup of
sql server you want file generated from a SQL Server Backup. The file level
backup will most likely be useless if you can even get it to copy while it
is running. Take a look at the BACKUP command in BooksOnLine. It will
generate a file(s) that you can use the NT backup process to copy and not
the original sql server files.
--
Andrew J. Kelly
SQL Server MVP
"Carlos" <anonymous@.discussions.microsoft.com> wrote in message
news:742101c3e765$bef02c30$a001280a@.phx.gbl...
> Hello!'
> New to the admin aspect of SQL 2000, wondering if there
> are any advantages/disadvantaged of backing the databases
> using the NT backup utility found in Windows 2000 server.
> Is there anything that should be excluded similar to,like,
> the M drive on Exchange 2000?
> Thanks !
> Carlos|||Abdrew,
Do you mean running the backup-to-file within the SQL 2000
and then backing up those files to tape, etc?
I have done that, however, when doing that within sql
2000, an event id 208 appears. I have changed the master
db to simple recovery model and set the msdb to full
recovery model. This should have resolved issue, but it
didn't.
Thanks for your help!
Carlos
>--Original Message--
>The NT backup will only copy files. You don't want a
file level backup of
>sql server you want file generated from a SQL Server
Backup. The file level
>backup will most likely be useless if you can even get it
to copy while it
>is running. Take a look at the BACKUP command in
BooksOnLine. It will
>generate a file(s) that you can use the NT backup process
to copy and not
>the original sql server files.
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"Carlos" <anonymous@.discussions.microsoft.com> wrote in
message
>news:742101c3e765$bef02c30$a001280a@.phx.gbl...
>> Hello!'
>> New to the admin aspect of SQL 2000, wondering if there
>> are any advantages/disadvantaged of backing the
databases
>> using the NT backup utility found in Windows 2000
server.
>> Is there anything that should be excluded similar
to,like,
>> the M drive on Exchange 2000?
>> Thanks !
>> Carlos
>
>.
>|||The MSDB should be set to simple recovery as well. If you mean you get a
SQL error number 208 then you most likely typed something in wrong or
possibly have spaces in the db name. If so use [] around the names. If you
could post the exact command and error it would help.
--
Andrew J. Kelly
SQL Server MVP
"Carlos" <anonymous@.discussions.microsoft.com> wrote in message
news:70a601c3e76d$71023f10$a101280a@.phx.gbl...
> Abdrew,
> Do you mean running the backup-to-file within the SQL 2000
> and then backing up those files to tape, etc?
> I have done that, however, when doing that within sql
> 2000, an event id 208 appears. I have changed the master
> db to simple recovery model and set the msdb to full
> recovery model. This should have resolved issue, but it
> didn't.
> Thanks for your help!
> Carlos
>
> >--Original Message--
> >The NT backup will only copy files. You don't want a
> file level backup of
> >sql server you want file generated from a SQL Server
> Backup. The file level
> >backup will most likely be useless if you can even get it
> to copy while it
> >is running. Take a look at the BACKUP command in
> BooksOnLine. It will
> >generate a file(s) that you can use the NT backup process
> to copy and not
> >the original sql server files.
> >
> >--
> >
> >Andrew J. Kelly
> >SQL Server MVP
> >
> >
> >"Carlos" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:742101c3e765$bef02c30$a001280a@.phx.gbl...
> >> Hello!'
> >>
> >> New to the admin aspect of SQL 2000, wondering if there
> >> are any advantages/disadvantaged of backing the
> databases
> >> using the NT backup utility found in Windows 2000
> server.
> >>
> >> Is there anything that should be excluded similar
> to,like,
> >> the M drive on Exchange 2000?
> >>
> >> Thanks !
> >>
> >> Carlos
> >
> >
> >.
> >|||Hello!
The exact event id 208 is:
SQL Server Scheduled Job 'DB Backup Job for DB Maintenance
Plan "Nightly Maintenance Plan"(hexnumber)- Status:
Failed -invoked on date - Message: The job failed. The Job
was invoked by Schedule 4 (Schedule 1). The last step was
step 1 (Step 1)
msdb was actually set back to simple
Thanks for your advise!
>--Original Message--
>The MSDB should be set to simple recovery as well. If
you mean you get a
>SQL error number 208 then you most likely typed something
in wrong or
>possibly have spaces in the db name. If so use [] around
the names. If you
>could post the exact command and error it would help.
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"Carlos" <anonymous@.discussions.microsoft.com> wrote in
message
>news:70a601c3e76d$71023f10$a101280a@.phx.gbl...
>> Abdrew,
>> Do you mean running the backup-to-file within the SQL
2000
>> and then backing up those files to tape, etc?
>> I have done that, however, when doing that within sql
>> 2000, an event id 208 appears. I have changed the
master
>> db to simple recovery model and set the msdb to full
>> recovery model. This should have resolved issue, but it
>> didn't.
>> Thanks for your help!
>> Carlos
>>
>> >--Original Message--
>> >The NT backup will only copy files. You don't want a
>> file level backup of
>> >sql server you want file generated from a SQL Server
>> Backup. The file level
>> >backup will most likely be useless if you can even get
it
>> to copy while it
>> >is running. Take a look at the BACKUP command in
>> BooksOnLine. It will
>> >generate a file(s) that you can use the NT backup
process
>> to copy and not
>> >the original sql server files.
>> >
>> >--
>> >
>> >Andrew J. Kelly
>> >SQL Server MVP
>> >
>> >
>> >"Carlos" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:742101c3e765$bef02c30$a001280a@.phx.gbl...
>> >> Hello!'
>> >>
>> >> New to the admin aspect of SQL 2000, wondering if
there
>> >> are any advantages/disadvantaged of backing the
>> databases
>> >> using the NT backup utility found in Windows 2000
>> server.
>> >>
>> >> Is there anything that should be excluded similar
>> to,like,
>> >> the M drive on Exchange 2000?
>> >>
>> >> Thanks !
>> >>
>> >> Carlos
>> >
>> >
>> >.
>> >
>
>.
>|||By the way, no spaces in the DB name.
>--Original Message--
>The MSDB should be set to simple recovery as well. If
you mean you get a
>SQL error number 208 then you most likely typed something
in wrong or
>possibly have spaces in the db name. If so use [] around
the names. If you
>could post the exact command and error it would help.
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"Carlos" <anonymous@.discussions.microsoft.com> wrote in
message
>news:70a601c3e76d$71023f10$a101280a@.phx.gbl...
>> Abdrew,
>> Do you mean running the backup-to-file within the SQL
2000
>> and then backing up those files to tape, etc?
>> I have done that, however, when doing that within sql
>> 2000, an event id 208 appears. I have changed the
master
>> db to simple recovery model and set the msdb to full
>> recovery model. This should have resolved issue, but it
>> didn't.
>> Thanks for your help!
>> Carlos
>>
>> >--Original Message--
>> >The NT backup will only copy files. You don't want a
>> file level backup of
>> >sql server you want file generated from a SQL Server
>> Backup. The file level
>> >backup will most likely be useless if you can even get
it
>> to copy while it
>> >is running. Take a look at the BACKUP command in
>> BooksOnLine. It will
>> >generate a file(s) that you can use the NT backup
process
>> to copy and not
>> >the original sql server files.
>> >
>> >--
>> >
>> >Andrew J. Kelly
>> >SQL Server MVP
>> >
>> >
>> >"Carlos" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:742101c3e765$bef02c30$a001280a@.phx.gbl...
>> >> Hello!'
>> >>
>> >> New to the admin aspect of SQL 2000, wondering if
there
>> >> are any advantages/disadvantaged of backing the
>> databases
>> >> using the NT backup utility found in Windows 2000
>> server.
>> >>
>> >> Is there anything that should be excluded similar
>> to,like,
>> >> the M drive on Exchange 2000?
>> >>
>> >> Thanks !
>> >>
>> >> Carlos
>> >
>> >
>> >.
>> >
>
>.
>|||This is one of the main reasons why I dislike the MP so much. It can be
very hard to tell what is going on and what the problems are. If you can't
easily find the issue I recommend just creating your own scheduled job that
issues the backups so you can have more control over what is going on.
Andrew J. Kelly
SQL Server MVP
"Carlos" <anonymous@.discussions.microsoft.com> wrote in message
news:886f01c3e9a5$e01c90f0$a001280a@.phx.gbl...
> Hello!
> The exact event id 208 is:
> SQL Server Scheduled Job 'DB Backup Job for DB Maintenance
> Plan "Nightly Maintenance Plan"(hexnumber)- Status:
> Failed -invoked on date - Message: The job failed. The Job
> was invoked by Schedule 4 (Schedule 1). The last step was
> step 1 (Step 1)
> msdb was actually set back to simple
> Thanks for your advise!
> >--Original Message--
> >The MSDB should be set to simple recovery as well. If
> you mean you get a
> >SQL error number 208 then you most likely typed something
> in wrong or
> >possibly have spaces in the db name. If so use [] around
> the names. If you
> >could post the exact command and error it would help.
> >
> >--
> >
> >Andrew J. Kelly
> >SQL Server MVP
> >
> >
> >"Carlos" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:70a601c3e76d$71023f10$a101280a@.phx.gbl...
> >> Abdrew,
> >>
> >> Do you mean running the backup-to-file within the SQL
> 2000
> >> and then backing up those files to tape, etc?
> >>
> >> I have done that, however, when doing that within sql
> >> 2000, an event id 208 appears. I have changed the
> master
> >> db to simple recovery model and set the msdb to full
> >> recovery model. This should have resolved issue, but it
> >> didn't.
> >>
> >> Thanks for your help!
> >>
> >> Carlos
> >>
> >>
> >> >--Original Message--
> >> >The NT backup will only copy files. You don't want a
> >> file level backup of
> >> >sql server you want file generated from a SQL Server
> >> Backup. The file level
> >> >backup will most likely be useless if you can even get
> it
> >> to copy while it
> >> >is running. Take a look at the BACKUP command in
> >> BooksOnLine. It will
> >> >generate a file(s) that you can use the NT backup
> process
> >> to copy and not
> >> >the original sql server files.
> >> >
> >> >--
> >> >
> >> >Andrew J. Kelly
> >> >SQL Server MVP
> >> >
> >> >
> >> >"Carlos" <anonymous@.discussions.microsoft.com> wrote in
> >> message
> >> >news:742101c3e765$bef02c30$a001280a@.phx.gbl...
> >> >> Hello!'
> >> >>
> >> >> New to the admin aspect of SQL 2000, wondering if
> there
> >> >> are any advantages/disadvantaged of backing the
> >> databases
> >> >> using the NT backup utility found in Windows 2000
> >> server.
> >> >>
> >> >> Is there anything that should be excluded similar
> >> to,like,
> >> >> the M drive on Exchange 2000?
> >> >>
> >> >> Thanks !
> >> >>
> >> >> Carlos
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >

Backing Up Muliple Databases in Same Instance

I've 6 databases in the same instance which I wish to se a backup routine
for. How can I select all the databases at a go and create a backup procedure
just once in enerprise manager. I tried highlighting all he databases, but
each time I click backup up. he sysem comes up with one database at a time
for me o create backup for.
Regards
Is this something you want to schedule or do only once?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ogemdi" <Ogemdi@.discussions.microsoft.com> wrote in message
news:A9E328AC-0817-4DF6-8EA6-AD550DFBDCDC@.microsoft.com...
> I've 6 databases in the same instance which I wish to se a backup routine
> for. How can I select all the databases at a go and create a backup procedure
> just once in enerprise manager. I tried highlighting all he databases, but
> each time I click backup up. he sysem comes up with one database at a time
> for me o create backup for.
> --
> Regards
|||I want to schedule it.
Regards
"Tibor Karaszi" wrote:

> Is this something you want to schedule or do only once?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Ogemdi" <Ogemdi@.discussions.microsoft.com> wrote in message
> news:A9E328AC-0817-4DF6-8EA6-AD550DFBDCDC@.microsoft.com...
>
|||Create a Maintenance Plans. The regular backup dialog doesn't support selecting multiple databases,
but a Maint Plans does.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ogemdi" <Ogemdi@.discussions.microsoft.com> wrote in message
news:7EFD5154-BBB8-40D0-8791-51EF2B744CC3@.microsoft.com...[vbcol=seagreen]
>I want to schedule it.
> --
> Regards
>
> "Tibor Karaszi" wrote:

Thursday, March 29, 2012

Backing Up System Databases...

Backing Up System Databases...

Backing Up Sys Databases

Backing Up Sys Databases

Backing up SSRS objects

Backing up SQL to a different server

Backing up SQL Server: VDI or SQLDMO?

Backing up SQL Server

Tuesday, March 27, 2012

backing up SQL qhile it it running

backing up SQL qhile it it running

Backing Up SQL DBs With Retrospecct

Backing up SQL databases

Backing up SQL Databases

Backing up SQL Databases

Backing up SQL databases

Backing up SQL databases

Backing up SQL Databases

Backing up SQL 2000

Backing Up Muliple Databases in Same Instance

Autonumber field

Blog Archive

About Me