sql backup files: February 2012

Saturday, February 25, 2012

Backup of Standby databases

I'm running an offsite replica of our database using Log shipping.
This is working very well, but I'd like a way to take a backup of the
replica without
breaking the log shipping. Making a backup at the hosting center and copying
to our development hq takes over a day.
I've got the full database at our shop now, being updated constantly with
log shipping. A backup operation on a database that is in standby mode
fails. I can restore ... with recovery, backup, but then I can't continue to
log ship.
Even though it really isn't documented, "backup database dbname with standby
"= 'xxx.tuf' " works fine. I've used it to switch my secondary to primary
and so forth. I'm wondering if I can
restore ... with recovery
backup the database
backup with standby
This would give me a backup, and put the database back into standby so it
could continue to apply logs, but I'm pretty sure that the backup operation
would write to the log and cause the LSN's to get out of sync, and so the
next log restore would not happen correctly.
My other idea is to stop the server, copy the database and log file, and
then reattach these copies as a new database name. I'm pretty sure this idea
is going to work, but I have to stop my server, and it is a bit manual.
Is there anyway to backup a database while it is in standby or no recovery
mode?
thanks
I doubt what you suggest would work, because the LSNs would be out of sync.
Most people use the normal backups of the orginal database, and do not worry
about backups of the log shipped db.. Will that not work for you?
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
"et" <et@.discussions.microsoft.com> wrote in message
news:96F275B6-D92F-46E9-99E3-996DCB59EADC@.microsoft.com...
> I'm running an offsite replica of our database using Log shipping.
> This is working very well, but I'd like a way to take a backup of the
> replica without
> breaking the log shipping. Making a backup at the hosting center and
copying
> to our development hq takes over a day.
> I've got the full database at our shop now, being updated constantly with
> log shipping. A backup operation on a database that is in standby mode
> fails. I can restore ... with recovery, backup, but then I can't continue
to
> log ship.
> Even though it really isn't documented, "backup database dbname with
standby
> "= 'xxx.tuf' " works fine. I've used it to switch my secondary to
primary
> and so forth. I'm wondering if I can
> restore ... with recovery
> backup the database
> backup with standby
> This would give me a backup, and put the database back into standby so it
> could continue to apply logs, but I'm pretty sure that the backup
operation
> would write to the log and cause the LSN's to get out of sync, and so the
> next log restore would not happen correctly.
> My other idea is to stop the server, copy the database and log file, and
> then reattach these copies as a new database name. I'm pretty sure this
idea
> is going to work, but I have to stop my server, and it is a bit manual.
> Is there anyway to backup a database while it is in standby or no recovery
> mode?
> thanks
>

Backup of Standby databases

I'm running an offsite replica of our database using Log shipping.
This is working very well, but I'd like a way to take a backup of the
replica without
breaking the log shipping. Making a backup at the hosting center and copying
to our development hq takes over a day.
I've got the full database at our shop now, being updated constantly with
log shipping. A backup operation on a database that is in standby mode
fails. I can restore ... with recovery, backup, but then I can't continue to
log ship.
Even though it really isn't documented, "backup database dbname with standby
"= 'xxx.tuf' " works fine. I've used it to switch my secondary to primary
and so forth. I'm wondering if I can
restore ... with recovery
backup the database
backup with standby
This would give me a backup, and put the database back into standby so it
could continue to apply logs, but I'm pretty sure that the backup operation
would write to the log and cause the LSN's to get out of sync, and so the
next log restore would not happen correctly.
My other idea is to stop the server, copy the database and log file, and
then reattach these copies as a new database name. I'm pretty sure this idea
is going to work, but I have to stop my server, and it is a bit manual.
Is there anyway to backup a database while it is in standby or no recovery
mode?
thanksI doubt what you suggest would work, because the LSNs would be out of sync.
Most people use the normal backups of the orginal database, and do not worry
about backups of the log shipped db.. Will that not work for you?
--
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
"et" <et@.discussions.microsoft.com> wrote in message
news:96F275B6-D92F-46E9-99E3-996DCB59EADC@.microsoft.com...
> I'm running an offsite replica of our database using Log shipping.
> This is working very well, but I'd like a way to take a backup of the
> replica without
> breaking the log shipping. Making a backup at the hosting center and
copying
> to our development hq takes over a day.
> I've got the full database at our shop now, being updated constantly with
> log shipping. A backup operation on a database that is in standby mode
> fails. I can restore ... with recovery, backup, but then I can't continue
to
> log ship.
> Even though it really isn't documented, "backup database dbname with
standby
> "= 'xxx.tuf' " works fine. I've used it to switch my secondary to
primary
> and so forth. I'm wondering if I can
> restore ... with recovery
> backup the database
> backup with standby
> This would give me a backup, and put the database back into standby so it
> could continue to apply logs, but I'm pretty sure that the backup
operation
> would write to the log and cause the LSN's to get out of sync, and so the
> next log restore would not happen correctly.
> My other idea is to stop the server, copy the database and log file, and
> then reattach these copies as a new database name. I'm pretty sure this
idea
> is going to work, but I have to stop my server, and it is a bit manual.
> Is there anyway to backup a database while it is in standby or no recovery
> mode?
> thanks
>

Backup of Standby databases

I'm running an offsite replica of our database using Log shipping.
This is working very well, but I'd like a way to take a backup of the
replica without
breaking the log shipping. Making a backup at the hosting center and copying
to our development hq takes over a day.
I've got the full database at our shop now, being updated constantly with
log shipping. A backup operation on a database that is in standby mode
fails. I can restore ... with recovery, backup, but then I can't continue t
o
log ship.
Even though it really isn't documented, "backup database dbname with standby
"= 'xxx.tuf' " works fine. I've used it to switch my secondary to primary
and so forth. I'm wondering if I can
restore ... with recovery
backup the database
backup with standby
This would give me a backup, and put the database back into standby so it
could continue to apply logs, but I'm pretty sure that the backup operation
would write to the log and cause the LSN's to get out of sync, and so the
next log restore would not happen correctly.
My other idea is to stop the server, copy the database and log file, and
then reattach these copies as a new database name. I'm pretty sure this idea
is going to work, but I have to stop my server, and it is a bit manual.
Is there anyway to backup a database while it is in standby or no recovery
mode?
thanksI doubt what you suggest would work, because the LSNs would be out of sync.
Most people use the normal backups of the orginal database, and do not worry
about backups of the log shipped db.. Will that not work for you?
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
"et" <et@.discussions.microsoft.com> wrote in message
news:96F275B6-D92F-46E9-99E3-996DCB59EADC@.microsoft.com...
> I'm running an offsite replica of our database using Log shipping.
> This is working very well, but I'd like a way to take a backup of the
> replica without
> breaking the log shipping. Making a backup at the hosting center and
copying
> to our development hq takes over a day.
> I've got the full database at our shop now, being updated constantly with
> log shipping. A backup operation on a database that is in standby mode
> fails. I can restore ... with recovery, backup, but then I can't continue
to
> log ship.
> Even though it really isn't documented, "backup database dbname with
standby
> "= 'xxx.tuf' " works fine. I've used it to switch my secondary to
primary
> and so forth. I'm wondering if I can
> restore ... with recovery
> backup the database
> backup with standby
> This would give me a backup, and put the database back into standby so it
> could continue to apply logs, but I'm pretty sure that the backup
operation
> would write to the log and cause the LSN's to get out of sync, and so the
> next log restore would not happen correctly.
> My other idea is to stop the server, copy the database and log file, and
> then reattach these copies as a new database name. I'm pretty sure this
idea
> is going to work, but I have to stop my server, and it is a bit manual.
> Is there anyway to backup a database while it is in standby or no recovery
> mode?
> thanks
>

Backup of Sql tables

I am a serious newbie. How do you backup only certain
tables in a database? Thank you.
Angela
There is no way to backup a set of files on a databases, unless these files
are on a different filegroup. So if you really want to do this you will
need a secondary filegroup where either the primary or secondary filegroup
contains the subset of tables you want to backup.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Angella Bennet" <anonymous@.discussions.microsoft.com> wrote in message
news:f33d01c43db6$40db90d0$a601280a@.phx.gbl...
> I am a serious newbie. How do you backup only certain
> tables in a database? Thank you.
> Angela
|||You export them. Using DTS, BCP or some other export method.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Angella Bennet" <anonymous@.discussions.microsoft.com> wrote in message
news:f33d01c43db6$40db90d0$a601280a@.phx.gbl...
> I am a serious newbie. How do you backup only certain
> tables in a database? Thank you.
> Angela
|||Thank you so much Tibor. That's what I thought but was
not sure. I will practice doing that and restoring
somewhere else to be sure. Thanks.
Angel

>--Original Message--
>You export them. Using DTS, BCP or some other export
method.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Angella Bennet" <anonymous@.discussions.microsoft.com>
wrote in message
>news:f33d01c43db6$40db90d0$a601280a@.phx.gbl...
>
>.
>

Backup of Sql tables

