How to encrypt sql backup¿?

Hi,
I need encrypt media containing a sql backup, this media travel around any
factorys, is very important the security of content in this media and
garantize don't read for anyware sql server, only read in the original
server, is possible¿?
resguards
JordiLook at www.netlib.com -- Encryptionizer allows you to encrypt backups.
On Fri, 20 Jan 2006 16:13:52 +0100, "jordi" <jordifarell@.hotmail.com> wrote:
>Hi,
>I need encrypt media containing a sql backup, this media travel around any
>factorys, is very important the security of content in this media and
>garantize don't read for anyware sql server, only read in the original
>server, is possible¿?
>resguards
>Jordi
>
-- AntiSpam/harvest --
Remove X's to send email to me.|||Some of the 3:rd party backup programs have built-in encryption. Check out my link page:
http://www.karaszi.com/SQLServer/links.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"jordi" <jordifarell@.hotmail.com> wrote in message news:OR8BhQdHGHA.1180@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I need encrypt media containing a sql backup, this media travel around any factorys, is very
> important the security of content in this media and garantize don't read for anyware sql server,
> only read in the original server, is possible¿?
> resguards
> Jordi
>

How to encrypt MS SQL Server 2000

How could i encrypt a ms sql 2000 database. what is when i do a backup of
the db. will i recieve a certificade with the key.
Does somebody have some experiences about the best way for encrypting an sql
server and the protocol.
Thank's for some infos
MichelMichel wrote:
> How could i encrypt a ms sql 2000 database. what is when i do a backup of
> the db. will i recieve a certificade with the key.
> Does somebody have some experiences about the best way for encrypting an sql
> server and the protocol.
> Thank's for some infos
> Michel
For encrypted backups: http://www.quest.com/litespeed_for_sql_server
Search Books Online for "encryption" to understand how to encrypt the
network protocol.
Encrypting the entire database rarely makes sense. If you need to store
or transmit the database via an insecure medium then use an encrypted
backup.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

How to encrypt MS SQL Server 2000

How could i encrypt a ms sql 2000 database. what is when i do a backup of
the db. will i recieve a certificade with the key.
Does somebody have some experiences about the best way for encrypting an sql
server and the protocol.
Thank's for some infos
MichelMichel wrote:
> How could i encrypt a ms sql 2000 database. what is when i do a backup of
> the db. will i recieve a certificade with the key.
> Does somebody have some experiences about the best way for encrypting an s
ql
> server and the protocol.
> Thank's for some infos
> Michel
For encrypted backups: http://www.quest.com/litespeed_for_sql_server
Search Books Online for "encryption" to understand how to encrypt the
network protocol.
Encrypting the entire database rarely makes sense. If you need to store
or transmit the database via an insecure medium then use an encrypted
backup.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

How to Encrypt a Backup File

Hello, I would like to password-protect or encrypt the .bak file for 3
selected databases on a MS SQL 2000 server. The DBs are all set for Full
recovery mode and the nighly backups run OK now using a DB Maintenance Plan.
How can I encrypt only the backup files without encrypting either the DB or
the server? Thanks, PanchoPancho,
You can use the PASSWORD argument of the BACKUP statment. For encryption of
the backup file, you can use one of many available file encryption
utilities.
HTH
Jerry
PS - Litespeed will encrypt and compress your backups if needed.
"Pancho" <Pancho@.discussions.microsoft.com> wrote in message
news:D6078784-DF5B-4B79-9C76-A26723EB0930@.microsoft.com...
> Hello, I would like to password-protect or encrypt the .bak file for 3
> selected databases on a MS SQL 2000 server. The DBs are all set for Full
> recovery mode and the nighly backups run OK now using a DB Maintenance
> Plan.
> How can I encrypt only the backup files without encrypting either the DB
> or
> the server? Thanks, Pancho|||Note that the password features on backups are now obsolete. According to
BOL (SQL Server 2005) the "protection is weak" and intended to "prevent an
incorrect restore". "It does not prevent the reading of the backup data".
Ben Nevarez, MCDBA, OCP
Database Administrator
"Jerry Spivey" wrote:

> Pancho,
> You can use the PASSWORD argument of the BACKUP statment. For encryption
of
> the backup file, you can use one of many available file encryption
> utilities.
> HTH
> Jerry
> PS - Litespeed will encrypt and compress your backups if needed.
> "Pancho" <Pancho@.discussions.microsoft.com> wrote in message
> news:D6078784-DF5B-4B79-9C76-A26723EB0930@.microsoft.com...
>
>|||place backup to encrypted folder
--
Aleksandar Grbic
MCDBA
"Pancho" wrote:

