Autonumber field: 2012

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 Stored procedures

Is there an easy simple way to backup all my personal Stored Procedures.

In the stored procedures collection, my stored procedures start with "DEF".

I would like to have the stored procedures places as text files in a personal backup folder.

Is this possible?

I have the query to get my SP's:

SELECT

FROM

SYSOBJECTS

WHERE

xtype='p'AND NameLIKE'DEF%'

ORDER

BY NAME

Next step is how to get the code inside each stored procedure to be exported to a .txt file.

How can I do this?

|||

You should do it at design time if possible.

You can try to send your stored procedure code as attachments in e-mail and next save this attachments or you can get stored procedure code using system stored procedure and save result to flat file output using SSIS package, You can also try to use Bulk copy?. You cannot save to file directly from T-SQL

Thanks

|||

You should do it at design time if possible.

Thanks

|||What do you mean by CLR stored procedure ?|||

CLR stored procedure is new for SQL 2005 and allow you to create stored procedure or function in .net language, so you can access database and any available .net stuff, and good thing is that you can run it on SQL serer as standard SQL stored procedure (in the past you had extended stored procedures created in C++).

Thanks

|||

(1) You can also generate the scripts from the DB, Right click on DB -> All Tasks -> Generate Scripts.

(2) You can get the text for a stored proc from syscomments.

(3) You can probably write some application to get the text from syscomments and save it to a file.

|||

With the following query, I get all my SP and the text in the query result.

=====

USE

PDSNT

SELECT

name,text

FROM

sysobjects

INNER

JOINsyscomments

sysobjects.id=syscomments.id

WHERE

xtype='p'AND NameLIKE'DEF%'

ORDER

BY NAME

=====

Next step would be to export them to .xml or .txt file.

Anyone can help?

Backing Up Stored Procedures

How do I Backup stored procedures?Backup the database which contains the stored procedures.
You backup everything in the database when you do a backup.
Not sure if you mean you want the stored procedures separate
from everything else or not but you could script out the
stored procedures if you need something along those lines.
-Sue
On Mon, 22 Mar 2004 10:34:36 -0800, "barb"
<anonymous@.discussions.microsoft.com> wrote:

>How do I Backup stored procedures?|||We do not need some of the default stored procedures.
Security docs say to drop. I want to backup before I drop
in case I need to add back at later date. How do I "script
out" the stored procedures? Thank you.

>--Original Message--
>Backup the database which contains the stored procedures.
>You backup everything in the database when you do a
backup.
>Not sure if you mean you want the stored procedures
separate
>from everything else or not but you could script out the
>stored procedures if you need something along those lines.
>-Sue
>On Mon, 22 Mar 2004 10:34:36 -0800, "barb"
><anonymous@.discussions.microsoft.com> wrote:
>
>.
>|||Right click on the db in Enterprise Manager and choose All Tasks - Generate
SQL Script. Here you can script them all or just selected ones.
Andrew J. Kelly SQL MVP
"barb" <anonymous@.discussions.microsoft.com> wrote in message
news:1198d01c4106d$70603750$a401280a@.phx
.gbl...
> We do not need some of the default stored procedures.
> Security docs say to drop. I want to backup before I drop
> in case I need to add back at later date. How do I "script
> out" the stored procedures? Thank you.
>
>
>
> backup.
> separate|||Thank You.
-- Andrew J. Kelly wrote: --
Right click on the db in Enterprise Manager and choose All Tasks - Generate
SQL Script. Here you can script them all or just selected ones.
Andrew J. Kelly SQL MVP
"barb" <anonymous@.discussions.microsoft.com> wrote in message
news:1198d01c4106d$70603750$a401280a@.phx
.gbl...
> We do not need some of the default stored procedures.
> Security docs say to drop. I want to backup before I drop
> in case I need to add back at later date. How do I "script
> out" the stored procedures? Thank you.
> backup.
> separate

Backing up Stored Procedure