I am a serious newbie. How do you backup only certain
tables in a database? Thank you.
AngelaThere is no way to backup a set of files on a databases, unless these files
are on a different filegroup. So if you really want to do this you will
need a secondary filegroup where either the primary or secondary filegroup
contains the subset of tables you want to backup.
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Angella Bennet" <anonymous@.discussions.microsoft.com> wrote in message
news:f33d01c43db6$40db90d0$a601280a@.phx.gbl...
> I am a serious newbie. How do you backup only certain
> tables in a database? Thank you.
> Angela|||You export them. Using DTS, BCP or some other export method.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Angella Bennet" <anonymous@.discussions.microsoft.com> wrote in message
news:f33d01c43db6$40db90d0$a601280a@.phx.gbl...
> I am a serious newbie. How do you backup only certain
> tables in a database? Thank you.
> Angela|||Thank you so much Tibor. That's what I thought but was
not sure. I will practice doing that and restoring
somewhere else to be sure. Thanks.
Angel

>--Original Message--
>You export them. Using DTS, BCP or some other export
method.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Angella Bennet" <anonymous@.discussions.microsoft.com>
wrote in message
>news:f33d01c43db6$40db90d0$a601280a@.phx.gbl...
>
>.
>

Backup of Sql tables

I am a serious newbie. How do you backup only certain
tables in a database? Thank you.
AngelaThere is no way to backup a set of files on a databases, unless these files
are on a different filegroup. So if you really want to do this you will
need a secondary filegroup where either the primary or secondary filegroup
contains the subset of tables you want to backup.
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Angella Bennet" <anonymous@.discussions.microsoft.com> wrote in message
news:f33d01c43db6$40db90d0$a601280a@.phx.gbl...
> I am a serious newbie. How do you backup only certain
> tables in a database? Thank you.
> Angela|||You export them. Using DTS, BCP or some other export method.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Angella Bennet" <anonymous@.discussions.microsoft.com> wrote in message
news:f33d01c43db6$40db90d0$a601280a@.phx.gbl...
> I am a serious newbie. How do you backup only certain
> tables in a database? Thank you.
> Angela|||Thank you so much Tibor. That's what I thought but was
not sure. I will practice doing that and restoring
somewhere else to be sure. Thanks.
Angel
>--Original Message--
>You export them. Using DTS, BCP or some other export
method.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Angella Bennet" <anonymous@.discussions.microsoft.com>
wrote in message
>news:f33d01c43db6$40db90d0$a601280a@.phx.gbl...
>> I am a serious newbie. How do you backup only certain
>> tables in a database? Thank you.
>> Angela
>
>.
>

Backup of SQL Server 2005 database to local P.C ?

Hi,
Could anyone please explain how I could backup a SQL Server database
on a server to my local P.C. ?
I am running Microsoft SQL Server 2005 Management Studio on my P.C.
I need to move the database to another server, but the current hosting
company are very inefficient (slow) at providing me with a database
backup file (.bak).
Is there any way that this can be done with T-SQL or C# or VB.Net
etc ?
I'd be grateful for any help.
David
Export the database on your hosting company to your local machine using
SSMS.
Ekrem nsoy
<Fresh_Air_Rider@.hotmail.com> wrote in message
news:885bc7de-4a19-42d9-821a-7ca513acea11@.s19g2000prg.googlegroups.com...
> Hi,
> Could anyone please explain how I could backup a SQL Server database
> on a server to my local P.C. ?
> I am running Microsoft SQL Server 2005 Management Studio on my P.C.
> I need to move the database to another server, but the current hosting
> company are very inefficient (slow) at providing me with a database
> backup file (.bak).
> Is there any way that this can be done with T-SQL or C# or VB.Net
> etc ?
> I'd be grateful for any help.
> David
|||Hi Ekrem,
Thanks for your suggestion.
The problem with the Import / Export wizard is that it doesn't include
primary keys, identities, default column values and foreign keys etc. I tried
creating the tables on my P.C. using a script, which works fine, but then
when I import the data, it resets all of the identity values, which is why I
really need some way of saving the backup (.bak) files to my P.C.
|||Sorry David, I don't know another way.
Ekrem ?nsoy
"David" <David@.discussions.microsoft.com> wrote in message
news:FC81D22C-8265-4BD5-8B9E-3EEA4F1A07DF@.microsoft.com...
> Hi Ekrem,
> Thanks for your suggestion.
> The problem with the Import / Export wizard is that it doesn't include
> primary keys, identities, default column values and foreign keys etc. I
> tried
> creating the tables on my P.C. using a script, which works fine, but then
> when I import the data, it resets all of the identity values, which is why
> I
> really need some way of saving the backup (.bak) files to my P.C.
|||Hello,
You can use SQL Server Management Studio to create the backup qutie easily:
1) Select the database you want to backup
2) Right click the database to backup
3) Select Tasks and then Backup
Another option is to just query your database with the following syntax:
BACKUP DATABASE
You can find out more about BACKUP DATABASE via SQL Server Books Online
(from Microsoft). This feature is available in SQL Server 2000 or later.
Regards,
James Simpson
Straightway Technologies Inc.
|||Hi James,
Many thanks indeed for taking the time to reply to my posting.
I think your suggestion will only allow the backup file to be stored
on the server and I was looking for a solution for storing the backup
file on my local P.C.
I tried simply copying the data from the destination machine to the
source machine but then hit the following bug in SQL Server 2005
http://rip747.wordpress.com/2007/10/01/sql-server-2005-import-export-reset-identity-keys-no-workaround-2005-sucks-period-the-end/
|||Fresh_Air_Rider@.hotmail.com wrote:
> Hi James,
> Many thanks indeed for taking the time to reply to my posting.
> I think your suggestion will only allow the backup file to be stored
> on the server and I was looking for a solution for storing the backup
> file on my local P.C.
> I tried simply copying the data from the destination machine to the
> source machine but then hit the following bug in SQL Server 2005
> http://rip747.wordpress.com/2007/10/01/sql-server-2005-import-export-reset-identity-keys-no-workaround-2005-sucks-period-the-end/
>
If I understand your issue correctly, you want to create a backup of
your database and copy that backup to your local machine. If so, then
you need to have access to a file share on the server, or be able to
access FTP on the server, or - be able to share a folder on your system.
If you have access to a file share on the server, just backup to that
file share and then copy the backup file. The same if you have access
to FTP on that server (not sure here, but maybe since you said you are
using a hosting service).
Finally, if you can create a share on your machine that the SQL Server
can see you can then backup using UNC to your file share.
Since this is a hosting service, I do not think you have any other options.
HTH,
Jeff
|||Not sure if this will help or not, but I found it useful.
http://www.associatedcontent.com/article/267055/how_to_setup_sql_server_2005_backups.html
On Dec 26, 3:12 pm, Jeffrey Williams <jeff.williams3...@.verizon.net>
wrote:
> Fresh_Air_Ri...@.hotmail.com wrote:
>
>
> If I understand your issue correctly, you want to create a backup of
> your database and copy that backup to your local machine. If so, then
> you need to have access to a file share on the server, or be able to
> access FTP on the server, or - be able to share a folder on your system.
> If you have access to a file share on the server, just backup to that
> file share and then copy the backup file. The same if you have access
> to FTP on that server (not sure here, but maybe since you said you are
> using a hosting service).
> Finally, if you can create a share on your machine that the SQL Server
> can see you can then backup using UNC to your file share.
> Since this is a hosting service, I do not think you have any other options.
> HTH,
> Jeff
|||Hi Jeff,
Many thanks indeed for taking the trouble to reply to my posting. I do
have FTP access, but unfortunately not to the directory where the SQL
Server backups are saved.
I like your suggestion of a creating a share on my machine that the
SQL Server can see you can then backup using UNC to my file share. I
might give that one a go.
I understand that SQL Server 2008 is due to be launched at the end
(27th) of Feb and hopefully Microsoft will have fixed all the bugs in
the Import / Export wizard, including the one that resets all the
identity column values.
Thanks again
David
|||Fresh_Air_Rider@.hotmail.com wrote:
> Hi Jeff,
> Many thanks indeed for taking the trouble to reply to my posting. I do
> have FTP access, but unfortunately not to the directory where the SQL
> Server backups are saved.
> I like your suggestion of a creating a share on my machine that the
> SQL Server can see you can then backup using UNC to my file share. I
> might give that one a go.
> I understand that SQL Server 2008 is due to be launched at the end
> (27th) of Feb and hopefully Microsoft will have fixed all the bugs in
> the Import / Export wizard, including the one that resets all the
> identity column values.
> Thanks again
> David
>
>
If you have access to FTP - can you create a new backup to the same
folder on the server? I don't see why not - but really depends on how
the hosting company set this up. I would try that first since it does
not require changing anything.

Backup of SQL Server 2005 database to local P.C ?