> Hello, I would like to password-protect or encrypt the .bak file for 3
> selected databases on a MS SQL 2000 server. The DBs are all set for Full
> recovery mode and the nighly backups run OK now using a DB Maintenance Pla
n.
> How can I encrypt only the backup files without encrypting either the DB o
r
> the server? Thanks, Pancho|||Thank you Jerry, Ben and Aleksandar. These were all helpful ideas and I
posted a helpful rating to each.
Best Regards, Pancho
"Aleksandar Grbic" wrote:
[vbcol=seagreen]
> place backup to encrypted folder
> --
> Aleksandar Grbic
> MCDBA
>
> "Pancho" wrote:
>

How to Encrypt a Backup File

Hello, I would like to password-protect or encrypt the .bak file for 3
selected databases on a MS SQL 2000 server. The DBs are all set for Full
recovery mode and the nighly backups run OK now using a DB Maintenance Plan.
How can I encrypt only the backup files without encrypting either the DB or
the server? Thanks, PanchoPancho,
You can use the PASSWORD argument of the BACKUP statment. For encryption of
the backup file, you can use one of many available file encryption
utilities.
HTH
Jerry
PS - Litespeed will encrypt and compress your backups if needed.
"Pancho" <Pancho@.discussions.microsoft.com> wrote in message
news:D6078784-DF5B-4B79-9C76-A26723EB0930@.microsoft.com...
> Hello, I would like to password-protect or encrypt the .bak file for 3
> selected databases on a MS SQL 2000 server. The DBs are all set for Full
> recovery mode and the nighly backups run OK now using a DB Maintenance
> Plan.
> How can I encrypt only the backup files without encrypting either the DB
> or
> the server? Thanks, Pancho|||Note that the password features on backups are now obsolete. According to
BOL (SQL Server 2005) the "protection is weak" and intended to "prevent an
incorrect restore". "It does not prevent the reading of the backup data".
Ben Nevarez, MCDBA, OCP
Database Administrator
"Jerry Spivey" wrote:
> Pancho,
> You can use the PASSWORD argument of the BACKUP statment. For encryption of
> the backup file, you can use one of many available file encryption
> utilities.
> HTH
> Jerry
> PS - Litespeed will encrypt and compress your backups if needed.
> "Pancho" <Pancho@.discussions.microsoft.com> wrote in message
> news:D6078784-DF5B-4B79-9C76-A26723EB0930@.microsoft.com...
> > Hello, I would like to password-protect or encrypt the .bak file for 3
> > selected databases on a MS SQL 2000 server. The DBs are all set for Full
> > recovery mode and the nighly backups run OK now using a DB Maintenance
> > Plan.
> > How can I encrypt only the backup files without encrypting either the DB
> > or
> > the server? Thanks, Pancho
>
>|||place backup to encrypted folder
--
Aleksandar Grbic
MCDBA
"Pancho" wrote:
> Hello, I would like to password-protect or encrypt the .bak file for 3
> selected databases on a MS SQL 2000 server. The DBs are all set for Full
> recovery mode and the nighly backups run OK now using a DB Maintenance Plan.
> How can I encrypt only the backup files without encrypting either the DB or
> the server? Thanks, Pancho|||Thank you Jerry, Ben and Aleksandar. These were all helpful ideas and I
posted a helpful rating to each.
Best Regards, Pancho
"Aleksandar Grbic" wrote:
> place backup to encrypted folder
> --
> Aleksandar Grbic
> MCDBA
>
> "Pancho" wrote:
> > Hello, I would like to password-protect or encrypt the .bak file for 3
> > selected databases on a MS SQL 2000 server. The DBs are all set for Full
> > recovery mode and the nighly backups run OK now using a DB Maintenance Plan.
> > How can I encrypt only the backup files without encrypting either the DB or
> > the server? Thanks, Pancho

How to edit backup history

I am using SQL Enterprise manager to manually backup test databases on
my PC.
As time goes on the list of backups is getting long and cumbersome,
and I no longer need many of them
Is there a way I can remove entries from the backup history for a
specific database to tidy up the even increasing list.
Thanks for any help
RichardF
Hi,
Use the system procedure:-
USE msdb
go
EXEC sp_delete_backuphistory '07/20/2004'
Deletes all the history older than july 20 2004. But there is no option to
clear the histories for a sinle database
Thanks
Hari
MCDBA
"RichardF" <no.one@.no.where.com> wrote in message
news:41114edb.172348764@.msnews.microsoft.com...
> I am using SQL Enterprise manager to manually backup test databases on
> my PC.
> As time goes on the list of backups is getting long and cumbersome,
> and I no longer need many of them
> Is there a way I can remove entries from the backup history for a
> specific database to tidy up the even increasing list.
> Thanks for any help
> RichardF
|||There is a separate proc to delete the history of a single database:
EXEC msdb.dbo.sp_delete_database_backuphistory dbname
You cannot specify a date however - it will delete all the backuphistory data
JustinR
"RichardF" wrote:

> I am using SQL Enterprise manager to manually backup test databases on
> my PC.
> As time goes on the list of backups is getting long and cumbersome,
> and I no longer need many of them
> Is there a way I can remove entries from the backup history for a
> specific database to tidy up the even increasing list.
> Thanks for any help
> RichardF
>

How to easieast way Incremental Backup of SQL Database?

Hi!
We have database which we are continuously updating, and also providing to
our client. And our client also using our database and updating at their
end. We want to give next update as a Incremental only so that our changes
should be given to them and their changes also should be remain there in
their database.
Is there any way to provide Incremental Database?
Thanks
Anil Lakhani
Message posted via http://www.sqlmonster.com
Do you mean data or schema changes ?
If you mean data, then a merge replication solution may work, although you
will need to consider conflict resolution for changes made at the client
that you also make.
If you are talking about schema changes, then you will need to write custom
scripts for this, especially if you are unsure about what changes have been
made at the client site.
Regards,
Rod Colledge.
"Lakhani Anil via SQLMonster.com" <forum@.nospam.SQLMonster.com> wrote in
message news:232d63e246b74a4eb2065d9bd1f26e9d@.SQLMonster.c om...
> Hi!
> We have database which we are continuously updating, and also providing to
> our client. And our client also using our database and updating at their
> end. We want to give next update as a Incremental only so that our changes
> should be given to them and their changes also should be remain there in
> their database.
> Is there any way to provide Incremental Database?
> Thanks
> Anil Lakhani
> --
> Message posted via http://www.sqlmonster.com

How to easieast way Incremental Backup of SQL Database?

Hi!
We have database which we are continuously updating, and also providing to
our client. And our client also using our database and updating at their
end. We want to give next update as a Incremental only so that our changes
should be given to them and their changes also should be remain there in
their database.
Is there any way to provide Incremental Database?
Thanks
Anil Lakhani
Message posted via http://www.droptable.comDo you mean data or schema changes ?
If you mean data, then a merge replication solution may work, although you
will need to consider conflict resolution for changes made at the client
that you also make.
If you are talking about schema changes, then you will need to write custom
scripts for this, especially if you are unsure about what changes have been
made at the client site.
Regards,
Rod Colledge.
"Lakhani Anil via droptable.com" <forum@.nospam.droptable.com> wrote in
message news:232d63e246b74a4eb2065d9bd1f26e9d@.SQ
droptable.com...
> Hi!
> We have database which we are continuously updating, and also providing to
> our client. And our client also using our database and updating at their
> end. We want to give next update as a Incremental only so that our changes
> should be given to them and their changes also should be remain there in
> their database.
> Is there any way to provide Incremental Database?
> Thanks
> Anil Lakhani
> --
> Message posted via http://www.droptable.com

How to easieast way Incremental Backup of SQL Database?

Hi!
We have database which we are continuously updating, and also providing to
our client. And our client also using our database and updating at their
end. We want to give next update as a Incremental only so that our changes
should be given to them and their changes also should be remain there in
their database.
Is there any way to provide Incremental Database?
Thanks
Anil Lakhani
--
Message posted via http://www.sqlmonster.comDo you mean data or schema changes ?
If you mean data, then a merge replication solution may work, although you
will need to consider conflict resolution for changes made at the client
that you also make.
If you are talking about schema changes, then you will need to write custom
scripts for this, especially if you are unsure about what changes have been
made at the client site.
Regards,
Rod Colledge.
"Lakhani Anil via SQLMonster.com" <forum@.nospam.SQLMonster.com> wrote in
message news:232d63e246b74a4eb2065d9bd1f26e9d@.SQLMonster.com...
> Hi!
> We have database which we are continuously updating, and also providing to
> our client. And our client also using our database and updating at their
> end. We want to give next update as a Incremental only so that our changes
> should be given to them and their changes also should be remain there in
> their database.
> Is there any way to provide Incremental Database?
> Thanks
> Anil Lakhani
> --
> Message posted via http://www.sqlmonster.com

How to drop offline file/filegroups after piecemeal restore

