Regarding drop and restore replication database

Hi Guys,
I have two questions regarding replication:
1, If I don't continue to use replication at all, how can I drop replication
completely? Because too many command, e.g. sp_removedbreplication or
sp_dropplication and so on. Which one can I use?
2. If I want to retore the replication database and want to keep replication
so that I can continue to use replication, do I need to setup some steps
before restore database?
Thanks.
To remove replication, use sp_removedbreplication. There might be a few
orphaned objects left behind but these can be deleted by hand or simply left
there as they won't cause any problems.
To restore a replicated database, pls have a look in BOL for the topic
"replication, backup and restore operations".
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Thank you very much. I use sp_removedbreplication to remove the replication,
it is ok. But like what you said, there are many orphaned objects and mark as
crose with read color, it is very uncomfortable. I use sp_dropdistpublisher
and sp_dropsubscriber to remove those objects and it is still marked even
though there isn't those objects. How to get rid of red color? thanks.
"Paul Ibison" wrote:

> To remove replication, use sp_removedbreplication. There might be a few
> orphaned objects left behind but these can be deleted by hand or simply left
> there as they won't cause any problems.
> To restore a replicated database, pls have a look in BOL for the topic
> "replication, backup and restore operations".
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
|||The replication monitor gets its info from
tempdb.dbo.MSreplication_agent_status and running
sp_MSload_replication_status refreshes this table. As restarting the SQL
Server Service causes the tempdb to be recreated from the model database,
this also removes the problem.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

REG. RESTORE DATABASE FROM SQL 2005

I am facing problem while restoring database:
"An excepting occured while executing a Transact-SQL statement or batch.
Too many backup device specified for backup or restore; only 64 are
allowed
*** Sent via Developersdex http://www.codecomments.com ***
sham,
Since BACKUP DATABASE only supports 64 backup devices, you should not need
more than 64 to restore. If you paste in your RESTORE command here, maybe
it will be apparent what is wrong.
RLF
"sham kulkarni" <sham252664@.gmail.com> wrote in message
news:uNS92ctIIHA.2480@.TK2MSFTNGP05.phx.gbl...
>I am facing problem while restoring database:
> "An excepting occured while executing a Transact-SQL statement or batch.
> Too many backup device specified for backup or restore; only 64 are
> allowed
>
> *** Sent via Developersdex http://www.codecomments.com ***
|||Russel, I think the OP tries to restored sql server 2005 database on sql
server 2000 version
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:ujgOF1tIIHA.1204@.TK2MSFTNGP03.phx.gbl...
> sham,
> Since BACKUP DATABASE only supports 64 backup devices, you should not need
> more than 64 to restore. If you paste in your RESTORE command here, maybe
> it will be apparent what is wrong.
> RLF
> "sham kulkarni" <sham252664@.gmail.com> wrote in message
> news:uNS92ctIIHA.2480@.TK2MSFTNGP05.phx.gbl...
>
|||Uri,
I did not think that of that possibility. Of course, if that is the case,
then it certainly will not work. But that is a strange message to get in
this case. I would have expected something like this:
Msg 3169, Level 16, State 1, Line 1
The backed-up database has on-disk structure version 611. The server
supports version 539 and cannot restore or upgrade this database.
RLF
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uxQBlqFJIHA.4592@.TK2MSFTNGP02.phx.gbl...
> Russel, I think the OP tries to restored sql server 2005 database on sql
> server 2000 version
>
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:ujgOF1tIIHA.1204@.TK2MSFTNGP03.phx.gbl...
>
sql

REG. RESTORE DATABASE FROM SQL 2005