Hi,
Could anyone please explain how I could backup a SQL Server database
on a server to my local P.C. ?
I am running Microsoft SQL Server 2005 Management Studio on my P.C.
I need to move the database to another server, but the current hosting
company are very inefficient (slow) at providing me with a database
backup file (.bak).
Is there any way that this can be done with T-SQL or C# or VB.Net
etc ?
I'd be grateful for any help.
DavidExport the database on your hosting company to your local machine using
SSMS.
--
Ekrem Önsoy
<Fresh_Air_Rider@.hotmail.com> wrote in message
news:885bc7de-4a19-42d9-821a-7ca513acea11@.s19g2000prg.googlegroups.com...
> Hi,
> Could anyone please explain how I could backup a SQL Server database
> on a server to my local P.C. ?
> I am running Microsoft SQL Server 2005 Management Studio on my P.C.
> I need to move the database to another server, but the current hosting
> company are very inefficient (slow) at providing me with a database
> backup file (.bak).
> Is there any way that this can be done with T-SQL or C# or VB.Net
> etc ?
> I'd be grateful for any help.
> David|||Hi Ekrem,
Thanks for your suggestion.
The problem with the Import / Export wizard is that it doesn't include
primary keys, identities, default column values and foreign keys etc. I tried
creating the tables on my P.C. using a script, which works fine, but then
when I import the data, it resets all of the identity values, which is why I
really need some way of saving the backup (.bak) files to my P.C.|||Sorry David, I don't know another way.
--
Ekrem Ã?nsoy
"David" <David@.discussions.microsoft.com> wrote in message
news:FC81D22C-8265-4BD5-8B9E-3EEA4F1A07DF@.microsoft.com...
> Hi Ekrem,
> Thanks for your suggestion.
> The problem with the Import / Export wizard is that it doesn't include
> primary keys, identities, default column values and foreign keys etc. I
> tried
> creating the tables on my P.C. using a script, which works fine, but then
> when I import the data, it resets all of the identity values, which is why
> I
> really need some way of saving the backup (.bak) files to my P.C.|||Hello,
You can use SQL Server Management Studio to create the backup qutie easily:
1) Select the database you want to backup
2) Right click the database to backup
3) Select Tasks and then Backup
Another option is to just query your database with the following syntax:
BACKUP DATABASE
You can find out more about BACKUP DATABASE via SQL Server Books Online
(from Microsoft). This feature is available in SQL Server 2000 or later.
Regards,
James Simpson
Straightway Technologies Inc.|||Hi James,
Many thanks indeed for taking the time to reply to my posting.
I think your suggestion will only allow the backup file to be stored
on the server and I was looking for a solution for storing the backup
file on my local P.C.
I tried simply copying the data from the destination machine to the
source machine but then hit the following bug in SQL Server 2005
http://rip747.wordpress.com/2007/10/01/sql-server-2005-import-export-reset-identity-keys-no-workaround-2005-sucks-period-the-end/|||Fresh_Air_Rider@.hotmail.com wrote:
> Hi James,
> Many thanks indeed for taking the time to reply to my posting.
> I think your suggestion will only allow the backup file to be stored
> on the server and I was looking for a solution for storing the backup
> file on my local P.C.
> I tried simply copying the data from the destination machine to the
> source machine but then hit the following bug in SQL Server 2005
> http://rip747.wordpress.com/2007/10/01/sql-server-2005-import-export-reset-identity-keys-no-workaround-2005-sucks-period-the-end/
>
If I understand your issue correctly, you want to create a backup of
your database and copy that backup to your local machine. If so, then
you need to have access to a file share on the server, or be able to
access FTP on the server, or - be able to share a folder on your system.
If you have access to a file share on the server, just backup to that
file share and then copy the backup file. The same if you have access
to FTP on that server (not sure here, but maybe since you said you are
using a hosting service).
Finally, if you can create a share on your machine that the SQL Server
can see you can then backup using UNC to your file share.
Since this is a hosting service, I do not think you have any other options.
HTH,
Jeff|||Not sure if this will help or not, but I found it useful.
http://www.associatedcontent.com/article/267055/how_to_setup_sql_server_2005_backups.html
On Dec 26, 3:12 pm, Jeffrey Williams <jeff.williams3...@.verizon.net>
wrote:
> Fresh_Air_Ri...@.hotmail.com wrote:
> > Hi James,
> > Many thanks indeed for taking the time to reply to my posting.
> > I think your suggestion will only allow the backup file to be stored
> > on the server and I was looking for a solution for storing the backup
> > file on my local P.C.
> > I tried simply copying the data from the destination machine to the
> > source machine but then hit the following bug in SQL Server 2005
> >http://rip747.wordpress.com/2007/10/01/sql-server-2005-import-export-...
> If I understand your issue correctly, you want to create a backup of
> your database and copy that backup to your local machine. If so, then
> you need to have access to a file share on the server, or be able to
> access FTP on the server, or - be able to share a folder on your system.
> If you have access to a file share on the server, just backup to that
> file share and then copy the backup file. The same if you have access
> to FTP on that server (not sure here, but maybe since you said you are
> using a hosting service).
> Finally, if you can create a share on your machine that the SQL Server
> can see you can then backup using UNC to your file share.
> Since this is a hosting service, I do not think you have any other options.
> HTH,
> Jeff|||Hi Jeff,
Many thanks indeed for taking the trouble to reply to my posting. I do
have FTP access, but unfortunately not to the directory where the SQL
Server backups are saved.
I like your suggestion of a creating a share on my machine that the
SQL Server can see you can then backup using UNC to my file share. I
might give that one a go.
I understand that SQL Server 2008 is due to be launched at the end
(27th) of Feb and hopefully Microsoft will have fixed all the bugs in
the Import / Export wizard, including the one that resets all the
identity column values.
Thanks again
David|||Fresh_Air_Rider@.hotmail.com wrote:
> Hi Jeff,
> Many thanks indeed for taking the trouble to reply to my posting. I do
> have FTP access, but unfortunately not to the directory where the SQL
> Server backups are saved.
> I like your suggestion of a creating a share on my machine that the
> SQL Server can see you can then backup using UNC to my file share. I
> might give that one a go.
> I understand that SQL Server 2008 is due to be launched at the end
> (27th) of Feb and hopefully Microsoft will have fixed all the bugs in
> the Import / Export wizard, including the one that resets all the
> identity column values.
> Thanks again
> David
>
>
If you have access to FTP - can you create a new backup to the same
folder on the server? I don't see why not - but really depends on how
the hosting company set this up. I would try that first since it does
not require changing anything.

Backup of SQL Server 2005 database to local P.C ?

