Create a UNC Share

hi all,

i'm trying to write a script just like "copy database wizard" operation, for detaching, copy and ataching a database from a remote sql server to a local server.

Now the problem is when i try to copy the mdf file from the sql remote server to my local server.

Is it possible to create a UNC share in the remote sql server and delete after with t-sql like the Copy Database wizard does?

hope i made my self clear enought.

regards

Things like this should be planned and not ad-hoc. Though it's possible...

Code Snippet

exec xp_cmdshell 'net share sharename=<drive path>'

Create a table variable from an existing table's schema

Hello.
What's the best way to copy a table's schema and create a "table" data type
inside of a stored procedure?
Thanks in advance,
MikeI think your only choice will be dynamic sql, and you will not have the
chance to access this variable from the original procedure. What are you
trying to do?. Can you use a temporary table created with SELECT ... INTO ..
.
FROM?
select col1, ..., coln
into #t
from table1
where 0 = 1
...
AMB
"MikeL" wrote:

> Hello.
> What's the best way to copy a table's schema and create a "table" data typ
e
> inside of a stored procedure?
> Thanks in advance,
> Mike
>
>|||> What's the best way to copy a table's schema and create a "table" data
type
> inside of a stored procedure?
Well, if you have to rely on dynamic determination of the data model, then
in my opinion: use a temp table. You can't use SELECT INTO for @.table
variables. Some other differences are listed here:
http://www.aspfaq.com/2475
There is no substitute for understanding your data model and not having to
generate this kind of thing on the fly. Why do you need to copy the
structure at all?
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.

Create a Table using another table!

Hi,
Sorry to trouble you all, but I am not getting anywhere!
I need to copy the contents of 1 table into a new temporary table. What I am
doing is :
SELECT *
FROM U_regulargiver
WHERE Status ='I'
OR Status ='N'
INTO TABLE #U_T_Committed
Obviously I'm doing something wrong, but I can't seem to figure out exactly
what. Can anyone please help?
Thanks in advance
RobYou screwed up with the syntax:

> SELECT *
> INTO TABLE #U_T_Committed
> FROM U_regulargiver
> WHERE Status ='I'
> OR Status ='N'
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Robert" <Robert@.discussions.microsoft.com> schrieb im Newsbeitrag
news:7BE6CA67-EF93-4A97-8BA7-22220C06E0FA@.microsoft.com...
> Hi,
> Sorry to trouble you all, but I am not getting anywhere!
> I need to copy the contents of 1 table into a new temporary table. What I
> am
> doing is :
> SELECT *
> FROM U_regulargiver
> WHERE Status ='I'
> OR Status ='N'
> INTO TABLE #U_T_Committed
> Obviously I'm doing something wrong, but I can't seem to figure out
> exactly
> what. Can anyone please help?
> Thanks in advance
> Rob
>|||You can get the results you want by writing the statement this way.
Create Table #temp
(
Test nvarchar (10),
Description nvarchar (255)
)
select * from testuser.dbo.MyTable
Insert into #temp
Select *
from testuser.dbo.MyTable
Select * from #temp
Drop table #temp
refer to Books Online for more information if you must use the INTO clause.
"Robert" wrote:

> Hi,
> Sorry to trouble you all, but I am not getting anywhere!
> I need to copy the contents of 1 table into a new temporary table. What I
am
> doing is :
> SELECT *
> FROM U_regulargiver
> WHERE Status ='I'
> OR Status ='N'
> INTO TABLE #U_T_Committed
> Obviously I'm doing something wrong, but I can't seem to figure out exactl
y
> what. Can anyone please help?
> Thanks in advance
> Rob
>

Create a new database with a backup