Hi,
I am using the Backup Wizard with following statement to backup entire DB
from Development Server.
BACKUP DATABASE [Dev_DB] TO [Dev_DB_Backup] WITH INIT , NOUNLOAD ,
NAME = N'Dev_DB Backup',
SKIP , STATS = 10,
NOFORMAT
It doesnot backup the stored procedures developed.
Is there a way to backup the stored procedures as well through scheduled
task or some script to be run at specific time.
TIA
KayAre you sure, the command above store all the data and the object
definitions (which is actually data stored in the system tables) ?
Please make sure and confirm this.
HTH, jens Suessmeyer.|||Systems table data is not required for the time being. But yes it is.
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1132850534.307481.196070@.g49g2000cwa.googlegroups.com...
> Are you sure, the command above store all the data and the object
> definitions (which is actually data stored in the system tables) ?
> Please make sure and confirm this.
> HTH, jens Suessmeyer.
>sql

Backing up Stored Procedure

Hi,
I am using the Backup Wizard with following statement to backup entire DB
from Development Server.
BACKUP DATABASE [Dev_DB] TO [Dev_DB_Backup] WITH INIT , NOUNLOAD ,
NAME = N'Dev_DB Backup',
SKIP , STATS = 10,
NOFORMAT
It doesnot backup the stored procedures developed.
Is there a way to backup the stored procedures as well through scheduled
task or some script to be run at specific time.
TIA
Kay
Are you sure, the command above store all the data and the object
definitions (which is actually data stored in the system tables) ?
Please make sure and confirm this.
HTH, jens Suessmeyer.
|||Systems table data is not required for the time being. But yes it is.
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1132850534.307481.196070@.g49g2000cwa.googlegr oups.com...
> Are you sure, the command above store all the data and the object
> definitions (which is actually data stored in the system tables) ?
> Please make sure and confirm this.
> HTH, jens Suessmeyer.
>

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 with Batch - and Backup Name Prompt

Hello. I update our SQL QA server about four times a day and back it up
before every update. There are three DBs that I have to backup. Each
backup is given a specific name accoring to the 'Update Name and Number".
This take a lot of time to go through Enterprise Manager every time.
For one backup session the the backup names would be:
DDMMYY_QA_UpdateNumber_DB1.bak
DDMMYY_QA_UpdateNumber_DB2.bak
DDMMYY_QA_UpdateNember_DB3.bak
I know how to backup the DBs with a dos batch, but I have to then change all
the names which is still a pain.
If I could have a batch file that I click on and it prompts me for:
DDMMYY_QA_UpdateNumber and I could plug that in say: 092404_QA_Update283 and
hit enter, and it would produce:
092404_QA_Update283_DB1.bak
092404_QA_Update283_DB2.bak
092404_QA_Update283_DB3.bak
My life would change!
Is this possible? How could it be done?
Thanks in advance for any help!!!
Hi,
No need to write a batch program, Go with a small procedure which generates
the backup file names based on MMDDYYHHMinSS
See the below site for script and details:-
http://www.microsoft.com/india/msdn/articles/190.aspx
Note:
Change the script slightly to backup to local drives...
Thanks
Hari
MCDBA
"Tom" <none@.none.com> wrote in message
news:ewQtjdmoEHA.1800@.TK2MSFTNGP15.phx.gbl...
> Hello. I update our SQL QA server about four times a day and back it up
> before every update. There are three DBs that I have to backup. Each
> backup is given a specific name accoring to the 'Update Name and Number".
> This take a lot of time to go through Enterprise Manager every time.
> For one backup session the the backup names would be:
> DDMMYY_QA_UpdateNumber_DB1.bak
> DDMMYY_QA_UpdateNumber_DB2.bak
> DDMMYY_QA_UpdateNember_DB3.bak
> I know how to backup the DBs with a dos batch, but I have to then change
> all
> the names which is still a pain.
> If I could have a batch file that I click on and it prompts me for:
> DDMMYY_QA_UpdateNumber and I could plug that in say: 092404_QA_Update283
> and
> hit enter, and it would produce:
> 092404_QA_Update283_DB1.bak
> 092404_QA_Update283_DB2.bak
> 092404_QA_Update283_DB3.bak
> My life would change!
> Is this possible? How could it be done?
> Thanks in advance for any help!!!
>

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 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 SQL Server express db that runs in user instance?

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

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

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

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

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