Hi,
Could anyone please explain how I could backup a SQL Server database
on a server to my local P.C. ?
I am running Microsoft SQL Server 2005 Management Studio on my P.C.
I need to move the database to another server, but the current hosting
company are very inefficient (slow) at providing me with a database
backup file (.bak).
Is there any way that this can be done with T-SQL or C# or VB.Net
etc ?
I'd be grateful for any help.
DavidExport the database on your hosting company to your local machine using
SSMS.
Ekrem nsoy
<Fresh_Air_Rider@.hotmail.com> wrote in message
news:885bc7de-4a19-42d9-821a-7ca513acea11@.s19g2000prg.googlegroups.com...
> Hi,
> Could anyone please explain how I could backup a SQL Server database
> on a server to my local P.C. ?
> I am running Microsoft SQL Server 2005 Management Studio on my P.C.
> I need to move the database to another server, but the current hosting
> company are very inefficient (slow) at providing me with a database
> backup file (.bak).
> Is there any way that this can be done with T-SQL or C# or VB.Net
> etc ?
> I'd be grateful for any help.
> David|||Hi Ekrem,
Thanks for your suggestion.
The problem with the Import / Export wizard is that it doesn't include
primary keys, identities, default column values and foreign keys etc. I trie
d
creating the tables on my P.C. using a script, which works fine, but then
when I import the data, it resets all of the identity values, which is why I
really need some way of saving the backup (.bak) files to my P.C.|||Sorry David, I don't know another way.
Ekrem ?nsoy
"David" <David@.discussions.microsoft.com> wrote in message
news:FC81D22C-8265-4BD5-8B9E-3EEA4F1A07DF@.microsoft.com...
> Hi Ekrem,
> Thanks for your suggestion.
> The problem with the Import / Export wizard is that it doesn't include
> primary keys, identities, default column values and foreign keys etc. I
> tried
> creating the tables on my P.C. using a script, which works fine, but then
> when I import the data, it resets all of the identity values, which is why
> I
> really need some way of saving the backup (.bak) files to my P.C.|||Hello,
You can use SQL Server Management Studio to create the backup qutie easily:
1) Select the database you want to backup
2) Right click the database to backup
3) Select Tasks and then Backup
Another option is to just query your database with the following syntax:
BACKUP DATABASE
You can find out more about BACKUP DATABASE via SQL Server Books Online
(from Microsoft). This feature is available in SQL Server 2000 or later.
Regards,
James Simpson
Straightway Technologies Inc.|||Hi James,
Many thanks indeed for taking the time to reply to my posting.
I think your suggestion will only allow the backup file to be stored
on the server and I was looking for a solution for storing the backup
file on my local P.C.
I tried simply copying the data from the destination machine to the
source machine but then hit the following bug in SQL Server 2005
http://rip747.wordpress.com/2007/10...period-the-end/|||Fresh_Air_Rider@.hotmail.com wrote:
> Hi James,
> Many thanks indeed for taking the time to reply to my posting.
> I think your suggestion will only allow the backup file to be stored
> on the server and I was looking for a solution for storing the backup
> file on my local P.C.
> I tried simply copying the data from the destination machine to the
> source machine but then hit the following bug in SQL Server 2005
> http://rip747.wordpress.com/2007/10...period-the-end/
>
If I understand your issue correctly, you want to create a backup of
your database and copy that backup to your local machine. If so, then
you need to have access to a file share on the server, or be able to
access FTP on the server, or - be able to share a folder on your system.
If you have access to a file share on the server, just backup to that
file share and then copy the backup file. The same if you have access
to FTP on that server (not sure here, but maybe since you said you are
using a hosting service).
Finally, if you can create a share on your machine that the SQL Server
can see you can then backup using UNC to your file share.
Since this is a hosting service, I do not think you have any other options.
HTH,
Jeff|||Not sure if this will help or not, but I found it useful.
http://www.associatedcontent.com/ar...
ps.html
On Dec 26, 3:12 pm, Jeffrey Williams <jeff.williams3...@.verizon.net>
wrote:
> Fresh_Air_Ri...@.hotmail.com wrote:
>
>
>
>
> If I understand your issue correctly, you want to create a backup of
> your database and copy that backup to your local machine. If so, then
> you need to have access to a file share on the server, or be able to
> access FTP on the server, or - be able to share a folder on your system.
> If you have access to a file share on the server, just backup to that
> file share and then copy the backup file. The same if you have access
> to FTP on that server (not sure here, but maybe since you said you are
> using a hosting service).
> Finally, if you can create a share on your machine that the SQL Server
> can see you can then backup using UNC to your file share.
> Since this is a hosting service, I do not think you have any other options
.
> HTH,
> Jeff|||Hi Jeff,
Many thanks indeed for taking the trouble to reply to my posting. I do
have FTP access, but unfortunately not to the directory where the SQL
Server backups are saved.
I like your suggestion of a creating a share on my machine that the
SQL Server can see you can then backup using UNC to my file share. I
might give that one a go.
I understand that SQL Server 2008 is due to be launched at the end
(27th) of Feb and hopefully Microsoft will have fixed all the bugs in
the Import / Export wizard, including the one that resets all the
identity column values.
Thanks again
David|||Fresh_Air_Rider@.hotmail.com wrote:
> Hi Jeff,
> Many thanks indeed for taking the trouble to reply to my posting. I do
> have FTP access, but unfortunately not to the directory where the SQL
> Server backups are saved.
> I like your suggestion of a creating a share on my machine that the
> SQL Server can see you can then backup using UNC to my file share. I
> might give that one a go.
> I understand that SQL Server 2008 is due to be launched at the end
> (27th) of Feb and hopefully Microsoft will have fixed all the bugs in
> the Import / Export wizard, including the one that resets all the
> identity column values.
> Thanks again
> David
>
>
If you have access to FTP - can you create a new backup to the same
folder on the server? I don't see why not - but really depends on how
the hosting company set this up. I would try that first since it does
not require changing anything.

backup of SQL Server 2000 loading to 2005

I want to restore a backup of a SQL Server 2000 database to SQL Server 2005. Would I need to do anything after running the restore to upgrade the database?Not really. When the database is restored, it will be in SQL 2000 compatibility mode, so you will not be able to use any of the new bells and whistles. To fix that, you just run

sp_dbcmptlevel dbname, 90

Backup of SQL DB with Veritas Agents

Help!
We just upgraded to version 10d of the Veritas/Symantec software - part of
upgrade was to update the remote backup exec agent that is used to backup SQL
db's.
This is the event log entries for the job that fails.
Source MSSQL Server
Event ID 17055
18210 :
BackupVirtualDeviceSet::Initialize: Open failure on backup device
'master_00__96b4386f_92d9_42ae_8cd2_00630edf161d_'. Operating system error
-2147024891(Access is denied.).
---
Source Backup Exec
Event ID 57859
An error occurred while executing the following query: "BACKUP DATABASE
[master] TO virtual_device = 'master_00__96b4386f_92d9_42ae_8cd2_00630edf161d_'".
On server: "CD_PA".
SQL error number: "0BC5".
SQL error message: "Backup or restore operation terminating abnormally.
".
For more information, click the following link:
http://eventlookup.veritas.com/eventlookup/EventLookup.jhtml
The jobs worked until we upgraded... Veritas wants me to make all these
changes... we are only running SQL7.0 no SQL 2000
Anybody have this issue...and had any luck getting it resolved withou
upgrading SQL?
Please advise.
Thanks.
-John
Citigate Cunningham HelpdeskHi
Have you checked account which is running veritas to see whether it has
privileges?
John
"Citigate Cunningham Helpdesk" wrote:
> Help!
> We just upgraded to version 10d of the Veritas/Symantec software - part of
> upgrade was to update the remote backup exec agent that is used to backup SQL
> db's.
> This is the event log entries for the job that fails.
> Source MSSQL Server
> Event ID 17055
> 18210 :
> BackupVirtualDeviceSet::Initialize: Open failure on backup device
> 'master_00__96b4386f_92d9_42ae_8cd2_00630edf161d_'. Operating system error
> -2147024891(Access is denied.).
>
> ---
> Source Backup Exec
> Event ID 57859
> An error occurred while executing the following query: "BACKUP DATABASE
> [master] TO virtual_device => 'master_00__96b4386f_92d9_42ae_8cd2_00630edf161d_'".
> On server: "CD_PA".
> SQL error number: "0BC5".
> SQL error message: "Backup or restore operation terminating abnormally.
> ".
> For more information, click the following link:
> http://eventlookup.veritas.com/eventlookup/EventLookup.jhtml
> The jobs worked until we upgraded... Veritas wants me to make all these
> changes... we are only running SQL7.0 no SQL 2000
> Anybody have this issue...and had any luck getting it resolved withou
> upgrading SQL?
> Please advise.
> Thanks.
> -John
> Citigate Cunningham Helpdesk
>|||John,
http://support.microsoft.com/default.aspx?scid=kb;en-us;280759
Veritas has me trying renaming some of the dll's ... I did update the
Sqlvdi.dll to the 2000 version.. it worked on one server a W2K server but
did not work on the NT40 box.
There were some additonal dlls that it recommended that also get copied
over... this is our finance server... and I don't want to mess it up.
Prior to the upgrade.... from 9.x to 10d it worked fine.
-John
"John Bell" wrote:
> Hi
> Have you checked account which is running veritas to see whether it has
> privileges?
> John
> "Citigate Cunningham Helpdesk" wrote:
> > Help!
> >
> > We just upgraded to version 10d of the Veritas/Symantec software - part of
> > upgrade was to update the remote backup exec agent that is used to backup SQL
> > db's.
> >
> > This is the event log entries for the job that fails.
> >
> > Source MSSQL Server
> > Event ID 17055
> > 18210 :
> > BackupVirtualDeviceSet::Initialize: Open failure on backup device
> > 'master_00__96b4386f_92d9_42ae_8cd2_00630edf161d_'. Operating system error
> > -2147024891(Access is denied.).
> >
> >
> > ---
> >
> > Source Backup Exec
> > Event ID 57859
> >
> > An error occurred while executing the following query: "BACKUP DATABASE
> > [master] TO virtual_device => > 'master_00__96b4386f_92d9_42ae_8cd2_00630edf161d_'".
> > On server: "CD_PA".
> > SQL error number: "0BC5".
> > SQL error message: "Backup or restore operation terminating abnormally.
> > ".
> >
> > For more information, click the following link:
> > http://eventlookup.veritas.com/eventlookup/EventLookup.jhtml
> >
> > The jobs worked until we upgraded... Veritas wants me to make all these
> > changes... we are only running SQL7.0 no SQL 2000
> >
> > Anybody have this issue...and had any luck getting it resolved withou
> > upgrading SQL?
> >
> > Please advise.
> >
> > Thanks.
> >
> > -John
> > Citigate Cunningham Helpdesk
> >|||Hi
If you have the disc space you may want to do away with the agent and just
back up to disc. Then you can schedule the normal file backup to put this
onto tape. Unless absolutly necessary I try an avoid using the agents as they
can cause so many issues like yours!
John
"Citigate Cunningham Helpdesk" wrote:
> John,
> http://support.microsoft.com/default.aspx?scid=kb;en-us;280759
> Veritas has me trying renaming some of the dll's ... I did update the
> Sqlvdi.dll to the 2000 version.. it worked on one server a W2K server but
> did not work on the NT40 box.
> There were some additonal dlls that it recommended that also get copied
> over... this is our finance server... and I don't want to mess it up.
> Prior to the upgrade.... from 9.x to 10d it worked fine.
> -John
> "John Bell" wrote:
> > Hi
> >
> > Have you checked account which is running veritas to see whether it has
> > privileges?
> >
> > John
> >
> > "Citigate Cunningham Helpdesk" wrote:
> >
> > > Help!
> > >
> > > We just upgraded to version 10d of the Veritas/Symantec software - part of
> > > upgrade was to update the remote backup exec agent that is used to backup SQL
> > > db's.
> > >
> > > This is the event log entries for the job that fails.
> > >
> > > Source MSSQL Server
> > > Event ID 17055
> > > 18210 :
> > > BackupVirtualDeviceSet::Initialize: Open failure on backup device
> > > 'master_00__96b4386f_92d9_42ae_8cd2_00630edf161d_'. Operating system error
> > > -2147024891(Access is denied.).
> > >
> > >
> > > ---
> > >
> > > Source Backup Exec
> > > Event ID 57859
> > >
> > > An error occurred while executing the following query: "BACKUP DATABASE
> > > [master] TO virtual_device => > > 'master_00__96b4386f_92d9_42ae_8cd2_00630edf161d_'".
> > > On server: "CD_PA".
> > > SQL error number: "0BC5".
> > > SQL error message: "Backup or restore operation terminating abnormally.
> > > ".
> > >
> > > For more information, click the following link:
> > > http://eventlookup.veritas.com/eventlookup/EventLookup.jhtml
> > >
> > > The jobs worked until we upgraded... Veritas wants me to make all these
> > > changes... we are only running SQL7.0 no SQL 2000
> > >
> > > Anybody have this issue...and had any luck getting it resolved withou
> > > upgrading SQL?
> > >
> > > Please advise.
> > >
> > > Thanks.
> > >
> > > -John
> > > Citigate Cunningham Helpdesk
> > >