Let say I have called database A and I make a backup copy
of it. I now want to create a new database on the same
server for testing purposes and I want to call it database
B. How do I create this database with the backup copy of
database A without overrighting database A in the restore?
Thank You
MichaelFirst off, you'll need to restore the database files in a seperate location
from database A.
You can restore from the backup set by naming the database in the restore
script:
--restore database A as B
Restore database B from disk = "c:\mybackup.bak'
with move 'A' to 'C:\A.mdf',
move 'A_Log' to 'C:\A.ldf'
Note that you cannot rename the Logical or Physical filenames, as they are
defined in the backup set.
If, before you restore, you need to get the filenames, use
Restore filelistonly from disk = 'c:\mybackup.bak'
and it will list all files in the backup.
-Morgan
"Michael" <java_html@.hotmail.com> wrote in message
news:080c01c37278$cd357320$a101280a@.phx.gbl...
> Let say I have called database A and I make a backup copy
> of it. I now want to create a new database on the same
> server for testing purposes and I want to call it database
> B. How do I create this database with the backup copy of
> database A without overrighting database A in the restore?
> Thank You
> Michael|||Thank You...
Michael
>--Original Message--
>First off, you'll need to restore the database files in a
seperate location
>from database A.
>You can restore from the backup set by naming the
database in the restore
>script:
>--restore database A as B
>Restore database B from disk = "c:\mybackup.bak'
>with move 'A' to 'C:\A.mdf',
>move 'A_Log' to 'C:\A.ldf'
>Note that you cannot rename the Logical or Physical
filenames, as they are
>defined in the backup set.
>If, before you restore, you need to get the filenames, use
>Restore filelistonly from disk = 'c:\mybackup.bak'
>and it will list all files in the backup.
>-Morgan
>
>"Michael" <java_html@.hotmail.com> wrote in message
>news:080c01c37278$cd357320$a101280a@.phx.gbl...
>> Let say I have called database A and I make a backup
copy
>> of it. I now want to create a new database on the same
>> server for testing purposes and I want to call it
database
>> B. How do I create this database with the backup copy of
>> database A without overrighting database A in the
restore?
>> Thank You
>> Michael
>
>.
>

Create a copy of database

Dear all,
From SQL Enterprise Manager, how to create a copy of existing database ?
i.e. there is a database called GL2005 and I want a copy of GL2005 with the
name called GL2006.
Thanks.
Vensia
1. backup/restore
2. deattach, copy files, attach
3. Use DTS or SSIS (depending upon version)
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"Vensia" <vensia2000_nospam@.yahoo.com> wrote in message
news:%23S$lcr1DGHA.2924@.tk2msftngp13.phx.gbl...
> Dear all,
> From SQL Enterprise Manager, how to create a copy of existing database ?
> i.e. there is a database called GL2005 and I want a copy of GL2005 with
> the
> name called GL2006.
> Thanks.
> Vensia
>
|||Hi
You may want to check out the following articles that describe the
techniques Mike has suggested
http://support.microsoft.com/kb/304692/ if using backup/restore you will
need to use the MOVE option to change the file names for the new database
http://support.microsoft.com/kb/221465/
If you are using detach/attach the existing database will be offline while
you copy the files and you will need to attach the original database as well
as the new one:
http://support.microsoft.com/kb/2240...22120121120120
Both of these methods would not remove the data in any way, and any
subsequent changes to the old database will not be reflected in the new one
(and vice-versa), therefore you may be only required to take the backup and
save it. (after checking that the backup has worked!).
John
"Vensia" <vensia2000_nospam@.yahoo.com> wrote in message
news:%23S$lcr1DGHA.2924@.tk2msftngp13.phx.gbl...
> Dear all,
> From SQL Enterprise Manager, how to create a copy of existing database ?
> i.e. there is a database called GL2005 and I want a copy of GL2005 with
> the
> name called GL2006.
> Thanks.
> Vensia
>
|||John & Michael,
Thanks for helping.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:O8LupD4DGHA.2380@.TK2MSFTNGP12.phx.gbl...
> Hi
> You may want to check out the following articles that describe the
> techniques Mike has suggested
> http://support.microsoft.com/kb/304692/ if using backup/restore you will
> need to use the MOVE option to change the file names for the new database
> http://support.microsoft.com/kb/221465/
> If you are using detach/attach the existing database will be offline while
> you copy the files and you will need to attach the original database as
well
> as the new one:
> http://support.microsoft.com/kb/2240...22120121120120
> Both of these methods would not remove the data in any way, and any
> subsequent changes to the old database will not be reflected in the new
one
> (and vice-versa), therefore you may be only required to take the backup
and
> save it. (after checking that the backup has worked!).
> John
> "Vensia" <vensia2000_nospam@.yahoo.com> wrote in message
> news:%23S$lcr1DGHA.2924@.tk2msftngp13.phx.gbl...
>

Create a copy of database

