I am getting the following error whenever I schedule a new DTS package:
SQL Server does not exist or access denied
This is new, all of my older scheduled packages run without any problem.
This just started occuring during the past week and is occuring on all of my
SQL Servers (7 and 2000).
Any ideas?John
Well , Perhaps you are created the DTS package on your computer and when
you execute the package it runs under account of your computer (SQL Agent
service)
By default, the owner of that service is LocalSystem (with has many
Administrator-style permission, but *NO* access to any network resources).
So, change the account of that service to a Domain Admin account (or other
suitable account with permissions on both computers (server and yours)
"John Hamilton" <jhamil@.nowhere.com> wrote in message
news:uxNQlFVnDHA.2628@.TK2MSFTNGP10.phx.gbl...
> I am getting the following error whenever I schedule a new DTS package:
> SQL Server does not exist or access denied
> This is new, all of my older scheduled packages run without any problem.
> This just started occuring during the past week and is occuring on all of
my
> SQL Servers (7 and 2000).
> Any ideas?
>|||I'm just curious why it was working, I haven't changed anything and now it
does not. The network guys applied some security patches about the time
this started.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eERZIZVnDHA.3612@.TK2MSFTNGP11.phx.gbl...
> John
> Well , Perhaps you are created the DTS package on your computer and when
> you execute the package it runs under account of your computer (SQL Agent
> service)
> By default, the owner of that service is LocalSystem (with has many
> Administrator-style permission, but *NO* access to any network resources).
> So, change the account of that service to a Domain Admin account (or other
> suitable account with permissions on both computers (server and yours)
> "John Hamilton" <jhamil@.nowhere.com> wrote in message
> news:uxNQlFVnDHA.2628@.TK2MSFTNGP10.phx.gbl...
> > I am getting the following error whenever I schedule a new DTS package:
> >
> > SQL Server does not exist or access denied
> >
> > This is new, all of my older scheduled packages run without any problem.
> > This just started occuring during the past week and is occuring on all
of
> my
> > SQL Servers (7 and 2000).
> >
> > Any ideas?
> >
> >
>
Showing posts with label following. Show all posts
Showing posts with label following. Show all posts
Friday, March 30, 2012
problem running sp_addpublication
When I am running sp_addpublication, I am getting the following error. Whats
wrong?
I was able to run the same script before and this stored procedure was
running fine.
Server: Msg 14294, Level 16, State 1, Procedure sp_verify_job_identifiers,
Line 24
Supply either @.job_id or @.job_name to identify the job.
Job 'Server_Name\InstanceNanme-DBName-1' started successfully.
Adam,
I suggest running profiler to see what are the parameters being sent to this
procedure and to debug where the process is going wrong. The most likely
cause is a changed servername, as the error is raised in several system
procedures and the code is usually of the form...
select @.distribution_jobid = job_id from msdb..sysjobs_view where
name = @.name and
UPPER(originating_server) = UPPER(CONVERT(sysname,
SERVERPROPERTY('ServerName')))
if @.distribution_jobid IS NULL
begin
-- Message from msdb.dbo.sp_verify_job_identifiers
RAISERROR(14262, -1, -1, 'Job', @.name)
GOTO UNDO
end
So, if your servername has changed, this could be the cause of the problem.
In this case:
Use Master
go
Select @.@.Servername
This should return your current server name but if it
returns NULL then try:
Use Master
go
Sp_DropServer 'OldName'
GO
Use Master
go
Sp_Addserver 'NewName', 'local'
GO
Stop and Start SQL Services
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thank you Paul for your response. Your solution does make sense, however in
my case the server name has not changed. I had forgotten to run the script to
create jobs, running it seems to have solve the problem. I am pasting a part
of that script to give you the idea.
if (select count(*) from msdb.dbo.syscategories where name =
N'REPL-LogReader') < 1
execute msdb.dbo.sp_add_category N'REPL-LogReader'
Thanks.
-A
"Paul Ibison" wrote:
> Adam,
> I suggest running profiler to see what are the parameters being sent to this
> procedure and to debug where the process is going wrong. The most likely
> cause is a changed servername, as the error is raised in several system
> procedures and the code is usually of the form...
> select @.distribution_jobid = job_id from msdb..sysjobs_view where
> name = @.name and
> UPPER(originating_server) = UPPER(CONVERT(sysname,
> SERVERPROPERTY('ServerName')))
> if @.distribution_jobid IS NULL
> begin
> -- Message from msdb.dbo.sp_verify_job_identifiers
> RAISERROR(14262, -1, -1, 'Job', @.name)
> GOTO UNDO
> end
> So, if your servername has changed, this could be the cause of the problem.
> In this case:
> Use Master
> go
> Select @.@.Servername
> This should return your current server name but if it
> returns NULL then try:
> Use Master
> go
> Sp_DropServer 'OldName'
> GO
> Use Master
> go
> Sp_Addserver 'NewName', 'local'
> GO
> Stop and Start SQL Services
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Hi Adam,
I do have the same issue like while buidling my replication using
scripts it is giving the following error
Server: Msg 14294, Level 16, State 1, Procedure sp_verify_job_identifiers,
Line 24
Supply either @.job_id or @.job_name to identify the job
even if I have created the job for 'REPL-LogReader', before creating
publication it is throwing the same error, is there any way that I can solve
this error.
Please help.
Thanks
Ramesh
"Adam" wrote:
[vbcol=seagreen]
> Thank you Paul for your response. Your solution does make sense, however in
> my case the server name has not changed. I had forgotten to run the script to
> create jobs, running it seems to have solve the problem. I am pasting a part
> of that script to give you the idea.
> if (select count(*) from msdb.dbo.syscategories where name =
> N'REPL-LogReader') < 1
> execute msdb.dbo.sp_add_category N'REPL-LogReader'
> Thanks.
> -A
> "Paul Ibison" wrote:
wrong?
I was able to run the same script before and this stored procedure was
running fine.
Server: Msg 14294, Level 16, State 1, Procedure sp_verify_job_identifiers,
Line 24
Supply either @.job_id or @.job_name to identify the job.
Job 'Server_Name\InstanceNanme-DBName-1' started successfully.
Adam,
I suggest running profiler to see what are the parameters being sent to this
procedure and to debug where the process is going wrong. The most likely
cause is a changed servername, as the error is raised in several system
procedures and the code is usually of the form...
select @.distribution_jobid = job_id from msdb..sysjobs_view where
name = @.name and
UPPER(originating_server) = UPPER(CONVERT(sysname,
SERVERPROPERTY('ServerName')))
if @.distribution_jobid IS NULL
begin
-- Message from msdb.dbo.sp_verify_job_identifiers
RAISERROR(14262, -1, -1, 'Job', @.name)
GOTO UNDO
end
So, if your servername has changed, this could be the cause of the problem.
In this case:
Use Master
go
Select @.@.Servername
This should return your current server name but if it
returns NULL then try:
Use Master
go
Sp_DropServer 'OldName'
GO
Use Master
go
Sp_Addserver 'NewName', 'local'
GO
Stop and Start SQL Services
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thank you Paul for your response. Your solution does make sense, however in
my case the server name has not changed. I had forgotten to run the script to
create jobs, running it seems to have solve the problem. I am pasting a part
of that script to give you the idea.
if (select count(*) from msdb.dbo.syscategories where name =
N'REPL-LogReader') < 1
execute msdb.dbo.sp_add_category N'REPL-LogReader'
Thanks.
-A
"Paul Ibison" wrote:
> Adam,
> I suggest running profiler to see what are the parameters being sent to this
> procedure and to debug where the process is going wrong. The most likely
> cause is a changed servername, as the error is raised in several system
> procedures and the code is usually of the form...
> select @.distribution_jobid = job_id from msdb..sysjobs_view where
> name = @.name and
> UPPER(originating_server) = UPPER(CONVERT(sysname,
> SERVERPROPERTY('ServerName')))
> if @.distribution_jobid IS NULL
> begin
> -- Message from msdb.dbo.sp_verify_job_identifiers
> RAISERROR(14262, -1, -1, 'Job', @.name)
> GOTO UNDO
> end
> So, if your servername has changed, this could be the cause of the problem.
> In this case:
> Use Master
> go
> Select @.@.Servername
> This should return your current server name but if it
> returns NULL then try:
> Use Master
> go
> Sp_DropServer 'OldName'
> GO
> Use Master
> go
> Sp_Addserver 'NewName', 'local'
> GO
> Stop and Start SQL Services
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Hi Adam,
I do have the same issue like while buidling my replication using
scripts it is giving the following error
Server: Msg 14294, Level 16, State 1, Procedure sp_verify_job_identifiers,
Line 24
Supply either @.job_id or @.job_name to identify the job
even if I have created the job for 'REPL-LogReader', before creating
publication it is throwing the same error, is there any way that I can solve
this error.
Please help.
Thanks
Ramesh
"Adam" wrote:
[vbcol=seagreen]
> Thank you Paul for your response. Your solution does make sense, however in
> my case the server name has not changed. I had forgotten to run the script to
> create jobs, running it seems to have solve the problem. I am pasting a part
> of that script to give you the idea.
> if (select count(*) from msdb.dbo.syscategories where name =
> N'REPL-LogReader') < 1
> execute msdb.dbo.sp_add_category N'REPL-LogReader'
> Thanks.
> -A
> "Paul Ibison" wrote:
Wednesday, March 28, 2012
problem retrieving correct max(date) row from this dataset
Hello,
I respectfully request if someone could help me modify the following query:
The goal is to retrieve a record with a curExpireDate of 12/31/05 where ther
e
is more than one row for a given curRecordID - note: in the following
dataset there is only one correct row that I want to retrieve. So for given
curRecordID there is more than one row for a curExpireDate >= 12/31/05. The
n
if curEntryDate for a curExpireDate of 12/31/05 is greater than the
curEntryDate for curExpireDate of 12/31/06 (for the same curRecordID) then I
want to retrieve that row.
The following subdataset (the actual dataset contains thousands of rows
where I need to do the same thing) contains one row where Max(curExpireDate)
= 12/31/05. I don't want that row. There are 3 more curRecordID's with
curExpireDate of 12/31/05 and 12/31/06. For the rows with curRecordID =
18537 this ID contains a row for curExpireDate = 12/31/05 where
Max(curEntryDate) is greater than the curEntryDate for curExpireDate of
12/31/06 for ID = 18537. None of the otherID's has this condition. I need
to retrieve ID 18537 where curEntryDate = 1/27/06 and curExpireDate =
12/31/05.
Here is the dataset and the query I have been experimenting with:
create table #temp4(curRecordID int,
curEntryDate datetime, curExpireDate datetime)
insert Into #temp4
select 12783, '2005-04-07', '2005-12-31' Union
select 12783, '2004-01-12', '2005-12-31' Union
select 12783, '2006-02-03', '2006-12-31' Union
select 12783, '2005-01-11', '2006-12-31' Union
select 29714, '2005-06-29', '2005-12-31' Union
select 29714, '2005-02-02', '2006-12-31' Union
select 29714, '2005-12-01', '2006-12-31' Union
select 18537, '2004-12-10', '2005-12-31' Union
select 18537, '2006-01-27', '2005-12-31' Union --<--yes want this row
select 18537, '2006-01-10', '2006-12-31' Union
select 38537, '2004-12-10', '2005-12-31' --<--don't want this row
SELECT * FROM #temp4 as X
WHERE
curRecordID NOT IN --the list of recid from beginning
(SELECT curRecordID FROM #temp4 GROUP BY curRecordID
HAVING MAX(curExpireDate)='12/31/05')
AND
curExpireDate = '12/31/05' --order by curRecordID
AND curEntryDate >
(select top 1 curEntryDate from #temp4 as Y
where X.curRecordID = Y.curRecordID
and Y.curExpireDate =
(select max(curExpireDate) from #temp4 as Z
where X.curRecordID = Z.curRecordID))
This is the current resultset - which is not the desired resultset
curRecID curEntryDate curExpireDate
12783 2005-04-07 00:00:00.000 2005-12-31 00:00:00.000 --X
18537 2006-01-27 00:00:00.000 2005-12-31 00:00:00.000 -- +
29714 2005-06-29 00:00:00.000 2005-12-31 00:00:00.000 --X
There is no way for me to isolate my desired row from this resultset
The desired resultset is this:
curRecID curEntryDate curExpireDate
18537 2006-01-27 00:00:00.000 2005-12-31 00:00:00.000
Any help appreciated,
Thanks,
RichI think I found my problem. I changed
(select top 1 curEntryDate from #temp4 as Y...
to
(select Max(curEntryDate) from #temp4 as Y
This is now giving me the desired result in my test case. Hopefully, will
do the same for the actual data.
"Rich" wrote:
> Hello,
> I respectfully request if someone could help me modify the following query
:
> The goal is to retrieve a record with a curExpireDate of 12/31/05 where th
ere
> is more than one row for a given curRecordID - note: in the following
> dataset there is only one correct row that I want to retrieve. So for giv
en
> curRecordID there is more than one row for a curExpireDate >= 12/31/05. T
hen
> if curEntryDate for a curExpireDate of 12/31/05 is greater than the
> curEntryDate for curExpireDate of 12/31/06 (for the same curRecordID) then
I
> want to retrieve that row.
> The following subdataset (the actual dataset contains thousands of rows
> where I need to do the same thing) contains one row where Max(curExpireDat
e)
> = 12/31/05. I don't want that row. There are 3 more curRecordID's with
> curExpireDate of 12/31/05 and 12/31/06. For the rows with curRecordID =
> 18537 this ID contains a row for curExpireDate = 12/31/05 where
> Max(curEntryDate) is greater than the curEntryDate for curExpireDate of
> 12/31/06 for ID = 18537. None of the otherID's has this condition. I nee
d
> to retrieve ID 18537 where curEntryDate = 1/27/06 and curExpireDate =
> 12/31/05.
> Here is the dataset and the query I have been experimenting with:
> create table #temp4(curRecordID int,
> curEntryDate datetime, curExpireDate datetime)
> insert Into #temp4
> select 12783, '2005-04-07', '2005-12-31' Union
> select 12783, '2004-01-12', '2005-12-31' Union
> select 12783, '2006-02-03', '2006-12-31' Union
> select 12783, '2005-01-11', '2006-12-31' Union
> select 29714, '2005-06-29', '2005-12-31' Union
> select 29714, '2005-02-02', '2006-12-31' Union
> select 29714, '2005-12-01', '2006-12-31' Union
> select 18537, '2004-12-10', '2005-12-31' Union
> select 18537, '2006-01-27', '2005-12-31' Union --<--yes want this row
> select 18537, '2006-01-10', '2006-12-31' Union
> select 38537, '2004-12-10', '2005-12-31' --<--don't want this row
> SELECT * FROM #temp4 as X
> WHERE
> curRecordID NOT IN --the list of recid from beginning
> (SELECT curRecordID FROM #temp4 GROUP BY curRecordID
> HAVING MAX(curExpireDate)='12/31/05')
> AND
> curExpireDate = '12/31/05' --order by curRecordID
> AND curEntryDate >
> (select top 1 curEntryDate from #temp4 as Y
> where X.curRecordID = Y.curRecordID
> and Y.curExpireDate =
> (select max(curExpireDate) from #temp4 as Z
> where X.curRecordID = Z.curRecordID))
>
> This is the current resultset - which is not the desired resultset
> curRecID curEntryDate curExpireDa
te
> 12783 2005-04-07 00:00:00.000 2005-12-31 00:00:00.000 --X
> 18537 2006-01-27 00:00:00.000 2005-12-31 00:00:00.000 -- +
> 29714 2005-06-29 00:00:00.000 2005-12-31 00:00:00.000 --X
> There is no way for me to isolate my desired row from this resultset
> The desired resultset is this:
> curRecID curEntryDate curExpireDa
te
> 18537 2006-01-27 00:00:00.000 2005-12-31 00:00:00.000
> Any help appreciated,
> Thanks,
> Rich
I respectfully request if someone could help me modify the following query:
The goal is to retrieve a record with a curExpireDate of 12/31/05 where ther
e
is more than one row for a given curRecordID - note: in the following
dataset there is only one correct row that I want to retrieve. So for given
curRecordID there is more than one row for a curExpireDate >= 12/31/05. The
n
if curEntryDate for a curExpireDate of 12/31/05 is greater than the
curEntryDate for curExpireDate of 12/31/06 (for the same curRecordID) then I
want to retrieve that row.
The following subdataset (the actual dataset contains thousands of rows
where I need to do the same thing) contains one row where Max(curExpireDate)
= 12/31/05. I don't want that row. There are 3 more curRecordID's with
curExpireDate of 12/31/05 and 12/31/06. For the rows with curRecordID =
18537 this ID contains a row for curExpireDate = 12/31/05 where
Max(curEntryDate) is greater than the curEntryDate for curExpireDate of
12/31/06 for ID = 18537. None of the otherID's has this condition. I need
to retrieve ID 18537 where curEntryDate = 1/27/06 and curExpireDate =
12/31/05.
Here is the dataset and the query I have been experimenting with:
create table #temp4(curRecordID int,
curEntryDate datetime, curExpireDate datetime)
insert Into #temp4
select 12783, '2005-04-07', '2005-12-31' Union
select 12783, '2004-01-12', '2005-12-31' Union
select 12783, '2006-02-03', '2006-12-31' Union
select 12783, '2005-01-11', '2006-12-31' Union
select 29714, '2005-06-29', '2005-12-31' Union
select 29714, '2005-02-02', '2006-12-31' Union
select 29714, '2005-12-01', '2006-12-31' Union
select 18537, '2004-12-10', '2005-12-31' Union
select 18537, '2006-01-27', '2005-12-31' Union --<--yes want this row
select 18537, '2006-01-10', '2006-12-31' Union
select 38537, '2004-12-10', '2005-12-31' --<--don't want this row
SELECT * FROM #temp4 as X
WHERE
curRecordID NOT IN --the list of recid from beginning
(SELECT curRecordID FROM #temp4 GROUP BY curRecordID
HAVING MAX(curExpireDate)='12/31/05')
AND
curExpireDate = '12/31/05' --order by curRecordID
AND curEntryDate >
(select top 1 curEntryDate from #temp4 as Y
where X.curRecordID = Y.curRecordID
and Y.curExpireDate =
(select max(curExpireDate) from #temp4 as Z
where X.curRecordID = Z.curRecordID))
This is the current resultset - which is not the desired resultset
curRecID curEntryDate curExpireDate
12783 2005-04-07 00:00:00.000 2005-12-31 00:00:00.000 --X
18537 2006-01-27 00:00:00.000 2005-12-31 00:00:00.000 -- +
29714 2005-06-29 00:00:00.000 2005-12-31 00:00:00.000 --X
There is no way for me to isolate my desired row from this resultset
The desired resultset is this:
curRecID curEntryDate curExpireDate
18537 2006-01-27 00:00:00.000 2005-12-31 00:00:00.000
Any help appreciated,
Thanks,
RichI think I found my problem. I changed
(select top 1 curEntryDate from #temp4 as Y...
to
(select Max(curEntryDate) from #temp4 as Y
This is now giving me the desired result in my test case. Hopefully, will
do the same for the actual data.
"Rich" wrote:
> Hello,
> I respectfully request if someone could help me modify the following query
:
> The goal is to retrieve a record with a curExpireDate of 12/31/05 where th
ere
> is more than one row for a given curRecordID - note: in the following
> dataset there is only one correct row that I want to retrieve. So for giv
en
> curRecordID there is more than one row for a curExpireDate >= 12/31/05. T
hen
> if curEntryDate for a curExpireDate of 12/31/05 is greater than the
> curEntryDate for curExpireDate of 12/31/06 (for the same curRecordID) then
I
> want to retrieve that row.
> The following subdataset (the actual dataset contains thousands of rows
> where I need to do the same thing) contains one row where Max(curExpireDat
e)
> = 12/31/05. I don't want that row. There are 3 more curRecordID's with
> curExpireDate of 12/31/05 and 12/31/06. For the rows with curRecordID =
> 18537 this ID contains a row for curExpireDate = 12/31/05 where
> Max(curEntryDate) is greater than the curEntryDate for curExpireDate of
> 12/31/06 for ID = 18537. None of the otherID's has this condition. I nee
d
> to retrieve ID 18537 where curEntryDate = 1/27/06 and curExpireDate =
> 12/31/05.
> Here is the dataset and the query I have been experimenting with:
> create table #temp4(curRecordID int,
> curEntryDate datetime, curExpireDate datetime)
> insert Into #temp4
> select 12783, '2005-04-07', '2005-12-31' Union
> select 12783, '2004-01-12', '2005-12-31' Union
> select 12783, '2006-02-03', '2006-12-31' Union
> select 12783, '2005-01-11', '2006-12-31' Union
> select 29714, '2005-06-29', '2005-12-31' Union
> select 29714, '2005-02-02', '2006-12-31' Union
> select 29714, '2005-12-01', '2006-12-31' Union
> select 18537, '2004-12-10', '2005-12-31' Union
> select 18537, '2006-01-27', '2005-12-31' Union --<--yes want this row
> select 18537, '2006-01-10', '2006-12-31' Union
> select 38537, '2004-12-10', '2005-12-31' --<--don't want this row
> SELECT * FROM #temp4 as X
> WHERE
> curRecordID NOT IN --the list of recid from beginning
> (SELECT curRecordID FROM #temp4 GROUP BY curRecordID
> HAVING MAX(curExpireDate)='12/31/05')
> AND
> curExpireDate = '12/31/05' --order by curRecordID
> AND curEntryDate >
> (select top 1 curEntryDate from #temp4 as Y
> where X.curRecordID = Y.curRecordID
> and Y.curExpireDate =
> (select max(curExpireDate) from #temp4 as Z
> where X.curRecordID = Z.curRecordID))
>
> This is the current resultset - which is not the desired resultset
> curRecID curEntryDate curExpireDa
te
> 12783 2005-04-07 00:00:00.000 2005-12-31 00:00:00.000 --X
> 18537 2006-01-27 00:00:00.000 2005-12-31 00:00:00.000 -- +
> 29714 2005-06-29 00:00:00.000 2005-12-31 00:00:00.000 --X
> There is no way for me to isolate my desired row from this resultset
> The desired resultset is this:
> curRecID curEntryDate curExpireDa
te
> 18537 2006-01-27 00:00:00.000 2005-12-31 00:00:00.000
> Any help appreciated,
> Thanks,
> Rich
problem restoring master , model , msdb datbase
Hi ,
I am having some issues restoring the following databases
i tried to follow the steps in the articles -->
http://support.microsoft.com/kb/224071/en-us
what i have done :
- i am trying to restore all the databases back to the same location after
changing to a new disk
- Have reinstall SQL Server 2000 with SP3a and is i have copied the
PREVIOUS system db .mdf & .ldf files
Ques 1 : What are the sequence in restoring the system db files ?
Ques 2 : do i need to restore tempdb the location that i have installed is
my original location
Ques 3 : i followed the steps of moving 'model' database
SQL Server 2000 and SQL Server 2005
In SQL Server 2000 and in SQL Server 2005, system databases cannot be
detached by using the sp_detach_db stored procedure. Running the sp_detach_db
'model' statement does not work, and you receive the following error message:
Server: Msg 7940, Level 16, State 1, Line 1
System databases master, model, msdb, and tempdb cannot be detached.
To move the model database, you must start SQL Server together with trace
flag 3608 so SQL Server does not recover any database except the master
database.
Note You will not be able to access any user databases after you do this. You
must not perform any operations, other than the following steps, while you
use this trace flag. To add trace flag 3608 as a SQL Server startup parameter,
follow these steps: 1. In SQL Server Enterprise Manager, right-click the
server name, and then click Properties.
2. On the General tab, click Startup Parameters.
3. Add the following new parameter:
-T3608
If you are using SQL Server 2005, you can use SQL Server Configuration
Manager to change the startup parameters of the SQL Server service. For more
information about how to change the startup parameters, visit the following
Microsoft Developer Network (MSDN) Web site:
http://msdn2.microsoft.com/en-us/library/ms190737.aspx (
http://msdn2.microsoft.com/en-us/library/ms190737.aspx)
After you add trace flag 3608, follow these steps: 1. Stop and then restart
SQL Server.
2. Detach the model database by using the following commands:use master
go
sp_detach_db 'model'
go
3. Move the Model.mdf and Modellog.ldf files from the D:\Mssql7\Data folder
to the E:\Sqldata folder.
4. Reattach the model database by using the following commands:use master
go
sp_attach_db 'model','E:\Sqldata\model.mdf','E:\Sqldata\modellog.ldf'
go
at step 4 , when i try to reattach i got the following error something like
"Connection Broken" and i could not continue
i'll apprecaite someone can give me the exact steps of restoring a System DBs
tks & rdgs
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1Hi All ,
I have managed to successfully restore my System Database with extracts from
the 2 articles and hopefully it'll be useful to those who might use it.
if you use the article in whole from Microsoft , somehow it gives error after
moving the "master" db
Step 1 : Moving ?master? database
First, right-click on SQL Server in Enterprise Manager (EM) and choose
Properties. Next,
click the Startup Parameters as shown in Figure A. As you can see in Figure A,
the
following parameters appear in
-d is the fully qualified path for the master database data file.
-e is the fully qualified path for the error log file.
-l is the fully qualified path for the master database log
- Now, if you want to move your files you would remove the current entry and
recreate
your new entry with the correct path. For example, I am going to move the
Master database log to C:\test\.
- I would delete the -l [path] by highlighting the old parameter and choosing
remove
- Next, I would add the following entry, -l C:\test\mastlog.ldf and click OK
twice. Now, you will stop SQL Server and move the mastlog.ldf to its new
location.
Note: Please make sure you move the mastlog.ldf to the location you
configured in your
startup parameters. If you move it to a location that is not specified in the
startup
parameters, SQL Server WILL NOT start.
- Finally, start SQL Server
Step 2 : Moving ?tempdb? database
- login to Query Analyzer
- run the following scripts where 'E:\Sqldata\tempdb.mdf' is the path of the
data file and 'E:\Sqldata\templog.ldf' is the path of the txn log file. Note :
the files can be in different location
use master
go
Alter database tempdb modify file (name = tempdev, filename = 'E:\Sqldata\
tempdb.mdf')
go
Alter database tempdb modify file (name = templog, filename = 'E:\Sqldata\
templog.ldf')
Go
- Stop & ReStart SQL Server
The above steps(1 & 2) are taken from this link ?
http://www.databasejournal.com/features/mssql/article.php/3379901
Step 3 : Moving ?model? database
- In SQL Server Enterprise Manager, right-click the server name, and then
click Properties.
- On the General tab, click Startup Parameters.
- Add the following new parameter: -T3608
- Stop and then restart SQL Server.
- Detach the model database by using the following commands:
use master
go
sp_detach_db 'model'
go
- Move the Model.mdf and Modellog.ldf from its original location to the new
location
- Reattach the model database by using the following commands:
use master
go
sp_attach_db 'model','E:\Sqldata\model.mdf','E:\Sqldata\modellog.ldf'
go
- Remove the -T3608 trace flag from the startup parameters in SQL
Server Enterprise Manager or in SQL
Server Configuration Manager.
- Stop and then restart SQL Server. You can verify the change in file
locations by using the sp_helpfile
stored procedure. For example, use the following command:
use model
go
sp_helpfile
go
Step 4 : Moving ?msdb? database
- In SQL Server Enterprise Manager, right-click the server name, and then
click Properties.
- On the General tab, click Startup Parameters.
- Add the following new parameter: -T3608
- Stop and then restart SQL Server.
- Detach the model database by using the following commands:
use master
go
sp_detach_db 'msdb'
go
- Move the Msdbdata.mdf and msdblog.ldf from its original location to the new
location
- Reattach the model database by using the following commands:
use master
go
sp_attach_db 'msdb','d:\Sqldata\msdb.mdf','E:\Sqldata\msdblog.ldf'
go
- Remove the -T3608 trace flag from the startup parameters in SQL
Server Enterprise Manager or in SQL
Server Configuration Manager.
- Stop and then restart SQL Server. You can verify the change in file
locations by using the sp_helpfile
stored procedure. For example, use the following command:
use msdb
go
sp_helpfile
go
Note : To check if the db is correct you may use ? sp_helpdb in Query
Analyzer take note that the dbid for ?model? MUST be 3 else there could be
some error !!!
The above steps ( 3 & 4) are taken from ?
http://support.microsoft.com/default.aspx?scid=kb;en-us;224071
tks & rdgs
maxzsim wrote:
>Hi ,
> I am having some issues restoring the following databases
> i tried to follow the steps in the articles -->
>http://support.microsoft.com/kb/224071/en-us
>what i have done :
>- i am trying to restore all the databases back to the same location after
>changing to a new disk
> - Have reinstall SQL Server 2000 with SP3a and is i have copied the
>PREVIOUS system db .mdf & .ldf files
>Ques 1 : What are the sequence in restoring the system db files ?
>Ques 2 : do i need to restore tempdb the location that i have installed is
>my original location
>Ques 3 : i followed the steps of moving 'model' database
>SQL Server 2000 and SQL Server 2005
>In SQL Server 2000 and in SQL Server 2005, system databases cannot be
>detached by using the sp_detach_db stored procedure. Running the sp_detach_db
>'model' statement does not work, and you receive the following error message:
>Server: Msg 7940, Level 16, State 1, Line 1
>System databases master, model, msdb, and tempdb cannot be detached.
>To move the model database, you must start SQL Server together with trace
>flag 3608 so SQL Server does not recover any database except the master
>database.
>Note You will not be able to access any user databases after you do this. You
>must not perform any operations, other than the following steps, while you
>use this trace flag. To add trace flag 3608 as a SQL Server startup parameter,
>follow these steps: 1. In SQL Server Enterprise Manager, right-click the
>server name, and then click Properties.
>2. On the General tab, click Startup Parameters.
>3. Add the following new parameter:
>-T3608
>If you are using SQL Server 2005, you can use SQL Server Configuration
>Manager to change the startup parameters of the SQL Server service. For more
>information about how to change the startup parameters, visit the following
>Microsoft Developer Network (MSDN) Web site:
>http://msdn2.microsoft.com/en-us/library/ms190737.aspx (
>http://msdn2.microsoft.com/en-us/library/ms190737.aspx)
>After you add trace flag 3608, follow these steps: 1. Stop and then restart
>SQL Server.
>2. Detach the model database by using the following commands:use master
> go
> sp_detach_db 'model'
> go
>3. Move the Model.mdf and Modellog.ldf files from the D:\Mssql7\Data folder
>to the E:\Sqldata folder.
>4. Reattach the model database by using the following commands:use master
> go
> sp_attach_db 'model','E:\Sqldata\model.mdf','E:\Sqldata\modellog.ldf'
> go
>
>at step 4 , when i try to reattach i got the following error something like
>"Connection Broken" and i could not continue
>i'll apprecaite someone can give me the exact steps of restoring a System DBs
>tks & rdgs
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200606/1
I am having some issues restoring the following databases
i tried to follow the steps in the articles -->
http://support.microsoft.com/kb/224071/en-us
what i have done :
- i am trying to restore all the databases back to the same location after
changing to a new disk
- Have reinstall SQL Server 2000 with SP3a and is i have copied the
PREVIOUS system db .mdf & .ldf files
Ques 1 : What are the sequence in restoring the system db files ?
Ques 2 : do i need to restore tempdb the location that i have installed is
my original location
Ques 3 : i followed the steps of moving 'model' database
SQL Server 2000 and SQL Server 2005
In SQL Server 2000 and in SQL Server 2005, system databases cannot be
detached by using the sp_detach_db stored procedure. Running the sp_detach_db
'model' statement does not work, and you receive the following error message:
Server: Msg 7940, Level 16, State 1, Line 1
System databases master, model, msdb, and tempdb cannot be detached.
To move the model database, you must start SQL Server together with trace
flag 3608 so SQL Server does not recover any database except the master
database.
Note You will not be able to access any user databases after you do this. You
must not perform any operations, other than the following steps, while you
use this trace flag. To add trace flag 3608 as a SQL Server startup parameter,
follow these steps: 1. In SQL Server Enterprise Manager, right-click the
server name, and then click Properties.
2. On the General tab, click Startup Parameters.
3. Add the following new parameter:
-T3608
If you are using SQL Server 2005, you can use SQL Server Configuration
Manager to change the startup parameters of the SQL Server service. For more
information about how to change the startup parameters, visit the following
Microsoft Developer Network (MSDN) Web site:
http://msdn2.microsoft.com/en-us/library/ms190737.aspx (
http://msdn2.microsoft.com/en-us/library/ms190737.aspx)
After you add trace flag 3608, follow these steps: 1. Stop and then restart
SQL Server.
2. Detach the model database by using the following commands:use master
go
sp_detach_db 'model'
go
3. Move the Model.mdf and Modellog.ldf files from the D:\Mssql7\Data folder
to the E:\Sqldata folder.
4. Reattach the model database by using the following commands:use master
go
sp_attach_db 'model','E:\Sqldata\model.mdf','E:\Sqldata\modellog.ldf'
go
at step 4 , when i try to reattach i got the following error something like
"Connection Broken" and i could not continue
i'll apprecaite someone can give me the exact steps of restoring a System DBs
tks & rdgs
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1Hi All ,
I have managed to successfully restore my System Database with extracts from
the 2 articles and hopefully it'll be useful to those who might use it.
if you use the article in whole from Microsoft , somehow it gives error after
moving the "master" db
Step 1 : Moving ?master? database
First, right-click on SQL Server in Enterprise Manager (EM) and choose
Properties. Next,
click the Startup Parameters as shown in Figure A. As you can see in Figure A,
the
following parameters appear in
-d is the fully qualified path for the master database data file.
-e is the fully qualified path for the error log file.
-l is the fully qualified path for the master database log
- Now, if you want to move your files you would remove the current entry and
recreate
your new entry with the correct path. For example, I am going to move the
Master database log to C:\test\.
- I would delete the -l [path] by highlighting the old parameter and choosing
remove
- Next, I would add the following entry, -l C:\test\mastlog.ldf and click OK
twice. Now, you will stop SQL Server and move the mastlog.ldf to its new
location.
Note: Please make sure you move the mastlog.ldf to the location you
configured in your
startup parameters. If you move it to a location that is not specified in the
startup
parameters, SQL Server WILL NOT start.
- Finally, start SQL Server
Step 2 : Moving ?tempdb? database
- login to Query Analyzer
- run the following scripts where 'E:\Sqldata\tempdb.mdf' is the path of the
data file and 'E:\Sqldata\templog.ldf' is the path of the txn log file. Note :
the files can be in different location
use master
go
Alter database tempdb modify file (name = tempdev, filename = 'E:\Sqldata\
tempdb.mdf')
go
Alter database tempdb modify file (name = templog, filename = 'E:\Sqldata\
templog.ldf')
Go
- Stop & ReStart SQL Server
The above steps(1 & 2) are taken from this link ?
http://www.databasejournal.com/features/mssql/article.php/3379901
Step 3 : Moving ?model? database
- In SQL Server Enterprise Manager, right-click the server name, and then
click Properties.
- On the General tab, click Startup Parameters.
- Add the following new parameter: -T3608
- Stop and then restart SQL Server.
- Detach the model database by using the following commands:
use master
go
sp_detach_db 'model'
go
- Move the Model.mdf and Modellog.ldf from its original location to the new
location
- Reattach the model database by using the following commands:
use master
go
sp_attach_db 'model','E:\Sqldata\model.mdf','E:\Sqldata\modellog.ldf'
go
- Remove the -T3608 trace flag from the startup parameters in SQL
Server Enterprise Manager or in SQL
Server Configuration Manager.
- Stop and then restart SQL Server. You can verify the change in file
locations by using the sp_helpfile
stored procedure. For example, use the following command:
use model
go
sp_helpfile
go
Step 4 : Moving ?msdb? database
- In SQL Server Enterprise Manager, right-click the server name, and then
click Properties.
- On the General tab, click Startup Parameters.
- Add the following new parameter: -T3608
- Stop and then restart SQL Server.
- Detach the model database by using the following commands:
use master
go
sp_detach_db 'msdb'
go
- Move the Msdbdata.mdf and msdblog.ldf from its original location to the new
location
- Reattach the model database by using the following commands:
use master
go
sp_attach_db 'msdb','d:\Sqldata\msdb.mdf','E:\Sqldata\msdblog.ldf'
go
- Remove the -T3608 trace flag from the startup parameters in SQL
Server Enterprise Manager or in SQL
Server Configuration Manager.
- Stop and then restart SQL Server. You can verify the change in file
locations by using the sp_helpfile
stored procedure. For example, use the following command:
use msdb
go
sp_helpfile
go
Note : To check if the db is correct you may use ? sp_helpdb in Query
Analyzer take note that the dbid for ?model? MUST be 3 else there could be
some error !!!
The above steps ( 3 & 4) are taken from ?
http://support.microsoft.com/default.aspx?scid=kb;en-us;224071
tks & rdgs
maxzsim wrote:
>Hi ,
> I am having some issues restoring the following databases
> i tried to follow the steps in the articles -->
>http://support.microsoft.com/kb/224071/en-us
>what i have done :
>- i am trying to restore all the databases back to the same location after
>changing to a new disk
> - Have reinstall SQL Server 2000 with SP3a and is i have copied the
>PREVIOUS system db .mdf & .ldf files
>Ques 1 : What are the sequence in restoring the system db files ?
>Ques 2 : do i need to restore tempdb the location that i have installed is
>my original location
>Ques 3 : i followed the steps of moving 'model' database
>SQL Server 2000 and SQL Server 2005
>In SQL Server 2000 and in SQL Server 2005, system databases cannot be
>detached by using the sp_detach_db stored procedure. Running the sp_detach_db
>'model' statement does not work, and you receive the following error message:
>Server: Msg 7940, Level 16, State 1, Line 1
>System databases master, model, msdb, and tempdb cannot be detached.
>To move the model database, you must start SQL Server together with trace
>flag 3608 so SQL Server does not recover any database except the master
>database.
>Note You will not be able to access any user databases after you do this. You
>must not perform any operations, other than the following steps, while you
>use this trace flag. To add trace flag 3608 as a SQL Server startup parameter,
>follow these steps: 1. In SQL Server Enterprise Manager, right-click the
>server name, and then click Properties.
>2. On the General tab, click Startup Parameters.
>3. Add the following new parameter:
>-T3608
>If you are using SQL Server 2005, you can use SQL Server Configuration
>Manager to change the startup parameters of the SQL Server service. For more
>information about how to change the startup parameters, visit the following
>Microsoft Developer Network (MSDN) Web site:
>http://msdn2.microsoft.com/en-us/library/ms190737.aspx (
>http://msdn2.microsoft.com/en-us/library/ms190737.aspx)
>After you add trace flag 3608, follow these steps: 1. Stop and then restart
>SQL Server.
>2. Detach the model database by using the following commands:use master
> go
> sp_detach_db 'model'
> go
>3. Move the Model.mdf and Modellog.ldf files from the D:\Mssql7\Data folder
>to the E:\Sqldata folder.
>4. Reattach the model database by using the following commands:use master
> go
> sp_attach_db 'model','E:\Sqldata\model.mdf','E:\Sqldata\modellog.ldf'
> go
>
>at step 4 , when i try to reattach i got the following error something like
>"Connection Broken" and i could not continue
>i'll apprecaite someone can give me the exact steps of restoring a System DBs
>tks & rdgs
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200606/1
problem restoring master , model , msdb datbase
Hi ,
I am having some issues restoring the following databases
i tried to follow the steps in the articles -->
http://support.microsoft.com/kb/224071/en-us
what i have done :
- i am trying to restore all the databases back to the same location after
changing to a new disk
- Have reinstall SQL Server 2000 with SP3a and is i have copied the
PREVIOUS system db .mdf & .ldf files
Ques 1 : What are the sequence in restoring the system db files ?
Ques 2 : do i need to restore tempdb the location that i have installed is
my original location
Ques 3 : i followed the steps of moving 'model' database
SQL Server 2000 and SQL Server 2005
In SQL Server 2000 and in SQL Server 2005, system databases cannot be
detached by using the sp_detach_db stored procedure. Running the sp_detach_d
b
'model' statement does not work, and you receive the following error message
:
Server: Msg 7940, Level 16, State 1, Line 1
System databases master, model, msdb, and tempdb cannot be detached.
To move the model database, you must start SQL Server together with trace
flag 3608 so SQL Server does not recover any database except the master
database.
Note You will not be able to access any user databases after you do this. Yo
u
must not perform any operations, other than the following steps, while you
use this trace flag. To add trace flag 3608 as a SQL Server startup paramete
r,
follow these steps: 1. In SQL Server Enterprise Manager, right-click the
server name, and then click Properties.
2. On the General tab, click Startup Parameters.
3. Add the following new parameter:
-T3608
If you are using SQL Server 2005, you can use SQL Server Configuration
Manager to change the startup parameters of the SQL Server service. For more
information about how to change the startup parameters, visit the following
Microsoft Developer Network (MSDN) Web site:
http://msdn2.microsoft.com/en-us/library/ms190737.aspx (
http://msdn2.microsoft.com/en-us/library/ms190737.aspx)
After you add trace flag 3608, follow these steps: 1. Stop and then restart
SQL Server.
2. Detach the model database by using the following commands:use master
go
sp_detach_db 'model'
go
3. Move the Model.mdf and Modellog.ldf files from the D:\Mssql7\Data folder
to the E:\Sqldata folder.
4. Reattach the model database by using the following commands:use master
go
sp_attach_db 'model','E:\Sqldata\model.mdf','E:\Sqldata\modellog.ldf'
go
at step 4 , when i try to reattach i got the following error something like
"Connection Broken" and i could not continue
i'll apprecaite someone can give me the exact steps of restoring a System DB
s
tks & rdgs
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200605/1Hi All ,
I have managed to successfully restore my System Database with extracts from
the 2 articles and hopefully it'll be useful to those who might use it.
if you use the article in whole from Microsoft , somehow it gives error afte
r
moving the "master" db
Step 1 : Moving master database
First, right-click on SQL Server in Enterprise Manager (EM) and choose
Properties. Next,
click the Startup Parameters as shown in Figure A. As you can see in Figure
A,
the
following parameters appear in
-d is the fully qualified path for the master database data file.
-e is the fully qualified path for the error log file.
-l is the fully qualified path for the master database log
- Now, if you want to move your files you would remove the current entry and
recreate
your new entry with the correct path. For example, I am going to move the
Master database log to C:\test\.
- I would delete the -l [path] by highlighting the old parameter and cho
osing
remove
- Next, I would add the following entry, -l C:\test\mastlog.ldf and click OK
twice. Now, you will stop SQL Server and move the mastlog.ldf to its new
location.
Note: Please make sure you move the mastlog.ldf to the location you
configured in your
startup parameters. If you move it to a location that is not specified in th
e
startup
parameters, SQL Server WILL NOT start.
- Finally, start SQL Server
Step 2 : Moving tempdb database
- login to Query Analyzer
- run the following scripts where 'E:\Sqldata\tempdb.mdf' is the path of the
data file and 'E:\Sqldata\templog.ldf' is the path of the txn log file. Note
:
the files can be in different location
use master
go
Alter database tempdb modify file (name = tempdev, filename = 'E:\Sqldata\
tempdb.mdf')
go
Alter database tempdb modify file (name = templog, filename = 'E:\Sqldata\
templog.ldf')
Go
- Stop & ReStart SQL Server
The above steps(1 & 2) are taken from this link ?
http://www.databasejournal.com/feat...cle.php/3379901
Step 3 : Moving model database
- In SQL Server Enterprise Manager, right-click the server name, and then
click Properties.
- On the General tab, click Startup Parameters.
- Add the following new parameter: -T3608
- Stop and then restart SQL Server.
- Detach the model database by using the following commands:
use master
go
sp_detach_db 'model'
go
- Move the Model.mdf and Modellog.ldf from its original location to the new
location
- Reattach the model database by using the following commands:
use master
go
sp_attach_db 'model','E:\Sqldata\model.mdf','E:\Sqldata\modellog.ldf'
go
- Remove the -T3608 trace flag from the startup parameters in SQL
Server Enterprise Manager or in SQL
Server Configuration Manager.
- Stop and then restart SQL Server. You can verify the change in file
locations by using the sp_helpfile
stored procedure. For example, use the following command:
use model
go
sp_helpfile
go
Step 4 : Moving msdb database
- In SQL Server Enterprise Manager, right-click the server name, and then
click Properties.
- On the General tab, click Startup Parameters.
- Add the following new parameter: -T3608
- Stop and then restart SQL Server.
- Detach the model database by using the following commands:
use master
go
sp_detach_db 'msdb'
go
- Move the Msdbdata.mdf and msdblog.ldf from its original location to the ne
w
location
- Reattach the model database by using the following commands:
use master
go
sp_attach_db 'msdb','d:\Sqldata\msdb.mdf','E:\Sqldata\msdblog.ldf'
go
- Remove the -T3608 trace flag from the startup parameters in SQL
Server Enterprise Manager or in SQL
Server Configuration Manager.
- Stop and then restart SQL Server. You can verify the change in file
locations by using the sp_helpfile
stored procedure. For example, use the following command:
use msdb
go
sp_helpfile
go
Note : To check if the db is correct you may use ? sp_helpdb in Query
Analyzer take note that the dbid for model MUST be 3 else there could be
some error !!!
The above steps ( 3 & 4) are taken from ?
http://support.microsoft.com/defaul...kb;en-us;224071
tks & rdgs
maxzsim wrote:
>Hi ,
> I am having some issues restoring the following databases
> i tried to follow the steps in the articles -->
>http://support.microsoft.com/kb/224071/en-us
>what i have done :
>- i am trying to restore all the databases back to the same location after
>changing to a new disk
> - Have reinstall SQL Server 2000 with SP3a and is i have copied the
>PREVIOUS system db .mdf & .ldf files
>Ques 1 : What are the sequence in restoring the system db files ?
>Ques 2 : do i need to restore tempdb the location that i have installed is
>my original location
>Ques 3 : i followed the steps of moving 'model' database
>SQL Server 2000 and SQL Server 2005
>In SQL Server 2000 and in SQL Server 2005, system databases cannot be
>detached by using the sp_detach_db stored procedure. Running the sp_detach_
db
>'model' statement does not work, and you receive the following error messag
e:
>Server: Msg 7940, Level 16, State 1, Line 1
>System databases master, model, msdb, and tempdb cannot be detached.
>To move the model database, you must start SQL Server together with trace
>flag 3608 so SQL Server does not recover any database except the master
>database.
>Note You will not be able to access any user databases after you do this. Y
ou
>must not perform any operations, other than the following steps, while you
>use this trace flag. To add trace flag 3608 as a SQL Server startup paramet
er,
>follow these steps: 1. In SQL Server Enterprise Manager, right-click the
>server name, and then click Properties.
>2. On the General tab, click Startup Parameters.
>3. Add the following new parameter:
>-T3608
>If you are using SQL Server 2005, you can use SQL Server Configuration
>Manager to change the startup parameters of the SQL Server service. For mor
e
>information about how to change the startup parameters, visit the following
>Microsoft Developer Network (MSDN) Web site:
>http://msdn2.microsoft.com/en-us/library/ms190737.aspx (
>http://msdn2.microsoft.com/en-us/library/ms190737.aspx)
>After you add trace flag 3608, follow these steps: 1. Stop and then restart
>SQL Server.
>2. Detach the model database by using the following commands:use master
> go
> sp_detach_db 'model'
> go
>3. Move the Model.mdf and Modellog.ldf files from the D:\Mssql7\Data folder
>to the E:\Sqldata folder.
>4. Reattach the model database by using the following commands:use master
> go
> sp_attach_db 'model','E:\Sqldata\model.mdf','E:\Sqldata\modellog.ldf'
> go
>
>at step 4 , when i try to reattach i got the following error something like
>"Connection Broken" and i could not continue
>i'll apprecaite someone can give me the exact steps of restoring a System D
Bs
>tks & rdgs
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200606/1
I am having some issues restoring the following databases
i tried to follow the steps in the articles -->
http://support.microsoft.com/kb/224071/en-us
what i have done :
- i am trying to restore all the databases back to the same location after
changing to a new disk
- Have reinstall SQL Server 2000 with SP3a and is i have copied the
PREVIOUS system db .mdf & .ldf files
Ques 1 : What are the sequence in restoring the system db files ?
Ques 2 : do i need to restore tempdb the location that i have installed is
my original location
Ques 3 : i followed the steps of moving 'model' database
SQL Server 2000 and SQL Server 2005
In SQL Server 2000 and in SQL Server 2005, system databases cannot be
detached by using the sp_detach_db stored procedure. Running the sp_detach_d
b
'model' statement does not work, and you receive the following error message
:
Server: Msg 7940, Level 16, State 1, Line 1
System databases master, model, msdb, and tempdb cannot be detached.
To move the model database, you must start SQL Server together with trace
flag 3608 so SQL Server does not recover any database except the master
database.
Note You will not be able to access any user databases after you do this. Yo
u
must not perform any operations, other than the following steps, while you
use this trace flag. To add trace flag 3608 as a SQL Server startup paramete
r,
follow these steps: 1. In SQL Server Enterprise Manager, right-click the
server name, and then click Properties.
2. On the General tab, click Startup Parameters.
3. Add the following new parameter:
-T3608
If you are using SQL Server 2005, you can use SQL Server Configuration
Manager to change the startup parameters of the SQL Server service. For more
information about how to change the startup parameters, visit the following
Microsoft Developer Network (MSDN) Web site:
http://msdn2.microsoft.com/en-us/library/ms190737.aspx (
http://msdn2.microsoft.com/en-us/library/ms190737.aspx)
After you add trace flag 3608, follow these steps: 1. Stop and then restart
SQL Server.
2. Detach the model database by using the following commands:use master
go
sp_detach_db 'model'
go
3. Move the Model.mdf and Modellog.ldf files from the D:\Mssql7\Data folder
to the E:\Sqldata folder.
4. Reattach the model database by using the following commands:use master
go
sp_attach_db 'model','E:\Sqldata\model.mdf','E:\Sqldata\modellog.ldf'
go
at step 4 , when i try to reattach i got the following error something like
"Connection Broken" and i could not continue
i'll apprecaite someone can give me the exact steps of restoring a System DB
s
tks & rdgs
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200605/1Hi All ,
I have managed to successfully restore my System Database with extracts from
the 2 articles and hopefully it'll be useful to those who might use it.
if you use the article in whole from Microsoft , somehow it gives error afte
r
moving the "master" db
Step 1 : Moving master database
First, right-click on SQL Server in Enterprise Manager (EM) and choose
Properties. Next,
click the Startup Parameters as shown in Figure A. As you can see in Figure
A,
the
following parameters appear in
-d is the fully qualified path for the master database data file.
-e is the fully qualified path for the error log file.
-l is the fully qualified path for the master database log
- Now, if you want to move your files you would remove the current entry and
recreate
your new entry with the correct path. For example, I am going to move the
Master database log to C:\test\.
- I would delete the -l [path] by highlighting the old parameter and cho
osing
remove
- Next, I would add the following entry, -l C:\test\mastlog.ldf and click OK
twice. Now, you will stop SQL Server and move the mastlog.ldf to its new
location.
Note: Please make sure you move the mastlog.ldf to the location you
configured in your
startup parameters. If you move it to a location that is not specified in th
e
startup
parameters, SQL Server WILL NOT start.
- Finally, start SQL Server
Step 2 : Moving tempdb database
- login to Query Analyzer
- run the following scripts where 'E:\Sqldata\tempdb.mdf' is the path of the
data file and 'E:\Sqldata\templog.ldf' is the path of the txn log file. Note
:
the files can be in different location
use master
go
Alter database tempdb modify file (name = tempdev, filename = 'E:\Sqldata\
tempdb.mdf')
go
Alter database tempdb modify file (name = templog, filename = 'E:\Sqldata\
templog.ldf')
Go
- Stop & ReStart SQL Server
The above steps(1 & 2) are taken from this link ?
http://www.databasejournal.com/feat...cle.php/3379901
Step 3 : Moving model database
- In SQL Server Enterprise Manager, right-click the server name, and then
click Properties.
- On the General tab, click Startup Parameters.
- Add the following new parameter: -T3608
- Stop and then restart SQL Server.
- Detach the model database by using the following commands:
use master
go
sp_detach_db 'model'
go
- Move the Model.mdf and Modellog.ldf from its original location to the new
location
- Reattach the model database by using the following commands:
use master
go
sp_attach_db 'model','E:\Sqldata\model.mdf','E:\Sqldata\modellog.ldf'
go
- Remove the -T3608 trace flag from the startup parameters in SQL
Server Enterprise Manager or in SQL
Server Configuration Manager.
- Stop and then restart SQL Server. You can verify the change in file
locations by using the sp_helpfile
stored procedure. For example, use the following command:
use model
go
sp_helpfile
go
Step 4 : Moving msdb database
- In SQL Server Enterprise Manager, right-click the server name, and then
click Properties.
- On the General tab, click Startup Parameters.
- Add the following new parameter: -T3608
- Stop and then restart SQL Server.
- Detach the model database by using the following commands:
use master
go
sp_detach_db 'msdb'
go
- Move the Msdbdata.mdf and msdblog.ldf from its original location to the ne
w
location
- Reattach the model database by using the following commands:
use master
go
sp_attach_db 'msdb','d:\Sqldata\msdb.mdf','E:\Sqldata\msdblog.ldf'
go
- Remove the -T3608 trace flag from the startup parameters in SQL
Server Enterprise Manager or in SQL
Server Configuration Manager.
- Stop and then restart SQL Server. You can verify the change in file
locations by using the sp_helpfile
stored procedure. For example, use the following command:
use msdb
go
sp_helpfile
go
Note : To check if the db is correct you may use ? sp_helpdb in Query
Analyzer take note that the dbid for model MUST be 3 else there could be
some error !!!
The above steps ( 3 & 4) are taken from ?
http://support.microsoft.com/defaul...kb;en-us;224071
tks & rdgs
maxzsim wrote:
>Hi ,
> I am having some issues restoring the following databases
> i tried to follow the steps in the articles -->
>http://support.microsoft.com/kb/224071/en-us
>what i have done :
>- i am trying to restore all the databases back to the same location after
>changing to a new disk
> - Have reinstall SQL Server 2000 with SP3a and is i have copied the
>PREVIOUS system db .mdf & .ldf files
>Ques 1 : What are the sequence in restoring the system db files ?
>Ques 2 : do i need to restore tempdb the location that i have installed is
>my original location
>Ques 3 : i followed the steps of moving 'model' database
>SQL Server 2000 and SQL Server 2005
>In SQL Server 2000 and in SQL Server 2005, system databases cannot be
>detached by using the sp_detach_db stored procedure. Running the sp_detach_
db
>'model' statement does not work, and you receive the following error messag
e:
>Server: Msg 7940, Level 16, State 1, Line 1
>System databases master, model, msdb, and tempdb cannot be detached.
>To move the model database, you must start SQL Server together with trace
>flag 3608 so SQL Server does not recover any database except the master
>database.
>Note You will not be able to access any user databases after you do this. Y
ou
>must not perform any operations, other than the following steps, while you
>use this trace flag. To add trace flag 3608 as a SQL Server startup paramet
er,
>follow these steps: 1. In SQL Server Enterprise Manager, right-click the
>server name, and then click Properties.
>2. On the General tab, click Startup Parameters.
>3. Add the following new parameter:
>-T3608
>If you are using SQL Server 2005, you can use SQL Server Configuration
>Manager to change the startup parameters of the SQL Server service. For mor
e
>information about how to change the startup parameters, visit the following
>Microsoft Developer Network (MSDN) Web site:
>http://msdn2.microsoft.com/en-us/library/ms190737.aspx (
>http://msdn2.microsoft.com/en-us/library/ms190737.aspx)
>After you add trace flag 3608, follow these steps: 1. Stop and then restart
>SQL Server.
>2. Detach the model database by using the following commands:use master
> go
> sp_detach_db 'model'
> go
>3. Move the Model.mdf and Modellog.ldf files from the D:\Mssql7\Data folder
>to the E:\Sqldata folder.
>4. Reattach the model database by using the following commands:use master
> go
> sp_attach_db 'model','E:\Sqldata\model.mdf','E:\Sqldata\modellog.ldf'
> go
>
>at step 4 , when i try to reattach i got the following error something like
>"Connection Broken" and i could not continue
>i'll apprecaite someone can give me the exact steps of restoring a System D
Bs
>tks & rdgs
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200606/1
Problem Restoring Database
I have a backup of a database from SQL Server. I try to restore that backup file to SQL Server Express and get the following error:
Cannot open backup device 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\TEST.BAK'. Operating system error 5(Access is denied.).
RESTORE HEADERONLY is terminating abnormally. (.Net SqlClient Data Provider)
When restoring other files, I would get this same error message, copy it to another folder and it would work. With this particular one, no matter what folder I copy it to I get the same error message. From what I can find online is that its a permission thing, but I cant find anywhere to change the permissions.
Any help is appreciated.
That can be based on serveral problems which either depends on the way you authenticate to the database server (you did not mention that) and the service account (running SQL Server authentication) or you own account (running Windows authenticaiton) does not have the appropiate rights to access the folder or you are running Vista, which needs elevated rights to access the special folder opn your system.
Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||I connect using mixed authentication. I am running windows XP.|||I have hit this issue, detaching a database then copying the .mdf file then attempting to attach on another server.
The 'copied-to' server in my case is running on Vista. I eventually bludgeoned it into working by opening up all the permissions on the containing folder and the .mdf file itself; so I guess this tends to confirm it's a permissions issue, but 'just give everyone access to everything' isn't really a satisfactory answer! Can anyone point us in the direction of more detail on how permissions and SQL Express interact - I gather it's particularly tricky with Vista and UAC etc. Jens - could you expand on the bit about Vista and the 'special folder' - I take it you mean the folder containing the .mdf?
TIA
pg
|||What I ended up doing was setting the mssql folder proprty to full access and took read only off and it worked. Thanks.|||There's some more about this issue here: http://msdn2.microsoft.com/en-us/library/ms189128.aspxpg
Problem Restoring Database
I have a backup of a database from SQL Server. I try to restore that backup file to SQL Server Express and get the following error:
Cannot open backup device 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\TEST.BAK'. Operating system error 5(Access is denied.).
RESTORE HEADERONLY is terminating abnormally. (.Net SqlClient Data Provider)
When restoring other files, I would get this same error message, copy it to another folder and it would work. With this particular one, no matter what folder I copy it to I get the same error message. From what I can find online is that its a permission thing, but I cant find anywhere to change the permissions.
Any help is appreciated.
That can be based on serveral problems which either depends on the way you authenticate to the database server (you did not mention that) and the service account (running SQL Server authentication) or you own account (running Windows authenticaiton) does not have the appropiate rights to access the folder or you are running Vista, which needs elevated rights to access the special folder opn your system.
Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||I connect using mixed authentication. I am running windows XP.|||I have hit this issue, detaching a database then copying the .mdf file then attempting to attach on another server.
The 'copied-to' server in my case is running on Vista. I eventually bludgeoned it into working by opening up all the permissions on the containing folder and the .mdf file itself; so I guess this tends to confirm it's a permissions issue, but 'just give everyone access to everything' isn't really a satisfactory answer! Can anyone point us in the direction of more detail on how permissions and SQL Express interact - I gather it's particularly tricky with Vista and UAC etc. Jens - could you expand on the bit about Vista and the 'special folder' - I take it you mean the folder containing the .mdf?
TIA
pg
|||What I ended up doing was setting the mssql folder proprty to full access and took read only off and it worked. Thanks.|||There's some more about this issue here: http://msdn2.microsoft.com/en-us/library/ms189128.aspxpg
Problem restoring a SQL 2005 database from a backup device
I am trying to restore a database from a backup device and when I try
to add the backup device in the restore database wizard I receive the
following error message:
An exception occurred while executing a transact-sql statement or
batch.
Too many backup devices specified for backup or restore; only 64 are
allowed. RESTORE HEADER ONLY is terminating abnormally.
The backup device was created using SQL Server 2005 and I am trying to
restore the database to an Express version of 2005.
I can restore the database successfully if I restore it to the same
server that I created the backup device on.
For a little background, I am trying to backup a database on a server
to a laptop for business continuity purposes. The database is under 1
gig in total size.
Any insight is greatly appreciated.Hi
Other posts indicate this error message occurs if you try and restore from
SQL 2005 to SQL 2000 see http://tinyurl.com/nqfls and http://tinyurl.com/kajam
John
"wolf.emissar@.gmail.com" wrote:
> I am trying to restore a database from a backup device and when I try
> to add the backup device in the restore database wizard I receive the
> following error message:
> An exception occurred while executing a transact-sql statement or
> batch.
> Too many backup devices specified for backup or restore; only 64 are
> allowed. RESTORE HEADER ONLY is terminating abnormally.
> The backup device was created using SQL Server 2005 and I am trying to
> restore the database to an Express version of 2005.
> I can restore the database successfully if I restore it to the same
> server that I created the backup device on.
> For a little background, I am trying to backup a database on a server
> to a laptop for business continuity purposes. The database is under 1
> gig in total size.
> Any insight is greatly appreciated.
>|||John, thanks for the response. I am trying to restore to a 2005
instance but it is an Express version, I don't know if this is the
source of the problem or not.
In the second link you attached Tibor mentions logging into a 2000
instance. This sounds very much like the error I am receiving, do you
know what he is referring to? Can express 2005 be configured to run as
a 2000 instance?
John Bell wrote:
> Hi
> Other posts indicate this error message occurs if you try and restore from
> SQL 2005 to SQL 2000 see http://tinyurl.com/nqfls and http://tinyurl.com/kajam
> John
> "wolf.emissar@.gmail.com" wrote:
> > I am trying to restore a database from a backup device and when I try
> > to add the backup device in the restore database wizard I receive the
> > following error message:
> >
> > An exception occurred while executing a transact-sql statement or
> > batch.
> >
> > Too many backup devices specified for backup or restore; only 64 are
> > allowed. RESTORE HEADER ONLY is terminating abnormally.
> >
> > The backup device was created using SQL Server 2005 and I am trying to
> > restore the database to an Express version of 2005.
> >
> > I can restore the database successfully if I restore it to the same
> > server that I created the backup device on.
> >
> > For a little background, I am trying to backup a database on a server
> > to a laptop for business continuity purposes. The database is under 1
> > gig in total size.
> >
> > Any insight is greatly appreciated.
> >
> >|||Hi
Both posters were trying to restore a SQL 2005 database onto a SQL 2000
instance and they have the error message you get. Are you sure that there are
no SQL 2000 instances on the machine you have? Database restores are
compatible between all versions of SQL 2005 therefore you should not have any
problems. Is this a beta or CTP version?
John
"wolf.emissar@.gmail.com" wrote:
> John, thanks for the response. I am trying to restore to a 2005
> instance but it is an Express version, I don't know if this is the
> source of the problem or not.
> In the second link you attached Tibor mentions logging into a 2000
> instance. This sounds very much like the error I am receiving, do you
> know what he is referring to? Can express 2005 be configured to run as
> a 2000 instance?
> John Bell wrote:
> > Hi
> >
> > Other posts indicate this error message occurs if you try and restore from
> > SQL 2005 to SQL 2000 see http://tinyurl.com/nqfls and http://tinyurl.com/kajam
> >
> > John
> >
> > "wolf.emissar@.gmail.com" wrote:
> >
> > > I am trying to restore a database from a backup device and when I try
> > > to add the backup device in the restore database wizard I receive the
> > > following error message:
> > >
> > > An exception occurred while executing a transact-sql statement or
> > > batch.
> > >
> > > Too many backup devices specified for backup or restore; only 64 are
> > > allowed. RESTORE HEADER ONLY is terminating abnormally.
> > >
> > > The backup device was created using SQL Server 2005 and I am trying to
> > > restore the database to an Express version of 2005.
> > >
> > > I can restore the database successfully if I restore it to the same
> > > server that I created the backup device on.
> > >
> > > For a little background, I am trying to backup a database on a server
> > > to a laptop for business continuity purposes. The database is under 1
> > > gig in total size.
> > >
> > > Any insight is greatly appreciated.
> > >
> > >
>|||wolf.emissar@.gmail.com wrote:
> I am trying to restore a database from a backup device and when I try
> to add the backup device in the restore database wizard I receive the
> following error message:
> An exception occurred while executing a transact-sql statement or
> batch.
> Too many backup devices specified for backup or restore; only 64 are
> allowed. RESTORE HEADER ONLY is terminating abnormally.
> The backup device was created using SQL Server 2005 and I am trying to
> restore the database to an Express version of 2005.
> I can restore the database successfully if I restore it to the same
> server that I created the backup device on.
> For a little background, I am trying to backup a database on a server
> to a laptop for business continuity purposes. The database is under 1
> gig in total size.
> Any insight is greatly appreciated.
>
Could you try to post the sql statement you are running?
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator|||Steen, here is the SQL that I am running for the restore:
restore database wolf_sonar
from disk =3D 'C:\sqlbackups\sonar_wolf_new.bak'
and I recieve the following error message:
Msg 3205, Level 16, State 2, Line 1
Too many backup devices specified for backup or restore; only 64 are
allowed.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
One thing that I did notice that is I don't have a compatibility level
of 90 on the local server on the laptop that the wolf_sonar database
resides on. I only have 70 and 80 as available choices.
John to answer your question, I do not have any 2000 instances on the
machine that I am trying to restore the database to. This is a fairly
new machine and I actually just installed 2005 Express on this box a
couple of days. As a further chack I only see sql server 2005 in add /
remove programs.
Steen Persson (DK) wrote:
> wolf.emissar@.gmail.com wrote:
> > I am trying to restore a database from a backup device and when I try
> > to add the backup device in the restore database wizard I receive the
> > following error message:
> >
> > An exception occurred while executing a transact-sql statement or
> > batch.
> >
> > Too many backup devices specified for backup or restore; only 64 are
> > allowed. RESTORE HEADER ONLY is terminating abnormally.
> >
> > The backup device was created using SQL Server 2005 and I am trying to
> > restore the database to an Express version of 2005.
> >
> > I can restore the database successfully if I restore it to the same
> > server that I created the backup device on.
> >
> > For a little background, I am trying to backup a database on a server
> > to a laptop for business continuity purposes. The database is under 1
> > gig in total size.
> >
> > Any insight is greatly appreciated.
> >
> Could you try to post the sql statement you are running?
>
> --
> Regards
> Steen Schl=FCter Persson
> Databaseadministrator / Systemadministrator|||John, I have a tried a few different version of 2005 express. The
latest one that I have installed is Version 9.00.2047.00. I do not
believe that this is a beta version.
John Bell wrote:
> Hi
> Both posters were trying to restore a SQL 2005 database onto a SQL 2000
> instance and they have the error message you get. Are you sure that there are
> no SQL 2000 instances on the machine you have? Database restores are
> compatible between all versions of SQL 2005 therefore you should not have any
> problems. Is this a beta or CTP version?
> John
> "wolf.emissar@.gmail.com" wrote:
> > John, thanks for the response. I am trying to restore to a 2005
> > instance but it is an Express version, I don't know if this is the
> > source of the problem or not.
> >
> > In the second link you attached Tibor mentions logging into a 2000
> > instance. This sounds very much like the error I am receiving, do you
> > know what he is referring to? Can express 2005 be configured to run as
> > a 2000 instance?
> >
> > John Bell wrote:
> > > Hi
> > >
> > > Other posts indicate this error message occurs if you try and restore from
> > > SQL 2005 to SQL 2000 see http://tinyurl.com/nqfls and http://tinyurl.com/kajam
> > >
> > > John
> > >
> > > "wolf.emissar@.gmail.com" wrote:
> > >
> > > > I am trying to restore a database from a backup device and when I try
> > > > to add the backup device in the restore database wizard I receive the
> > > > following error message:
> > > >
> > > > An exception occurred while executing a transact-sql statement or
> > > > batch.
> > > >
> > > > Too many backup devices specified for backup or restore; only 64 are
> > > > allowed. RESTORE HEADER ONLY is terminating abnormally.
> > > >
> > > > The backup device was created using SQL Server 2005 and I am trying to
> > > > restore the database to an Express version of 2005.
> > > >
> > > > I can restore the database successfully if I restore it to the same
> > > > server that I created the backup device on.
> > > >
> > > > For a little background, I am trying to backup a database on a server
> > > > to a laptop for business continuity purposes. The database is under 1
> > > > gig in total size.
> > > >
> > > > Any insight is greatly appreciated.
> > > >
> > > >
> >
> >|||Hi
If you ran SELECT @.@.VERSION to get the version number in your other post,
then you are on SQL 2005 Service Pack 1. If you obtained it otherwise run the
command in Management Studio or SQLCMD.
I am not sure where you are getting the compatibility level from but if you
ran EXEC sp_helpdb 'MyDatabase' it will tell you at the end column of the
first resultset.
Try running:
RESTORE FILELISTONLY
FROM disk = 'C:\sqlbackups\sonar_wolf_new.bak'
John
"wolf.emissar@.gmail.com" wrote:
> Steen, here is the SQL that I am running for the restore:
> restore database wolf_sonar
> from disk = 'C:\sqlbackups\sonar_wolf_new.bak'
> and I recieve the following error message:
> Msg 3205, Level 16, State 2, Line 1
> Too many backup devices specified for backup or restore; only 64 are
> allowed.
> Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> One thing that I did notice that is I don't have a compatibility level
> of 90 on the local server on the laptop that the wolf_sonar database
> resides on. I only have 70 and 80 as available choices.
> John to answer your question, I do not have any 2000 instances on the
> machine that I am trying to restore the database to. This is a fairly
> new machine and I actually just installed 2005 Express on this box a
> couple of days. As a further chack I only see sql server 2005 in add /
> remove programs.
>
> Steen Persson (DK) wrote:
> > wolf.emissar@.gmail.com wrote:
> > > I am trying to restore a database from a backup device and when I try
> > > to add the backup device in the restore database wizard I receive the
> > > following error message:
> > >
> > > An exception occurred while executing a transact-sql statement or
> > > batch.
> > >
> > > Too many backup devices specified for backup or restore; only 64 are
> > > allowed. RESTORE HEADER ONLY is terminating abnormally.
> > >
> > > The backup device was created using SQL Server 2005 and I am trying to
> > > restore the database to an Express version of 2005.
> > >
> > > I can restore the database successfully if I restore it to the same
> > > server that I created the backup device on.
> > >
> > > For a little background, I am trying to backup a database on a server
> > > to a laptop for business continuity purposes. The database is under 1
> > > gig in total size.
> > >
> > > Any insight is greatly appreciated.
> > >
> >
> > Could you try to post the sql statement you are running?
> >
> >
> > --
> > Regards
> > Steen Schlüter Persson
> > Databaseadministrator / Systemadministrator
>|||wolf.emissar@.gmail.com wrote:
> Steen, here is the SQL that I am running for the restore:
> restore database wolf_sonar
> from disk = 'C:\sqlbackups\sonar_wolf_new.bak'
> and I recieve the following error message:
> Msg 3205, Level 16, State 2, Line 1
> Too many backup devices specified for backup or restore; only 64 are
> allowed.
> Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> One thing that I did notice that is I don't have a compatibility level
> of 90 on the local server on the laptop that the wolf_sonar database
> resides on. I only have 70 and 80 as available choices.
> John to answer your question, I do not have any 2000 instances on the
> machine that I am trying to restore the database to. This is a fairly
> new machine and I actually just installed 2005 Express on this box a
> couple of days. As a further chack I only see sql server 2005 in add /
> remove programs.
>
It sounds like you haven't got a SQL2005 instance on the PC.
Could you try to run SELECT @.@.VERSION on the laptop to see which version
it reports to be running.
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator|||Thats it, I was logging into the 2000 server. I didn't realize that
2005 SQL Express installed a 2000 version and a 2005 version of the
database server on my local machine, and for some reason it defaults to
the 2000 version of the server. When I select and connect to the 2005
version everything works as expected.
Thanks for the help, I really appreciate it.
Steen Persson (DK) wrote:
> wolf.emissar@.gmail.com wrote:
> > Steen, here is the SQL that I am running for the restore:
> >
> > restore database wolf_sonar
> > from disk =3D 'C:\sqlbackups\sonar_wolf_new.bak'
> >
> > and I recieve the following error message:
> >
> > Msg 3205, Level 16, State 2, Line 1
> > Too many backup devices specified for backup or restore; only 64 are
> > allowed.
> > Msg 3013, Level 16, State 1, Line 1
> > RESTORE DATABASE is terminating abnormally.
> >
> > One thing that I did notice that is I don't have a compatibility level
> > of 90 on the local server on the laptop that the wolf_sonar database
> > resides on. I only have 70 and 80 as available choices.
> >
> > John to answer your question, I do not have any 2000 instances on the
> > machine that I am trying to restore the database to. This is a fairly
> > new machine and I actually just installed 2005 Express on this box a
> > couple of days. As a further chack I only see sql server 2005 in add /
> > remove programs.
> >
> It sounds like you haven't got a SQL2005 instance on the PC.
> Could you try to run SELECT @.@.VERSION on the laptop to see which version
> it reports to be running.
>
> --
> Regards
> Steen Schl=FCter Persson
> Databaseadministrator / Systemadministrator|||wolf.emissar@.gmail.com wrote:
> Thats it, I was logging into the 2000 server. I didn't realize that
> 2005 SQL Express installed a 2000 version and a 2005 version of the
> database server on my local machine, and for some reason it defaults to
> the 2000 version of the server. When I select and connect to the 2005
> version everything works as expected.
> Thanks for the help, I really appreciate it.
>
Good to hear that you got the problem solved. A SQL2005 install will NOT
install a SQL2000 instance in anyway. It's more likely that you already
had a SQL2000 instance installed on the PC. When you then installed the
SQL2005 instance it would only be able to install as a named instance
because the SQL2000 was the default instance.
Regards
Steen Schlüter Persson
Database Administrator / System Administrator|||Hi
It sounds like SQL 2000 is your default instance, in which case if you don't
specify a specific instance to log into it will default to that!
John
"wolf.emissar@.gmail.com" wrote:
> Thats it, I was logging into the 2000 server. I didn't realize that
> 2005 SQL Express installed a 2000 version and a 2005 version of the
> database server on my local machine, and for some reason it defaults to
> the 2000 version of the server. When I select and connect to the 2005
> version everything works as expected.
> Thanks for the help, I really appreciate it.
> Steen Persson (DK) wrote:
> > wolf.emissar@.gmail.com wrote:
> > > Steen, here is the SQL that I am running for the restore:
> > >
> > > restore database wolf_sonar
> > > from disk = 'C:\sqlbackups\sonar_wolf_new.bak'
> > >
> > > and I recieve the following error message:
> > >
> > > Msg 3205, Level 16, State 2, Line 1
> > > Too many backup devices specified for backup or restore; only 64 are
> > > allowed.
> > > Msg 3013, Level 16, State 1, Line 1
> > > RESTORE DATABASE is terminating abnormally.
> > >
> > > One thing that I did notice that is I don't have a compatibility level
> > > of 90 on the local server on the laptop that the wolf_sonar database
> > > resides on. I only have 70 and 80 as available choices.
> > >
> > > John to answer your question, I do not have any 2000 instances on the
> > > machine that I am trying to restore the database to. This is a fairly
> > > new machine and I actually just installed 2005 Express on this box a
> > > couple of days. As a further chack I only see sql server 2005 in add /
> > > remove programs.
> > >
> >
> > It sounds like you haven't got a SQL2005 instance on the PC.
> > Could you try to run SELECT @.@.VERSION on the laptop to see which version
> > it reports to be running.
> >
> >
> > --
> > Regards
> > Steen Schlüter Persson
> > Databaseadministrator / Systemadministrator
>|||Hi John -
Can you point me to a document confirming backups are compatible across SQL
Server 2005 versions? This has been a subject of debate here.
Thanks!
dj
"John Bell" wrote:
> Hi
> Both posters were trying to restore a SQL 2005 database onto a SQL 2000
> instance and they have the error message you get. Are you sure that there are
> no SQL 2000 instances on the machine you have? Database restores are
> compatible between all versions of SQL 2005 therefore you should not have any
> problems. Is this a beta or CTP version?
> John
> "wolf.emissar@.gmail.com" wrote:
> > John, thanks for the response. I am trying to restore to a 2005
> > instance but it is an Express version, I don't know if this is the
> > source of the problem or not.
> >
> > In the second link you attached Tibor mentions logging into a 2000
> > instance. This sounds very much like the error I am receiving, do you
> > know what he is referring to? Can express 2005 be configured to run as
> > a 2000 instance?
> >
> > John Bell wrote:
> > > Hi
> > >
> > > Other posts indicate this error message occurs if you try and restore from
> > > SQL 2005 to SQL 2000 see http://tinyurl.com/nqfls and http://tinyurl.com/kajam
> > >
> > > John
> > >
> > > "wolf.emissar@.gmail.com" wrote:
> > >
> > > > I am trying to restore a database from a backup device and when I try
> > > > to add the backup device in the restore database wizard I receive the
> > > > following error message:
> > > >
> > > > An exception occurred while executing a transact-sql statement or
> > > > batch.
> > > >
> > > > Too many backup devices specified for backup or restore; only 64 are
> > > > allowed. RESTORE HEADER ONLY is terminating abnormally.
> > > >
> > > > The backup device was created using SQL Server 2005 and I am trying to
> > > > restore the database to an Express version of 2005.
> > > >
> > > > I can restore the database successfully if I restore it to the same
> > > > server that I created the backup device on.
> > > >
> > > > For a little background, I am trying to backup a database on a server
> > > > to a laptop for business continuity purposes. The database is under 1
> > > > gig in total size.
> > > >
> > > > Any insight is greatly appreciated.
> > > >
> > > >
> >
> >|||Hi
I don't think there is a single document! It is basically the same database
engine so why would you not think it is compatible?
Assuming that both instances are at the same service pack/hotfix level, then
if there are issues restoring from one version to another it should not be
because of the version which is running e.g. an unsupported collations on the
destination instance.
If you are planning a DR strategy, you should always test the recovery
scenarios and plans regardless!
John
"dj" wrote:
> Hi John -
> Can you point me to a document confirming backups are compatible across SQL
> Server 2005 versions? This has been a subject of debate here.
> Thanks!
> dj
> "John Bell" wrote:
> > Hi
> >
> > Both posters were trying to restore a SQL 2005 database onto a SQL 2000
> > instance and they have the error message you get. Are you sure that there are
> > no SQL 2000 instances on the machine you have? Database restores are
> > compatible between all versions of SQL 2005 therefore you should not have any
> > problems. Is this a beta or CTP version?
> >
> > John
> >
> > "wolf.emissar@.gmail.com" wrote:
> >
> > > John, thanks for the response. I am trying to restore to a 2005
> > > instance but it is an Express version, I don't know if this is the
> > > source of the problem or not.
> > >
> > > In the second link you attached Tibor mentions logging into a 2000
> > > instance. This sounds very much like the error I am receiving, do you
> > > know what he is referring to? Can express 2005 be configured to run as
> > > a 2000 instance?
> > >
> > > John Bell wrote:
> > > > Hi
> > > >
> > > > Other posts indicate this error message occurs if you try and restore from
> > > > SQL 2005 to SQL 2000 see http://tinyurl.com/nqfls and http://tinyurl.com/kajam
> > > >
> > > > John
> > > >
> > > > "wolf.emissar@.gmail.com" wrote:
> > > >
> > > > > I am trying to restore a database from a backup device and when I try
> > > > > to add the backup device in the restore database wizard I receive the
> > > > > following error message:
> > > > >
> > > > > An exception occurred while executing a transact-sql statement or
> > > > > batch.
> > > > >
> > > > > Too many backup devices specified for backup or restore; only 64 are
> > > > > allowed. RESTORE HEADER ONLY is terminating abnormally.
> > > > >
> > > > > The backup device was created using SQL Server 2005 and I am trying to
> > > > > restore the database to an Express version of 2005.
> > > > >
> > > > > I can restore the database successfully if I restore it to the same
> > > > > server that I created the backup device on.
> > > > >
> > > > > For a little background, I am trying to backup a database on a server
> > > > > to a laptop for business continuity purposes. The database is under 1
> > > > > gig in total size.
> > > > >
> > > > > Any insight is greatly appreciated.
> > > > >
> > > > >
> > >
> > >|||Call me cautious by nature. :)
Are they compatible between 32-bit and 64-bit editions?
"John Bell" wrote:
> Hi
> I don't think there is a single document! It is basically the same database
> engine so why would you not think it is compatible?
> Assuming that both instances are at the same service pack/hotfix level, then
> if there are issues restoring from one version to another it should not be
> because of the version which is running e.g. an unsupported collations on the
> destination instance.
> If you are planning a DR strategy, you should always test the recovery
> scenarios and plans regardless!
> John
> "dj" wrote:
> > Hi John -
> >
> > Can you point me to a document confirming backups are compatible across SQL
> > Server 2005 versions? This has been a subject of debate here.
> >
> > Thanks!
> > dj
> >
> > "John Bell" wrote:
> >
> > > Hi
> > >
> > > Both posters were trying to restore a SQL 2005 database onto a SQL 2000
> > > instance and they have the error message you get. Are you sure that there are
> > > no SQL 2000 instances on the machine you have? Database restores are
> > > compatible between all versions of SQL 2005 therefore you should not have any
> > > problems. Is this a beta or CTP version?
> > >
> > > John
> > >
> > > "wolf.emissar@.gmail.com" wrote:
> > >
> > > > John, thanks for the response. I am trying to restore to a 2005
> > > > instance but it is an Express version, I don't know if this is the
> > > > source of the problem or not.
> > > >
> > > > In the second link you attached Tibor mentions logging into a 2000
> > > > instance. This sounds very much like the error I am receiving, do you
> > > > know what he is referring to? Can express 2005 be configured to run as
> > > > a 2000 instance?
> > > >
> > > > John Bell wrote:
> > > > > Hi
> > > > >
> > > > > Other posts indicate this error message occurs if you try and restore from
> > > > > SQL 2005 to SQL 2000 see http://tinyurl.com/nqfls and http://tinyurl.com/kajam
> > > > >
> > > > > John
> > > > >
> > > > > "wolf.emissar@.gmail.com" wrote:
> > > > >
> > > > > > I am trying to restore a database from a backup device and when I try
> > > > > > to add the backup device in the restore database wizard I receive the
> > > > > > following error message:
> > > > > >
> > > > > > An exception occurred while executing a transact-sql statement or
> > > > > > batch.
> > > > > >
> > > > > > Too many backup devices specified for backup or restore; only 64 are
> > > > > > allowed. RESTORE HEADER ONLY is terminating abnormally.
> > > > > >
> > > > > > The backup device was created using SQL Server 2005 and I am trying to
> > > > > > restore the database to an Express version of 2005.
> > > > > >
> > > > > > I can restore the database successfully if I restore it to the same
> > > > > > server that I created the backup device on.
> > > > > >
> > > > > > For a little background, I am trying to backup a database on a server
> > > > > > to a laptop for business continuity purposes. The database is under 1
> > > > > > gig in total size.
> > > > > >
> > > > > > Any insight is greatly appreciated.
> > > > > >
> > > > > >
> > > >
> > > >|||Hi
Yes, the format for a backed up database does not change.
John
"dj" wrote:
> Call me cautious by nature. :)
> Are they compatible between 32-bit and 64-bit editions?
> "John Bell" wrote:
> > Hi
> >
> > I don't think there is a single document! It is basically the same database
> > engine so why would you not think it is compatible?
> >
> > Assuming that both instances are at the same service pack/hotfix level, then
> > if there are issues restoring from one version to another it should not be
> > because of the version which is running e.g. an unsupported collations on the
> > destination instance.
> >
> > If you are planning a DR strategy, you should always test the recovery
> > scenarios and plans regardless!
> >
> > John
> >
> > "dj" wrote:
> >
> > > Hi John -
> > >
> > > Can you point me to a document confirming backups are compatible across SQL
> > > Server 2005 versions? This has been a subject of debate here.
> > >
> > > Thanks!
> > > dj
> > >
> > > "John Bell" wrote:
> > >
> > > > Hi
> > > >
> > > > Both posters were trying to restore a SQL 2005 database onto a SQL 2000
> > > > instance and they have the error message you get. Are you sure that there are
> > > > no SQL 2000 instances on the machine you have? Database restores are
> > > > compatible between all versions of SQL 2005 therefore you should not have any
> > > > problems. Is this a beta or CTP version?
> > > >
> > > > John
> > > >
> > > > "wolf.emissar@.gmail.com" wrote:
> > > >
> > > > > John, thanks for the response. I am trying to restore to a 2005
> > > > > instance but it is an Express version, I don't know if this is the
> > > > > source of the problem or not.
> > > > >
> > > > > In the second link you attached Tibor mentions logging into a 2000
> > > > > instance. This sounds very much like the error I am receiving, do you
> > > > > know what he is referring to? Can express 2005 be configured to run as
> > > > > a 2000 instance?
> > > > >
> > > > > John Bell wrote:
> > > > > > Hi
> > > > > >
> > > > > > Other posts indicate this error message occurs if you try and restore from
> > > > > > SQL 2005 to SQL 2000 see http://tinyurl.com/nqfls and http://tinyurl.com/kajam
> > > > > >
> > > > > > John
> > > > > >
> > > > > > "wolf.emissar@.gmail.com" wrote:
> > > > > >
> > > > > > > I am trying to restore a database from a backup device and when I try
> > > > > > > to add the backup device in the restore database wizard I receive the
> > > > > > > following error message:
> > > > > > >
> > > > > > > An exception occurred while executing a transact-sql statement or
> > > > > > > batch.
> > > > > > >
> > > > > > > Too many backup devices specified for backup or restore; only 64 are
> > > > > > > allowed. RESTORE HEADER ONLY is terminating abnormally.
> > > > > > >
> > > > > > > The backup device was created using SQL Server 2005 and I am trying to
> > > > > > > restore the database to an Express version of 2005.
> > > > > > >
> > > > > > > I can restore the database successfully if I restore it to the same
> > > > > > > server that I created the backup device on.
> > > > > > >
> > > > > > > For a little background, I am trying to backup a database on a server
> > > > > > > to a laptop for business continuity purposes. The database is under 1
> > > > > > > gig in total size.
> > > > > > >
> > > > > > > Any insight is greatly appreciated.
> > > > > > >
> > > > > > >
> > > > >
> > > > >|||Hi Sheryl
If you issue a NET START command at a command prompt you can verify is
SQLExpress is running. The default name for a SQL Express instance is
SERVER\SQLEXPRESS (where SERVER is the hostname). If SQLExpress is running
you should be able to use SERVER\EXPRESS this in the connection details.
Have you downloaded Express Management Studio or got the Advanced Services
options?
If you are wanting to enable remote access to your SQL Express instance you
can enable this through the Surface Area Configuration tool sqlsac.exe
John
"Sheryl Briggs" wrote:
> you wrote:
> "It sounds like SQL 2000 is your default instance, in which case if you
> don't
> specify a specific instance to log into it will default to that"
> I installed SQL 2005 express on a machine that already had 2000 on it
> and am having the same problem, but there are only 2 instance names
> showing up in the connection mgr in Express 2005, and both say they are
> version 2000? How do i get to the 2005 connection? THe new instance
> name that appeared when I installed 2005 also says its version is
> 2000? SHould I reinstall it?
> Thanks
> *** Sent via Developersdex http://www.developersdex.com ***
>
to add the backup device in the restore database wizard I receive the
following error message:
An exception occurred while executing a transact-sql statement or
batch.
Too many backup devices specified for backup or restore; only 64 are
allowed. RESTORE HEADER ONLY is terminating abnormally.
The backup device was created using SQL Server 2005 and I am trying to
restore the database to an Express version of 2005.
I can restore the database successfully if I restore it to the same
server that I created the backup device on.
For a little background, I am trying to backup a database on a server
to a laptop for business continuity purposes. The database is under 1
gig in total size.
Any insight is greatly appreciated.Hi
Other posts indicate this error message occurs if you try and restore from
SQL 2005 to SQL 2000 see http://tinyurl.com/nqfls and http://tinyurl.com/kajam
John
"wolf.emissar@.gmail.com" wrote:
> I am trying to restore a database from a backup device and when I try
> to add the backup device in the restore database wizard I receive the
> following error message:
> An exception occurred while executing a transact-sql statement or
> batch.
> Too many backup devices specified for backup or restore; only 64 are
> allowed. RESTORE HEADER ONLY is terminating abnormally.
> The backup device was created using SQL Server 2005 and I am trying to
> restore the database to an Express version of 2005.
> I can restore the database successfully if I restore it to the same
> server that I created the backup device on.
> For a little background, I am trying to backup a database on a server
> to a laptop for business continuity purposes. The database is under 1
> gig in total size.
> Any insight is greatly appreciated.
>|||John, thanks for the response. I am trying to restore to a 2005
instance but it is an Express version, I don't know if this is the
source of the problem or not.
In the second link you attached Tibor mentions logging into a 2000
instance. This sounds very much like the error I am receiving, do you
know what he is referring to? Can express 2005 be configured to run as
a 2000 instance?
John Bell wrote:
> Hi
> Other posts indicate this error message occurs if you try and restore from
> SQL 2005 to SQL 2000 see http://tinyurl.com/nqfls and http://tinyurl.com/kajam
> John
> "wolf.emissar@.gmail.com" wrote:
> > I am trying to restore a database from a backup device and when I try
> > to add the backup device in the restore database wizard I receive the
> > following error message:
> >
> > An exception occurred while executing a transact-sql statement or
> > batch.
> >
> > Too many backup devices specified for backup or restore; only 64 are
> > allowed. RESTORE HEADER ONLY is terminating abnormally.
> >
> > The backup device was created using SQL Server 2005 and I am trying to
> > restore the database to an Express version of 2005.
> >
> > I can restore the database successfully if I restore it to the same
> > server that I created the backup device on.
> >
> > For a little background, I am trying to backup a database on a server
> > to a laptop for business continuity purposes. The database is under 1
> > gig in total size.
> >
> > Any insight is greatly appreciated.
> >
> >|||Hi
Both posters were trying to restore a SQL 2005 database onto a SQL 2000
instance and they have the error message you get. Are you sure that there are
no SQL 2000 instances on the machine you have? Database restores are
compatible between all versions of SQL 2005 therefore you should not have any
problems. Is this a beta or CTP version?
John
"wolf.emissar@.gmail.com" wrote:
> John, thanks for the response. I am trying to restore to a 2005
> instance but it is an Express version, I don't know if this is the
> source of the problem or not.
> In the second link you attached Tibor mentions logging into a 2000
> instance. This sounds very much like the error I am receiving, do you
> know what he is referring to? Can express 2005 be configured to run as
> a 2000 instance?
> John Bell wrote:
> > Hi
> >
> > Other posts indicate this error message occurs if you try and restore from
> > SQL 2005 to SQL 2000 see http://tinyurl.com/nqfls and http://tinyurl.com/kajam
> >
> > John
> >
> > "wolf.emissar@.gmail.com" wrote:
> >
> > > I am trying to restore a database from a backup device and when I try
> > > to add the backup device in the restore database wizard I receive the
> > > following error message:
> > >
> > > An exception occurred while executing a transact-sql statement or
> > > batch.
> > >
> > > Too many backup devices specified for backup or restore; only 64 are
> > > allowed. RESTORE HEADER ONLY is terminating abnormally.
> > >
> > > The backup device was created using SQL Server 2005 and I am trying to
> > > restore the database to an Express version of 2005.
> > >
> > > I can restore the database successfully if I restore it to the same
> > > server that I created the backup device on.
> > >
> > > For a little background, I am trying to backup a database on a server
> > > to a laptop for business continuity purposes. The database is under 1
> > > gig in total size.
> > >
> > > Any insight is greatly appreciated.
> > >
> > >
>|||wolf.emissar@.gmail.com wrote:
> I am trying to restore a database from a backup device and when I try
> to add the backup device in the restore database wizard I receive the
> following error message:
> An exception occurred while executing a transact-sql statement or
> batch.
> Too many backup devices specified for backup or restore; only 64 are
> allowed. RESTORE HEADER ONLY is terminating abnormally.
> The backup device was created using SQL Server 2005 and I am trying to
> restore the database to an Express version of 2005.
> I can restore the database successfully if I restore it to the same
> server that I created the backup device on.
> For a little background, I am trying to backup a database on a server
> to a laptop for business continuity purposes. The database is under 1
> gig in total size.
> Any insight is greatly appreciated.
>
Could you try to post the sql statement you are running?
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator|||Steen, here is the SQL that I am running for the restore:
restore database wolf_sonar
from disk =3D 'C:\sqlbackups\sonar_wolf_new.bak'
and I recieve the following error message:
Msg 3205, Level 16, State 2, Line 1
Too many backup devices specified for backup or restore; only 64 are
allowed.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
One thing that I did notice that is I don't have a compatibility level
of 90 on the local server on the laptop that the wolf_sonar database
resides on. I only have 70 and 80 as available choices.
John to answer your question, I do not have any 2000 instances on the
machine that I am trying to restore the database to. This is a fairly
new machine and I actually just installed 2005 Express on this box a
couple of days. As a further chack I only see sql server 2005 in add /
remove programs.
Steen Persson (DK) wrote:
> wolf.emissar@.gmail.com wrote:
> > I am trying to restore a database from a backup device and when I try
> > to add the backup device in the restore database wizard I receive the
> > following error message:
> >
> > An exception occurred while executing a transact-sql statement or
> > batch.
> >
> > Too many backup devices specified for backup or restore; only 64 are
> > allowed. RESTORE HEADER ONLY is terminating abnormally.
> >
> > The backup device was created using SQL Server 2005 and I am trying to
> > restore the database to an Express version of 2005.
> >
> > I can restore the database successfully if I restore it to the same
> > server that I created the backup device on.
> >
> > For a little background, I am trying to backup a database on a server
> > to a laptop for business continuity purposes. The database is under 1
> > gig in total size.
> >
> > Any insight is greatly appreciated.
> >
> Could you try to post the sql statement you are running?
>
> --
> Regards
> Steen Schl=FCter Persson
> Databaseadministrator / Systemadministrator|||John, I have a tried a few different version of 2005 express. The
latest one that I have installed is Version 9.00.2047.00. I do not
believe that this is a beta version.
John Bell wrote:
> Hi
> Both posters were trying to restore a SQL 2005 database onto a SQL 2000
> instance and they have the error message you get. Are you sure that there are
> no SQL 2000 instances on the machine you have? Database restores are
> compatible between all versions of SQL 2005 therefore you should not have any
> problems. Is this a beta or CTP version?
> John
> "wolf.emissar@.gmail.com" wrote:
> > John, thanks for the response. I am trying to restore to a 2005
> > instance but it is an Express version, I don't know if this is the
> > source of the problem or not.
> >
> > In the second link you attached Tibor mentions logging into a 2000
> > instance. This sounds very much like the error I am receiving, do you
> > know what he is referring to? Can express 2005 be configured to run as
> > a 2000 instance?
> >
> > John Bell wrote:
> > > Hi
> > >
> > > Other posts indicate this error message occurs if you try and restore from
> > > SQL 2005 to SQL 2000 see http://tinyurl.com/nqfls and http://tinyurl.com/kajam
> > >
> > > John
> > >
> > > "wolf.emissar@.gmail.com" wrote:
> > >
> > > > I am trying to restore a database from a backup device and when I try
> > > > to add the backup device in the restore database wizard I receive the
> > > > following error message:
> > > >
> > > > An exception occurred while executing a transact-sql statement or
> > > > batch.
> > > >
> > > > Too many backup devices specified for backup or restore; only 64 are
> > > > allowed. RESTORE HEADER ONLY is terminating abnormally.
> > > >
> > > > The backup device was created using SQL Server 2005 and I am trying to
> > > > restore the database to an Express version of 2005.
> > > >
> > > > I can restore the database successfully if I restore it to the same
> > > > server that I created the backup device on.
> > > >
> > > > For a little background, I am trying to backup a database on a server
> > > > to a laptop for business continuity purposes. The database is under 1
> > > > gig in total size.
> > > >
> > > > Any insight is greatly appreciated.
> > > >
> > > >
> >
> >|||Hi
If you ran SELECT @.@.VERSION to get the version number in your other post,
then you are on SQL 2005 Service Pack 1. If you obtained it otherwise run the
command in Management Studio or SQLCMD.
I am not sure where you are getting the compatibility level from but if you
ran EXEC sp_helpdb 'MyDatabase' it will tell you at the end column of the
first resultset.
Try running:
RESTORE FILELISTONLY
FROM disk = 'C:\sqlbackups\sonar_wolf_new.bak'
John
"wolf.emissar@.gmail.com" wrote:
> Steen, here is the SQL that I am running for the restore:
> restore database wolf_sonar
> from disk = 'C:\sqlbackups\sonar_wolf_new.bak'
> and I recieve the following error message:
> Msg 3205, Level 16, State 2, Line 1
> Too many backup devices specified for backup or restore; only 64 are
> allowed.
> Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> One thing that I did notice that is I don't have a compatibility level
> of 90 on the local server on the laptop that the wolf_sonar database
> resides on. I only have 70 and 80 as available choices.
> John to answer your question, I do not have any 2000 instances on the
> machine that I am trying to restore the database to. This is a fairly
> new machine and I actually just installed 2005 Express on this box a
> couple of days. As a further chack I only see sql server 2005 in add /
> remove programs.
>
> Steen Persson (DK) wrote:
> > wolf.emissar@.gmail.com wrote:
> > > I am trying to restore a database from a backup device and when I try
> > > to add the backup device in the restore database wizard I receive the
> > > following error message:
> > >
> > > An exception occurred while executing a transact-sql statement or
> > > batch.
> > >
> > > Too many backup devices specified for backup or restore; only 64 are
> > > allowed. RESTORE HEADER ONLY is terminating abnormally.
> > >
> > > The backup device was created using SQL Server 2005 and I am trying to
> > > restore the database to an Express version of 2005.
> > >
> > > I can restore the database successfully if I restore it to the same
> > > server that I created the backup device on.
> > >
> > > For a little background, I am trying to backup a database on a server
> > > to a laptop for business continuity purposes. The database is under 1
> > > gig in total size.
> > >
> > > Any insight is greatly appreciated.
> > >
> >
> > Could you try to post the sql statement you are running?
> >
> >
> > --
> > Regards
> > Steen Schlüter Persson
> > Databaseadministrator / Systemadministrator
>|||wolf.emissar@.gmail.com wrote:
> Steen, here is the SQL that I am running for the restore:
> restore database wolf_sonar
> from disk = 'C:\sqlbackups\sonar_wolf_new.bak'
> and I recieve the following error message:
> Msg 3205, Level 16, State 2, Line 1
> Too many backup devices specified for backup or restore; only 64 are
> allowed.
> Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> One thing that I did notice that is I don't have a compatibility level
> of 90 on the local server on the laptop that the wolf_sonar database
> resides on. I only have 70 and 80 as available choices.
> John to answer your question, I do not have any 2000 instances on the
> machine that I am trying to restore the database to. This is a fairly
> new machine and I actually just installed 2005 Express on this box a
> couple of days. As a further chack I only see sql server 2005 in add /
> remove programs.
>
It sounds like you haven't got a SQL2005 instance on the PC.
Could you try to run SELECT @.@.VERSION on the laptop to see which version
it reports to be running.
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator|||Thats it, I was logging into the 2000 server. I didn't realize that
2005 SQL Express installed a 2000 version and a 2005 version of the
database server on my local machine, and for some reason it defaults to
the 2000 version of the server. When I select and connect to the 2005
version everything works as expected.
Thanks for the help, I really appreciate it.
Steen Persson (DK) wrote:
> wolf.emissar@.gmail.com wrote:
> > Steen, here is the SQL that I am running for the restore:
> >
> > restore database wolf_sonar
> > from disk =3D 'C:\sqlbackups\sonar_wolf_new.bak'
> >
> > and I recieve the following error message:
> >
> > Msg 3205, Level 16, State 2, Line 1
> > Too many backup devices specified for backup or restore; only 64 are
> > allowed.
> > Msg 3013, Level 16, State 1, Line 1
> > RESTORE DATABASE is terminating abnormally.
> >
> > One thing that I did notice that is I don't have a compatibility level
> > of 90 on the local server on the laptop that the wolf_sonar database
> > resides on. I only have 70 and 80 as available choices.
> >
> > John to answer your question, I do not have any 2000 instances on the
> > machine that I am trying to restore the database to. This is a fairly
> > new machine and I actually just installed 2005 Express on this box a
> > couple of days. As a further chack I only see sql server 2005 in add /
> > remove programs.
> >
> It sounds like you haven't got a SQL2005 instance on the PC.
> Could you try to run SELECT @.@.VERSION on the laptop to see which version
> it reports to be running.
>
> --
> Regards
> Steen Schl=FCter Persson
> Databaseadministrator / Systemadministrator|||wolf.emissar@.gmail.com wrote:
> Thats it, I was logging into the 2000 server. I didn't realize that
> 2005 SQL Express installed a 2000 version and a 2005 version of the
> database server on my local machine, and for some reason it defaults to
> the 2000 version of the server. When I select and connect to the 2005
> version everything works as expected.
> Thanks for the help, I really appreciate it.
>
Good to hear that you got the problem solved. A SQL2005 install will NOT
install a SQL2000 instance in anyway. It's more likely that you already
had a SQL2000 instance installed on the PC. When you then installed the
SQL2005 instance it would only be able to install as a named instance
because the SQL2000 was the default instance.
Regards
Steen Schlüter Persson
Database Administrator / System Administrator|||Hi
It sounds like SQL 2000 is your default instance, in which case if you don't
specify a specific instance to log into it will default to that!
John
"wolf.emissar@.gmail.com" wrote:
> Thats it, I was logging into the 2000 server. I didn't realize that
> 2005 SQL Express installed a 2000 version and a 2005 version of the
> database server on my local machine, and for some reason it defaults to
> the 2000 version of the server. When I select and connect to the 2005
> version everything works as expected.
> Thanks for the help, I really appreciate it.
> Steen Persson (DK) wrote:
> > wolf.emissar@.gmail.com wrote:
> > > Steen, here is the SQL that I am running for the restore:
> > >
> > > restore database wolf_sonar
> > > from disk = 'C:\sqlbackups\sonar_wolf_new.bak'
> > >
> > > and I recieve the following error message:
> > >
> > > Msg 3205, Level 16, State 2, Line 1
> > > Too many backup devices specified for backup or restore; only 64 are
> > > allowed.
> > > Msg 3013, Level 16, State 1, Line 1
> > > RESTORE DATABASE is terminating abnormally.
> > >
> > > One thing that I did notice that is I don't have a compatibility level
> > > of 90 on the local server on the laptop that the wolf_sonar database
> > > resides on. I only have 70 and 80 as available choices.
> > >
> > > John to answer your question, I do not have any 2000 instances on the
> > > machine that I am trying to restore the database to. This is a fairly
> > > new machine and I actually just installed 2005 Express on this box a
> > > couple of days. As a further chack I only see sql server 2005 in add /
> > > remove programs.
> > >
> >
> > It sounds like you haven't got a SQL2005 instance on the PC.
> > Could you try to run SELECT @.@.VERSION on the laptop to see which version
> > it reports to be running.
> >
> >
> > --
> > Regards
> > Steen Schlüter Persson
> > Databaseadministrator / Systemadministrator
>|||Hi John -
Can you point me to a document confirming backups are compatible across SQL
Server 2005 versions? This has been a subject of debate here.
Thanks!
dj
"John Bell" wrote:
> Hi
> Both posters were trying to restore a SQL 2005 database onto a SQL 2000
> instance and they have the error message you get. Are you sure that there are
> no SQL 2000 instances on the machine you have? Database restores are
> compatible between all versions of SQL 2005 therefore you should not have any
> problems. Is this a beta or CTP version?
> John
> "wolf.emissar@.gmail.com" wrote:
> > John, thanks for the response. I am trying to restore to a 2005
> > instance but it is an Express version, I don't know if this is the
> > source of the problem or not.
> >
> > In the second link you attached Tibor mentions logging into a 2000
> > instance. This sounds very much like the error I am receiving, do you
> > know what he is referring to? Can express 2005 be configured to run as
> > a 2000 instance?
> >
> > John Bell wrote:
> > > Hi
> > >
> > > Other posts indicate this error message occurs if you try and restore from
> > > SQL 2005 to SQL 2000 see http://tinyurl.com/nqfls and http://tinyurl.com/kajam
> > >
> > > John
> > >
> > > "wolf.emissar@.gmail.com" wrote:
> > >
> > > > I am trying to restore a database from a backup device and when I try
> > > > to add the backup device in the restore database wizard I receive the
> > > > following error message:
> > > >
> > > > An exception occurred while executing a transact-sql statement or
> > > > batch.
> > > >
> > > > Too many backup devices specified for backup or restore; only 64 are
> > > > allowed. RESTORE HEADER ONLY is terminating abnormally.
> > > >
> > > > The backup device was created using SQL Server 2005 and I am trying to
> > > > restore the database to an Express version of 2005.
> > > >
> > > > I can restore the database successfully if I restore it to the same
> > > > server that I created the backup device on.
> > > >
> > > > For a little background, I am trying to backup a database on a server
> > > > to a laptop for business continuity purposes. The database is under 1
> > > > gig in total size.
> > > >
> > > > Any insight is greatly appreciated.
> > > >
> > > >
> >
> >|||Hi
I don't think there is a single document! It is basically the same database
engine so why would you not think it is compatible?
Assuming that both instances are at the same service pack/hotfix level, then
if there are issues restoring from one version to another it should not be
because of the version which is running e.g. an unsupported collations on the
destination instance.
If you are planning a DR strategy, you should always test the recovery
scenarios and plans regardless!
John
"dj" wrote:
> Hi John -
> Can you point me to a document confirming backups are compatible across SQL
> Server 2005 versions? This has been a subject of debate here.
> Thanks!
> dj
> "John Bell" wrote:
> > Hi
> >
> > Both posters were trying to restore a SQL 2005 database onto a SQL 2000
> > instance and they have the error message you get. Are you sure that there are
> > no SQL 2000 instances on the machine you have? Database restores are
> > compatible between all versions of SQL 2005 therefore you should not have any
> > problems. Is this a beta or CTP version?
> >
> > John
> >
> > "wolf.emissar@.gmail.com" wrote:
> >
> > > John, thanks for the response. I am trying to restore to a 2005
> > > instance but it is an Express version, I don't know if this is the
> > > source of the problem or not.
> > >
> > > In the second link you attached Tibor mentions logging into a 2000
> > > instance. This sounds very much like the error I am receiving, do you
> > > know what he is referring to? Can express 2005 be configured to run as
> > > a 2000 instance?
> > >
> > > John Bell wrote:
> > > > Hi
> > > >
> > > > Other posts indicate this error message occurs if you try and restore from
> > > > SQL 2005 to SQL 2000 see http://tinyurl.com/nqfls and http://tinyurl.com/kajam
> > > >
> > > > John
> > > >
> > > > "wolf.emissar@.gmail.com" wrote:
> > > >
> > > > > I am trying to restore a database from a backup device and when I try
> > > > > to add the backup device in the restore database wizard I receive the
> > > > > following error message:
> > > > >
> > > > > An exception occurred while executing a transact-sql statement or
> > > > > batch.
> > > > >
> > > > > Too many backup devices specified for backup or restore; only 64 are
> > > > > allowed. RESTORE HEADER ONLY is terminating abnormally.
> > > > >
> > > > > The backup device was created using SQL Server 2005 and I am trying to
> > > > > restore the database to an Express version of 2005.
> > > > >
> > > > > I can restore the database successfully if I restore it to the same
> > > > > server that I created the backup device on.
> > > > >
> > > > > For a little background, I am trying to backup a database on a server
> > > > > to a laptop for business continuity purposes. The database is under 1
> > > > > gig in total size.
> > > > >
> > > > > Any insight is greatly appreciated.
> > > > >
> > > > >
> > >
> > >|||Call me cautious by nature. :)
Are they compatible between 32-bit and 64-bit editions?
"John Bell" wrote:
> Hi
> I don't think there is a single document! It is basically the same database
> engine so why would you not think it is compatible?
> Assuming that both instances are at the same service pack/hotfix level, then
> if there are issues restoring from one version to another it should not be
> because of the version which is running e.g. an unsupported collations on the
> destination instance.
> If you are planning a DR strategy, you should always test the recovery
> scenarios and plans regardless!
> John
> "dj" wrote:
> > Hi John -
> >
> > Can you point me to a document confirming backups are compatible across SQL
> > Server 2005 versions? This has been a subject of debate here.
> >
> > Thanks!
> > dj
> >
> > "John Bell" wrote:
> >
> > > Hi
> > >
> > > Both posters were trying to restore a SQL 2005 database onto a SQL 2000
> > > instance and they have the error message you get. Are you sure that there are
> > > no SQL 2000 instances on the machine you have? Database restores are
> > > compatible between all versions of SQL 2005 therefore you should not have any
> > > problems. Is this a beta or CTP version?
> > >
> > > John
> > >
> > > "wolf.emissar@.gmail.com" wrote:
> > >
> > > > John, thanks for the response. I am trying to restore to a 2005
> > > > instance but it is an Express version, I don't know if this is the
> > > > source of the problem or not.
> > > >
> > > > In the second link you attached Tibor mentions logging into a 2000
> > > > instance. This sounds very much like the error I am receiving, do you
> > > > know what he is referring to? Can express 2005 be configured to run as
> > > > a 2000 instance?
> > > >
> > > > John Bell wrote:
> > > > > Hi
> > > > >
> > > > > Other posts indicate this error message occurs if you try and restore from
> > > > > SQL 2005 to SQL 2000 see http://tinyurl.com/nqfls and http://tinyurl.com/kajam
> > > > >
> > > > > John
> > > > >
> > > > > "wolf.emissar@.gmail.com" wrote:
> > > > >
> > > > > > I am trying to restore a database from a backup device and when I try
> > > > > > to add the backup device in the restore database wizard I receive the
> > > > > > following error message:
> > > > > >
> > > > > > An exception occurred while executing a transact-sql statement or
> > > > > > batch.
> > > > > >
> > > > > > Too many backup devices specified for backup or restore; only 64 are
> > > > > > allowed. RESTORE HEADER ONLY is terminating abnormally.
> > > > > >
> > > > > > The backup device was created using SQL Server 2005 and I am trying to
> > > > > > restore the database to an Express version of 2005.
> > > > > >
> > > > > > I can restore the database successfully if I restore it to the same
> > > > > > server that I created the backup device on.
> > > > > >
> > > > > > For a little background, I am trying to backup a database on a server
> > > > > > to a laptop for business continuity purposes. The database is under 1
> > > > > > gig in total size.
> > > > > >
> > > > > > Any insight is greatly appreciated.
> > > > > >
> > > > > >
> > > >
> > > >|||Hi
Yes, the format for a backed up database does not change.
John
"dj" wrote:
> Call me cautious by nature. :)
> Are they compatible between 32-bit and 64-bit editions?
> "John Bell" wrote:
> > Hi
> >
> > I don't think there is a single document! It is basically the same database
> > engine so why would you not think it is compatible?
> >
> > Assuming that both instances are at the same service pack/hotfix level, then
> > if there are issues restoring from one version to another it should not be
> > because of the version which is running e.g. an unsupported collations on the
> > destination instance.
> >
> > If you are planning a DR strategy, you should always test the recovery
> > scenarios and plans regardless!
> >
> > John
> >
> > "dj" wrote:
> >
> > > Hi John -
> > >
> > > Can you point me to a document confirming backups are compatible across SQL
> > > Server 2005 versions? This has been a subject of debate here.
> > >
> > > Thanks!
> > > dj
> > >
> > > "John Bell" wrote:
> > >
> > > > Hi
> > > >
> > > > Both posters were trying to restore a SQL 2005 database onto a SQL 2000
> > > > instance and they have the error message you get. Are you sure that there are
> > > > no SQL 2000 instances on the machine you have? Database restores are
> > > > compatible between all versions of SQL 2005 therefore you should not have any
> > > > problems. Is this a beta or CTP version?
> > > >
> > > > John
> > > >
> > > > "wolf.emissar@.gmail.com" wrote:
> > > >
> > > > > John, thanks for the response. I am trying to restore to a 2005
> > > > > instance but it is an Express version, I don't know if this is the
> > > > > source of the problem or not.
> > > > >
> > > > > In the second link you attached Tibor mentions logging into a 2000
> > > > > instance. This sounds very much like the error I am receiving, do you
> > > > > know what he is referring to? Can express 2005 be configured to run as
> > > > > a 2000 instance?
> > > > >
> > > > > John Bell wrote:
> > > > > > Hi
> > > > > >
> > > > > > Other posts indicate this error message occurs if you try and restore from
> > > > > > SQL 2005 to SQL 2000 see http://tinyurl.com/nqfls and http://tinyurl.com/kajam
> > > > > >
> > > > > > John
> > > > > >
> > > > > > "wolf.emissar@.gmail.com" wrote:
> > > > > >
> > > > > > > I am trying to restore a database from a backup device and when I try
> > > > > > > to add the backup device in the restore database wizard I receive the
> > > > > > > following error message:
> > > > > > >
> > > > > > > An exception occurred while executing a transact-sql statement or
> > > > > > > batch.
> > > > > > >
> > > > > > > Too many backup devices specified for backup or restore; only 64 are
> > > > > > > allowed. RESTORE HEADER ONLY is terminating abnormally.
> > > > > > >
> > > > > > > The backup device was created using SQL Server 2005 and I am trying to
> > > > > > > restore the database to an Express version of 2005.
> > > > > > >
> > > > > > > I can restore the database successfully if I restore it to the same
> > > > > > > server that I created the backup device on.
> > > > > > >
> > > > > > > For a little background, I am trying to backup a database on a server
> > > > > > > to a laptop for business continuity purposes. The database is under 1
> > > > > > > gig in total size.
> > > > > > >
> > > > > > > Any insight is greatly appreciated.
> > > > > > >
> > > > > > >
> > > > >
> > > > >|||Hi Sheryl
If you issue a NET START command at a command prompt you can verify is
SQLExpress is running. The default name for a SQL Express instance is
SERVER\SQLEXPRESS (where SERVER is the hostname). If SQLExpress is running
you should be able to use SERVER\EXPRESS this in the connection details.
Have you downloaded Express Management Studio or got the Advanced Services
options?
If you are wanting to enable remote access to your SQL Express instance you
can enable this through the Surface Area Configuration tool sqlsac.exe
John
"Sheryl Briggs" wrote:
> you wrote:
> "It sounds like SQL 2000 is your default instance, in which case if you
> don't
> specify a specific instance to log into it will default to that"
> I installed SQL 2005 express on a machine that already had 2000 on it
> and am having the same problem, but there are only 2 instance names
> showing up in the connection mgr in Express 2005, and both say they are
> version 2000? How do i get to the 2005 connection? THe new instance
> name that appeared when I installed 2005 also says its version is
> 2000? SHould I reinstall it?
> Thanks
> *** Sent via Developersdex http://www.developersdex.com ***
>
problem restore database SQL2005
Dear all,
I'm trying to restore a database saved last week in a .bak file through the
GUI of Management Studio but i have the following message error : "the
database is in use".
So i restarted the service engine SQL and changed the option to put Single
User but i have still the same error message when trying to restore.
Do you the procedure to restore a database with SQL2005 through the
Management Studio ?
Thanks in advance.
Check the ActivityLog to find out who is connected to database.
Thanks & Rate the Postings.
-Ravi-
"Ving" wrote:
> Dear all,
> I'm trying to restore a database saved last week in a .bak file through the
> GUI of Management Studio but i have the following message error : "the
> database is in use".
> So i restarted the service engine SQL and changed the option to put Single
> User but i have still the same error message when trying to restore.
> Do you the procedure to restore a database with SQL2005 through the
> Management Studio ?
> Thanks in advance.
>
>
I'm trying to restore a database saved last week in a .bak file through the
GUI of Management Studio but i have the following message error : "the
database is in use".
So i restarted the service engine SQL and changed the option to put Single
User but i have still the same error message when trying to restore.
Do you the procedure to restore a database with SQL2005 through the
Management Studio ?
Thanks in advance.
Check the ActivityLog to find out who is connected to database.
Thanks & Rate the Postings.
-Ravi-
"Ving" wrote:
> Dear all,
> I'm trying to restore a database saved last week in a .bak file through the
> GUI of Management Studio but i have the following message error : "the
> database is in use".
> So i restarted the service engine SQL and changed the option to put Single
> User but i have still the same error message when trying to restore.
> Do you the procedure to restore a database with SQL2005 through the
> Management Studio ?
> Thanks in advance.
>
>
problem restore database SQL2005
Dear all,
I'm trying to restore a database saved last week in a .bak file through the
GUI of Management Studio but i have the following message error : "the
database is in use".
So i restarted the service engine SQL and changed the option to put Single
User but i have still the same error message when trying to restore.
Do you the procedure to restore a database with SQL2005 through the
Management Studio ?
Thanks in advance.Check the ActivityLog to find out who is connected to database.
--
Thanks & Rate the Postings.
-Ravi-
"Ving" wrote:
> Dear all,
> I'm trying to restore a database saved last week in a .bak file through the
> GUI of Management Studio but i have the following message error : "the
> database is in use".
> So i restarted the service engine SQL and changed the option to put Single
> User but i have still the same error message when trying to restore.
> Do you the procedure to restore a database with SQL2005 through the
> Management Studio ?
> Thanks in advance.
>
>
I'm trying to restore a database saved last week in a .bak file through the
GUI of Management Studio but i have the following message error : "the
database is in use".
So i restarted the service engine SQL and changed the option to put Single
User but i have still the same error message when trying to restore.
Do you the procedure to restore a database with SQL2005 through the
Management Studio ?
Thanks in advance.Check the ActivityLog to find out who is connected to database.
--
Thanks & Rate the Postings.
-Ravi-
"Ving" wrote:
> Dear all,
> I'm trying to restore a database saved last week in a .bak file through the
> GUI of Management Studio but i have the following message error : "the
> database is in use".
> So i restarted the service engine SQL and changed the option to put Single
> User but i have still the same error message when trying to restore.
> Do you the procedure to restore a database with SQL2005 through the
> Management Studio ?
> Thanks in advance.
>
>
problem restore database SQL2005
Dear all,
I'm trying to restore a database saved last week in a .bak file through the
GUI of Management Studio but i have the following message error : "the
database is in use".
So i restarted the service engine SQL and changed the option to put Single
User but i have still the same error message when trying to restore.
Do you the procedure to restore a database with SQL2005 through the
Management Studio ?
Thanks in advance.Check the ActivityLog to find out who is connected to database.
--
Thanks & Rate the Postings.
-Ravi-
"Ving" wrote:
> Dear all,
> I'm trying to restore a database saved last week in a .bak file through th
e
> GUI of Management Studio but i have the following message error : "the
> database is in use".
> So i restarted the service engine SQL and changed the option to put Single
> User but i have still the same error message when trying to restore.
> Do you the procedure to restore a database with SQL2005 through the
> Management Studio ?
> Thanks in advance.
>
>
I'm trying to restore a database saved last week in a .bak file through the
GUI of Management Studio but i have the following message error : "the
database is in use".
So i restarted the service engine SQL and changed the option to put Single
User but i have still the same error message when trying to restore.
Do you the procedure to restore a database with SQL2005 through the
Management Studio ?
Thanks in advance.Check the ActivityLog to find out who is connected to database.
--
Thanks & Rate the Postings.
-Ravi-
"Ving" wrote:
> Dear all,
> I'm trying to restore a database saved last week in a .bak file through th
e
> GUI of Management Studio but i have the following message error : "the
> database is in use".
> So i restarted the service engine SQL and changed the option to put Single
> User but i have still the same error message when trying to restore.
> Do you the procedure to restore a database with SQL2005 through the
> Management Studio ?
> Thanks in advance.
>
>
PROBLEM RESTORE [URGENT]
Mrs,
I'm having a serious problem while trying to restore a database backup. Before concluding the process, the following error apear's:
MICROSOFT SQL - DMO (ODBC SQLSTATE: 42000) The media family on device “d:\backup\dbprinc.bak” is incorrectly formed. This SQL Server cannot process media family. RESTORE DATABASE is terminating abnormally.
When I try “to attach” the database, the following message apear's: LOADING…. but the tables didn't open. I'll be grateful if somebody could help me, because I need to restore this database urgently.
Att.
Jess Augusto / Brazil
hope you are not restoring /attaching SQL Server 2005 database to SQL Server 2000 ... pse post the version and edition of the source and target
Madhu
|||Sorry,
is Sql Server 2000 SP4.
Tanks
|||
The first message indicates that the file DBPRINC.BAK is not in the format of a TSQL backup. This could indicate that the file is corrupted or that it was produced by something other than TSQL backups.
I'm not sure why you would attempt to ATTACH a backup file.
Can you tell us how the file DBPRINC.BAK was produced? That will help us understand how to proceed.
Monday, March 26, 2012
Problem referencing a column in a Table Variable in a query.
Hello All
I have the following problem running an sp with a table variable (sql server 2000) - the error which occurs at the end of the query is: "must declare the variable @.THeader" . @.THeader is the name of the variable table and the error occurs with such references as @.THeader.ApplyAmt, @.THeader.TransactionHeaderID, etc.
declare @.THeader TABLE (
TransactionHeaderID [int] NOT NULL ,
PatientID [int] NOT NULL ,
TransactionAllocationAmount [money] NOT NULL ,
ApplyAmt [money] NULL ) - create table variable
insert into @.THeader select TransactionHeaderID,PatientID,TransactionAllocationAmount,ApplyAmt from mtblTransactionHeader where PatientID = 9 - fill the table variable
UPDATE @.THeader
set TransactionAllocationAmount =
(SELECT isnull(Sum(mtblTransactionAllocation.Amount),0)
FROM mtblTransactionAllocation where mtblTransactionAllocation.DRID = TransactionHeaderID or
mtblTransactionAllocation.CRID = TransactionHeaderID) from @.THeader, mtblTransactionAllocation - do the updates on the table variable
Update @.THeader
set ApplyAmt = (SELECT mtblTransactionAllocation.Amount
FROM mtblTransactionAllocation where mtblTransactionAllocation.DRID = TransactionHeaderID and
mtblTransactionAllocation.CRID = 187 and PatientID = 9) from @.THeader, mtblTransactionAllocation - do the updates on the table variable
- below is where the problems occur. It occurs with statements referencing columns in the table variable, i.e. @.THeader.ApplyAmt
UPDATE mtblTransactionHeader
SET mtblTransactionHeader.TransactionAllocationAmount = @.THeader.TransactionAllocationAmount,
mtblTransactionHeader.ApplyAmt = @.THeader.ApplyAmt
FROM @.THeader, mtblTransactionHeader
WHERE @.THeader.TransactionHeaderID = mtblTransactionHeader.TransactionHeaderID - put the values back into original table
Thanks in advance
smHaig
Try adding an alias to @.THeader
UPDATE mtblTransactionHeader
SET mtblTransactionHeader.TransactionAllocationAmount = t.TransactionAllocationAmount,
mtblTransactionHeader.ApplyAmt = t.ApplyAmt
FROM @.THeader t, mtblTransactionHeader
WHERE t.TransactionHeaderID = mtblTransactionHeader.TransactionHeaderID -- put the values back into original table
Friday, March 23, 2012
Problem querying SysObjects and SysIndexes
When executing a basic query against (see below) the system object
table in a SQL 2000 database it displays the following error.
SELECT 1 FROM SYSOBJECTS
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'SYSOBJECTS'.
The table does exist and the query is being run as an account with SQL
Admin privileges. This also occurs when trying to query the SysIndexes
table. Other databases on the server are able to query these tables.
What is causing this problem on this one database and how can it be
resolved?Some ideas.
1)Try SELECT 1 FROM sysobjects
2)Try SELECT 1 FROM dbo.sysobjects
--
Jack Vamvas
___________________________________
Need an IT job? http://www.ITjobfeed.com/SQL
"Robin9876" <robin9876@.hotmail.com> wrote in message
news:1190368228.718871.96350@.57g2000hsv.googlegroups.com...
> When executing a basic query against (see below) the system object
> table in a SQL 2000 database it displays the following error.
> SELECT 1 FROM SYSOBJECTS
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'SYSOBJECTS'.
> The table does exist and the query is being run as an account with SQL
> Admin privileges. This also occurs when trying to query the SysIndexes
> table. Other databases on the server are able to query these tables.
> What is causing this problem on this one database and how can it be
> resolved?
>|||> What is causing this problem on this one database and how can it be
> resolved?
Object name case sensitivity is determined by the database collation. I
suspect the following query will return a case-sensitive collation:
SELECT DATABASEPROPERTYEX(DB_NAME(), 'Collation')
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Robin9876" <robin9876@.hotmail.com> wrote in message
news:1190368228.718871.96350@.57g2000hsv.googlegroups.com...
> When executing a basic query against (see below) the system object
> table in a SQL 2000 database it displays the following error.
> SELECT 1 FROM SYSOBJECTS
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'SYSOBJECTS'.
> The table does exist and the query is being run as an account with SQL
> Admin privileges. This also occurs when trying to query the SysIndexes
> table. Other databases on the server are able to query these tables.
> What is causing this problem on this one database and how can it be
> resolved?
>|||Since posting I had resolved it.
I already thought of the suggestions that Jack posted, it was what Dan
has suggested a particular 3rd party database had been setup as case
sensitive.
On 21 Sep, 12:22, "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
wrote:
> > What is causing this problem on this one database and how can it be
> > resolved?
> Object name case sensitivity is determined by the database collation. I
> suspect the following query will return a case-sensitive collation:
> SELECT DATABASEPROPERTYEX(DB_NAME(), 'Collation')
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Robin9876" <robin9...@.hotmail.com> wrote in message
> news:1190368228.718871.96350@.57g2000hsv.googlegroups.com...
> > When executing a basic query against (see below) the system object
> > table in a SQL 2000 database it displays the following error.
> > SELECT 1 FROM SYSOBJECTS
> > Server: Msg 208, Level 16, State 1, Line 1
> > Invalid object name 'SYSOBJECTS'.
> > The table does exist and the query is being run as an account with SQL
> > Admin privileges. This also occurs when trying to query the SysIndexes
> > table. Other databases on the server are able to query these tables.
> > What is causing this problem on this one database and how can it be
> > resolved?
table in a SQL 2000 database it displays the following error.
SELECT 1 FROM SYSOBJECTS
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'SYSOBJECTS'.
The table does exist and the query is being run as an account with SQL
Admin privileges. This also occurs when trying to query the SysIndexes
table. Other databases on the server are able to query these tables.
What is causing this problem on this one database and how can it be
resolved?Some ideas.
1)Try SELECT 1 FROM sysobjects
2)Try SELECT 1 FROM dbo.sysobjects
--
Jack Vamvas
___________________________________
Need an IT job? http://www.ITjobfeed.com/SQL
"Robin9876" <robin9876@.hotmail.com> wrote in message
news:1190368228.718871.96350@.57g2000hsv.googlegroups.com...
> When executing a basic query against (see below) the system object
> table in a SQL 2000 database it displays the following error.
> SELECT 1 FROM SYSOBJECTS
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'SYSOBJECTS'.
> The table does exist and the query is being run as an account with SQL
> Admin privileges. This also occurs when trying to query the SysIndexes
> table. Other databases on the server are able to query these tables.
> What is causing this problem on this one database and how can it be
> resolved?
>|||> What is causing this problem on this one database and how can it be
> resolved?
Object name case sensitivity is determined by the database collation. I
suspect the following query will return a case-sensitive collation:
SELECT DATABASEPROPERTYEX(DB_NAME(), 'Collation')
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Robin9876" <robin9876@.hotmail.com> wrote in message
news:1190368228.718871.96350@.57g2000hsv.googlegroups.com...
> When executing a basic query against (see below) the system object
> table in a SQL 2000 database it displays the following error.
> SELECT 1 FROM SYSOBJECTS
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'SYSOBJECTS'.
> The table does exist and the query is being run as an account with SQL
> Admin privileges. This also occurs when trying to query the SysIndexes
> table. Other databases on the server are able to query these tables.
> What is causing this problem on this one database and how can it be
> resolved?
>|||Since posting I had resolved it.
I already thought of the suggestions that Jack posted, it was what Dan
has suggested a particular 3rd party database had been setup as case
sensitive.
On 21 Sep, 12:22, "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
wrote:
> > What is causing this problem on this one database and how can it be
> > resolved?
> Object name case sensitivity is determined by the database collation. I
> suspect the following query will return a case-sensitive collation:
> SELECT DATABASEPROPERTYEX(DB_NAME(), 'Collation')
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Robin9876" <robin9...@.hotmail.com> wrote in message
> news:1190368228.718871.96350@.57g2000hsv.googlegroups.com...
> > When executing a basic query against (see below) the system object
> > table in a SQL 2000 database it displays the following error.
> > SELECT 1 FROM SYSOBJECTS
> > Server: Msg 208, Level 16, State 1, Line 1
> > Invalid object name 'SYSOBJECTS'.
> > The table does exist and the query is being run as an account with SQL
> > Admin privileges. This also occurs when trying to query the SysIndexes
> > table. Other databases on the server are able to query these tables.
> > What is causing this problem on this one database and how can it be
> > resolved?
Problem processing cube partition
I am getting the following error when trying to process a partition, as if
the process is trying to overwrite the database. How can I fix this?
TIA
Error 1 Errors in the metadata manager. The database with the name of
'ACRPROD_OLAP' already exists in the 'MBPENTSQL01R\ACR' server. 0 0
Are you deploying from BIDS? Did you rename a database that was previously deployed? The following post might shed some light. You might check the Name and ID properties of each deployed database and see whether any have the ID of ACRPROD_OLAP regardless of what Name they have.
http://geekswithblogs.net/darrengosbell/archive/2007/06/06/BIDSHelper-DeployMDXScript-feature-enhanced.aspx
Easy fix... delete the conflicting database off the server. More complex fix... instead of renaming a database on the server, backup the database, delete it, then restore it as the new name... that will make sure the new name matches the new ID.
sqlWednesday, March 21, 2012
Problem Primary Key will not be created?
I use following config in my vb Script to BulkLoad Data and set up tables in
our DB:
objBL.SGDropTables = True
objBL.SchemaGen = True
objBL.SGUseID = True
objBL.BulkLoad = True
The Mapping schema looks like this:
<?xml version="1.0" ?>
<Schema xmlns="urn:schemas-microsoft-com:xml-data"
xmlns:dt="urn:schemas-microsoft-com:xml:datatypes"
xmlns:sql="urn:schemas-microsoft-com:xml-sql" >
<ElementType name="IAM_Kostenerfassung.DatensatzNr." dt:type="id"
sql:datatype="nvarchar(15)"/>
<ElementType name="Report" sql:is-constant="1">
<element type="Kosten" />
</ElementType>
<element type="IAM_Kostenerfassung.DatensatzNr." sql:field="DatensatzNr"/>
</Schema>
You will need to use the sql:key-field annotation for this.
Andrew Conrad
Microsoft Corp
http://blogs.msdn.com/aconrad
|||How does that work in SQLXML Doc i found that:
"sql:key-fields
XML Bulk Load always ignores this annotation."
Can you give me an example for a working XDR-Schema?
""Andrew Conrad"" wrote:
> You will need to use the sql:key-field annotation for this.
> Andrew Conrad
> Microsoft Corp
> http://blogs.msdn.com/aconrad
>
|||I'm sorry - you are correct. SqlXmlBulkload does not any database
constraints.
our DB:
objBL.SGDropTables = True
objBL.SchemaGen = True
objBL.SGUseID = True
objBL.BulkLoad = True
The Mapping schema looks like this:
<?xml version="1.0" ?>
<Schema xmlns="urn:schemas-microsoft-com:xml-data"
xmlns:dt="urn:schemas-microsoft-com:xml:datatypes"
xmlns:sql="urn:schemas-microsoft-com:xml-sql" >
<ElementType name="IAM_Kostenerfassung.DatensatzNr." dt:type="id"
sql:datatype="nvarchar(15)"/>
<ElementType name="Report" sql:is-constant="1">
<element type="Kosten" />
</ElementType>
<element type="IAM_Kostenerfassung.DatensatzNr." sql:field="DatensatzNr"/>
</Schema>
You will need to use the sql:key-field annotation for this.
Andrew Conrad
Microsoft Corp
http://blogs.msdn.com/aconrad
|||How does that work in SQLXML Doc i found that:
"sql:key-fields
XML Bulk Load always ignores this annotation."
Can you give me an example for a working XDR-Schema?
""Andrew Conrad"" wrote:
> You will need to use the sql:key-field annotation for this.
> Andrew Conrad
> Microsoft Corp
> http://blogs.msdn.com/aconrad
>
|||I'm sorry - you are correct. SqlXmlBulkload does not any database
constraints.
Problem Primary Key will not be created?
I use following config in my vb Script to BulkLoad Data and set up tables in
our DB:
objBL.SGDropTables = True
objBL.SchemaGen = True
objBL.SGUseID = True
objBL.BulkLoad = True
The Mapping schema looks like this:
<?xml version="1.0" ?>
<Schema xmlns="urn:schemas-microsoft-com:xml-data"
xmlns:dt="urn:schemas-microsoft-com:xml:datatypes"
xmlns:sql="urn:schemas-microsoft-com:xml-sql" >
<ElementType name="IAM_Kostenerfassung.DatensatzNr." dt:type="id"
sql:datatype="nvarchar(15)"/>
<ElementType name="Report" sql:is-constant="1">
<element type="Kosten" />
</ElementType>
<element type="IAM_Kostenerfassung.DatensatzNr." sql:field="DatensatzNr"/>
</Schema>You will need to use the sql:key-field annotation for this.
Andrew Conrad
Microsoft Corp
http://blogs.msdn.com/aconrad|||How does that work in SQLXML Doc i found that:
"sql:key-fields
XML Bulk Load always ignores this annotation."
Can you give me an example for a working XDR-Schema?
""Andrew Conrad"" wrote:
> You will need to use the sql:key-field annotation for this.
> Andrew Conrad
> Microsoft Corp
> http://blogs.msdn.com/aconrad
>|||I'm sorry - you are correct. SqlXmlBulkload does not any database
constraints.
our DB:
objBL.SGDropTables = True
objBL.SchemaGen = True
objBL.SGUseID = True
objBL.BulkLoad = True
The Mapping schema looks like this:
<?xml version="1.0" ?>
<Schema xmlns="urn:schemas-microsoft-com:xml-data"
xmlns:dt="urn:schemas-microsoft-com:xml:datatypes"
xmlns:sql="urn:schemas-microsoft-com:xml-sql" >
<ElementType name="IAM_Kostenerfassung.DatensatzNr." dt:type="id"
sql:datatype="nvarchar(15)"/>
<ElementType name="Report" sql:is-constant="1">
<element type="Kosten" />
</ElementType>
<element type="IAM_Kostenerfassung.DatensatzNr." sql:field="DatensatzNr"/>
</Schema>You will need to use the sql:key-field annotation for this.
Andrew Conrad
Microsoft Corp
http://blogs.msdn.com/aconrad|||How does that work in SQLXML Doc i found that:
"sql:key-fields
XML Bulk Load always ignores this annotation."
Can you give me an example for a working XDR-Schema?
""Andrew Conrad"" wrote:
> You will need to use the sql:key-field annotation for this.
> Andrew Conrad
> Microsoft Corp
> http://blogs.msdn.com/aconrad
>|||I'm sorry - you are correct. SqlXmlBulkload does not any database
constraints.
Problem performing a join on a function in a SQL query
Hello,
Can someone explain why this code contains the following error:
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "TheTable.StartValue" could not be bound.
CREATE FUNCTION MyFunction(@.StartValue int)
RETURNS @.MyTable TABLE
(
NextValue int NOT NULL
)
AS
BEGIN
INSERT INTO @.MyTable(NextValue)
VALUES (@.StartValue + 1)
INSERT INTO @.MyTable(NextValue)
VALUES (@.StartValue + 2)
RETURN
END
GO
CREATE TABLE TheTable
(
StartValue int NOT NULL
)
GO
INSERT INTO TheTable(StartValue)
VALUES (10)
INSERT INTO TheTable(StartValue)
VALUES (20)
GO
SELECT *
FROM TheTable CROSS JOIN
MyFunction(TheTable.StartValue)
You can′t do that per row. The logic is quite simple that you presented here, what about doing
SELECT StartValue, StartValue+1,StartValue+2
From SomeTable
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de|||
In SQL Server 2000, this is not possible. However, in 2005, you can use the CROSS APPLY join operator:
SELECT *
FROM TheTable
CROSS APPLY MyFunction(TheTable.StartValue)
Interesting function. If you don't mind, could you share the purpose?
|||Hi,
you cannot use Table's Column as a parameter to the function. Only variables or Static Literals can be passed as an argument to the function
|||Thanks for your reply.
I wrote that function as an example of what I was trying to do. I have a vertical bar delimited column (eg. this|is|my|column). I used a CLR function to get all the values. I then run a aggregate of these values based on another field in another table. So the output should be something like this.
this: 2
is: 4
my: 0
column: 1
I can't do a straight aggregate because the "my" values above would be omitted.
|||Thanks for your reply.
I wrote that function as an example of what I was trying to do. I have a vertical bar delimited column (eg. this|is|my|column). I used a CLR function to get all the values. I then run a aggregate of these values based on another field in another table. So the output should be something like this.
this: 2
is: 4
my: 0
column: 1
I can't do a straight aggregate because the "my" values above would be omitted.
Tuesday, March 20, 2012
problem on sending message
Hi
few weeks ago I found an example of conversation using service broker.
I used the following code:
USE Test GO -- First, we need to create a message type. Note that our message type is -- very simple and allowed any type of content CREATE MESSAGE TYPE HelloMessage VALIDATION = NONE GO -- Once the message type has been created, we need to create a contract -- that specifies who can send what types of messages CREATE CONTRACT HelloContract (HelloMessage SENT BY INITIATOR) select * from sys.service_contracts GO -- The communication is between two endpoints. Thus, we need two queues to -- hold messages CREATE QUEUE [SenderQueue] with status = on select * from sys.service_queues --CREATE QUEUE ReceiverQueue Create QUEUE [ReceiverQueue] with status = on select * from sys.service_queues GO -- Create the required services and bind them to be above created queues CREATE SERVICE [Sender] ON QUEUE [SenderQueue] (HelloContract) CREATE SERVICE [Receiver] ON QUEUE [ReceiverQueue] (HelloContract) GO
Then I try to send a message:
DECLARE @.conversationHandle UNIQUEIDENTIFIER DECLARE @.message NVARCHAR(100) BEGIN BEGIN DIALOG @.conversationHandle FROM SERVICE Sender TO SERVICE 'Receiver' ON CONTRACT HelloContract -- Send a message on the conversation SET @.message = N'Hello, World'; SEND ON CONVERSATION @.conversationHandle MESSAGE TYPE HelloMessage (@.message) END
Then I read the message in the ReceiverQueue
RECEIVE message_body FROM dbo.receiverqueue
I get no messages, can you help me to discover why?
A master key has probably not been setup for that database. See the documentation on CREATE MASTER KEY -- http://msdn2.microsoft.com/en-us/library/ms174382.aspx.
Also, when messages cannot be delivered immediately, they are placed in sys.transmission_queue.
-mike
|||BEGIN DIALOG @.conversationHandle
FROM SERVICE Sender
TO SERVICE 'Receiver'
ON CONTRACT HelloContract
with encryption = off
problem on running replication on pocket pc
hi i tried to view a replication on a pocket pc app that i created. and i used the following code
private void Sync()
{
public string AppPath = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase);
SqlCeReplication repl = new SqlCeReplication();
repl.InternetUrl = @."http://naomi/sqlmobile/sqlcesa30.dll";
repl.Publisher = @."Naomi";
repl.PublisherDatabase = @."SQLMobile";
repl.PublisherSecurityMode = SecurityType.DBAuthentication;
repl.PublisherLogin = @."sa";
repl.PublisherPassword = @."<...>";
repl.Publication = @."SQLMobile";
repl.Subscriber = @."SQLMobile";
repl.SubscriberConnectionString = @."Data Source=""" + AppPath + @."\SqlMobile.sdf"";Max Database Size=128;Default Lock Escalation =100;";
try
{
repl.AddSubscription(AddOption.ExistingDatabase);
repl.Synchronize();
}
catch (SqlCeException e)
{
MessageBox.Show(e.ToString());
}
when i run it i keep on getting the error:
The SQL Mobile Subscription already exists. Publisher, PublisherDatabase, and Publication for this subscription should be different from any existing subscription.
However, i can still view the contents of the datagrid that refers to the database subscription.
1. What should i do in order to stop getting the error above?
2. If i edit the contents of the datagrid in the pocket pc app how will i update the replication in the sql server? thanks
With the line of code "repl.AddSubscription(AddOption.ExistingDatabase)" you are effectively trying to add a new subscription to your local SQL Mobile database every time your synchronize. You only need to perform the AddSubscription the first time you synchronize with the server.
What I typically do is use AddOption.CreateDatabase, which dynamically creates the SQL Mobile database (if it does not already exist) the first time synchronization occurs.
-Darren
Subscribe to:
Posts (Atom)