Backup of SQL Database using VB 6.0

hi all
Please help me
I want to know how can I take the backup of my database which I've made in SQL Server by using VB as a front end tool.
If any 1 of u can please mail me, I would be thankful :). My email address is love1best@.yahoo.com
JamalJanYour VB could send an SQL script containing the BACKUP command.

blindman|||Originally posted by blindman
Your VB could send an SQL script containing the BACKUP command.

blindman

how ? :(
Pls Explain. If anyone can tell me the codes, I'll be really thankful.

Thanking in Advance.

JamalJan

[love1best@.yahoo.com]|||From ASP:

set execommand = Server.CreateObject("ADODB.Command")
execommand.CommandText = "backup database test_db to disk='f:\backup\test_db.bak'"
execommand.execute
set rs=nothing
set execommand=nothing

For VB it is almost the same...|||Originally posted by snail
From ASP:

set execommand = Server.CreateObject("ADODB.Command")
execommand.CommandText = "backup database test_db to disk='f:\backup\test_db.bak'"
execommand.execute
set rs=nothing
set execommand=nothing

For VB it is almost the same...

thanks :)

Jani

Backup of SAN databases

I'm looking for some "best practices" help from the SAN gurus out there.
We're looking to move some of our databases to an EMC SAN. Currently we run
full backups every night and tran logs every 15 minutes, which are then used
by logshipping to restore to warm standby servers.
After moving to the SAN and a clustered environment, the logshipping system
will be dropped. Since the only other reason for backups is DR, there's some
dispute as to whether SQL backups will even be necessary going forward.
We're being told that the SnapView software we're getting with the SAN will
be able to create clones of the production LUNs, from which we can then back
up to tape or restore back to live if/when necessary.
As a long-time DBA it goes against my grain to turn off SQL backups and put
the databases in simple recovery mode <g>. Is that generally how things are
done on a SAN? I should add that I fully understand the "point-in-time"
issue, that is if we take a new clone every night of the live LUNs we may
lose up to 24 hours of transactions if we have to re-sync back. Other than
this issue, are there good or bad sides to SnapView as our primary SQL
backup strategy?
Thanks
Randy Rabin
Hi
No, no, no.
We have a massive SAN environment comprising of EMC and Hitachi, and even
though we use EMC's SRDF to have data moved to DR in real time, we still do
Full backups daily and Log dumps every 15 minutes. I trust EMC to store the
data, but once EMC wants to be the only form of backup I have, I start to
worry. The also have bugs. SnapView and SRDF will faithfully transfer the
corruption that your DB could have to the backup copy as it knows no better.
If you run simple recovery mode, how can you do point in time restores? Most
DB outages are caused by human error like dropping tables, or deleting too
many rows from a table. What it an application error destroys data and then
you get asked, we need a restore up to 10:28 this morning?
Clones of LUNs are not feasible to be run every 15 minutes. Put the dumps on
different LUNS to the data and log. Even an EMC engineer can mess up a
configuration and then you have no good backup.
A SAN is a storage mechanism and not a replacement for good backups.
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/
"Randy Rabin" <randyr@.channeladvisor.com> wrote in message
news:e2B9Pp$WFHA.2080@.TK2MSFTNGP15.phx.gbl...
> I'm looking for some "best practices" help from the SAN gurus out there.
> We're looking to move some of our databases to an EMC SAN. Currently we
> run
> full backups every night and tran logs every 15 minutes, which are then
> used
> by logshipping to restore to warm standby servers.
> After moving to the SAN and a clustered environment, the logshipping
> system
> will be dropped. Since the only other reason for backups is DR, there's
> some
> dispute as to whether SQL backups will even be necessary going forward.
> We're being told that the SnapView software we're getting with the SAN
> will
> be able to create clones of the production LUNs, from which we can then
> back
> up to tape or restore back to live if/when necessary.
> As a long-time DBA it goes against my grain to turn off SQL backups and
> put
> the databases in simple recovery mode <g>. Is that generally how things
> are
> done on a SAN? I should add that I fully understand the "point-in-time"
> issue, that is if we take a new clone every night of the live LUNs we may
> lose up to 24 hours of transactions if we have to re-sync back. Other than
> this issue, are there good or bad sides to SnapView as our primary SQL
> backup strategy?
> Thanks
> Randy Rabin
>

Backup of Remote SQL 2005 Server

Hello Friends:

My problem relates to backing up my MS SQL 2005 database which is sitting on a shared server at a hosting company.

OVERVIEW:
- Hosting company is using MS SQL 2005
- I am using the SQL Server Management Studio that comes with SQL Server 2005 Standard (NOT Express), which is installed on MY PC.
- So, I am connecting to the SQL server over the internet

WHAT I WANT TO ACHIEVE:
- I would like to backup the data sitting on the Hosting company's MS SQL Server. I only have one database on this SQL Server. There are of course 100s of other databases on the same server which belong to other customers of the hosting company.

- I want to bring the backup to MY PC, from the SQL Server.

- As far as I can tell the following options within SQL Server Management Studio may be of help to me. I do not know which one I should use or which one is best or what is the proper method. 1) Select Backup option from the Tasks menu (but it only shows me drives/devices on the Hosting SQL Server, not my PC, so I cant backup to my PC) 2) Export Data (it does not work, showing errors not a trusted connection I have no clue what a trusted connection is. 3) Copy Database (which is supposed to copy the remote database on the Hosting company SQL server, to my local SQL Server running on my PC). I go through the wizard, on the last screen it just hangs i.e. shows- not responding)

MY QUESTION TO THE COMMUNITY:
How do I backup the database sitting on the hosting company SQL server? I of course need to bring the backup to my PC.What you'll need to check is whether you have access to a folder from SSMS which also can be accessed by other means, eg FTP. If so, you can run a backup to that folder, and then download the backup file. SSMS itself does NOT give access to your local disk drives.

But, why do you want to backup the database yourself, does not the hosting company take backups for you?|||Management studio does not support the UNC path, generally it will show you the local disk to the server. To perform network back up you need to write the TSQL command.
The username by which you login into CMS, requires permission to perform back up and restore. Basically the Admin of hosting company will assign permissions to each and every user. Speak to hosting company Administrator for help.|||Thank you all for your quick reply. My ISP does perform a backup. Then why do I want to do a backup to my local PC?

Well, I am planning to move to a new ISP. And so I need to move my database also. I first need to backup the SQL database (or extract the data) from my existing provider. Then I need to somehow upload the database to the new provider.

I am sure this is a very common issue. People must on occasions move from one hosting provider to another, and if they have a database they will not want to lose all the data.

Since I posted my first message, I signed-up with 3 ISPs. It did not work with 2 of them, and tech support could not tell me what to do. Then I signed up with Network Solutions, and I can now at least Export the data.

Copy Database still does not work. But I think I know why. Since there are 100s of other databases on the same server (and I do not have access to them) the Copy Database wizard probably tries to access them while displaying the list (which it does when it asks you for the Source Database).

I think a user with Sysadmin rights would be able to run Copy Database, but getting those rights of course would not be possible in a shared hosting environment.

Over the last several days, I have called the Tech Support of most major hosting companies in US, UK, CA, and some countries in Europe. I was able to speak to 'real tech humans' in each company. But surprisingly NOT EVEN ONE could answer my questions, or even shed some light on the issue. They were completely clueless!!

It seems either no one changes hosting companies or no one has any important data to backup or everyone who knows about these issues uses only dedicated servers (which cost lot more money). Either way, the tech support in these hosting companies (most of whom have at least 200 employees and $4million+ turnover) had little idea about what I was talking about. Network Solutions seems to be the best and has techs available 24X7, and atleast they all speak clearly and try to help.