I have a very large database (5 TB) with many file groups. Basically each
file group consists of one large table (70 - 80 GB). The backup structure is
set up to backup each filegroup after it's table is loaded (the data is
static) and then perform a differetial backup. The server crashed the other
day and I began the restore process. Unfortunately due to space limitations,
all of the file group backup files were not available. So I did a piecemeal
restore with the primary FG backup file and then all of the other available
FG backup files. The last differential file was restored with RECOVERY and
the database is now online. However there are about 30 file groups offline
now. How do I drop/remove these file/filegroups.
I can not remove the files with the alter database statement, this gets the
error:
Cannot add, remove, or modify a file in filegroup 'FG0001' because the
filegroup is offline
I am running SQL Server 2005 Enterprise edition (sp2)
Thanx in advance for any and all help,
Jeff Carrington
DBA
Comscore
According to Books Online, you should be able to use ALTER DATABASE ... REMOVE FILE to get rid of a
"defunkt" file and filegroup:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/055f9c6a-5c18-4942-98e7-ec918f0ff975.htm
Did you do the initial restore using the PARTIAL option?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jeff Carrington" <JeffCarrington@.discussions.microsoft.com> wrote in message
news:A3407889-76CA-45E5-89BE-19765A67639B@.microsoft.com...
>I have a very large database (5 TB) with many file groups. Basically each
> file group consists of one large table (70 - 80 GB). The backup structure is
> set up to backup each filegroup after it's table is loaded (the data is
> static) and then perform a differetial backup. The server crashed the other
> day and I began the restore process. Unfortunately due to space limitations,
> all of the file group backup files were not available. So I did a piecemeal
> restore with the primary FG backup file and then all of the other available
> FG backup files. The last differential file was restored with RECOVERY and
> the database is now online. However there are about 30 file groups offline
> now. How do I drop/remove these file/filegroups.
> I can not remove the files with the alter database statement, this gets the
> error:
> Cannot add, remove, or modify a file in filegroup 'FG0001' because the
> filegroup is offline
> I am running SQL Server 2005 Enterprise edition (sp2)
> Thanx in advance for any and all help,
> --
> Jeff Carrington
> DBA
> Comscore
|||Yes I did use the PARTIAL option when executing the first restore of the
primary file group. I read the same section in BOL, but my attempt to remove
the file gets the error stating the file group is offline.
Thanx for the help...
Jeff Carrington
"Tibor Karaszi" wrote:

> According to Books Online, you should be able to use ALTER DATABASE ... REMOVE FILE to get rid of a
> "defunkt" file and filegroup:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/055f9c6a-5c18-4942-98e7-ec918f0ff975.htm
> Did you do the initial restore using the PARTIAL option?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Jeff Carrington" <JeffCarrington@.discussions.microsoft.com> wrote in message
> news:A3407889-76CA-45E5-89BE-19765A67639B@.microsoft.com...
>
>

How to drop offline file/filegroups after piecemeal restore

I have a very large database (5 TB) with many file groups. Basically each
file group consists of one large table (70 - 80 GB). The backup structure is
set up to backup each filegroup after it's table is loaded (the data is
static) and then perform a differetial backup. The server crashed the other
day and I began the restore process. Unfortunately due to space limitations,
all of the file group backup files were not available. So I did a piecemeal
restore with the primary FG backup file and then all of the other available
FG backup files. The last differential file was restored with RECOVERY and
the database is now online. However there are about 30 file groups offline
now. How do I drop/remove these file/filegroups.
I can not remove the files with the alter database statement, this gets the
error:
Cannot add, remove, or modify a file in filegroup 'FG0001' because the
filegroup is offline
I am running SQL Server 2005 Enterprise edition (sp2)
Thanx in advance for any and all help,
--
Jeff Carrington
DBA
ComscoreAccording to Books Online, you should be able to use ALTER DATABASE ... REMOVE FILE to get rid of a
"defunkt" file and filegroup:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/055f9c6a-5c18-4942-98e7-ec918f0ff975.htm
Did you do the initial restore using the PARTIAL option?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jeff Carrington" <JeffCarrington@.discussions.microsoft.com> wrote in message
news:A3407889-76CA-45E5-89BE-19765A67639B@.microsoft.com...
>I have a very large database (5 TB) with many file groups. Basically each
> file group consists of one large table (70 - 80 GB). The backup structure is
> set up to backup each filegroup after it's table is loaded (the data is
> static) and then perform a differetial backup. The server crashed the other
> day and I began the restore process. Unfortunately due to space limitations,
> all of the file group backup files were not available. So I did a piecemeal
> restore with the primary FG backup file and then all of the other available
> FG backup files. The last differential file was restored with RECOVERY and
> the database is now online. However there are about 30 file groups offline
> now. How do I drop/remove these file/filegroups.
> I can not remove the files with the alter database statement, this gets the
> error:
> Cannot add, remove, or modify a file in filegroup 'FG0001' because the
> filegroup is offline
> I am running SQL Server 2005 Enterprise edition (sp2)
> Thanx in advance for any and all help,
> --
> Jeff Carrington
> DBA
> Comscore|||Yes I did use the PARTIAL option when executing the first restore of the
primary file group. I read the same section in BOL, but my attempt to remove
the file gets the error stating the file group is offline.
Thanx for the help...
--
Jeff Carrington
"Tibor Karaszi" wrote:
> According to Books Online, you should be able to use ALTER DATABASE ... REMOVE FILE to get rid of a
> "defunkt" file and filegroup:
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/055f9c6a-5c18-4942-98e7-ec918f0ff975.htm
> Did you do the initial restore using the PARTIAL option?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Jeff Carrington" <JeffCarrington@.discussions.microsoft.com> wrote in message
> news:A3407889-76CA-45E5-89BE-19765A67639B@.microsoft.com...
> >I have a very large database (5 TB) with many file groups. Basically each
> > file group consists of one large table (70 - 80 GB). The backup structure is
> > set up to backup each filegroup after it's table is loaded (the data is
> > static) and then perform a differetial backup. The server crashed the other
> > day and I began the restore process. Unfortunately due to space limitations,
> > all of the file group backup files were not available. So I did a piecemeal
> > restore with the primary FG backup file and then all of the other available
> > FG backup files. The last differential file was restored with RECOVERY and
> > the database is now online. However there are about 30 file groups offline
> > now. How do I drop/remove these file/filegroups.
> >
> > I can not remove the files with the alter database statement, this gets the
> > error:
> > Cannot add, remove, or modify a file in filegroup 'FG0001' because the
> > filegroup is offline
> >
> > I am running SQL Server 2005 Enterprise edition (sp2)
> >
> > Thanx in advance for any and all help,
> > --
> > Jeff Carrington
> > DBA
> > Comscore
>
>

