CREATE DATABASE failed: restore

Showing posts with label restore. Show all posts

Tuesday, March 20, 2012

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

Monday, March 19, 2012

Crazy Row Numbering Poblem

I'm using ms sql 2000.
First here is a sample of my XML input:
- <scan ID="18.0" Section="System Restore">
- <scanattributes>
<scanattribute ID="18.0.0.0" ParentID="" Name="Description">Removed
ClientScanServiceSetup</scanattribute>
<scanattribute ID="18.0.0.0.0" ParentID="18.0.0.0" Name="Creation
Time">5/4/2006 11:42 AM</scanattribute>
<scanattribute ID="18.0.0.1" ParentID="" Name="Description">Installed
ClientScanServiceSetup</scanattribute>
<scanattribute ID="18.0.0.1.1" ParentID="18.0.0.1" Name="Creation
Time">5/4/2006 11:48 AM</scanattribute>
<scanattribute ID="18.0.0.2" ParentID="" Name="Description">Removed
ClientScanServiceSetup</scanattribute>
<scanattribute ID="18.0.0.2.2" ParentID="18.0.0.2" Name="Creation
Time">5/4/2006 12:05 PM</scanattribute>
<scanattribute ID="18.0.0.3" ParentID="" Name="Description">Installed
ClientScanServiceSetup</scanattribute>
<scanattribute ID="18.0.0.3.3" ParentID="18.0.0.3" Name="Creation
Time">5/4/2006 12:06 PM</scanattribute>
<scanattribute ID="18.0.0.4" ParentID="" Name="Description">Removed
ClientScanServiceSetup</scanattribute>
<scanattribute ID="18.0.0.4.4" ParentID="18.0.0.4" Name="Creation
Time">5/4/2006 12:15 PM</scanattribute>
<scanattribute ID="18.0.0.5" ParentID="" Name="Description">Installed
ClientScanServiceSetup</scanattribute>
<scanattribute ID="18.0.0.5.5" ParentID="18.0.0.5" Name="Creation
Time">5/4/2006 12:36 PM</scanattribute>
<scanattribute ID="18.0.0.6" ParentID="" Name="Description">Removed
ClientScanServiceSetup</scanattribute>
<scanattribute ID="18.0.0.6.6" ParentID="18.0.0.6" Name="Creation
Time">5/4/2006 12:57 PM</scanattribute>
<scanattribute ID="18.0.0.7" ParentID="" Name="Description">Installed
ClientScanServiceSetup</scanattribute>
<scanattribute ID="18.0.0.7.7" ParentID="18.0.0.7" Name="Creation
Time">5/4/2006 12:59 PM</scanattribute>
<scanattribute ID="18.0.0.8" ParentID="" Name="Description">Removed
ClientScanServiceSetup</scanattribute>
<scanattribute ID="18.0.0.8.8" ParentID="18.0.0.8" Name="Creation
Time">5/4/2006 1:04 PM</scanattribute>
<scanattribute ID="18.0.0.9" ParentID="" Name="Description">Installed
ClientScanServiceSetup</scanattribute>
<scanattribute ID="18.0.0.9.9" ParentID="18.0.0.9" Name="Creation
Time">5/4/2006 1:11 PM</scanattribute>
</scanattributes>
</scan>
Here is what I have so far:
declare @.iTree int
create table #temp (ID nvarchar(50), ParentID nvarchar(50), Name
nvarchar(50), scanattribute nvarchar(50))
create table #dup (attid nvarchar(50), name nvarchar (50), ID
nvarchar(50))
EXEC sp_xml_preparedocument @.iTree OUTPUT, @.doc
*/
INSERT INTO #temp
SELECT * FROM openxml(@.iTree,
'ComputerScan/scans/scan/scanattributes/scanattribute', 1)
WITH(
ID nvarchar(50) './@.ID',
ParentID nvarchar(50) './@.ParentID',
Name nvarchar(50) './@.Name',
scanattribute nvarchar(50) '.'
)
INSERT INTO #dup
SELECT ScanAttributeID, #temp.scanattribute, #temp.ID FROM
tblScanAttribute, #temp
WHERE #temp.ID like '18.%' AND tblScanAttribute.Name = #temp.Name AND
tblScanAttribute.ScanSectionID like '18'
INSERT INTO tblTest3(instance, attid, sectionid, name)
SELECT instance = (select count(*) from #dup where #dup.attid =
tblScanAttribute.ScanAttributeID
AND #dup.name<=#temp.scanattribute), tblScanAttribute.ScanAttributeID,
tblScanAttribute.ScanSectionID, #temp.scanattribute
FROM tblScanAttribute, #temp
WHERE #temp.ID like '18.%' AND tblScanAttribute.Name = #temp.Name AND
tblScanAttribute.ScanSectionID like '18'
The Results are as follows for tblTest3:
2 151 18 5/4/2006 1:11 PM
9 151 18 5/4/2006 12:57 PM
10 151 18 5/4/2006 12:59 PM
1 151 18 5/4/2006 1:04 PM
6 151 18 5/4/2006 12:06 PM
7 151 18 5/4/2006 12:15 PM
8 151 18 5/4/2006 12:36 PM
3 151 18 5/4/2006 11:42 AM
4 151 18 5/4/2006 11:48 AM
5 151 18 5/4/2006 12:05 PM
5 152 18 Installed ClientScanServiceSetup
10 152 18 Removed ClientScanServiceSetup
5 152 18 Installed ClientScanServiceSetup
10 152 18 Removed ClientScanServiceSetup
5 152 18 Installed ClientScanServiceSetup
10 152 18 Removed ClientScanServiceSetup
5 152 18 Installed ClientScanServiceSetup
10 152 18 Removed ClientScanServiceSetup
5 152 18 Installed ClientScanServiceSetup
10 152 18 Removed ClientScanServiceSetup
As you can see I am recording each repeating instace of the second
column, however when the fourth column has a repeating value the method
does not work, this is what I want it to look like:
2 151 18 5/4/2006 1:11 PM
9 151 18 5/4/2006 12:57 PM
10 151 18 5/4/2006 12:59 PM
1 151 18 5/4/2006 1:04 PM
6 151 18 5/4/2006 12:06 PM
7 151 18 5/4/2006 12:15 PM
8 151 18 5/4/2006 12:36 PM
3 151 18 5/4/2006 11:42 AM
4 151 18 5/4/2006 11:48 AM
5 151 18 5/4/2006 12:05 PM
1 152 18 Installed ClientScanServiceSetup
10 152 18 Removed ClientScanServiceSetup
9 152 18 Installed ClientScanServiceSetup
2 152 18 Removed ClientScanServiceSetup
8 152 18 Installed ClientScanServiceSetup
3 152 18 Removed ClientScanServiceSetup
5 152 18 Installed ClientScanServiceSetup
7 152 18 Removed ClientScanServiceSetup
6 152 18 Installed ClientScanServiceSetup
4 152 18 Removed ClientScanServiceSetup
IF anyone can help me out with this I would appreciate it greatly.I forgot to include the other tbl where I get the attid from. so here
it is.
151 18 Creation Time ND 0 5/9/2006 1:56:00 PM 5/9/2006 1:56:00 PM 0
152 18 Description ND 0 5/9/2006 1:56:00 PM 5/9/2006 1:56:00 PM 0|||It's not your main question, but I thought I'd point out that
the ordering of datetimes is probably not what you want:
1:04 PM
1:11 PM
11:42 AM
11:48 AM
...
If you have more than one year, it will get worse.
Steve Kass
Drew University
rhaazy wrote:

>I'm using ms sql 2000.
>First here is a sample of my XML input:
>- <scan ID="18.0" Section="System Restore">
>- <scanattributes>
> <scanattribute ID="18.0.0.0" ParentID="" Name="Description">Removed
>ClientScanServiceSetup</scanattribute>
> <scanattribute ID="18.0.0.0.0" ParentID="18.0.0.0" Name="Creation
>Time">5/4/2006 11:42 AM</scanattribute>
> <scanattribute ID="18.0.0.1" ParentID="" Name="Description">Installed
>ClientScanServiceSetup</scanattribute>
> <scanattribute ID="18.0.0.1.1" ParentID="18.0.0.1" Name="Creation
>Time">5/4/2006 11:48 AM</scanattribute>
> <scanattribute ID="18.0.0.2" ParentID="" Name="Description">Removed
>ClientScanServiceSetup</scanattribute>
> <scanattribute ID="18.0.0.2.2" ParentID="18.0.0.2" Name="Creation
>Time">5/4/2006 12:05 PM</scanattribute>
> <scanattribute ID="18.0.0.3" ParentID="" Name="Description">Installed
>ClientScanServiceSetup</scanattribute>
> <scanattribute ID="18.0.0.3.3" ParentID="18.0.0.3" Name="Creation
>Time">5/4/2006 12:06 PM</scanattribute>
> <scanattribute ID="18.0.0.4" ParentID="" Name="Description">Removed
>ClientScanServiceSetup</scanattribute>
> <scanattribute ID="18.0.0.4.4" ParentID="18.0.0.4" Name="Creation
>Time">5/4/2006 12:15 PM</scanattribute>
> <scanattribute ID="18.0.0.5" ParentID="" Name="Description">Installed
>ClientScanServiceSetup</scanattribute>
> <scanattribute ID="18.0.0.5.5" ParentID="18.0.0.5" Name="Creation
>Time">5/4/2006 12:36 PM</scanattribute>
> <scanattribute ID="18.0.0.6" ParentID="" Name="Description">Removed
>ClientScanServiceSetup</scanattribute>
> <scanattribute ID="18.0.0.6.6" ParentID="18.0.0.6" Name="Creation
>Time">5/4/2006 12:57 PM</scanattribute>
> <scanattribute ID="18.0.0.7" ParentID="" Name="Description">Installed
>ClientScanServiceSetup</scanattribute>
> <scanattribute ID="18.0.0.7.7" ParentID="18.0.0.7" Name="Creation
>Time">5/4/2006 12:59 PM</scanattribute>
> <scanattribute ID="18.0.0.8" ParentID="" Name="Description">Removed
>ClientScanServiceSetup</scanattribute>
> <scanattribute ID="18.0.0.8.8" ParentID="18.0.0.8" Name="Creation
>Time">5/4/2006 1:04 PM</scanattribute>
> <scanattribute ID="18.0.0.9" ParentID="" Name="Description">Installed
>ClientScanServiceSetup</scanattribute>
> <scanattribute ID="18.0.0.9.9" ParentID="18.0.0.9" Name="Creation
>Time">5/4/2006 1:11 PM</scanattribute>
> </scanattributes>
> </scan>
>Here is what I have so far:
>declare @.iTree int
>create table #temp (ID nvarchar(50), ParentID nvarchar(50), Name
>nvarchar(50), scanattribute nvarchar(50))
>create table #dup (attid nvarchar(50), name nvarchar (50), ID
>nvarchar(50))
>EXEC sp_xml_preparedocument @.iTree OUTPUT, @.doc
>*/
> INSERT INTO #temp
> SELECT * FROM openxml(@.iTree,
>'ComputerScan/scans/scan/scanattributes/scanattribute', 1)
> WITH(
> ID nvarchar(50) './@.ID',
> ParentID nvarchar(50) './@.ParentID',
> Name nvarchar(50) './@.Name',
> scanattribute nvarchar(50) '.'
> )
> INSERT INTO #dup
> SELECT ScanAttributeID, #temp.scanattribute, #temp.ID FROM
>tblScanAttribute, #temp
> WHERE #temp.ID like '18.%' AND tblScanAttribute.Name = #temp.Name AND
>tblScanAttribute.ScanSectionID like '18'
> INSERT INTO tblTest3(instance, attid, sectionid, name)
> SELECT instance = (select count(*) from #dup where #dup.attid =
>tblScanAttribute.ScanAttributeID
> AND #dup.name<=#temp.scanattribute), tblScanAttribute.ScanAttributeID,
>tblScanAttribute.ScanSectionID, #temp.scanattribute
> FROM tblScanAttribute, #temp
> WHERE #temp.ID like '18.%' AND tblScanAttribute.Name = #temp.Name AND
>tblScanAttribute.ScanSectionID like '18'
>The Results are as follows for tblTest3:
> 2 151 18 5/4/2006 1:11 PM
> 9 151 18 5/4/2006 12:57 PM
> 10 151 18 5/4/2006 12:59 PM
> 1 151 18 5/4/2006 1:04 PM
> 6 151 18 5/4/2006 12:06 PM
> 7 151 18 5/4/2006 12:15 PM
> 8 151 18 5/4/2006 12:36 PM
> 3 151 18 5/4/2006 11:42 AM
> 4 151 18 5/4/2006 11:48 AM
> 5 151 18 5/4/2006 12:05 PM
> 5 152 18 Installed ClientScanServiceSetup
> 10 152 18 Removed ClientScanServiceSetup
> 5 152 18 Installed ClientScanServiceSetup
> 10 152 18 Removed ClientScanServiceSetup
> 5 152 18 Installed ClientScanServiceSetup
> 10 152 18 Removed ClientScanServiceSetup
> 5 152 18 Installed ClientScanServiceSetup
> 10 152 18 Removed ClientScanServiceSetup
> 5 152 18 Installed ClientScanServiceSetup
> 10 152 18 Removed ClientScanServiceSetup
>As you can see I am recording each repeating instace of the second
>column, however when the fourth column has a repeating value the method
>does not work, this is what I want it to look like:
> 2 151 18 5/4/2006 1:11 PM
> 9 151 18 5/4/2006 12:57 PM
> 10 151 18 5/4/2006 12:59 PM
> 1 151 18 5/4/2006 1:04 PM
> 6 151 18 5/4/2006 12:06 PM
> 7 151 18 5/4/2006 12:15 PM
> 8 151 18 5/4/2006 12:36 PM
> 3 151 18 5/4/2006 11:42 AM
> 4 151 18 5/4/2006 11:48 AM
> 5 151 18 5/4/2006 12:05 PM
> 1 152 18 Installed ClientScanServiceSetup
> 10 152 18 Removed ClientScanServiceSetup
> 9 152 18 Installed ClientScanServiceSetup
> 2 152 18 Removed ClientScanServiceSetup
> 8 152 18 Installed ClientScanServiceSetup
> 3 152 18 Removed ClientScanServiceSetup
> 5 152 18 Installed ClientScanServiceSetup
> 7 152 18 Removed ClientScanServiceSetup
> 6 152 18 Installed ClientScanServiceSetup
> 4 152 18 Removed ClientScanServiceSetup
>IF anyone can help me out with this I would appreciate it greatly.
>
>|||rhaazy,
we need another column to break the ties. Try adding an identity column to
the temporary table #temp.
create table #temp (
[ID] nvarchar(50),
ParentID nvarchar(50),
[Name] nvarchar(50),
scanattribute nvarchar(50),
pk int not null identity unique -- new column
)
INSERT INTO #temp ([ID], ParentID, [Name], scanattribute)
SELECT * FROM openxml(...
...
INSERT INTO tblTest3(instance, attid, sectionid, name)
SELECT
instance = (
select count(*)
from #dup
where
#dup.attid = tblScanAttribute.ScanAttributeID
AND
(
#dup.[name] <= #temp.scanattribute
or
(#dup.[name] = #temp.scanattribute and #dup.puk < #temp)
)
),
tblScanAttribute.ScanAttributeID,
tblScanAttribute.ScanSectionID,
#temp.scanattribute
FROM
tblScanAttribute, #temp
WHERE
#temp.ID like '18.%'
AND tblScanAttribute.Name = #temp.Name
AND tblScanAttribute.ScanSectionID like '18'
AMB
"rhaazy" wrote:

> I forgot to include the other tbl where I get the attid from. so here
> it is.
> 151 18 Creation Time ND 0 5/9/2006 1:56:00 PM 5/9/2006 1:56:00 PM 0
> 152 18 Description ND 0 5/9/2006 1:56:00 PM 5/9/2006 1:56:00 PM 0
>|||NSERT INTO tblTest3(instance, attid, sectionid, name)
SELECT
instance = (
select count(*)
from #dup
where
#dup.attid = tblScanAttribute.ScanAttributeID
AND
(
#dup.[name] <= #temp.scanattribute
or
(#dup.[name] = #temp.scanattribute and #dup.puk <
#temp)
)
),
Does this imply I also need to add the pk column to the #dup table?|||You should be able to use your ID column to resolve duplicates.
Try changing
INSERT INTO tblTest3(instance, attid, sectionid, name)
SELECT instance = (select count(*) from #dup where #dup.attid =
tblScanAttribute.ScanAttributeID
AND #dup.name<=#temp.scanattribute),
tblScanAttribute.ScanAttributeID,
tblScanAttribute.ScanSectionID, #temp.scanattribute
FROM tblScanAttribute, #temp
WHERE #temp.ID like '18.%' AND tblScanAttribute.Name =
#temp.Name AND
tblScanAttribute.ScanSectionID like '18'
to
INSERT INTO tblTest3(instance, attid, sectionid, name)
SELECT instance = (select count(*) from #dup where #dup.attid =
tblScanAttribute.ScanAttributeID
AND ((#dup.name<#temp.scanattribute)
or (#dup.name=#temp.scanattribute) and
(#dup.ID<=#temp.ID))),
tblScanAttribute.ScanAttributeID,
tblScanAttribute.ScanSectionID,
#temp.scanattribute
FROM tblScanAttribute, #temp
WHERE #temp.ID like '18.%' AND tblScanAttribute.Name =
#temp.Name AND
tblScanAttribute.ScanSectionID like '18'|||rhaazy,
Sure, but here it will not have identity property. I will be populated from
#temp.
AMB
"rhaazy" wrote:

> NSERT INTO tblTest3(instance, attid, sectionid, name)
> SELECT
> instance = (
> select count(*)
> from #dup
> where
> #dup.attid = tblScanAttribute.ScanAttributeID
> AND
> (
> #dup.[name] <= #temp.scanattribute
> or
> (#dup.[name] = #temp.scanattribute and #dup.puk <
> #temp)
> )
> ),
> Does this imply I also need to add the pk column to the #dup table?
>|||I don't know who you are or where you come from but you are the
greatest person who ever lived... I have spent all w

trying to
figure this out, I knew it needed an 'or' clause in there but I
couldn't quite get it right. I am an intern and I've only been
database programming for 2 w

s and was pretty pleased I got as far as
I did. I just wanted to tell you how thankful I am for you helping me.
The fact you were able to make sense of all the crazy stuff I gave and
crank out exactly what I needed is truely amazing to me.
Thanks again.|||Good catch!!!
"markc600@.hotmail.com" wrote:

> You should be able to use your ID column to resolve duplicates.
> Try changing
> INSERT INTO tblTest3(instance, attid, sectionid, name)
> SELECT instance = (select count(*) from #dup where #dup.attid =
> tblScanAttribute.ScanAttributeID
> AND #dup.name<=#temp.scanattribute),
> tblScanAttribute.ScanAttributeID,
> tblScanAttribute.ScanSectionID, #temp.scanattribute
> FROM tblScanAttribute, #temp
> WHERE #temp.ID like '18.%' AND tblScanAttribute.Name =
> #temp.Name AND
> tblScanAttribute.ScanSectionID like '18'
>
> to
>
> INSERT INTO tblTest3(instance, attid, sectionid, name)
> SELECT instance = (select count(*) from #dup where #dup.attid =
> tblScanAttribute.ScanAttributeID
> AND ((#dup.name<#temp.scanattribute)
> or (#dup.name=#temp.scanattribute) and
> (#dup.ID<=#temp.ID))),
> tblScanAttribute.ScanAttributeID,
> tblScanAttribute.ScanSectionID,
> #temp.scanattribute
> FROM tblScanAttribute, #temp
> WHERE #temp.ID like '18.%' AND tblScanAttribute.Name =
> #temp.Name AND
> tblScanAttribute.ScanSectionID like '18'
>

Crash Recovery

I am trying to restore a crashed server. The server has an SQL2K
installation with a small database (say) DATA. The file DATA.MDF has gone
missing during the crash. The file DATA.LDF has been recovered. There is
also a backup file DATA.BAK. This backup is about 4 MB and I suspect it was
used to install the database in the first place, as it is several months
old. The log file LDF has been recently updated and is 47 Mb.
Is there any way to recover this database to a reasonable up to date version
using DATA.BAK and DATA.LDF. All suggestions are welcome?
BobHi,
Since the MDF file is not avaible you will not able be use the LDF.
But using DATA.BAK file you can recover the database back to the time in
whcih backup is created.
How to restore the BAK file.
From Query analyzer execute the below command
1. Copy the DATA.BAK to c:\backup folder
Restore database <dbname> from disk='c:\backup\data.bak'
You may need to use WITH Move option along with restore statement.
Thanks
Hari
MCDBA
"Bob Morris" <bmorris@.kenyaonline.com> wrote in message
news:#fVLgrcBEHA.3348@.TK2MSFTNGP11.phx.gbl...
> I am trying to restore a crashed server. The server has an SQL2K
> installation with a small database (say) DATA. The file DATA.MDF has gone
> missing during the crash. The file DATA.LDF has been recovered. There is
> also a backup file DATA.BAK. This backup is about 4 MB and I suspect it
was
> used to install the database in the first place, as it is several months
> old. The log file LDF has been recently updated and is 47 Mb.
> Is there any way to recover this database to a reasonable up to date
version
> using DATA.BAK and DATA.LDF. All suggestions are welcome?
> Bob
>
>|||Thanks,
But I need to know if there's any way to apply the LDF. The MDB file was
probably created on this machine from the BAK file. (i.e. the database was
created elsewhere and "restored" to this machine with the force restore
option). The LDF will reflect changes since that was done, and that's what I
want to try and do.
Regards
Bob
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:eAF4v9cBEHA.3568@.tk2msftngp13.phx.gbl...
> Hi,
> Since the MDF file is not avaible you will not able be use the LDF.
> But using DATA.BAK file you can recover the database back to the time in
> whcih backup is created.
> How to restore the BAK file.
> From Query analyzer execute the below command
> 1. Copy the DATA.BAK to c:\backup folder
> Restore database <dbname> from disk='c:\backup\data.bak'
> You may need to use WITH Move option along with restore statement.
> Thanks
> Hari
> MCDBA
>
>
>
> "Bob Morris" <bmorris@.kenyaonline.com> wrote in message
> news:#fVLgrcBEHA.3348@.TK2MSFTNGP11.phx.gbl...
gone
> was
> version
>|||Thanks, but what I need is to try and apply the LDF after I restore the
backup.
Regards
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:eAF4v9cBEHA.3568@.tk2msftngp13.phx.gbl...
> Hi,
> Since the MDF file is not avaible you will not able be use the LDF.
> But using DATA.BAK file you can recover the database back to the time in
> whcih backup is created.
> How to restore the BAK file.
> From Query analyzer execute the below command
> 1. Copy the DATA.BAK to c:\backup folder
> Restore database <dbname> from disk='c:\backup\data.bak'
> You may need to use WITH Move option along with restore statement.
> Thanks
> Hari
> MCDBA
>
>
>
> "Bob Morris" <bmorris@.kenyaonline.com> wrote in message
> news:#fVLgrcBEHA.3348@.TK2MSFTNGP11.phx.gbl...
gone
> was
> version
>|||You can't apply an LDF file to a restored database backup. If you're lucky,
you might be able to dig something out of the LDF file using some log reader
program. See my signature, the links.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Bob Morris" <bmorris@.kenyaonline.com> wrote in message
news:eiZLHtfBEHA.2768@.tk2msftngp13.phx.gbl...
> Thanks, but what I need is to try and apply the LDF after I restore the
> backup.
> Regards
> "Hari" <hari_prasad_k@.hotmail.com> wrote in message
> news:eAF4v9cBEHA.3568@.tk2msftngp13.phx.gbl...
> gone
is
it
months
>

Tuesday, March 20, 2012

Create "TEST" copy of replicated database

Create "TEST" copy of replicated database

Monday, March 19, 2012

Crazy Row Numbering Poblem

Crash Recovery

CREATE DATABASE failed

Blog Archive

About Me