By the way I have been in IT for 20 years, I did make sure I was asking the questions clearly.|||why not create a share from the host to your machine, open a query session and use t-sql to backup the database to your machine?

Why does it have to be GUi based?|||This might be an easy answer to a complicated question/issue-- but I've used a free tool for backup that has proved to be actually high-performance stuff... can be downloaded free at: www.idera.com/freetools, and its called SQLsafe Freeware Edition.

Hope that can do the trick, why not, its the right price... free. ha!

Enjoy, and let me know how it works for you.

Backup of Read Only Database.

Hi,
Is it possible to take backup of read only database?
If yes then
How? and
If No? then
There is any Microsoft official link which can clarify the answer.
please give reply as soon as possible.
Kind Regards,
Sajid.Hi,
Yes you can backup a read only database (confirmed it for myself on SQL
Server 2000 and 2005).
You can do it using Enterprise Manager (SQL Server 2000) or Management
Studio (SQL Server 2005). Alternatively, you can use T-SQL to do it
through the use of the BACKUP DATABASE statement.
SQL Server Books Online is a good resource to look up how to do it if
you need a refresher.
Hope that helps a bit|||Hi,
Thanks for this reply.
Can you pls. giveme the exact BACKUP DATABASE Statement.
Thanks and Regards,
Sajid.|||Hi,
Thanks for this reply.
Can you pls. giveme the exact BACKUP DATABASE Statement.
Thanks and Regards,
Sajid.|||Hi,
If you want to back up your database to a file, then your statement
should look like:
BACKUP DATABASE <Insert Database Name>
TO DISK = 'C:\Example_Backup.bak'
Hope that helps a bit|||Hi,
Thanks for your quick reply.
Again i want to know that can i take a backup of database which is in
standby mode.
if the ans. is yes then pls. tell me how can i do it?
Thanks and Regards,
Sajid.
nate.vu@.gmail.com wrote:
> Hi,
> If you want to back up your database to a file, then your statement
> should look like:
> BACKUP DATABASE <Insert Database Name>
> TO DISK = 'C:\Example_Backup.bak'
> Hope that helps a bit|||Read only is not the same as standby mode. Unfortunately, I believe that the tools that comes with
SQL Server blurs this. No, you cannot take a backup of a database in standby mode (restored using
the STANDBY option).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<csajid@.gmail.com> wrote in message news:1147689430.310228.284590@.v46g2000cwv.googlegroups.com...
> Hi,
> Thanks for your quick reply.
> Again i want to know that can i take a backup of database which is in
> standby mode.
> if the ans. is yes then pls. tell me how can i do it?
> Thanks and Regards,
> Sajid.
>
> nate.vu@.gmail.com wrote:
>> Hi,
>> If you want to back up your database to a file, then your statement
>> should look like:
>> BACKUP DATABASE <Insert Database Name>
>> TO DISK = 'C:\Example_Backup.bak'
>> Hope that helps a bit
>

Backup of Read Only Database.

Hi,
Is it possible to take backup of read only database?
If yes then
How? and
If No? then
There is any Microsoft official link which can clarify the answer.
please give reply as soon as possible.
Kind Regards,
Sajid.Hi,
Yes you can backup a read only database (confirmed it for myself on SQL
Server 2000 and 2005).
You can do it using Enterprise Manager (SQL Server 2000) or Management
Studio (SQL Server 2005). Alternatively, you can use T-SQL to do it
through the use of the BACKUP DATABASE statement.
SQL Server Books Online is a good resource to look up how to do it if
you need a refresher.
Hope that helps a bit|||Hi,
Thanks for this reply.
Can you pls. giveme the exact BACKUP DATABASE Statement.
Thanks and Regards,
Sajid.|||Hi,
Thanks for this reply.
Can you pls. giveme the exact BACKUP DATABASE Statement.
Thanks and Regards,
Sajid.|||Hi,
If you want to back up your database to a file, then your statement
should look like:
BACKUP DATABASE <Insert Database Name>
TO DISK = 'C:\Example_Backup.bak'
Hope that helps a bit|||Hi,
Thanks for your quick reply.
Again i want to know that can i take a backup of database which is in
standby mode.
if the ans. is yes then pls. tell me how can i do it?
Thanks and Regards,
Sajid.
nate.vu@.gmail.com wrote:
> Hi,
> If you want to back up your database to a file, then your statement
> should look like:
> BACKUP DATABASE <Insert Database Name>
> TO DISK = 'C:\Example_Backup.bak'
> Hope that helps a bit|||Read only is not the same as standby mode. Unfortunately, I believe that the
tools that comes with
SQL Server blurs this. No, you cannot take a backup of a database in standby
mode (restored using
the STANDBY option).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<csajid@.gmail.com> wrote in message news:1147689430.310228.284590@.v46g2000cwv.googlegroups.c
om...
> Hi,
> Thanks for your quick reply.
> Again i want to know that can i take a backup of database which is in
> standby mode.
> if the ans. is yes then pls. tell me how can i do it?
> Thanks and Regards,
> Sajid.
>
> nate.vu@.gmail.com wrote:
>

Backup of mssql server in LAN

hi everyone,
I am trying to backup and restore mssql server using VDI Interface.
Where i can list the no. of sqlserver's in LAN.
To backup and restore, i have to separately run my application in
individual machines where
mssql server was installed. I would like to configure backup and
restore mssql server available in connected LAN, and to control in one
machine using only one application in only one machine.
I am getting GetConfiguration method of VDI API time out while trying
to connect mssql server another machine.
vds->GetConfiguration(INFINITE, &config)
never times out but for more than half an hour couldn't get connected
with mssql server
running in another machine.
I would like to get a solution to connect all mssql server for backup
and restore from a single
machine in a LAN using VDI API without time out in GetConfiguration
method.
Any Suggestions on this will be greatly appreciated.
Thanx in advance
regards,
deenaHi
As no-one else has replied I thought I would put in my 2c!!
Most backup solutions seem to have an agent component, which would indicate
that it may not be possible to do this. You may have to log a call for this
as it is not a feature that many people would use.
John
"tsgd84@.gmail.com" wrote:

> hi everyone,
> I am trying to backup and restore mssql server using VDI Interface.
> Where i can list the no. of sqlserver's in LAN.
> To backup and restore, i have to separately run my application in
> individual machines where
> mssql server was installed. I would like to configure backup and
> restore mssql server available in connected LAN, and to control in one
> machine using only one application in only one machine.
> I am getting GetConfiguration method of VDI API time out while trying
> to connect mssql server another machine.
> vds->GetConfiguration(INFINITE, &config)
> never times out but for more than half an hour couldn't get connected
> with mssql server
> running in another machine.
> I would like to get a solution to connect all mssql server for backup
> and restore from a single
> machine in a LAN using VDI API without time out in GetConfiguration
> method.
> Any Suggestions on this will be greatly appreciated.
> Thanx in advance
> regards,
> deena
>

Backup of mssql server in LAN

hi everyone,
I am trying to backup and restore mssql server using VDI Interface.
Where i can list the no. of sqlserver's in LAN.
To backup and restore, i have to separately run my application in
individual machines where
mssql server was installed. I would like to configure backup and
restore mssql server available in connected LAN, and to control in one
machine using only one application in only one machine.
I am getting GetConfiguration method of VDI API time out while trying
to connect mssql server another machine.
vds->GetConfiguration(INFINITE, &config)
never times out but for more than half an hour couldn't get connected
with mssql server
running in another machine.
I would like to get a solution to connect all mssql server for backup
and restore from a single
machine in a LAN using VDI API without time out in GetConfiguration
method.
Any Suggestions on this will be greatly appreciated.
Thanx in advance
regards,
deenaHi
As no-one else has replied I thought I would put in my 2c!!
Most backup solutions seem to have an agent component, which would indicate
that it may not be possible to do this. You may have to log a call for this
as it is not a feature that many people would use.
John
"tsgd84@.gmail.com" wrote:
> hi everyone,
> I am trying to backup and restore mssql server using VDI Interface.
> Where i can list the no. of sqlserver's in LAN.
> To backup and restore, i have to separately run my application in
> individual machines where
> mssql server was installed. I would like to configure backup and
> restore mssql server available in connected LAN, and to control in one
> machine using only one application in only one machine.
> I am getting GetConfiguration method of VDI API time out while trying
> to connect mssql server another machine.
> vds->GetConfiguration(INFINITE, &config)
> never times out but for more than half an hour couldn't get connected
> with mssql server
> running in another machine.
> I would like to get a solution to connect all mssql server for backup
> and restore from a single
> machine in a LAN using VDI API without time out in GetConfiguration
> method.
> Any Suggestions on this will be greatly appreciated.
> Thanx in advance
> regards,
> deena
>

Backup of mirrored databases using TSM

Hi,

I currently have 2 mirrored servers and would like to implement a backup solution using an existing TSM server. The first thing that comes to mind is using the TSM client or Litespeed by Quest, but I'd like to know the effects of performing backups on principal and mirrored servers first.

Will using one of these products cause errors or problems should the backup client try to backup a mirrored database? Can anyone make any recommendations on the effects of using TSM client or Litespeed for a mirrored environment?

