Restrict login to one instance

Hello All,
I'm using SQL Server 2000 and wish to restrict the user login to one
instance only. Is this possible? Is this wise?"Fred Payne" <fpayne@.ryerson.ca> wrote in message
news:bnohcm$u46$1@.news.ryerson.ca...
> Hello All,
> I'm using SQL Server 2000 and wish to restrict the user login to one
> instance only. Is this possible? Is this wise?

Not sure what you mean. You can sit a database to single user, but that's
generally only for maintenance purposes.

Without knowing more, I can only say it's probably NOT wise.
|||"Fred Payne" <fpayne@.ryerson.ca> wrote in message
news:bnohcm$u46$1@.news.ryerson.ca...
> Hello All,
> I'm using SQL Server 2000 and wish to restrict the user login to one
> instance only. Is this possible? Is this wise?

Not sure what you mean. You can sit a database to single user, but that's
generally only for maintenance purposes.

Without knowing more, I can only say it's probably NOT wise.
|||"Fred Payne" <fpayne@.ryerson.ca> wrote in message news:<bnohcm$u46$1@.news.ryerson.ca>...
> Hello All,
> I'm using SQL Server 2000 and wish to restrict the user login to one
> instance only. Is this possible? Is this wise?

Assuming that you mean 'instance' as in 'named instance' (see Books
Online), then each instance is a separate installation, so you manage
security separately for each one. If a user doesn't need access to one
of the instances, then you simply don't grant access to that one. If
this doesn't help, perhaps you could clarify exactly what you want to
achieve?

Simon

restoring system db's on new instance

Hi There

We have a database server that must be completely re-setup, the hard drives will be replaced and the OS and Sql Server 2000 EE edition re-installed from scratch, a fresh new instance.

However the client has requested that we restore the master and msdb databases of the old instance over the new one once it is installed.

Now is this ok to do ? Can i simply restore a backup of master and msdb ont he new instance and what is the correct procedure to do this ?

I am not to worried about the user DB's i will simply dettach and re-attach. But must this be done before or after restoring the old master if i can do that ?

Thanx

To restore Master db you should build your box with exactly same OS and SQL configuration as from which you have taken the backup. ie. OS and sql server has to be same version /edition/sp /patches applied

Refer this : http://support.microsoft.com/kb/264474

Madhu

|||

These resources may help:

http://msdn2.microsoft.com/en-us/library/ms345408(en-US,SQL.90).aspx Moving system dbs 2005
http://www.databasejournal.com/features/mssql/article.php/3379901 Moving system DB's 2000
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases to a New Location with Detach/Attach
http://www.support.microsoft.com/?id=221465 Using WITH MOVE in a Restore
http://www.sqlservercentral.com/columnists/cBunch/movingyouruserswiththeirdatabases.asp Moving Users
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to users

Restoring from 2000 backup to 2005 instance