How to do complete DB backup using a warm standby server

I've set up log shipping between a production server and a secondary server, all work fine. Now I want to use the secondary server or the warm standby server to do complete database backup every night, since I want to off load the backup from the producti
on server, however, I get this error message for the DB backup job:
"
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3036: [Microsoft][ODBC SQL Server Driver][SQL Server]Database 'aspnetforums' is in warm-standby state (set by executing RESTORE WITH STANDBY) and cannot be backed up until the entire load sequence is comple
ted.
[Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is terminating abnormally.
"
My settings are:
the restore job runs from 10:15am till 8:45 am next day, then the DB backup job starts at 9:10am. So the two jobs are not overlapping each other. However, even if I manually run the back up job at times after 9:10am, it fails with the same error message.
I am wondering if there is any extra setting I need to set to make it work.
Thank you for any help
Yi,
you'd need to Recover your database on the standby server for this to work,
which then prevents the logshipping chain from continuing. If you are sure
there is no access to the database during this period, you could Recover the
standby database then back it up, drop the logshipping maintenance plan,
then readd logshipping with the option that the initial backup has been made
and transferred.
HTH,
Paul Ibison

how to do backup of msde?

hello,
we use Protection Pilot (from McAffee), it installs a MSDE that it uses
to store info about clients.
so, I need to do a backup of the MSDE to send to their tech support
people but their backup utility isn't working.
They've tried for about 2 weeks now and it's not working.
I asked the tech person about doing backup via command line and she
looked into it, but it started asking for password!
Neither of use knew about any MSDE passwords, so that didn't work either.
I figured I'd ask here, what is a quick/easy way to do a backup of a
MSDE via command line (taking into consideration I don't know what
password it's asking for). I guess the sa account can be used, but how
can I reset that account's password so the backups will work?
thanks,
dave
In message <#3qDpRkjFHA.1204@.TK2MSFTNGP12.phx.gbl>, pheonix1t
<nothing@.nothing.gone> writes
>hello,
>we use Protection Pilot (from McAffee), it installs a MSDE that it uses
>to store info about clients.
>so, I need to do a backup of the MSDE to send to their tech support
>people but their backup utility isn't working.
>They've tried for about 2 weeks now and it's not working.
>I asked the tech person about doing backup via command line and she
>looked into it, but it started asking for password!
>Neither of use knew about any MSDE passwords, so that didn't work either.
This sounds a little fishy if you ask me.
The instance of MSDE they installed for use with their software must
have got one or more user accounts assigned to it and these have
probably been assigned passwords. At a very minimum it MUST have an "sa"
user account. If your normal PC's Administrator password does not work
then it would suggest MSDE is running in SQL User Only mode. This then
brings the point that McAffee MUST know the passwords used when
installing and configuring the installation. It is therefore more likely
that they do NOT want to tell YOU (probably for very good reasons,
however, if their own tools ain't working ...).

>I figured I'd ask here, what is a quick/easy way to do a backup of a
>MSDE via command line (taking into consideration I don't know what
>password it's asking for). I guess the sa account can be used, but how
>can I reset that account's password so the backups will work?
>thanks,
>dave
One possible solution would be to use OSQL to detach their database from
the instance of MSDE and then send them the MDF & LDF files (they will
need both). They could then re-attach the database at their end to fix
the problems.
Another, slightly more risky move, would be to stop MSDE from running
(ie: NET STOP [services]) and then copy the MDF and LDF files to them.
Again, they will need both files. Don't forget to restart the MSDE
services after the files have been copied.
Andrew D. Newbould E-Mail: newsgroups@.NOSPAMzadsoft.com
ZAD Software Systems Web : www.zadsoft.com
|||hi Dave,
pheonix1t wrote:
> hello,
> we use Protection Pilot (from McAffee), it installs a MSDE that it
> uses to store info about clients.
> so, I need to do a backup of the MSDE to send to their tech support
> people but their backup utility isn't working.
> They've tried for about 2 weeks now and it's not working.
> I asked the tech person about doing backup via command line and she
> looked into it, but it started asking for password!
> Neither of use knew about any MSDE passwords, so that didn't work
> either.
> I figured I'd ask here, what is a quick/easy way to do a backup of a
> MSDE via command line (taking into consideration I don't know what
> password it's asking for). I guess the sa account can be used, but
> how can I reset that account's password so the backups will work?
if they did not deny access to the MSDE instance to local administrators you
can log in as one of them and connect to the instance via a trusted
connection, not requiring a standard SQL Server login's password...
once connected (for instance via oSql.exe command line utility,
http://msdn.microsoft.com/library/de..._osql_1wxl.asp ,
http://support.microsoft.com/default...;EN-US;q325003)
c:\...\osql.exe -E
you get a command prompt like
1>
where you can execute the backup statement as desired...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Why is it more risky to copy the files after stopping the server, rather than
after a detach. I am asking because I want to in part use this as a method
for backup and restore.
Periodically, I want to take copies of the mdf and log files for later
attachment under a new database name - but without detaching the existing
database. So since I will stop the server anyway during backup, I thought it
unnecessary to go through an extra step of detach/attach of the original that
still need to run after the backup.
Regards
Bo
"Andrew D. Newbould" wrote:

> In message <#3qDpRkjFHA.1204@.TK2MSFTNGP12.phx.gbl>, pheonix1t
> <nothing@.nothing.gone> writes
> This sounds a little fishy if you ask me.
> The instance of MSDE they installed for use with their software must
> have got one or more user accounts assigned to it and these have
> probably been assigned passwords. At a very minimum it MUST have an "sa"
> user account. If your normal PC's Administrator password does not work
> then it would suggest MSDE is running in SQL User Only mode. This then
> brings the point that McAffee MUST know the passwords used when
> installing and configuring the installation. It is therefore more likely
> that they do NOT want to tell YOU (probably for very good reasons,
> however, if their own tools ain't working ...).
>
> One possible solution would be to use OSQL to detach their database from
> the instance of MSDE and then send them the MDF & LDF files (they will
> need both). They could then re-attach the database at their end to fix
> the problems.
> Another, slightly more risky move, would be to stop MSDE from running
> (ie: NET STOP [services]) and then copy the MDF and LDF files to them.
> Again, they will need both files. Don't forget to restart the MSDE
> services after the files have been copied.
> --
> Andrew D. Newbould E-Mail: newsgroups@.NOSPAMzadsoft.com
> ZAD Software Systems Web : www.zadsoft.com
>
|||I can't give you a complete answer as there are others better suited to
explain however in general terms when you detach a database the logs
files are flushed, statistics updated and completed transactions
committed to the database. In other words a clean-up process is
performed ready to transport the database.
When you stop the server processes the clean-up routines are not
performed to the extent that a detach does. The idea being the server
can continue where it left off when it comes back on-line.
In my experience most backup regimes involve stopping the server
processes, backing up all MDF & LDF database files required and then
restarting the server processes. This tends to be a faster process than
scheduling backups to tape etc and causes the databases to be off-line
for the shortest periods.
Don't get me wrong, you can do on-line backups as well via Enterprise
Manager but many DBA's I know won't trust the scheduling as it can get
broken. Some backup software like Veritas can perform on-line backups as
well however be careful in your choice as some only backup the DATA and
not the Structure etc so in a failure situation its no good restoring
data when you don't have the correct structure first.
Kind Regards
Andrew D. Newbould
In message <F662A14E-E095-437D-BC05-0958E00BD903@.microsoft.com>, bo
<bo@.discussions.microsoft.com> writes[vbcol=seagreen]
>Why is it more risky to copy the files after stopping the server, rather than
>after a detach. I am asking because I want to in part use this as a method
>for backup and restore.
>Periodically, I want to take copies of the mdf and log files for later
>attachment under a new database name - but without detaching the existing
>database. So since I will stop the server anyway during backup, I thought it
>unnecessary to go through an extra step of detach/attach of the original that
>still need to run after the backup.
>Regards
>Bo
>"Andrew D. Newbould" wrote:
Andrew D. Newbould E-Mail: newsgroups@.NOSPAMzadsoft.com
ZAD Software Systems Web : www.zadsoft.com

How to do backup in Cluster

Hi,

I have a cluster set up and working fine. I am in a stage where I need to do the backups. I have added 4 disks as dependecies to the sql service for the instance. The backups should not be on these disks. When I do a simple backup I can see only these 4 disks so my question is how do I do the backup to fileshare or other disks that are not partof the cluster. I have 3 virtual servers so what wil be the best thing to do for backup

Thanks

Adminanup,

What is preventing you from backing up to a fileshare?

-Matt

|||

Is there a reason for not sending your backups to disks which are part of your cluster group?

Certainly you wouldn't send backups to the same disk as the database is stored on, but another disk in the same group should work just fine.

|||

Thanks

It was a firewall issue and could not backup to a fileshare.

Like you have mentioned what is the best practices in backing up DB in cluster

1. fileshare

2. add a disk resouce(which I assume will fail evertime SQL will failover) to the SQL group in cluster and add as dependency

Thanks

|||I would choose #2 for performance reasons.|||

Thanks Kevin,

I would think backing to fileshare would be efficient as in case of failover it has to failover the backup disk as a resouce so can you please give me some insight as why this is recommended as compnared to #1

|||

Hello,

The failover of a disk resource is usually very quick (and given it is used for backup which can be reissued, thus not impacting service, is not time critical), so the duration of failover should not be an issue. I would think #2 was chosen for performance as it does not involve moving data across the network as per option 1.

Cheers,
Rob

How to do backup in Cluster

Hi,

I have a cluster set up and working fine. I am in a stage where I need to do the backups. I have added 4 disks as dependecies to the sql service for the instance. The backups should not be on these disks. When I do a simple backup I can see only these 4 disks so my question is how do I do the backup to fileshare or other disks that are not partof the cluster. I have 3 virtual servers so what wil be the best thing to do for backup

Thanks

Adminanup,

What is preventing you from backing up to a fileshare?

-Matt

|||

Is there a reason for not sending your backups to disks which are part of your cluster group?

Certainly you wouldn't send backups to the same disk as the database is stored on, but another disk in the same group should work just fine.

|||

Thanks

It was a firewall issue and could not backup to a fileshare.

Like you have mentioned what is the best practices in backing up DB in cluster

1. fileshare

2. add a disk resouce(which I assume will fail evertime SQL will failover) to the SQL group in cluster and add as dependency

Thanks

|||I would choose #2 for performance reasons.|||

Thanks Kevin,

I would think backing to fileshare would be efficient as in case of failover it has to failover the backup disk as a resouce so can you please give me some insight as why this is recommended as compnared to #1

|||

Hello,

The failover of a disk resource is usually very quick (and given it is used for backup which can be reissued, thus not impacting service, is not time critical), so the duration of failover should not be an issue. I would think #2 was chosen for performance as it does not involve moving data across the network as per option 1.

Cheers,
Rob

how to do automatic backup for transaction log and truncate it?

Hi,
For example, i have set my log files to 2G maximum, if
reach 1.5G, i want the sql server to help me truncate the
log automatic. Can anyone help me?
Thanks a lot
regards,
florence
You could add a second step to your transaction log backup job that runs once
the backup step runs successfully:
USE Database_Name
GO
DBCC SHRINKFILE (Log_File_Name, 0)
GO
"florencelee" wrote:

> Hi,
>
> For example, i have set my log files to 2G maximum, if
> reach 1.5G, i want the sql server to help me truncate the
> log automatic. Can anyone help me?
> Thanks a lot
> regards,
> florence
>
|||hi..
create a Backup device for temp usage.. ( example name .. logresizebackup
Create Proc master.dbo._resizemydb
as
backup log ecatalog to logresizebackup
create a job that runs master.dbo._resizemydb dont shedule
Create an Alert ..
type must be : SQL Server Performance Condition alert
select SQL server databases as Object
select Data file(s) size as counter
select DB name instance as
and alert if Counter must be rises above and give the value of 15000000
then
in to the response tab.. Select execute Job and Select our Job that ll
execute master.dbo._resizemydb
thats it
"florencelee" <florencelee@.visualsolutions.com.my> wrote in message
news:09e401c4ac62$6165a3c0$a301280a@.phx.gbl...
> Hi,
>
> For example, i have set my log files to 2G maximum, if
> reach 1.5G, i want the sql server to help me truncate the
> log automatic. Can anyone help me?
> Thanks a lot
> regards,
> florence
|||If you only want to truncate the log and not retain the information that
will allow you to do point in time restore you might want to think about
putting the recovery mode to SIMPLE. Then it will truncate the log when it
reaches 70% full which is pretty close to what you are after.
Andrew J. Kelly SQL MVP
"florencelee" <florencelee@.visualsolutions.com.my> wrote in message
news:09e401c4ac62$6165a3c0$a301280a@.phx.gbl...
> Hi,
>
> For example, i have set my log files to 2G maximum, if
> reach 1.5G, i want the sql server to help me truncate the
> log automatic. Can anyone help me?
> Thanks a lot
> regards,
> florence

how to do automatic backup for transaction log and truncate it?

Hi,
For example, i have set my log files to 2G maximum, if
reach 1.5G, i want the sql server to help me truncate the
log automatic. Can anyone help me?
Thanks a lot
regards,
florenceYou could add a second step to your transaction log backup job that runs once
the backup step runs successfully:
USE Database_Name
GO
DBCC SHRINKFILE (Log_File_Name, 0)
GO
"florencelee" wrote:
> Hi,
>
> For example, i have set my log files to 2G maximum, if
> reach 1.5G, i want the sql server to help me truncate the
> log automatic. Can anyone help me?
> Thanks a lot
> regards,
> florence
>|||hi..
create a Backup device for temp usage.. ( example name .. logresizebackup
Create Proc master.dbo._resizemydb
as
backup log ecatalog to logresizebackup
create a job that runs master.dbo._resizemydb dont shedule
Create an Alert ..
type must be : SQL Server Performance Condition alert
select SQL server databases as Object
select Data file(s) size as counter
select DB name instance as
and alert if Counter must be rises above and give the value of 15000000
then
in to the response tab.. Select execute Job and Select our Job that ll
execute master.dbo._resizemydb
thats it
"florencelee" <florencelee@.visualsolutions.com.my> wrote in message
news:09e401c4ac62$6165a3c0$a301280a@.phx.gbl...
> Hi,
>
> For example, i have set my log files to 2G maximum, if
> reach 1.5G, i want the sql server to help me truncate the
> log automatic. Can anyone help me?
> Thanks a lot
> regards,
> florence|||If you only want to truncate the log and not retain the information that
will allow you to do point in time restore you might want to think about
putting the recovery mode to SIMPLE. Then it will truncate the log when it
reaches 70% full which is pretty close to what you are after.
--
Andrew J. Kelly SQL MVP
"florencelee" <florencelee@.visualsolutions.com.my> wrote in message
news:09e401c4ac62$6165a3c0$a301280a@.phx.gbl...
> Hi,
>
> For example, i have set my log files to 2G maximum, if
> reach 1.5G, i want the sql server to help me truncate the
> log automatic. Can anyone help me?
> Thanks a lot
> regards,
> florence

how to do a complete backup of SQL Server 2000

Hi,
we are starting to deploy SQL Server 2000 and in the SQL
Server Book it said that regular backups of SQL Server are
important in order to delete transaction logs.
I have done a backup of 2 of our databases, but the
transaction logs remain the same size - why is that?
The second thing I would like to ask, is backup possible
only for single databases? And how can I for example
backup the security logins,...?
Thanks for reply.
Bodo> I have done a backup of 2 of our databases, but the
> transaction logs remain the same size - why is that?
A backup of the database does nothing to the data within the transaction
log, you need to issue backup log command for this. If you do not require
transactional recovery, set your database to simple recovery mode.
> The second thing I would like to ask, is backup possible
> only for single databases?
Yes, backup database, see "BACKUP, BACKUP (described)" in BOL for syntax
>And how can I for example
> backup the security logins,...?
This page is the best for that, it will script the permissions, users, etc.
http://support.microsoft.com/default.aspx?scid=kb;en-us;246133
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"bodo" <bodobecker@.hotmail.com> wrote in message
news:019001c370c4$60f81d80$a501280a@.phx.gbl...
> Hi,
> we are starting to deploy SQL Server 2000 and in the SQL
> Server Book it said that regular backups of SQL Server are
> important in order to delete transaction logs.
> I have done a backup of 2 of our databases, but the
> transaction logs remain the same size - why is that?
> The second thing I would like to ask, is backup possible
> only for single databases? And how can I for example
> backup the security logins,...?
> Thanks for reply.
> Bodo

How to do a backup with sqlmaint in a different directory

Somobody know how to say to sqlmaint in which directory do the backup to avo
id use the default directory?Go to database maintenance plans in enterprise manager and edit your plan
there. There's an option to specify the directory where you want your
backups stored. If you're storing backup files on a different server, use a
UNC path, for example \\remoteserver\sharename
Carlos E. Rojas
SQL Server MVP
Co-Author SQL Server 2000 programming by Example
"Alfonso Gmez" <anonymous@.discussions.microsoft.com> wrote in message
news:61C9EEB0-9639-48A2-B0E4-6C2AAC7A9330@.microsoft.com...
> Somobody know how to say to sqlmaint in which directory do the backup to
avoid use the default directory?