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~
JimDear 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
> |
>