Restore user db'd on servers with different service pack levels

Is there any documentation on comaptibility related to restoring
databases backed up from server A to server B as it pertains to
editions and service pack levels?
For example, can you restore a SQL2k sp3 database onto a SQL2ksp4
database, and vice versa?take a look at
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/508c686d-2bd4-41ba-8602-
48ebca266659.htm
in your SQL 2005 books online
or
search sp_dbcmptlevel for SQL 2000
SQL Server is forward Compatable as a rule of thumb. Meaning you can
usually go from an older version of 2000 to a 2005. It does not support
backwards compatablity moving from 2005 to 2000.
So to answer your question. Yes you can move forward and restore onto the
newest service pack as long as these are USER databases and not system.
thanks,
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"Jeff McAhren" <mcahren@.gmail.com> wrote in message
news:1158004530.422905.146510@.i3g2000cwc.googlegroups.com...
> Is there any documentation on comaptibility related to restoring
> databases backed up from server A to server B as it pertains to
> editions and service pack levels?
> For example, can you restore a SQL2k sp3 database onto a SQL2ksp4
> database, and vice versa?
>

Restore master database with SQL 2005 made an error with me

- First I made a full backup of master database

- Then I stopped SQL Server Service

- in cmd: cd C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Binn

then sqlservr.exe -m

- I entered that single user mode

> Problem here:

- I tried to connect to sql by sqlcmd -E >

HResult 0xE9, Level 16, State 1
Shared Memory Provider: No process is on the other end of the pipe.

Sqlcmd: Error: Microsoft SQL Native Client : Communication link failure.

> I tried to connect to management studio:

TITLE: Connect to Server

Cannot connect to BISHOYSERVER.

ADDITIONAL INFORMATION:

Login failed for user 'OFFICIALPROJECT\Administrator'. Reason: Server is in single user mode. Only one administrator can connect at this time. (Microsoft SQL Server, Error: 18461)

You can not utilize Management Studio when SQL Server is in single user mode. SSMS launches multiple connections which is not allowed. When you launch sqlcmd, add the -S parameter to specify an instance name.|||

The SQL server will not allow a login for anything once in single user mode. I have been trying for a week to move the System databases to two RAID 1+0 disk enclosures (one for MDF and one for the Transaction logs) with no success.

The information given in documentation is inaccurate as the solution does not work.

How does one run the query 'ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME = data, FILENAME = 'E:\SQLData\mssqlsystemresource.mdf'); go' if you cannot attach to the single user mode database?

Who is not telling us what?

|||If you can't connect, then that means someone or something else is grabbing the only connection when you put it in single user mode. You need to find every connection that is normally there and make sure that every application which would connect is shut down and can not get to the SQL Server and that no one else is connecting either. Worst case scenario if you can't get everything to quit connecting and tying up your connection is that you walk into the data center, login directly to the console, walk around behind the machine, yank the network cable out of the back, and then do your work. At that point, it is completely impossible for anyone else to connect to the machine.|||I was having this same problem. This is a brand new server that's no one is even connected to, so when i saw Michael's post, I realized that there were a few other SQL related services that were running that could be connecting to the database server.

My guess is that the reporting service is what was connecting, but just to be sure, I stopped all services and then followed Microsoft's guide to moving the master and mssqlsystemresource databases. This seemed to fix the problem.

Hope this helps!
|||

Stop the SQL server reporting services

and login using syntax

sqlcmd -E -S %SQLName% -Q"ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME = 'log', FILENAME = 'f:\sql05data\mssqlsystemresource.ldf')"

Cheers !!!

Nithin

Restore master database with SQL 2005 made an error with me