Dear all,
From SQL Enterprise Manager, how to create a copy of existing database ?
i.e. there is a database called GL2005 and I want a copy of GL2005 with the
name called GL2006.
Thanks.
Vensia1. backup/restore
2. deattach, copy files, attach
3. Use DTS or SSIS (depending upon version)
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"Vensia" <vensia2000_nospam@.yahoo.com> wrote in message
news:%23S$lcr1DGHA.2924@.tk2msftngp13.phx.gbl...
> Dear all,
> From SQL Enterprise Manager, how to create a copy of existing database ?
> i.e. there is a database called GL2005 and I want a copy of GL2005 with
> the
> name called GL2006.
> Thanks.
> Vensia
>|||Hi
You may want to check out the following articles that describe the
techniques Mike has suggested
http://support.microsoft.com/kb/304692/ if using backup/restore you will
need to use the MOVE option to change the file names for the new database
http://support.microsoft.com/kb/221465/
If you are using detach/attach the existing database will be offline while
you copy the files and you will need to attach the original database as well
as the new one:
http://support.microsoft.com/kb/224...122120121120120
Both of these methods would not remove the data in any way, and any
subsequent changes to the old database will not be reflected in the new one
(and vice-versa), therefore you may be only required to take the backup and
save it. (after checking that the backup has worked!).
John
"Vensia" <vensia2000_nospam@.yahoo.com> wrote in message
news:%23S$lcr1DGHA.2924@.tk2msftngp13.phx.gbl...
> Dear all,
> From SQL Enterprise Manager, how to create a copy of existing database ?
> i.e. there is a database called GL2005 and I want a copy of GL2005 with
> the
> name called GL2006.
> Thanks.
> Vensia
>|||John & Michael,
Thanks for helping.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:O8LupD4DGHA.2380@.TK2MSFTNGP12.phx.gbl...
> Hi
> You may want to check out the following articles that describe the
> techniques Mike has suggested
> http://support.microsoft.com/kb/304692/ if using backup/restore you will
> need to use the MOVE option to change the file names for the new database
> http://support.microsoft.com/kb/221465/
> If you are using detach/attach the existing database will be offline while
> you copy the files and you will need to attach the original database as
well
> as the new one:
> http://support.microsoft.com/kb/224...122120121120120
> Both of these methods would not remove the data in any way, and any
> subsequent changes to the old database will not be reflected in the new
one
> (and vice-versa), therefore you may be only required to take the backup
and
> save it. (after checking that the backup has worked!).
> John
> "Vensia" <vensia2000_nospam@.yahoo.com> wrote in message
> news:%23S$lcr1DGHA.2924@.tk2msftngp13.phx.gbl...
>

Create a copy of database

Dear all,
From SQL Enterprise Manager, how to create a copy of existing database ?
i.e. there is a database called GL2005 and I want a copy of GL2005 with the
name called GL2006.
Thanks.
Vensia1. backup/restore
2. deattach, copy files, attach
3. Use DTS or SSIS (depending upon version)
--
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"Vensia" <vensia2000_nospam@.yahoo.com> wrote in message
news:%23S$lcr1DGHA.2924@.tk2msftngp13.phx.gbl...
> Dear all,
> From SQL Enterprise Manager, how to create a copy of existing database ?
> i.e. there is a database called GL2005 and I want a copy of GL2005 with
> the
> name called GL2006.
> Thanks.
> Vensia
>|||Hi
You may want to check out the following articles that describe the
techniques Mike has suggested
http://support.microsoft.com/kb/304692/ if using backup/restore you will
need to use the MOVE option to change the file names for the new database
http://support.microsoft.com/kb/221465/
If you are using detach/attach the existing database will be offline while
you copy the files and you will need to attach the original database as well
as the new one:
http://support.microsoft.com/kb/224071/#XSLTH3125121122120121120120
Both of these methods would not remove the data in any way, and any
subsequent changes to the old database will not be reflected in the new one
(and vice-versa), therefore you may be only required to take the backup and
save it. (after checking that the backup has worked!).
John
"Vensia" <vensia2000_nospam@.yahoo.com> wrote in message
news:%23S$lcr1DGHA.2924@.tk2msftngp13.phx.gbl...
> Dear all,
> From SQL Enterprise Manager, how to create a copy of existing database ?
> i.e. there is a database called GL2005 and I want a copy of GL2005 with
> the
> name called GL2006.
> Thanks.
> Vensia
>|||John & Michael,
Thanks for helping.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:O8LupD4DGHA.2380@.TK2MSFTNGP12.phx.gbl...
> Hi
> You may want to check out the following articles that describe the
> techniques Mike has suggested
> http://support.microsoft.com/kb/304692/ if using backup/restore you will
> need to use the MOVE option to change the file names for the new database
> http://support.microsoft.com/kb/221465/
> If you are using detach/attach the existing database will be offline while
> you copy the files and you will need to attach the original database as
well
> as the new one:
> http://support.microsoft.com/kb/224071/#XSLTH3125121122120121120120
> Both of these methods would not remove the data in any way, and any
> subsequent changes to the old database will not be reflected in the new
one
> (and vice-versa), therefore you may be only required to take the backup
and
> save it. (after checking that the backup has worked!).
> John
> "Vensia" <vensia2000_nospam@.yahoo.com> wrote in message
> news:%23S$lcr1DGHA.2924@.tk2msftngp13.phx.gbl...
> > Dear all,
> >
> > From SQL Enterprise Manager, how to create a copy of existing database ?
> > i.e. there is a database called GL2005 and I want a copy of GL2005 with
> > the
> > name called GL2006.
> > Thanks.
> >
> > Vensia
> >
> >
>