I am facing problem while restoring database:
"An excepting occured while executing a Transact-SQL statement or batch.
Too many backup device specified for backup or restore; only 64 are
allowed
*** Sent via Developersdex http://www.codecomments.com ***sham,
Since BACKUP DATABASE only supports 64 backup devices, you should not need
more than 64 to restore. If you paste in your RESTORE command here, maybe
it will be apparent what is wrong.
RLF
"sham kulkarni" <sham252664@.gmail.com> wrote in message
news:uNS92ctIIHA.2480@.TK2MSFTNGP05.phx.gbl...
>I am facing problem while restoring database:
> "An excepting occured while executing a Transact-SQL statement or batch.
> Too many backup device specified for backup or restore; only 64 are
> allowed
>
> *** Sent via Developersdex http://www.codecomments.com ***|||Russel, I think the OP tries to restored sql server 2005 database on sql
server 2000 version
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:ujgOF1tIIHA.1204@.TK2MSFTNGP03.phx.gbl...
> sham,
> Since BACKUP DATABASE only supports 64 backup devices, you should not need
> more than 64 to restore. If you paste in your RESTORE command here, maybe
> it will be apparent what is wrong.
> RLF
> "sham kulkarni" <sham252664@.gmail.com> wrote in message
> news:uNS92ctIIHA.2480@.TK2MSFTNGP05.phx.gbl...
>|||Uri,
I did not think that of that possibility. Of course, if that is the case,
then it certainly will not work. But that is a strange message to get in
this case. I would have expected something like this:
Msg 3169, Level 16, State 1, Line 1
The backed-up database has on-disk structure version 611. The server
supports version 539 and cannot restore or upgrade this database.
RLF
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uxQBlqFJIHA.4592@.TK2MSFTNGP02.phx.gbl...
> Russel, I think the OP tries to restored sql server 2005 database on sql
> server 2000 version
>
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:ujgOF1tIIHA.1204@.TK2MSFTNGP03.phx.gbl...
>

REG. RESTORE DATABASE FROM SQL 2005

I am facing problem while restoring database:
"An excepting occured while executing a Transact-SQL statement or batch.
Too many backup device specified for backup or restore; only 64 are
allowed
*** Sent via Developersdex http://www.developersdex.com ***sham,
Since BACKUP DATABASE only supports 64 backup devices, you should not need
more than 64 to restore. If you paste in your RESTORE command here, maybe
it will be apparent what is wrong.
RLF
"sham kulkarni" <sham252664@.gmail.com> wrote in message
news:uNS92ctIIHA.2480@.TK2MSFTNGP05.phx.gbl...
>I am facing problem while restoring database:
> "An excepting occured while executing a Transact-SQL statement or batch.
> Too many backup device specified for backup or restore; only 64 are
> allowed
>
> *** Sent via Developersdex http://www.developersdex.com ***|||Russel, I think the OP tries to restored sql server 2005 database on sql
server 2000 version
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:ujgOF1tIIHA.1204@.TK2MSFTNGP03.phx.gbl...
> sham,
> Since BACKUP DATABASE only supports 64 backup devices, you should not need
> more than 64 to restore. If you paste in your RESTORE command here, maybe
> it will be apparent what is wrong.
> RLF
> "sham kulkarni" <sham252664@.gmail.com> wrote in message
> news:uNS92ctIIHA.2480@.TK2MSFTNGP05.phx.gbl...
>>I am facing problem while restoring database:
>> "An excepting occured while executing a Transact-SQL statement or batch.
>> Too many backup device specified for backup or restore; only 64 are
>> allowed
>>
>> *** Sent via Developersdex http://www.developersdex.com ***
>|||Uri,
I did not think that of that possibility. Of course, if that is the case,
then it certainly will not work. But that is a strange message to get in
this case. I would have expected something like this:
Msg 3169, Level 16, State 1, Line 1
The backed-up database has on-disk structure version 611. The server
supports version 539 and cannot restore or upgrade this database.
RLF
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uxQBlqFJIHA.4592@.TK2MSFTNGP02.phx.gbl...
> Russel, I think the OP tries to restored sql server 2005 database on sql
> server 2000 version
>
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:ujgOF1tIIHA.1204@.TK2MSFTNGP03.phx.gbl...
>> sham,
>> Since BACKUP DATABASE only supports 64 backup devices, you should not
>> need more than 64 to restore. If you paste in your RESTORE command here,
>> maybe it will be apparent what is wrong.
>> RLF
>> "sham kulkarni" <sham252664@.gmail.com> wrote in message
>> news:uNS92ctIIHA.2480@.TK2MSFTNGP05.phx.gbl...
>>I am facing problem while restoring database:
>> "An excepting occured while executing a Transact-SQL statement or batch.
>> Too many backup device specified for backup or restore; only 64 are
>> allowed
>>
>> *** Sent via Developersdex http://www.developersdex.com ***
>>
>

Redundant DR Site Cluster Restore