I did a backup of a 2000 database using the 2005 management studio. I then
created a blank database on my 2005 instance. I then tried to do a database
restore to the new 2005 database. I got an error:
System.Data.SqlClient.SqlError: The backup set holds a backup of a database
other than the existing 'Winstis' database. (Microsoft.SqlServer.Smo)
I even tried the option to Overwrite Existing database and I got a different
error:
System.Data.SqlClient.SqlError: The operating system returned the error '32
(The process cannot access the file because it is being used by another
process.)' while attempting 'RestoreContainer::ValidateTargetForCrea
tion' on
'D:\Program Files\Microsoft SQL Server\MSSQL\Data\test.ldf'. (Microsoft.
SqlServer.Smo)
Also the 2005 instance database is on C:\Program Files\Microsoft SQL Server\
MSSQL.1\MSSQL\Data\Winstis_Data.mdf
and the 2000 instance is on
D:\Program Files\Microsoft SQL Server\MSSQL\Data\Winstis_Data.mdf
How can I accomplish getting the database from 2000 to 2005?
Message posted via http://www.droptable.comHello,
Do not create the blank database instead do the below steps:-
1. Do the SQL Server 2000 backup using Backup Database WITH INIT
2. Copy the .BAK file to SQL 2005 machine
3. Create the required folders for MDF and LDF in SQL 2005 server
3. Do the below command
RESTORE DATABASE Winstis FROM DISK='D:\backup\dbbackup2000.bak'
with MOVE 'LogicalMDFName' to 'd:\Data\Winstis_Data.mdf',
with MOVE 'LogicalLDFName' to 'd:\Data\Winstis_log.ldf',stats=2
Ge the logical MDF and LDF names using RESTORE FILELISTONLY command
Thanks
Hari
"dcuff via droptable.com" <u12346@.uwe> wrote in message
news:6941157063204@.uwe...
>I did a backup of a 2000 database using the 2005 management studio. I then
> created a blank database on my 2005 instance. I then tried to do a
> database
> restore to the new 2005 database. I got an error:
> System.Data.SqlClient.SqlError: The backup set holds a backup of a
> database
> other than the existing 'Winstis' database. (Microsoft.SqlServer.Smo)
> I even tried the option to Overwrite Existing database and I got a
> different
> error:
> System.Data.SqlClient.SqlError: The operating system returned the error
> '32
> (The process cannot access the file because it is being used by another
> process.)' while attempting 'RestoreContainer::ValidateTargetForCrea
tion'
> on
> 'D:\Program Files\Microsoft SQL Server\MSSQL\Data\test.ldf'. (Microsoft.
> SqlServer.Smo)
> Also the 2005 instance database is on C:\Program Files\Microsoft SQL
> Server\
> MSSQL.1\MSSQL\Data\Winstis_Data.mdf
> and the 2000 instance is on
> D:\Program Files\Microsoft SQL Server\MSSQL\Data\Winstis_Data.mdf
> How can I accomplish getting the database from 2000 to 2005?
> --
> Message posted via http://www.droptable.com
>|||Thanks for the response Hari. This procedure will not affect the Winstis
database on SQL2000 will it. That is our production instance. I only want to
copy the database to the SQL2005 instance not a complete move. That way I ca
n
test our ERP on it.
Thanks,
David
Hari Prasad wrote:[vbcol=seagreen]
>Hello,
>Do not create the blank database instead do the below steps:-
>1. Do the SQL Server 2000 backup using Backup Database WITH INIT
>2. Copy the .BAK file to SQL 2005 machine
>3. Create the required folders for MDF and LDF in SQL 2005 server
>3. Do the below command
> RESTORE DATABASE Winstis FROM DISK='D:\backup\dbbackup2000.bak'
> with MOVE 'LogicalMDFName' to 'd:\Data\Winstis_Data.mdf',
> with MOVE 'LogicalLDFName' to 'd:\Data\Winstis_log.ldf',stats=2
>Ge the logical MDF and LDF names using RESTORE FILELISTONLY command
>Thanks
>Hari
>
>[quoted text clipped - 25 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200611/1|||>I did a backup of a 2000 database using the 2005 management studio. I then
> created a blank database on my 2005 instance. I then tried to >do a
> database
You don't need to create a blank database. Do just restore commnd
"dcuff via droptable.com" <u12346@.uwe> wrote in message
news:694268af1752f@.uwe...
> Thanks for the response Hari. This procedure will not affect the Winstis
> database on SQL2000 will it. That is our production instance. I only want
> to
> copy the database to the SQL2005 instance not a complete move. That way I
> can
> test our ERP on it.
> Thanks,
> David
> Hari Prasad wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200611/1
>

Restoring from 2000 backup to 2005 instance

I did a backup of a SQL2000 database (named Winstis) using the 2005 management studio. I then created a blank database on my 2005 instance (named Winstis). I then tried to do a database restore to the new 2005 database. I got an error:

System.Data.SqlClient.SqlError: The backup set holds a backup of a database other than the existing 'Winstis' database. (Microsoft.SqlServer.Smo)

I even tried the option to Overwrite Existing database and I got a different error:

System.Data.SqlClient.SqlError: The operating system returned the error '32(The process cannot access the file because it is being used by another process.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'D:\Program Files\Microsoft SQL Server\MSSQL\Data\test.ldf'. (Microsoft.SqlServer.Smo)

Also the 2005 instance database is on C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Winstis_Data.mdf

and the 2000 instance is on
D:\Program Files\Microsoft SQL Server\MSSQL\Data\Winstis_Data.mdf

How can I accomplish getting the database from 2000 to 2005 with all tables, store procedures, and functions?you can restore from a 2000 backup to a 2005 server, but only if the db you are restoring to doesn't exist on the 2005 server at restore time.

so delete the winstis db on your 2005 instance then restore from the bak, should work ok then.|||Hi Jezemine,
I actually tried it that way as well. I finally got an answer from another forum. The key was using the move options on the RESTORE.

Thanks though.

Restoring from 2000 backup to 2005 instance

I did a backup of a 2000 database using the 2005 management studio. I then
created a blank database on my 2005 instance. I then tried to do a database
restore to the new 2005 database. I got an error:
System.Data.SqlClient.SqlError: The backup set holds a backup of a database
other than the existing 'Winstis' database. (Microsoft.SqlServer.Smo)
I even tried the option to Overwrite Existing database and I got a different
error:
System.Data.SqlClient.SqlError: The operating system returned the error '32
(The process cannot access the file because it is being used by another
process.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on
'D:\Program Files\Microsoft SQL Server\MSSQL\Data\test.ldf'. (Microsoft.
SqlServer.Smo)
Also the 2005 instance database is on C:\Program Files\Microsoft SQL Server\
MSSQL.1\MSSQL\Data\Winstis_Data.mdf
and the 2000 instance is on
D:\Program Files\Microsoft SQL Server\MSSQL\Data\Winstis_Data.mdf
How can I accomplish getting the database from 2000 to 2005?
Message posted via http://www.droptable.com
Hello,
Do not create the blank database instead do the below steps:-
1. Do the SQL Server 2000 backup using Backup Database WITH INIT
2. Copy the .BAK file to SQL 2005 machine
3. Create the required folders for MDF and LDF in SQL 2005 server
3. Do the below command
RESTORE DATABASE Winstis FROM DISK='D:\backup\dbbackup2000.bak'
with MOVE 'LogicalMDFName' to 'd:\Data\Winstis_Data.mdf',
with MOVE 'LogicalLDFName' to 'd:\Data\Winstis_log.ldf',stats=2
Ge the logical MDF and LDF names using RESTORE FILELISTONLY command
Thanks
Hari
"dcuff via droptable.com" <u12346@.uwe> wrote in message
news:6941157063204@.uwe...
>I did a backup of a 2000 database using the 2005 management studio. I then
> created a blank database on my 2005 instance. I then tried to do a
> database
> restore to the new 2005 database. I got an error:
> System.Data.SqlClient.SqlError: The backup set holds a backup of a
> database
> other than the existing 'Winstis' database. (Microsoft.SqlServer.Smo)
> I even tried the option to Overwrite Existing database and I got a
> different
> error:
> System.Data.SqlClient.SqlError: The operating system returned the error
> '32
> (The process cannot access the file because it is being used by another
> process.)' while attempting 'RestoreContainer::ValidateTargetForCreation'
> on
> 'D:\Program Files\Microsoft SQL Server\MSSQL\Data\test.ldf'. (Microsoft.
> SqlServer.Smo)
> Also the 2005 instance database is on C:\Program Files\Microsoft SQL
> Server\
> MSSQL.1\MSSQL\Data\Winstis_Data.mdf
> and the 2000 instance is on
> D:\Program Files\Microsoft SQL Server\MSSQL\Data\Winstis_Data.mdf
> How can I accomplish getting the database from 2000 to 2005?
> --
> Message posted via http://www.droptable.com
>
|||Thanks for the response Hari. This procedure will not affect the Winstis
database on SQL2000 will it. That is our production instance. I only want to
copy the database to the SQL2005 instance not a complete move. That way I can
test our ERP on it.
Thanks,
David
Hari Prasad wrote:[vbcol=seagreen]
>Hello,
>Do not create the blank database instead do the below steps:-
>1. Do the SQL Server 2000 backup using Backup Database WITH INIT
>2. Copy the .BAK file to SQL 2005 machine
>3. Create the required folders for MDF and LDF in SQL 2005 server
>3. Do the below command
> RESTORE DATABASE Winstis FROM DISK='D:\backup\dbbackup2000.bak'
> with MOVE 'LogicalMDFName' to 'd:\Data\Winstis_Data.mdf',
> with MOVE 'LogicalLDFName' to 'd:\Data\Winstis_log.ldf',stats=2
>Ge the logical MDF and LDF names using RESTORE FILELISTONLY command
>Thanks
>Hari
>[quoted text clipped - 25 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200611/1
|||>I did a backup of a 2000 database using the 2005 management studio. I then
> created a blank database on my 2005 instance. I then tried to >do a
> database
You don't need to create a blank database. Do just restore commnd
"dcuff via droptable.com" <u12346@.uwe> wrote in message
news:694268af1752f@.uwe...
> Thanks for the response Hari. This procedure will not affect the Winstis
> database on SQL2000 will it. That is our production instance. I only want
> to
> copy the database to the SQL2005 instance not a complete move. That way I
> can
> test our ERP on it.
> Thanks,
> David
> Hari Prasad wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server/200611/1
>

Restoring from 2000 backup to 2005 instance

I did a backup of a 2000 database using the 2005 management studio. I then
created a blank database on my 2005 instance. I then tried to do a database
restore to the new 2005 database. I got an error:
System.Data.SqlClient.SqlError: The backup set holds a backup of a database
other than the existing 'Winstis' database. (Microsoft.SqlServer.Smo)
I even tried the option to Overwrite Existing database and I got a different
error:
System.Data.SqlClient.SqlError: The operating system returned the error '32
(The process cannot access the file because it is being used by another
process.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on
'D:\Program Files\Microsoft SQL Server\MSSQL\Data\test.ldf'. (Microsoft.
SqlServer.Smo)
Also the 2005 instance database is on C:\Program Files\Microsoft SQL Server\
MSSQL.1\MSSQL\Data\Winstis_Data.mdf
and the 2000 instance is on
D:\Program Files\Microsoft SQL Server\MSSQL\Data\Winstis_Data.mdf
How can I accomplish getting the database from 2000 to 2005?
--
Message posted via http://www.sqlmonster.comHello,
Do not create the blank database instead do the below steps:-
1. Do the SQL Server 2000 backup using Backup Database WITH INIT
2. Copy the .BAK file to SQL 2005 machine
3. Create the required folders for MDF and LDF in SQL 2005 server
3. Do the below command
RESTORE DATABASE Winstis FROM DISK='D:\backup\dbbackup2000.bak'
with MOVE 'LogicalMDFName' to 'd:\Data\Winstis_Data.mdf',
with MOVE 'LogicalLDFName' to 'd:\Data\Winstis_log.ldf',stats=2
Ge the logical MDF and LDF names using RESTORE FILELISTONLY command
Thanks
Hari
"dcuff via SQLMonster.com" <u12346@.uwe> wrote in message
news:6941157063204@.uwe...
>I did a backup of a 2000 database using the 2005 management studio. I then
> created a blank database on my 2005 instance. I then tried to do a
> database
> restore to the new 2005 database. I got an error:
> System.Data.SqlClient.SqlError: The backup set holds a backup of a
> database
> other than the existing 'Winstis' database. (Microsoft.SqlServer.Smo)
> I even tried the option to Overwrite Existing database and I got a
> different
> error:
> System.Data.SqlClient.SqlError: The operating system returned the error
> '32
> (The process cannot access the file because it is being used by another
> process.)' while attempting 'RestoreContainer::ValidateTargetForCreation'
> on
> 'D:\Program Files\Microsoft SQL Server\MSSQL\Data\test.ldf'. (Microsoft.
> SqlServer.Smo)
> Also the 2005 instance database is on C:\Program Files\Microsoft SQL
> Server\
> MSSQL.1\MSSQL\Data\Winstis_Data.mdf
> and the 2000 instance is on
> D:\Program Files\Microsoft SQL Server\MSSQL\Data\Winstis_Data.mdf
> How can I accomplish getting the database from 2000 to 2005?
> --
> Message posted via http://www.sqlmonster.com
>|||Thanks for the response Hari. This procedure will not affect the Winstis
database on SQL2000 will it. That is our production instance. I only want to
copy the database to the SQL2005 instance not a complete move. That way I can
test our ERP on it.
Thanks,
David
Hari Prasad wrote:
>Hello,
>Do not create the blank database instead do the below steps:-
>1. Do the SQL Server 2000 backup using Backup Database WITH INIT
>2. Copy the .BAK file to SQL 2005 machine
>3. Create the required folders for MDF and LDF in SQL 2005 server
>3. Do the below command
> RESTORE DATABASE Winstis FROM DISK='D:\backup\dbbackup2000.bak'
> with MOVE 'LogicalMDFName' to 'd:\Data\Winstis_Data.mdf',
> with MOVE 'LogicalLDFName' to 'd:\Data\Winstis_log.ldf',stats=2
>Ge the logical MDF and LDF names using RESTORE FILELISTONLY command
>Thanks
>Hari
>>I did a backup of a 2000 database using the 2005 management studio. I then
>> created a blank database on my 2005 instance. I then tried to do a
>[quoted text clipped - 25 lines]
>> How can I accomplish getting the database from 2000 to 2005?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200611/1|||>I did a backup of a 2000 database using the 2005 management studio. I then
> created a blank database on my 2005 instance. I then tried to >do a
> database
You don't need to create a blank database. Do just restore commnd
"dcuff via SQLMonster.com" <u12346@.uwe> wrote in message
news:694268af1752f@.uwe...
> Thanks for the response Hari. This procedure will not affect the Winstis
> database on SQL2000 will it. That is our production instance. I only want
> to
> copy the database to the SQL2005 instance not a complete move. That way I
> can
> test our ERP on it.
> Thanks,
> David
> Hari Prasad wrote:
>>Hello,
>>Do not create the blank database instead do the below steps:-
>>1. Do the SQL Server 2000 backup using Backup Database WITH INIT
>>2. Copy the .BAK file to SQL 2005 machine
>>3. Create the required folders for MDF and LDF in SQL 2005 server
>>3. Do the below command
>> RESTORE DATABASE Winstis FROM DISK='D:\backup\dbbackup2000.bak'
>> with MOVE 'LogicalMDFName' to 'd:\Data\Winstis_Data.mdf',
>> with MOVE 'LogicalLDFName' to 'd:\Data\Winstis_log.ldf',stats=2
>>Ge the logical MDF and LDF names using RESTORE FILELISTONLY command
>>Thanks
>>Hari
>>I did a backup of a 2000 database using the 2005 management studio. I
>>then
>> created a blank database on my 2005 instance. I then tried to do a
>>[quoted text clipped - 25 lines]
>> How can I accomplish getting the database from 2000 to 2005?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200611/1
>

restoring database leaves me with (Restoring...) message

I did a full backup of a db from one server(Express2005) and trying to restore it to a different instance of SQL2005 on the same development machine. (Also had some fulltext columns if that means anything)

Many failures but finally got it to report all was successful except the icon in Object Explorer shows (Restoring...) with no indication of any real activity going on. It's a tiny database with hardly any data in it.

Just not sure what the heck is going on there. It also won't let me into the database until this the (Restoring...) goes away.

Any advice on how to get this thing finished out?

can u post the script which u r used for backup and restore. did u use with Move option while restoring.

Madhu

|||

Hi,

try

restore database <yourdatabase> with recovery

and as suggested plese post the script you used to restore database.

Hemantgiri S. Goswami

Restoring database in SQL - 7

Hi,
my problem is :-
I have installed SQL - 7 at my home and work. instance name of both SQL is
differ.
I have created mydata.bak file at home and trying to restore at work, but I
am fail .
Any hints will be sincerly appreciated.
Hardik ShahJust taking a stab in the dark, since you have not provided what error
message you are getting. If you are trying to restore using Enterprise
Manager, you must click the Options tab, and check 'force restore over
existing database'. Then, change the location of the data files to match th
e
physical locations on the machine you are restoring to.
"h" wrote:

> Hi,
> my problem is :-
> I have installed SQL - 7 at my home and work. instance name of both SQL is
> differ.
> I have created mydata.bak file at home and trying to restore at work, but
I
> am fail .
> Any hints will be sincerly appreciated.
> Hardik Shah
>
>
>

Restoring database in SQL - 7

Hi,
my problem is :-
I have installed SQL - 7 at my home and work. instance name of both SQL is
differ.
I have created mydata.bak file at home and trying to restore at work, but I
am fail .
Any hints will be sincerly appreciated.
Hardik ShahJust taking a stab in the dark, since you have not provided what error
message you are getting. If you are trying to restore using Enterprise
Manager, you must click the Options tab, and check 'force restore over
existing database'. Then, change the location of the data files to match the
physical locations on the machine you are restoring to.
"h" wrote:
> Hi,
> my problem is :-
> I have installed SQL - 7 at my home and work. instance name of both SQL is
> differ.
> I have created mydata.bak file at home and trying to restore at work, but I
> am fail .
> Any hints will be sincerly appreciated.
> Hardik Shah
>
>
>