Create a copy of a table in sql server

I have a table I'd like to copy so I can edit it and play around with
the data. How do I create copy of a table in SQl Server?

Thanks,

BillThe easiest method to copy a table with data is SELECT ... INTO:

SELECT *
INTO MyNewTable
FROM MyTable

Note that this method does not copy constraints and indexes.

--
Hope this helps.

Dan Guzman
SQL Server MVP

--------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index...epartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--------

"Bill" <billzimmerman@.gospellight.com> wrote in message
news:8da5f4f4.0307221412.380787ad@.posting.google.c om...
> I have a table I'd like to copy so I can edit it and play around with
> the data. How do I create copy of a table in SQl Server?
> Thanks,
> Bill|||Hi

I suggest that you backup the database and restore it as a different
database.

See the following (for move read copy!):

http://support.microsoft.com/defaul...en-us;Q314546#2

John

"Bill" <billzimmerman@.gospellight.com> wrote in message
news:8da5f4f4.0307221412.380787ad@.posting.google.c om...
> I have a table I'd like to copy so I can edit it and play around with
> the data. How do I create copy of a table in SQl Server?
> Thanks,
> Bill|||Isn't that overkill? You'll not only copy the table, but you'll copy
everything else in that database as well!!!!

Thanks,
Brian

John Bell wrote:
> Hi
> I suggest that you backup the database and restore it as a different
> database.
> See the following (for move read copy!):
> http://support.microsoft.com/defaul...en-us;Q314546#2
> John
> "Bill" <billzimmerman@.gospellight.com> wrote in message
> news:8da5f4f4.0307221412.380787ad@.posting.google.c om...
> > I have a table I'd like to copy so I can edit it and play around with
> > the data. How do I create copy of a table in SQl Server?
> > Thanks,
> > Bill

--
================================================== =================

Brian Peasland
dba@.remove_spam.peasland.com

Remove the "remove_spam." from the email address to email me.

"I can give it to you cheap, quick, and good. Now pick two out of
the three"|||if you're moving it from one database to another on the same server, i
would use DTS and "copy object." if you're moving it to another
server this method will sometimes fail.

i'm not sure but i think it has something to do with object
permissions granted to logins that exist on the source server but do
not exist on the destination server. if it's all on the same server
it's a non-issue. i'm pretty sure this will also bring over your
indexes, constraints, etc.|||When playing mistakes can be made!

John

"Brian Peasland" <dba@.remove_spam.peasland.com> wrote in message
news:3F1E8D4A.D0B2AB56@.remove_spam.peasland.com...
> Isn't that overkill? You'll not only copy the table, but you'll copy
> everything else in that database as well!!!!
> Thanks,
> Brian
> John Bell wrote:
> > Hi
> > I suggest that you backup the database and restore it as a different
> > database.
> > See the following (for move read copy!):
> > http://support.microsoft.com/defaul...en-us;Q314546#2
> > John
> > "Bill" <billzimmerman@.gospellight.com> wrote in message
> > news:8da5f4f4.0307221412.380787ad@.posting.google.c om...
> > > I have a table I'd like to copy so I can edit it and play around with
> > > the data. How do I create copy of a table in SQl Server?
> > > > Thanks,
> > > > Bill
> --
> ================================================== =================
> Brian Peasland
> dba@.remove_spam.peasland.com
> Remove the "remove_spam." from the email address to email me.
>
> "I can give it to you cheap, quick, and good. Now pick two out of
> the three"sql

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

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