Create "TEST" copy of replicated database

I attempted to restore a database from a backup of a production database that
is being replicated. I changed the database name with a prefix of "TEST" and
verified that the files being restored had the same "TEST" prefix.
I also made sure during the restore that the "Preserve Replication Settings"
was NOT checked because I don't want the "test" copy of the database to
replicate.
The problem is that during the restore, it woud get to 100% complete, but
would never actually finish. I assume it was building a snapshot for
replication because when I tried to DROP the newly restored database, it said
it couldn't because it was currently being replicated.
Can someone point me to some information on how I SHOULD accomplish this task.
Thanks~
Jim
Dear Jim,
Thank you for posting here.
From your description, please use the following methods to check if we are
able to restore the database successfully:
1. First of all, please remove the replication records on the 'TEST'
database by running the following commands:
USE master
EXEC sp_removedbreplication <TEST db>
GO
2. Run the command "DROP DATABASE <TEST db>" to remove the database again.
3. Then create an empty database with the same name as the TEST database
and restore the db from backup using the "Overwrite the existing database"
option and keep the "Preserve Replication Settings" option unchecked.
4. Please check what if we are able to restore the database successfully.
If the issue persists, please help create a MPSReport for us, which will
collect ERRORLOG, Windows event log and other helpful information about the
SQL Server. To create a MPSReport, please visit the following web site:
http://www.microsoft.com/downloads/details.aspx?FamilyId=CEBF3C7C-7CA5-408F-
88B7-F9C79B7306C0&displaylang=en
And look for MPSRPT_SQL.EXE. Download it and run it on the machine. Dismiss
the prompted dialog boxes and the readme window (usually a Notepad) after
you read them. After the Command Prompt window closes itself, collect the
cab file the tool has generated and sent it to me at v-adamqu@.microsoft.com
If anything is unclear in my post, please don't hesitate to let me know and
I will be glad to help.
Have a nice day!
Best regards,
Adams Qu
MCSE, MCDBA, MCTS
Microsoft Online Support
Microsoft Global Technical Support Center
Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
| Thread-Topic: Create "TEST" copy of replicated database
| thread-index: AchXuMJRCP6rp3dKTIqe00pr3DGgCw==
| X-WBNR-Posting-Host: 207.46.193.207
| From: =?Utf-8?B?QmlnSmltQ2FzaA==?= <Bigjimcash@.noemail.noemail>
| Subject: Create "TEST" copy of replicated database
| Date: Tue, 15 Jan 2008 12:54:02 -0800
| Lines: 17
| Message-ID: <40AD657C-2569-436B-88BA-7FE5BED4F04F@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.2992
| Newsgroups: microsoft.public.sqlserver.server
| Path: TK2MSFTNGHUB02.phx.gbl
| Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.server:34747
| NNTP-Posting-Host: tk2msftsbfm01.phx.gbl 10.40.244.148
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| I attempted to restore a database from a backup of a production database
that
| is being replicated. I changed the database name with a prefix of "TEST"
and
| verified that the files being restored had the same "TEST" prefix.
|
| I also made sure during the restore that the "Preserve Replication
Settings"
| was NOT checked because I don't want the "test" copy of the database to
| replicate.
|
| The problem is that during the restore, it woud get to 100% complete, but
| would never actually finish. I assume it was building a snapshot for
| replication because when I tried to DROP the newly restored database, it
said
| it couldn't because it was currently being replicated.
|
| Can someone point me to some information on how I SHOULD accomplish this
task.
| --
| Thanks~
| Jim
|
|||Adams Qu,
Thank you for your post. As an FYI, my assumption was incorrect as to why
it was taking so long to complete the restore. It was not trying to build a
snapshot, it was actually deleting the replication articles before bringing
the DB online. I found an article (for SQL2000) that indicated that if
restoring a DB to another machine, it would automatically delete the
replication articles after the files were restored.
My first attempt, I stopped execution of of the restore and manually broght
the DB online. It did have replication (publications) and a bunch of errors
that it couldn't find the distributor Agent.
After reading the article, and your post, I just let the restore take as
long as it needed an about 8 minutes later, the restore was sucessful and
replication had been removed from the restored database.
I guess I just have to learn to be more paitent.
Thanks again.
Jim
"Adams Qu [MSFT]" wrote:

> Dear Jim,
> Thank you for posting here.
> From your description, please use the following methods to check if we are
> able to restore the database successfully:
> 1. First of all, please remove the replication records on the 'TEST'
> database by running the following commands:
> USE master
> EXEC sp_removedbreplication <TEST db>
> GO
> 2. Run the command "DROP DATABASE <TEST db>" to remove the database again.
> 3. Then create an empty database with the same name as the TEST database
> and restore the db from backup using the "Overwrite the existing database"
> option and keep the "Preserve Replication Settings" option unchecked.
> 4. Please check what if we are able to restore the database successfully.
> If the issue persists, please help create a MPSReport for us, which will
> collect ERRORLOG, Windows event log and other helpful information about the
> SQL Server. To create a MPSReport, please visit the following web site:
> http://www.microsoft.com/downloads/details.aspx?FamilyId=CEBF3C7C-7CA5-408F-
> 88B7-F9C79B7306C0&displaylang=en
> And look for MPSRPT_SQL.EXE. Download it and run it on the machine. Dismiss
> the prompted dialog boxes and the readme window (usually a Notepad) after
> you read them. After the Command Prompt window closes itself, collect the
> cab file the tool has generated and sent it to me at v-adamqu@.microsoft.com
> If anything is unclear in my post, please don't hesitate to let me know and
> I will be glad to help.
> Have a nice day!
> Best regards,
> Adams Qu
> MCSE, MCDBA, MCTS
> Microsoft Online Support
> Microsoft Global Technical Support Center
> Get Secure! - www.microsoft.com/security
> ================================================== ===
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no rights.
> --
> | Thread-Topic: Create "TEST" copy of replicated database
> | thread-index: AchXuMJRCP6rp3dKTIqe00pr3DGgCw==
> | X-WBNR-Posting-Host: 207.46.193.207
> | From: =?Utf-8?B?QmlnSmltQ2FzaA==?= <Bigjimcash@.noemail.noemail>
> | Subject: Create "TEST" copy of replicated database
> | Date: Tue, 15 Jan 2008 12:54:02 -0800
> | Lines: 17
> | Message-ID: <40AD657C-2569-436B-88BA-7FE5BED4F04F@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.2992
> | Newsgroups: microsoft.public.sqlserver.server
> | Path: TK2MSFTNGHUB02.phx.gbl
> | Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.server:34747
> | NNTP-Posting-Host: tk2msftsbfm01.phx.gbl 10.40.244.148
> | X-Tomcat-NG: microsoft.public.sqlserver.server
> |
> | I attempted to restore a database from a backup of a production database
> that
> | is being replicated. I changed the database name with a prefix of "TEST"
> and
> | verified that the files being restored had the same "TEST" prefix.
> |
> | I also made sure during the restore that the "Preserve Replication
> Settings"
> | was NOT checked because I don't want the "test" copy of the database to
> | replicate.
> |
> | The problem is that during the restore, it woud get to 100% complete, but
> | would never actually finish. I assume it was building a snapshot for
> | replication because when I tried to DROP the newly restored database, it
> said
> | it couldn't because it was currently being replicated.
> |
> | Can someone point me to some information on how I SHOULD accomplish this
> task.
> | --
> | Thanks~
> | Jim
> |
>
|||Dear Jim,
Thank you for your update.
I am glad to hear that the restore was successful and replication had been
removed from the restored database.
If you have any other questions or concerns, please do not hesitate to post
back.
Have a nice day!
Best regards,
Adams Qu
MCSE, MCDBA, MCTS
Microsoft Online Support
Microsoft Global Technical Support Center
Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
| Thread-Topic: Create "TEST" copy of replicated database
| thread-index: AchZFL6Rz8abohyfQxmbgPj/y/Sqyg==
| X-WBNR-Posting-Host: 207.46.193.207
| From: =?Utf-8?B?QmlnSmltQ2FzaA==?= <Bigjimcash@.noemail.noemail>
| References: <40AD657C-2569-436B-88BA-7FE5BED4F04F@.microsoft.com>
<G7Gb9VAWIHA.360@.TK2MSFTNGHUB02.phx.gbl>
| Subject: RE: Create "TEST" copy of replicated database
| Date: Thu, 17 Jan 2008 06:25:01 -0800
| Lines: 128
| Message-ID: <EBD04ED9-F10B-415F-AAA0-6EB9FD3B1EDD@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.2992
| Newsgroups: microsoft.public.sqlserver.server
| Path: TK2MSFTNGHUB02.phx.gbl
| Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.server:34862
| NNTP-Posting-Host: tk2msftibfm01.phx.gbl 10.40.244.149
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| Adams Qu,
|
| Thank you for your post. As an FYI, my assumption was incorrect as to
why
| it was taking so long to complete the restore. It was not trying to build
a
| snapshot, it was actually deleting the replication articles before
bringing
| the DB online. I found an article (for SQL2000) that indicated that if
| restoring a DB to another machine, it would automatically delete the
| replication articles after the files were restored.
|
| My first attempt, I stopped execution of of the restore and manually
broght
| the DB online. It did have replication (publications) and a bunch of
errors
| that it couldn't find the distributor Agent.
|
| After reading the article, and your post, I just let the restore take as
| long as it needed an about 8 minutes later, the restore was sucessful and
| replication had been removed from the restored database.
|
| I guess I just have to learn to be more paitent.
|
| Thanks again.
| Jim
|
|
| "Adams Qu [MSFT]" wrote:
|
| > Dear Jim,
| >
| > Thank you for posting here.
| >
| > From your description, please use the following methods to check if we
are
| > able to restore the database successfully:
| >
| > 1. First of all, please remove the replication records on the 'TEST'
| > database by running the following commands:
| >
| > USE master
| > EXEC sp_removedbreplication <TEST db>
| > GO
| >
| > 2. Run the command "DROP DATABASE <TEST db>" to remove the database
again.
| >
| > 3. Then create an empty database with the same name as the TEST
database
| > and restore the db from backup using the "Overwrite the existing
database"
| > option and keep the "Preserve Replication Settings" option unchecked.
| >
| > 4. Please check what if we are able to restore the database
successfully.
| >
| > If the issue persists, please help create a MPSReport for us, which
will
| > collect ERRORLOG, Windows event log and other helpful information about
the
| > SQL Server. To create a MPSReport, please visit the following web site:
| >
| >
http://www.microsoft.com/downloads/details.aspx?FamilyId=CEBF3C7C-7CA5-408F-
| > 88B7-F9C79B7306C0&displaylang=en
| >
| > And look for MPSRPT_SQL.EXE. Download it and run it on the machine.
Dismiss
| > the prompted dialog boxes and the readme window (usually a Notepad)
after
| > you read them. After the Command Prompt window closes itself, collect
the
| > cab file the tool has generated and sent it to me at
v-adamqu@.microsoft.com
| >
| > If anything is unclear in my post, please don't hesitate to let me know
and
| > I will be glad to help.
| >
| > Have a nice day!
| >
| > Best regards,
| >
| > Adams Qu
| > MCSE, MCDBA, MCTS
| > Microsoft Online Support
| >
| > Microsoft Global Technical Support Center
| >
| > Get Secure! - www.microsoft.com/security
| > ================================================== ===
| > When responding to posts, please "Reply to Group" via your newsreader
so
| > that others may learn and benefit from your issue.
| > ================================================== ===
| > This posting is provided "AS IS" with no warranties, and confers no
rights.
| >
| > --
| > | Thread-Topic: Create "TEST" copy of replicated database
| > | thread-index: AchXuMJRCP6rp3dKTIqe00pr3DGgCw==
| > | X-WBNR-Posting-Host: 207.46.193.207
| > | From: =?Utf-8?B?QmlnSmltQ2FzaA==?= <Bigjimcash@.noemail.noemail>
| > | Subject: Create "TEST" copy of replicated database
| > | Date: Tue, 15 Jan 2008 12:54:02 -0800
| > | Lines: 17
| > | Message-ID: <40AD657C-2569-436B-88BA-7FE5BED4F04F@.microsoft.com>
| > | MIME-Version: 1.0
| > | Content-Type: text/plain;
| > | charset="Utf-8"
| > | Content-Transfer-Encoding: 7bit
| > | X-Newsreader: Microsoft CDO for Windows 2000
| > | Content-Class: urn:content-classes:message
| > | Importance: normal
| > | Priority: normal
| > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.2992
| > | Newsgroups: microsoft.public.sqlserver.server
| > | Path: TK2MSFTNGHUB02.phx.gbl
| > | Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.server:34747
| > | NNTP-Posting-Host: tk2msftsbfm01.phx.gbl 10.40.244.148
| > | X-Tomcat-NG: microsoft.public.sqlserver.server
| > |
| > | I attempted to restore a database from a backup of a production
database
| > that
| > | is being replicated. I changed the database name with a prefix of
"TEST"
| > and
| > | verified that the files being restored had the same "TEST" prefix.
| > |
| > | I also made sure during the restore that the "Preserve Replication
| > Settings"
| > | was NOT checked because I don't want the "test" copy of the database
to
| > | replicate.
| > |
| > | The problem is that during the restore, it woud get to 100% complete,
but
| > | would never actually finish. I assume it was building a snapshot for
| > | replication because when I tried to DROP the newly restored database,
it
| > said
| > | it couldn't because it was currently being replicated.
| > |
| > | Can someone point me to some information on how I SHOULD accomplish
this
| > task.
| > | --
| > | Thanks~
| > | Jim
| > |
| >
| >
|
sql