Thanks.

That depends on how you would like to do the restore process. The restore process will dictate how you may want to do your backups and not the other way around. In our case we just use the native SQL Server agent to generate the backup files and have TSM to pick up the backup files. This is a disadvantage if your backing up terrabytes of data. In this case, LightSpeed will help decrease backup time.|||

Obviously, if you back up the principal while it is under heavy load, it will compete for resources. Backup tends to consume all available IO bandwidth, but not much CPU.

At the present time, you cannot back up a mirror database. Yes, I know, you want to, and we'll get to it, but not now.

Log backups will not interfere with mirroring as they would in a log-shipping environment, so that is not an issue.

Is there anything else you are concerned about?

|||

Sorry for the delay in my reply. Both of your suggestions were helpful and will consider all my options.
Thanks.

Backup of mirrored databases using TSM

Hi,

Thanks.

Obviously, if you back up the principal while it is under heavy load, it will compete for resources. Backup tends to consume all available IO bandwidth, but not much CPU.

At the present time, you cannot back up a mirror database. Yes, I know, you want to, and we'll get to it, but not now.

Log backups will not interfere with mirroring as they would in a log-shipping environment, so that is not an issue.

Is there anything else you are concerned about?

|||

Sorry for the delay in my reply. Both of your suggestions were helpful and will consider all my options.
Thanks.

Backup of Log files

Dear All,
My nightly backups for log files are failing as there are
users still attached to the database being backup up.
What is the best stratergy for dealing with this ?
Do I
1) Make the DB single user ?
2) Have a script to kill all connections
3) Modify my backup plans
4) Some other
Any help here would be greatly appriciated.
PeterHi Peter,
Which version of SQL Server do you use? SQL Server 2000 and 7 (and probably
some earlier versions as well, but I don't have experience with them) allow
database and log backups when there are users connected to the database.
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Peter" <nospam@.thisemailaddress.co.uk> wrote in message
news:05ae01c35fe8$90bfb4c0$a101280a@.phx.gbl...
> Dear All,
> My nightly backups for log files are failing as there are
> users still attached to the database being backup up.
> What is the best stratergy for dealing with this ?
> Do I
> 1) Make the DB single user ?
> 2) Have a script to kill all connections
> 3) Modify my backup plans
> 4) Some other
> Any help here would be greatly appriciated.
> Peter|||Hello,
Thanks for both of your replies.
I am using SQL 2000. However the backups I am doing are
part of the database maintence plan backups.
I do know that you can back up log files when people are
connected (I do this every hour with our production
database) but once a week I perform a backup of all our
databases with all sort of integrity checks, re builds of
indexes and so on. Its these back ups the log file back
ups are failing on.
Thanks
Peter
>--Original Message--
>Dear All,
>My nightly backups for log files are failing as there are
>users still attached to the database being backup up.
>What is the best stratergy for dealing with this ?
>Do I
>1) Make the DB single user ?
>2) Have a script to kill all connections
>3) Modify my backup plans
>4) Some other
>Any help here would be greatly appriciated.
>Peter
>.
>|||How do you know that they are failing because users are attached? Where are
you getting these error messages from ?
What is the exact message from the Maintenance plan history ?
--
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Peter" <nospam@.thisemailaddress.co.uk> wrote in message
news:060601c35fee$5e973990$a101280a@.phx.gbl...
> Hello,
> Thanks for both of your replies.
> I am using SQL 2000. However the backups I am doing are
> part of the database maintence plan backups.
> I do know that you can back up log files when people are
> connected (I do this every hour with our production
> database) but once a week I perform a backup of all our
> databases with all sort of integrity checks, re builds of
> indexes and so on. Its these back ups the log file back
> ups are failing on.
> Thanks
> Peter
> >--Original Message--
> >Dear All,
> >
> >My nightly backups for log files are failing as there are
> >users still attached to the database being backup up.
> >
> >What is the best stratergy for dealing with this ?
> >
> >Do I
> >1) Make the DB single user ?
> >2) Have a script to kill all connections
> >3) Modify my backup plans
> >4) Some other
> >
> >Any help here would be greatly appriciated.
> >
> >Peter
> >.
> >

Backup of Log fails.

It appears that the data is backing up fine but logs do not. Obviously something has changed but I cannot see what. I have plenty of space and privs but the backups fail with the below errorlog. If anyone can point me in a new direction I would greatly appreciate it.

2005-12-31 08:22:34.16 spid11 Analysis of database 'CEMS_PRO' (7) is 100% complete (approximately 0 more seconds)
2005-12-31 08:22:34.25 spid5 Starting up database 'tempdb'.
2005-12-31 08:22:34.58 spid3 Recovery complete.
2005-12-31 08:22:34.58 spid3 SQL global counter collection task is created.
2005-12-31 08:22:36.22 spid51 Using 'xpsqlbot.dll' version '2000.80.194' to execute extended stored procedure 'xp_qv'.
2005-12-31 09:11:52.18 spid173 Using 'xpstar.dll' version '2000.80.760' to execute extended stored procedure 'xp_regread'.
2005-12-31 09:11:54.85 spid173 Using 'xplog70.dll' version '2000.80.760' to execute extended stored procedure 'xp_cmdshell'.
2005-12-31 21:00:00.75 backup BACKUP failed to complete the command BACKUP LOG CEMS_PRO TO CEMS_Log_Back2 WITH NOINIT, NOSKIP , STATS = 10, NOFORMAT
2005-12-31 22:00:00.92 backup BACKUP failed to complete the command BACKUP LOG CEMS_PRO TO CEMS_Log_Back2 WITH NOINIT, NOSKIP , STATS = 10, NOFORMAT
2005-12-31 23:00:00.86 backup BACKUP failed to complete the command BACKUP LOG CEMS_PRO TO CEMS_Log_Back2 WITH NOINIT, NOSKIP , STATS = 10, NOFORMAT
2006-01-01 00:45:00.91 backup BACKUP failed to complete the command BACKUP LOG CEMS_PRO TO CEMS_Log_Back2 WITH INIT, NOSKIP , STATS = 10, NOFORMAT
2006-01-01 02:25:22.67 backup Database backed up: Database: CEMS_PRO, creation date(time): 2005/01/17(12:36:00), pages dumped: 17259208, first LSN: 45585:18685:21, last LSN: 45586:81369:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'CEMS_Backup'}).
2006-01-01 02:25:23.03 backup BACKUP failed to complete the command backup log CEMS_PRO TO CEMS_Log_Back2 WITH INIT , NOUNLOAD , NAME = N'CEMS_Log_Backup', NOSKIP , STATS = 10, NOFORMAT

You might try running the sql profiler tool to capture the error messages that are being sent back to the backup application.

If you are running this as part of a maintenance plan, then there should be job history logs that capture the error messages.

|||

We are recieving the exact same error:

SQL Profiler is a powerful tool for debugging SQL. What parts do you recommend to monitor?

Thanks

|||What happens if you take the command which is shown in the log and run it in QA or SSMS?

Backup of Log fails.

You might try running the sql profiler tool to capture the error messages that are being sent back to the backup application.

If you are running this as part of a maintenance plan, then there should be job history logs that capture the error messages.

|||

We are recieving the exact same error:

SQL Profiler is a powerful tool for debugging SQL. What parts do you recommend to monitor?

Thanks

|||What happens if you take the command which is shown in the log and run it in QA or SSMS?

Backup of Log fails.

I've recently been having problems similar to this, try doing the following:

1. Make sure you have the latest patches & SPK for SQL

2. Defrag your drives to stop memory related issues

3. Set the tempdb DB properties to use Recovery (FULL) not simple

NB>You can change all of them to Full if you want however, DO NOT change "master" to FULL! Anyway at least that is what M$ says...

4. In maintenance plan jobs make sure you specify the location and add -SupportComputedColumn'

i.e. EXECUTE master.dbo.xp_sqlmaint N'-S Servername\SQLServername -PlanIDXXXXXXXXXXXXXXXX.....-SupportComputedColumn'

5. Use a seperate Maintenance plan for "transaction backup logs" and don't include the "masterDB" as that doesn't work.

Double check

Recovery model of your databases should be FULL

backup of existing Reports and restore on new server

I was working with SSRS September CTP release and developed few reports. there are still few queries are open and working it to fix.

Meanwhile recently I shifted my reports from SSRS 2005 September CTP release to SSRS 2005 final release, before moving my reports I took backup as shown below,

- By using Report Manager, I went to edit report properties and save all report definition files ( like rdl files, Model smdl file)

Unfortunately, I was not able to restore these report files on new SSRS system. Am I missing something here?

Otherwise it is a complete rework for me to make all reports again. and this will continue as and when I need to move my reports to live environment.

This should work, what error message were you seeing?

-Daniel

|||

I am getting following error,

Error in class instance declaration for class Microsoft.ReportingServices.SemanticQueryReportLibrary.DrillthroughContextBuilder: [BC30002] Type 'Microsoft.ReportingServices.SemanticQueryReportLibrary.DrillthroughContextBuilder' is not defined. (rsCompilerErrorInClassInstanceDeclaration)