Backing up SQL Server 2005 Express

How can I back up existing SQL Server 2005 Express tables? I'm presently developing on a 4 year old laptop which is toooo sloooow and need to transfer the tables and data to a desktop system.

Copy the MyDatabase.mdf and MyDatabase.ldf files to the new system.

In the new system, use Attach... from SQL studio, to connect to the copied database.

backing up SQL Server 2000 database to named pipe

would appreciate if you could comment on how to backup sql server 2000
database to named pipe
Thank you for your help beforehand,
Miron.I believe you must create a logical dump device of the type "Named Pipe" and
then issue the backup to that device. See sp_addumpdevice in BOL for more
details.
Andrew J. Kelly SQL MVP
"miron" <nospam_berlin.miron@.verizon.net> wrote in message
news:%23tu$B3JOFHA.904@.tk2msftngp13.phx.gbl...
> would appreciate if you could comment on how to backup sql server 2000
> database to named pipe
> Thank you for your help beforehand,
> Miron.
>|||I am short on time. It would be nice to have a working script for database
pubs and a named pipe \\\pipe\mypipe
Thanks,
Miron.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%235J$lIKOFHA.3512@.TK2MSFTNGP15.phx.gbl...
> I believe you must create a logical dump device of the type "Named Pipe"
and
> then issue the backup to that device. See sp_addumpdevice in BOL for more
> details.
> --
> Andrew J. Kelly SQL MVP
>
> "miron" <nospam_berlin.miron@.verizon.net> wrote in message
> news:%23tu$B3JOFHA.904@.tk2msftngp13.phx.gbl...
>|||Try the following script to create the device and then backup the PUBS
database to the newly created device:
EXEC sp_addumpdevice 'pipe', 'PUBS_PIPE_DUMP', '\\\pipe\mypipe'
BACKUP DATABASE pubs TO PUBS_PIPE_DUMP
- Peter Ward
WARDY IT Solutions
"miron" wrote:

> I am short on time. It would be nice to have a working script for database
> pubs and a named pipe \\\pipe\mypipe
> Thanks,
> Miron.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%235J$lIKOFHA.3512@.TK2MSFTNGP15.phx.gbl...
> and
>
>|||Peter, thank you for the script. While using lines I run into issues, some
did resolve, others still need assistance. Here is step by step
1.) create the dump device using the first line of the script
EXEC sp_addumpdevice 'pipe', 'PUBS_PIPE_DUMP', '\\\pipe\mypipe'
2.) executed
BACKUP DATABASE pubs TO PUBS_PIPE_DUMP
and SQL Server immediately returned an error message:
Msg 3201, Level 16, State 4, Server ZEBRA, Line 1
Cannot open backup device 'PUBS_PIPE_DUMP'. Device error or device off-line.
See the SQL Server error log for more details.
Msg 3013, Level 16, State 1, Server ZEBRA, Line 1
BACKUP DATABASE is terminating abnormally.
3.) modified the dump device as following:
exec sp_dropdevice 'PUBS_PIPE_DUMP'
go
EXEC sp_addumpdevice 'pipe', 'PUBS_PIPE_DUMP', '\\.\pipe\mypipe'
go
4.) tried to execute again
BACKUP DATABASE pubs TO PUBS_PIPE_DUMP
this time osql.exe cursor blinked for a few minutes. Then SQL Server
returned the same error message...
Msg 3201, Level 16, State 4, Server ZEBRA, Line 1
Cannot open backup device 'PUBS_PIPE_DUMP'. Device error or device off-line.
See the SQL Server error log for more details.
Msg 3013, Level 16, State 1, Server ZEBRA, Line 1
BACKUP DATABASE is terminating abnormally.
5.) just to see how much time it takes to backup pubs I run script
1> BACKUP DATABASE pubs TO disk = 'C:\pubs_20050404003334827.bkp'
2> go
Here is SQL Server message returned when completed backup:
Processed 144 pages for database 'pubs', file 'pubs' on file 1.
Processed 1 pages for database 'pubs', file 'pubs_log' on file 1.
BACKUP DATABASE successfully processed 145 pages in 1.021 seconds (1.157
MB/sec).
and it happened in a mere second. So, it does look like I am close to
succeeding at baking SQL Server 2000 database to a named pipe, yet something
is not letting backup to complete in reasonable time. I guess what really
needed is a working script, that would allow to store backup and then
restore from it using named pipe. I do greatly appreciate any further
assistance.
Miron.
"P. Ward" <peter@.remove_online.wardyit.com> wrote in message
news:5ACAA03C-AC69-47AD-A638-F971557B2D5F@.microsoft.com...
> Try the following script to create the device and then backup the PUBS
> database to the newly created device:
> EXEC sp_addumpdevice 'pipe', 'PUBS_PIPE_DUMP', '\\\pipe\mypipe'
> BACKUP DATABASE pubs TO PUBS_PIPE_DUMP
>
> - Peter Ward
> WARDY IT Solutions
>
> "miron" wrote:
>
database
Pipe"
more
2000|||"miron" <nospam_berlin.miron@.verizon.net> wrote in message
news:%23NM7zvOOFHA.1392@.TK2MSFTNGP10.phx.gbl...
> Peter, thank you for the script. While using lines I run into issues, some
> did resolve, others still need assistance. Here is step by step
> 1.) create the dump device using the first line of the script
> EXEC sp_addumpdevice 'pipe', 'PUBS_PIPE_DUMP', '\\\pipe\mypipe'
> 2.) executed
> BACKUP DATABASE pubs TO PUBS_PIPE_DUMP
> and SQL Server immediately returned an error message:
> Msg 3201, Level 16, State 4, Server ZEBRA, Line 1
> Cannot open backup device 'PUBS_PIPE_DUMP'. Device error or device
> off-line.
> See the SQL Server error log for more details.
> Msg 3013, Level 16, State 1, Server ZEBRA, Line 1
> BACKUP DATABASE is terminating abnormally.
> 3.) modified the dump device as following:
> exec sp_dropdevice 'PUBS_PIPE_DUMP'
> go
> EXEC sp_addumpdevice 'pipe', 'PUBS_PIPE_DUMP', '\\.\pipe\mypipe'
> go
> 4.) tried to execute again
> BACKUP DATABASE pubs TO PUBS_PIPE_DUMP
> this time osql.exe cursor blinked for a few minutes. Then SQL Server
> returned the same error message...
> Msg 3201, Level 16, State 4, Server ZEBRA, Line 1
> Cannot open backup device 'PUBS_PIPE_DUMP'. Device error or device
> off-line.
> See the SQL Server error log for more details.
> Msg 3013, Level 16, State 1, Server ZEBRA, Line 1
> BACKUP DATABASE is terminating abnormally.
> 5.) just to see how much time it takes to backup pubs I run script
> 1> BACKUP DATABASE pubs TO disk = 'C:\pubs_20050404003334827.bkp'
> 2> go
> Here is SQL Server message returned when completed backup:
> Processed 144 pages for database 'pubs', file 'pubs' on file 1.
> Processed 1 pages for database 'pubs', file 'pubs_log' on file 1.
> BACKUP DATABASE successfully processed 145 pages in 1.021 seconds (1.157
> MB/sec).
> and it happened in a mere second. So, it does look like I am close to
> succeeding at baking SQL Server 2000 database to a named pipe, yet
> something
> is not letting backup to complete in reasonable time. I guess what really
> needed is a working script, that would allow to store backup and then
> restore from it using named pipe. I do greatly appreciate any further
> assistance.
You need to have something reading or writing data in or out of the named
pipe, within a reasonable amount of time, or the backup/restore will fail.
Simple example using lzop (google for it) to compress backups on-the-fly.
start cmd /C isql /E /Q"backup database <db_name> to pipe = '\\.\pipe\bck'"
sleep 3
lzop -v -o<db_name>.lzo < \\.\pipe\bck
The sleep command just allows a bit of time for the backup to get started
and the pipe to be setup.
To restore the data :-
start cmd /C isql /E /Q"restore database <db_name> from pipe
='\\.\pipe\bck'"
sleep 3
lzop -d -n -v -c <db_name>.lzo > \\.\pipe\bck
You can also use this to make quick copies of databases :-
start isql -E -Q"backup database <db_name> to pipe = '\\.\pipe\bck_o' with
stats = 10"
start isql -E -Q"restore database <db_name_new> from pipe = '\\.\pipe\bck_i'
stats = 10"
sleep 2
cat \\.\pipe\bck_o > \\.\pipe\bck_i
(The restore command will need 'with move' parameters, they are not shown
for clarity)
(copy didn't work when I first tried it, so I used cat, copy /b may work)
Ian.|||Actually you can do it directly
backup database prod to pipe='\\myserver\mypipe'
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"miron" <nospam_berlin.miron@.verizon.net> wrote in message
news:%23tu$B3JOFHA.904@.tk2msftngp13.phx.gbl...
> would appreciate if you could comment on how to backup sql server 2000
> database to named pipe
> Thank you for your help beforehand,
> Miron.
>

Backing Up sql server

Hey, please forgive me if my infomation source was incorrect. But a friend of mine mentioned to me he had hurd of a program, or code that will make an "install script" of your database. Basically it`ll back up all the tables on your server and if the worst case senario happens then you can run the file like an install script. Is this true? It sounds invalueable! If it isnt true, can someone tell me if it is possible to use code to create a "install script" of your own database? I am using "Microsoft SQL Server".

Thanks in advance John

It's called Enterprise Manager and the documentation is in Books Online. You installed both when you installed Microsoft SQL Server.

Jeff

|||Your firend is right. This is possible to make database backup by scripts. :)|||

so its already installed with Microsofts SQL server?

It is possible to make a page on your site, (that only Admin can access) to create a backup using "Enterprise Manager"

Im finding it hard to find any infomation on Enterprise Manager on MSDN, or am I looking in the wrong place? thanks si!

backing up SQL server

Hi, I'm new to SQL server and hope someone can help me. I have SQL 7 and am
wanting to back it up remotely. I'm currently using a batch script that sh
uts down the service and then uses an xcopy command to copy the SQL director
y to a temporary location.
The script then uses a net start command to restart the services on a schedu
le. I then backup the temporary files that the xcopy command created. My q
uestion is, in the event of a System Crash, can I reinstall and configure SQ
L server and then restore
the temporary files and have them work? If not, is there another way to suc
cesfully back up SQL Server? Any help is very much appreciated. Thanks in
advance.Hi,
Please go thu the script will take the UNC path as the parameter and will
Backup Master, MSDB and all the User databases to the remote machine. This
script will create the unique Backup files names, this will ensure that old
backup sets were not overwritten.
Prerequisites
1.. SQL server and SQL Server Agent should be configured to start in
Domain Account
2.. This Domain account should have change privileges to add files to the
Remote machine
Script
CREATE PROCEDURE BACKUP_SP @.UNCPATH VARCHAR(200) AS
BEGIN
SET NOCOUNT ON
DECLARE @.NAME VARCHAR(100),
DECLARE @.DBNAME VARCHAR(100)
DECLARE BACKUP_CUR CURSOR FOR
SELECT name FROM master..Sysdatabases where name not in
('model','pubs','tempdb','northwind')
OPEN BACKUP_CUR
FETCH NEXT FROM BACKUP_CUR INTO @.DBNAME
WHILE @.@.FETCH_STATUS=0
BEGIN
SELECT
NAME=@.UNCPATH+@.DBNAME+'_'+ltrim (rtrim (convert (char,
getdate(),105)))+'Dump.bak'
BACKUP DATABASE @.DBNAME TO DISK = @.NAME WITH INIT , NOUNLOAD ,
NAME
= @.DBNAME, NOSKIP, STATS = 10, NOFORMAT
FETCH NEXT FROM BACKUP_CUR INTO @.DBNAME
END
CLOSE BACKUP_CUR
DEALLOCATE BACKUP_CUR
END
How to Execute
This procedure will take @.UNCPATH as the input parameter, Say you have to
backup the database to machine BACKUPSERVER in to share SQLBACKUP then the
execution will be
EXEC BACKUP_SP '\\BACKUPSERVER\SQLBACKUP'
This will backup all the databases to the SQLBACKUP folder in BACKUPSERVER.
Thanks
Hari
MCDBA
"Mike" <mhebner@.getnet.net> wrote in message
news:9CB58890-10EF-477E-84E1-C953D7EE6534@.microsoft.com...
> Hi, I'm new to SQL server and hope someone can help me. I have SQL 7 and
am wanting to back it up remotely. I'm currently using a batch script that
shuts down the service and then uses an xcopy command to copy the SQL
directory to a temporary location. The script then uses a net start command
to restart the services on a schedule. I then backup the temporary files
that the xcopy command created. My question is, in the event of a System
Crash, can I reinstall and configure SQL server and then restore the
temporary files and have them work? If not, is there another way to
succesfully back up SQL Server? Any help is very much appreciated. Thanks
in advance.|||"Mike" <mhebner@.getnet.net> wrote in message
news:9CB58890-10EF-477E-84E1-C953D7EE6534@.microsoft.com...
My question is, in the event of a System Crash, can I reinstall and
configure SQL server and then restore the temporary files and have them
work?
You should try out restoring the files to a different server and see if it
works. There is no substitute to this. It is always better if you have
carried out some test restores to confirm that your process will work, even
if somebody else says that it will, as it gives you some experience for when
you might have to do it for real.

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

Backing Up SQL Server

I currently have two SQL servers, one of which is a replicator, and I have decided that it is long past time to start backing up properly. I have looked at a number of solutions but Veritas Backup Exec seems to be a decent product. I was wondering if I could get you opinions on this and any other products there might be out there (keeping in mind I am a small company on a budget). THXWhat types of backups are you talking about? Are you wanting to do database backups, or file backups to a tape device or something? Database backups can be done reliably using the sql tools, tape backups are a different matter.|||If you already have a server with a backup device you can configure the SQL Server to perform its backups to a share on that server. The files will be backed up along with the existing backup on the existing server (hopefully this is still clear).|||Im talking about doing a backup of my database and of the system databases. I was going to backup to a tape (AIT3) drive. I was looking at using Server's backup tool but I didn't know if it could backup hot or not. Once again thanks.|||SQL Server has actually made this very simple for you. Open up Enterprise Manager, and expand the Management folder. Then Right Click on the Database Maintenance Plans icon and select new maintenance plan. Then follow it through. SQL can back up any and all of its database while still serving data and filling requests. Then you can back up the .bak or .trn files dumped to tape after the maintenance plan has completed. Read up on maintenance plans in books online. There is alot of info in there.|||Yea I saw that but then I got to wondering if there was something else to that because I see companies like Veritas and commVolt making these backup packages and I kinda figured if they did that there must be somthing missing from Microsoft's backup, or is there? Thanks, SQLhack|||I have only come across one problem that I couldnt solve with the bundled SQL tools. We had a transaction log that was corrupted and couldnt apply it to our standby server. We actually had to get Lumigent Log Explorer to script it out and then played it forward. But that is another story. The other reason SQL backup solution may not work is for larger databases (100 Gig +) the restore times can be slow. Especially if your site is down during the restore. For the rest of my 6 years of SQL Server experience, database and log dumps copied off the server and backed up to tape have always gotten me out of any problem that hase come along.|||We used Veritas Backup Exec on many NT4 servers, and we upgraded our servers to Win2k, we decided to use Microsoft Backup instead, cause many tech of these sites complains about complexity to manage backup, etc.

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,
Sam
Yes, 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
>

Backing up SQL Server

Backing Up SQL Serv

I've installed MSDE SP3, and I'm looking for a script that runs once
before back-up to shut down the server, and once after backing-up, to
restart the server. This is supposed to go automatically. I'm running
Veritas Back_up Exec 9.0 (Without SQL extension... ).
Does anyone have an easy script for to run on my Win2k Server?
--
Jakob /n Powder (i.e. Snow) To The People
Posted via http://dbforums.comWhy do you want to shutdown the server, for creating a backup? The native
SQL Server BACKUP command is online and can backup databases that are
currently being accessed.
Are you trying to copy the physical files? If so, you may want to look at
NET STOP and NET START commands for stopping and starting services.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
What hardware is your SQL Server running on?
http://vyaskn.tripod.com/poll.htm
"JakobDi" <Jakob_di@.hotmail.com> wrote in message
news:3381077.1063810544@.dbforums.com...
I've installed MSDE SP3, and I'm looking for a script that runs once
before back-up to shut down the server, and once after backing-up, to
restart the server. This is supposed to go automatically. I'm running
Veritas Back_up Exec 9.0 (Without SQL extension... ).
Does anyone have an easy script for to run on my Win2k Server?
Jakob /n Powder (i.e. Snow) To The People
Posted via http://dbforums.com|||do a simple .BAT file to stop SQL service before backup runs and another
.BAT file after backup done to start SQL service. You can place the path to
run these files into Backup Exec job. I never use MSDE so I ain't sure but
for SQL you can similar to this:
BEFORE.BAT
net stop mssqlserver
AFTER.BAT
net start mssqlserver
Are you trying to backup the .MDF and LDF files? I prefer using native SQL
backup tools to backup databases.
hth,
"JakobDi" <Jakob_di@.hotmail.com> wrote in message
news:3381077.1063810544@.dbforums.com...
> I've installed MSDE SP3, and I'm looking for a script that runs once
> before back-up to shut down the server, and once after backing-up, to
> restart the server. This is supposed to go automatically. I'm running
> Veritas Back_up Exec 9.0 (Without SQL extension... ).
>
> Does anyone have an easy script for to run on my Win2k Server?
>
> --
> Jakob /n Powder (i.e. Snow) To The People
>
> Posted via http://dbforums.com|||Thanks All;
net stop mssql$test2 & net start msssql$test2 performed as expected,
(and the solution was rather simple; didn't think it would be
this easy...)
:-)
Originally posted by Flicker
> do a simple .BAT file to stop SQL service before backup runs
> and another
> .BAT file after backup done to start SQL service. You can place
> the path to
> run these files into Backup Exec job. I never use MSDE so I ain't
> sure but
> for SQL you can similar to this:
>
> BEFORE.BAT
> net stop mssqlserver
>
> AFTER.BAT
> net start mssqlserver
>
>
> Are you trying to backup the .MDF and LDF files? I prefer using
> native SQL
> backup tools to backup databases.
>
> hth,
>
>
>
>
> "JakobDi" <Jakob_di@.hotmail.com> wrote in message
> news:3381077.1063810544@.dbforums.com"]news:3381077.1063810544@.d-
> bforums.com[/url]...
> > I've installed MSDE SP3, and I'm looking for a script that runs
> once
> > before back-up to shut down the server, and once after backing-
> up, to
> > restart the server. This is supposed to go automatically. I'm
> running
> > Veritas Back_up Exec 9.0 (Without SQL extension... ).
> > Does anyone have an easy script for to run on my Win2k
> Server?
> > --
> > Jakob /n Powder (i.e. Snow) To The People
> Posted via
http://dbforums.com/http://dbforums.com
Jakob /n Powder (i.e. Snow) To The People
Posted via http://dbforums.com

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 Express

Hi everyone,
Can anyone tell me if tehre is a tool or some preffered means of backing
up SQL Express on a schedule.
I typically use SQL Agent to schedule backup jobs but SQL Express doesnt
seem to come with that.
Many thanks
Simon
What about doing a AT job within Windows with specifying something like
this as the command:
SQLCMD.EXE -S .\SQLExpress -Q -U Username -P Password -Q "BACKUP
DATABASE..."
HTH; Jens Suessmeyer.

Backing up SQL Express

Hi everyone,
Can anyone tell me if tehre is a tool or some preffered means of backing
up SQL Express on a schedule.
I typically use SQL Agent to schedule backup jobs but SQL Express doesnt
seem to come with that.
Many thanks
SimonWhat about doing a AT job within Windows with specifying something like
this as the command:
SQLCMD.EXE -S .\SQLExpress -Q -U Username -P Password -Q "BACKUP
DATABASE..."
HTH; Jens Suessmeyer.

Backing up SQL Express

Hi everyone,
Can anyone tell me if tehre is a tool or some preffered means of backing
up SQL Express on a schedule.
I typically use SQL Agent to schedule backup jobs but SQL Express doesnt
seem to come with that.
Many thanks
SimonWhat about doing a AT job within Windows with specifying something like
this as the command:
SQLCMD.EXE -S .\SQLExpress -Q -U Username -P Password -Q "BACKUP
DATABASE..."
HTH; Jens Suessmeyer.

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 DB

We're trying to create a .bak file to send it to our site's admin to restore it on the

server:

1. When I try to do that get this error:
http://www.radiorient.net/errors/export_sql.gif

Why is that? and how can I fix it?

2. Should we add both files of the db (.MDF, and .LDF) or only the .MDF file?

Can please someone help me with this, Thanx.

Do not bakcup a datbase to its own data/log file, instead, you can backup to any other file that can be written by SQL Server. The purpose of backup is to gurantee the database can be recovered in case of database corruption (usually the physical files are corrupted), so bakcup a database to its data/log files doesn't make sense, right? Smile

For more information about backup, please refer to:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ba-bz_35ww.asp

|||

Thanx, we did it, we have some debugging to do now, but at least the DB is online, I hate H-Sphere, I worked with many control panels that is a lot easier.

Now when we upload image using the admin area, it doesn't upload the image on the server, we checked the permission of the image folder and it's 777, so what could be wrong?!

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).
[vbcol=seagreen]
>--Original Message--
to
>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.[vbcol=seagreen]
SQl
>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[vbcol=seagreen]
>news:451701c4800c$cb3dbbc0$a301280a@.phx.gbl...
to[vbcol=seagreen]
SQl
>
>.
>
|||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...[vbcol=seagreen]
> 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).
> to
> independently. However, your
> databases to be in
> you know enough
> isn't a problem.
> SQl
> as part of your
> restored if they are
> before your inventory
> message
> to
> SQl
|||>> Lastly if I backup these three databases at night at[vbcol=seagreen]
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[vbcol=seagreen]
>news:4b1f01c4801a$65fef810$a401280a@.phx.gbl...
these[vbcol=seagreen]
be[vbcol=seagreen]
they[vbcol=seagreen]
I[vbcol=seagreen]
unless[vbcol=seagreen]
to[vbcol=seagreen]
point[vbcol=seagreen]
the
>
>.
>

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

Thursday, March 29, 2012

Backing Up System Databases...

Backing Up System Databases...

Backing Up Sys Databases

Backing Up Sys Databases

Backing up Stored procedures

Backing Up Stored Procedures

Backing up Stored Procedure

Backing up Stored Procedure

Backing up Stored Procedure

Backing up SSRS objects

Backing up SQL with Batch - and Backup Name Prompt

Backing up SQL to a different server

Backing up SQL Server: VDI or SQLDMO?

Backing up SQL Server MSDE and Express

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

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

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

Backing up SQL Server 2005 Express

backing up SQL Server 2000 database to named pipe

Backing Up sql server

backing up SQL server

Backing up SQL Server

Backing Up SQL Server

Backing up SQL Server

Backing up SQL Server

Backing Up SQL Serv

Tuesday, March 27, 2012

backing up SQL qhile it it running

backing up SQL qhile it it running

Backing up SQL Express

Backing up SQL Express

Backing up SQL Express

Backing Up SQL DBs With Retrospecct

Backing up SQL DB

Backing up SQL databases

Backing up SQL Databases

Autonumber field

Blog Archive

About Me