Hello,
We are restoring our production SQL cluster to an isolated DR site. The
server and SAN hardware are the same, the domain is the same, all of the IP
addresses are the same because the network is isolated.
Typically, we back our production servers up to tape and restore them on top
of a vanilla OS using NetBackup for our Windows DR restores. This usually
works fine, but we are always required to remove the restored server to the
domain, add it to a workgroup, and re-add the server back to the domain to
get rid of the " The trust
relationship between this workstation and the primary domain failed" error.
I have restored the OS of both nodes of the cluster to servers in the DR
site. The cluster service obviously will not start because of the "trust
error" which can't be resolved until the server is removed from the domain
and re-added.
My question is, if I do this to a node in the server, what will happen to
the cluster service/quorum/etc after I remove it from the domain. I'm going
to readd it immediately, but I'm wondering if it will cause any issues or if
there are any precautions I need to take? Or will the cluster just come up
assuming no other changes have been made?
Just go in and take all of the resource groups offline. Then stop the
cluster service in the service control applet. Once that is done, get the
servers added back to the domain. Once added back in, start the cluster
service, connect to your cluster, and start up all other groups.
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"jason" <jason@.discussions.microsoft.com> wrote in message
news:ADAEB407-C959-45A4-941D-A29629DB6533@.microsoft.com...
> Hello,
> We are restoring our production SQL cluster to an isolated DR site. The
> server and SAN hardware are the same, the domain is the same, all of the
> IP
> addresses are the same because the network is isolated.
> Typically, we back our production servers up to tape and restore them on
> top
> of a vanilla OS using NetBackup for our Windows DR restores. This usually
> works fine, but we are always required to remove the restored server to
> the
> domain, add it to a workgroup, and re-add the server back to the domain to
> get rid of the " The trust
> relationship between this workstation and the primary domain failed"
> error.
> I have restored the OS of both nodes of the cluster to servers in the DR
> site. The cluster service obviously will not start because of the "trust
> error" which can't be resolved until the server is removed from the domain
> and re-added.
> My question is, if I do this to a node in the server, what will happen to
> the cluster service/quorum/etc after I remove it from the domain. I'm
> going
> to readd it immediately, but I'm wondering if it will cause any issues or
> if
> there are any precautions I need to take? Or will the cluster just come
> up
> assuming no other changes have been made?

Reducing LDF files

Hi,
If I understand it correctly, you only need an LDF file to restore to a point in time after the last full backup? If this is so, then Could the LDF file not be reduced in size on perfoming a full backup?
Most of the time it's not an issue as there is enough space on the HDD, but is it possible to reduce the ldf file size periodically (manually would be fine). Is changing the recovery mode from FULL to SIMPLE and then back to FULL an option?
If so, is anyone able to tell me how, exactly, I can do this? ... I've sifted through the documentaion to no avail ... :eek:
Many thanks
RobNo, you cannot just truncate the log when you take the full backup, because you do not know whether the error forcing you to restore the database will occur prior to or after the last full backup. You may leave the office at 4pm, at 5 pm some tables disappear. Backups are scheduled to run at midnight. If the logs are truncated when you arrive at the office the next morning, you cannot restore to point in time anymore.

Thus, the ONLY way you should free space in the logfiles is backing them up with the backup log statement, which frees up place internally in the log files.|||do not change recovery mode FULL to SIMPLE. it will break log chain.|||If you are just starting off with SQL Server administration, I would recommend using the maintenance plan wizard (if you are using SQL 2005, be sure to download and install SP2, which has significant improvements to maintenance plans in general).

General guidelines:

SQL 7.0/2000:
1. Create a maintenance plan that includes only the system databases (master, model, msdb). Do a full backup daily.

2. Create another maintenance plan that includes only the user databases. Do a full backup daily and a transaction log backup as often as you are comfortable (and as often as needed to keep the logs at a stable/reasonable size).

3. For both plans:
a. Back up over the network (if your network is stable), or straight to disk (separate from data/log files).
b. Retain full backups on disk for 2-3 days (more if you can afford it).
c. Retain log backups for as long as necessary to get back to your last good full backup.
c. Make sure there's another process that writes the db backups to tape.

SQL 2005
1. Create one plan for full backups for all databases

2. Create another plan for log backups for all user databases.

3. Follow other guidelines as above.

There are lots of tweaks and other things that you can do to improve backup performance, this is just a guide to get you started.

Be sure to also:
1. Practice recovery:
a. Practice full database restore
b. Practive database restore to point in time
c. Practice recovery from tape
d. Practice recovery from a dead server (ie, full rebuild/fresh install)

2. Validate your tape backups

3. Document your backup strategy and document your recovery plan.

Regards,

hmscott