Details
Event ID:	rsCompilerErrorInClassInstanceDeclaration
Source:	Microsoft.ReportingServices.ReportProcessing.RPRes

And there is no online help available for this exception.

My assumptions are,

1. GUID for my semantic model is different from which I compiled and deploy again. All of old report is mapping to definition in old semantic model.

2. All of my reports are having navigation from one report to another with parameter, which i modified using report designer. This may cause a problem to identify definition of my reports while uploading to Report server using Report Manager.

backup of existing Reports and restore on new server

I was working with SSRS September CTP release and developed few reports. there are still few queries are open and working it to fix.

Meanwhile recently I shifted my reports from SSRS 2005 September CTP release to SSRS 2005 final release, before moving my reports I took backup as shown below,

- By using Report Manager, I went to edit report properties and save all report definition files ( like rdl files, Model smdl file)

Unfortunately, I was not able to restore these report files on new SSRS system. Am I missing something here?

Otherwise it is a complete rework for me to make all reports again. and this will continue as and when I need to move my reports to live environment.

This should work, what error message were you seeing?

-Daniel

|||

I am getting following error,

Details
Event ID:	rsCompilerErrorInClassInstanceDeclaration
Source:	Microsoft.ReportingServices.ReportProcessing.RPRes

And there is no online help available for this exception.

My assumptions are,

1. GUID for my semantic model is different from which I compiled and deploy again. All of old report is mapping to definition in old semantic model.

Backup of databases within SQL Server 2005 Express

Hi there!

Is there any replacement for the missing SQL Server Agent which could be configured to backup "all databases" and fix minor errors in databases, rebuild indexes, etc.?

Or let's better ask: is there any solution/tool out there to create and execute maintenance plans without the need of SQL Server Agent? (It might be a simple tool that enumerates the DBs and executes some T-SQL statements on them)

Thanks
JochenManagement Studio Express provides UI to do those things, but there is no Agent in SQL Server Express, so the tasks can only be executed once in the UI.

Instead, you could use the UI to create the T-SQL, script it to a file rather than execute it, and then use the Windows scheduler to launch sqlcmd.exe to execute the script every day.|||This goes into the right direction. With that I could create the backups manually. But it is very time consuming to setup this for many SQL servers and many databases. Especially, when a new database will be created, it's important to not forget to create a new task for the additional database.

Is there another, more elegant way or better a tool to do the job?|||I would recommend using the auto-close feature in your connection string. Basically, when all the users disconnect the database file will automatically detach from the sql server, leaving it free to access by regular backup programs. I really like this feature!

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsse/html/sseoverview.asp|||Automating Database maintenance in SQL 2005 Express Edition Part I

Automating Database maintenance in SQL 2005 Express Edition Part II

Part I deals with creating scheduled tasks and using SQLCMD. Part II use SMO.

Backup of databases

Hi
I would like to know if is possible to do a traditional backup to tape
every day as full database and also remote backup. The remote backup as the
comunications are not very good should be the first time full but the other
days should be differential. Is possible to have both backups mode as I
explain ?. Or only will I have one method to do a backup?. I never work with
SQL and I don't know if this configuration could be done.
Thanks in advance
> I would like to know if is possible to do a traditional backup to tape
> every day as full database and also remote backup.
Yes. You do two backups, one to tape, the other to disk on a share on another machine (or locally
and then copying the file).

>The remote backup as the
> comunications are not very good should be the first time full but the other
> days should be differential. Is possible to have both backups mode as I
> explain ?
YEs, but there's no automation that does it for you. You have to create your own scripts/jobs to
handle this.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Juanchu" <Juanchu@.discussions.microsoft.com> wrote in message
news:6B96D8B2-83C5-415B-830C-C85B043FD4E4@.microsoft.com...
> Hi
> I would like to know if is possible to do a traditional backup to tape
> every day as full database and also remote backup. The remote backup as the
> comunications are not very good should be the first time full but the other
> days should be differential. Is possible to have both backups mode as I
> explain ?. Or only will I have one method to do a backup?. I never work with
> SQL and I don't know if this configuration could be done.
> Thanks in advance

Backup of Databases

For a static READ Only database with no scheduled job, is there any
databases other than those mentioned below I have to make a backup for
recovery purpose ?
1) User Database
2) Master
3) MSDB
Thanks
"Stephen" schrieb:
> For a static READ Only database with no scheduled job, is there any
> databases other than those mentioned below I have to make a backup for
> recovery purpose ?
> 1) User Database
> 2) Master
> 3) MSDB
> Thanks
No. With no scheduled jobs you probably don't even need the msdb (if there's
no alerts, operators, or email support defined).
The master db is only needed to restore the logins that might map to db user
accounts.
The user db is generally restorable without any system db restore (that's
why you can restore a user db on any given SQL Server ...).
|||Hi,
You are good enough.
If it is a read only database, then you could backup the database once and
keep it in a safe location. This includes all your system databases
(Master and MSDB) as well as your user databases.
Thanks
Hari
SQL Server MVP
"Stephen" <anonymous@.discussions.microsoft.com> wrote in message
news:%23h5mcSXeFHA.3864@.TK2MSFTNGP10.phx.gbl...
> For a static READ Only database with no scheduled job, is there any
> databases other than those mentioned below I have to make a backup for
> recovery purpose ?
> 1) User Database
> 2) Master
> 3) MSDB
> Thanks
>

Backup of databases

Hi
I would like to know if is possible to do a traditional backup to tape
every day as full database and also remote backup. The remote backup as the
comunications are not very good should be the first time full but the other
days should be differential. Is possible to have both backups mode as I
explain ?. Or only will I have one method to do a backup?. I never work with
SQL and I don't know if this configuration could be done.
Thanks in advance> I would like to know if is possible to do a traditional backup to tape
> every day as full database and also remote backup.
Yes. You do two backups, one to tape, the other to disk on a share on anothe
r machine (or locally
and then copying the file).

>The remote backup as the
> comunications are not very good should be the first time full but the othe
r
> days should be differential. Is possible to have both backups mode as I
> explain ?
YEs, but there's no automation that does it for you. You have to create your
own scripts/jobs to
handle this.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Juanchu" <Juanchu@.discussions.microsoft.com> wrote in message
news:6B96D8B2-83C5-415B-830C-C85B043FD4E4@.microsoft.com...
> Hi
> I would like to know if is possible to do a traditional backup to tape
> every day as full database and also remote backup. The remote backup as th
e
> comunications are not very good should be the first time full but the othe
r
> days should be differential. Is possible to have both backups mode as I
> explain ?. Or only will I have one method to do a backup?. I never work wi
th
> SQL and I don't know if this configuration could be done.
> Thanks in advance

Backup of Databases

For a static READ Only database with no scheduled job, is there any
databases other than those mentioned below I have to make a backup for
recovery purpose ?
1) User Database
2) Master
3) MSDB
Thanks"Stephen" schrieb:
> For a static READ Only database with no scheduled job, is there any
> databases other than those mentioned below I have to make a backup for
> recovery purpose ?
> 1) User Database
> 2) Master
> 3) MSDB
> Thanks
No. With no scheduled jobs you probably don't even need the msdb (if there's
no alerts, operators, or email support defined).
The master db is only needed to restore the logins that might map to db user
accounts.
The user db is generally restorable without any system db restore (that's
why you can restore a user db on any given SQL Server ...).|||Hi,
You are good enough.
If it is a read only database, then you could backup the database once and
keep it in a safe location. This includes all your system databases
(Master and MSDB) as well as your user databases.
Thanks
Hari
SQL Server MVP
"Stephen" <anonymous@.discussions.microsoft.com> wrote in message
news:%23h5mcSXeFHA.3864@.TK2MSFTNGP10.phx.gbl...
> For a static READ Only database with no scheduled job, is there any
> databases other than those mentioned below I have to make a backup for
> recovery purpose ?
> 1) User Database
> 2) Master
> 3) MSDB
> Thanks
>

Saturday, February 25, 2012

Backup of Standby databases

Backup of Standby databases

Backup of Standby databases

Backup of Sql tables

Backup of Sql tables

Backup of Sql tables

Backup of SQL Server 2005 database to local P.C ?

Backup of SQL Server 2005 database to local P.C ?

Backup of SQL Server 2005 database to local P.C ?

backup of SQL Server 2000 loading to 2005

Backup of SQL DB with Veritas Agents

Backup of SQL DB with Veritas Agents

Backup of SQL Database using VB 6.0

Backup of SAN databases

Backup of Remote SQL 2005 Server

Backup of Read Only Database.

Backup of Read Only Database.

Backup of mssql server in LAN

Backup of mssql server in LAN

Backup of mirrored databases using TSM

Backup of mirrored databases using TSM

Backup of Log files

Backup of Log fails.

Backup of Log fails.

Backup of Log fails.

backup of existing Reports and restore on new server

backup of existing Reports and restore on new server

Backup of databases within SQL Server 2005 Express

Backup of databases

Backup of Databases

Backup of databases

Backup of Databases

sql backup files

Blog Archive

About Me