- First I made a full backup of master database
- Then I stopped SQL Server Service
- in cmd: cd C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Binn
then sqlservr.exe -m
- I entered that single user mode
--> Problem here:
- I tried to connect to sql by sqlcmd -E -->
HResult 0xE9, Level 16, State 1
Shared Memory Provider: No process is on the other end of the pipe.
Sqlcmd: Error: Microsoft SQL Native Client : Communication link failure.
--> I tried to connect to management studio:
TITLE: Connect to Server
--
Cannot connect to BISHOYSERVER.
--
ADDITIONAL INFORMATION:
Login failed for user 'OFFICIALPROJECT\Administrator'. Reason: Server is in
single user mode. Only one administrator can connect at this time. (Microsoft
SQL Server, Error: 18461)
Thank you
BishoySomebody else is connected. That might be Agent service, some user or some other service or local
app. Hunt down this, then connect using SQLCMD.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Bishoy" <Bishoy@.discussions.microsoft.com> wrote in message
news:5D9C15D7-1B68-444E-957B-83932E76AC9E@.microsoft.com...
>- First I made a full backup of master database
> - Then I stopped SQL Server Service
> - in cmd: cd C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Binn
> then sqlservr.exe -m
> - I entered that single user mode
> --> Problem here:
> - I tried to connect to sql by sqlcmd -E -->
> HResult 0xE9, Level 16, State 1
> Shared Memory Provider: No process is on the other end of the pipe.
> Sqlcmd: Error: Microsoft SQL Native Client : Communication link failure.
> --> I tried to connect to management studio:
> TITLE: Connect to Server
> --
> Cannot connect to BISHOYSERVER.
> --
> ADDITIONAL INFORMATION:
> Login failed for user 'OFFICIALPROJECT\Administrator'. Reason: Server is in
> single user mode. Only one administrator can connect at this time. (Microsoft
> SQL Server, Error: 18461)
> Thank you
> Bishoy|||No one else is connected.
Agent service, as you should know, is stopped immediately when you stop
MSSqlServer service.
Also this SQL server instance is on my machine and I am sure no application
uses it.
Thanks
Bishoy
--
"Tibor Karaszi" wrote:
> Somebody else is connected. That might be Agent service, some user or some other service or local
> app. Hunt down this, then connect using SQLCMD.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Bishoy" <Bishoy@.discussions.microsoft.com> wrote in message
> news:5D9C15D7-1B68-444E-957B-83932E76AC9E@.microsoft.com...
> >- First I made a full backup of master database
> >
> > - Then I stopped SQL Server Service
> >
> > - in cmd: cd C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Binn
> >
> > then sqlservr.exe -m
> >
> > - I entered that single user mode
> >
> > --> Problem here:
> >
> > - I tried to connect to sql by sqlcmd -E -->
> >
> > HResult 0xE9, Level 16, State 1
> > Shared Memory Provider: No process is on the other end of the pipe.
> >
> > Sqlcmd: Error: Microsoft SQL Native Client : Communication link failure.
> >
> > --> I tried to connect to management studio:
> >
> > TITLE: Connect to Server
> > --
> >
> > Cannot connect to BISHOYSERVER.
> >
> > --
> > ADDITIONAL INFORMATION:
> >
> > Login failed for user 'OFFICIALPROJECT\Administrator'. Reason: Server is in
> > single user mode. Only one administrator can connect at this time. (Microsoft
> > SQL Server, Error: 18461)
> >
> > Thank you
> > Bishoy
>|||Is it a named instance?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Bishoy" <Bishoy@.discussions.microsoft.com> wrote in message
news:7C480D1F-D426-41CA-A850-B5679ABB9785@.microsoft.com...
> No one else is connected.
> Agent service, as you should know, is stopped immediately when you stop
> MSSqlServer service.
> Also this SQL server instance is on my machine and I am sure no application
> uses it.
> Thanks
> Bishoy
> --
> "Tibor Karaszi" wrote:
>> Somebody else is connected. That might be Agent service, some user or some other service or local
>> app. Hunt down this, then connect using SQLCMD.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Bishoy" <Bishoy@.discussions.microsoft.com> wrote in message
>> news:5D9C15D7-1B68-444E-957B-83932E76AC9E@.microsoft.com...
>> >- First I made a full backup of master database
>> >
>> > - Then I stopped SQL Server Service
>> >
>> > - in cmd: cd C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Binn
>> >
>> > then sqlservr.exe -m
>> >
>> > - I entered that single user mode
>> >
>> > --> Problem here:
>> >
>> > - I tried to connect to sql by sqlcmd -E -->
>> >
>> > HResult 0xE9, Level 16, State 1
>> > Shared Memory Provider: No process is on the other end of the pipe.
>> >
>> > Sqlcmd: Error: Microsoft SQL Native Client : Communication link failure.
>> >
>> > --> I tried to connect to management studio:
>> >
>> > TITLE: Connect to Server
>> > --
>> >
>> > Cannot connect to BISHOYSERVER.
>> >
>> > --
>> > ADDITIONAL INFORMATION:
>> >
>> > Login failed for user 'OFFICIALPROJECT\Administrator'. Reason: Server is in
>> > single user mode. Only one administrator can connect at this time. (Microsoft
>> > SQL Server, Error: 18461)
>> >
>> > Thank you
>> > Bishoy
>>