Hi,
I want to restrict the access to enterprise manager from the developer
computer. They need the access the develpment machine so they require
enterprise manager on their machines. But i do not want them to access
the production database from their workstation enterprise manager. Also
because they are developers they know the username/password for the
production database.
If there is any tool or utility which check who is login in and from
which program and than it can accept or reject the connection would ba
great help.
Regards,
Puneet
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
"Puneet Parashar" <parashar22@.hotmail.com> wrote in message
news:#tPxEX5YEHA.3432@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I want to restrict the access to enterprise manager from the developer
> computer. They need the access the develpment machine so they require
> enterprise manager on their machines. But i do not want them to access
> the production database from their workstation enterprise manager. Also
> because they are developers they know the username/password for the
> production database.
> If there is any tool or utility which check who is login in and from
> which program and than it can accept or reject the connection would ba
> great help.
>
> Regards,
> Puneet
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!
|||Hi,
Check the sql server security in BOL.
1) Delete the guest user account from the database.
2) Restrict the users and give the permissions only to the specific
databases which they need.
"Puneet Parashar" <parashar22@.hotmail.com> wrote in message
news:#tPxEX5YEHA.3432@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I want to restrict the access to enterprise manager from the developer
> computer. They need the access the develpment machine so they require
> enterprise manager on their machines. But i do not want them to access
> the production database from their workstation enterprise manager. Also
> because they are developers they know the username/password for the
> production database.
> If there is any tool or utility which check who is login in and from
> which program and than it can accept or reject the connection would ba
> great help.
>
> Regards,
> Puneet
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!
Showing posts with label machine. Show all posts
Showing posts with label machine. Show all posts
Friday, March 30, 2012
Monday, March 26, 2012
Restoring to a different directory structure
We are trying to import our backups made on one SQL Server 2000
machine to another SQL Server 2000 machine. The problem is the
directory structures are different. After the import is done, I get
errors trying to start SQL Server. I have to move the master database
around. This makes me wonder what other things are in the wrong place?
What other problems will we have? etc.
I don't understand what the problem is exactly, shouldn't SQL Server
import the database and put its files wherever the current 'data'
directory is?
The source server was originally running 7.0, which we upgraded in
place to 2000. All the sql server directories, like data, repldata,
ftdata, logs, etc are in our d:\database directory.
On the destination server, it is a fresh install of SQL Server 2000. I
tried to specify "d:\database" as our data directory when installing,
but it insists on creating an MSSQL directory, and then putting all of
its directories within that directory (argghhh!!).
Any ideas?
Thanks,
ThomasThomas,
you should use the MOVE option of the Restore command. Check for syntax in
Books OnLine.
--
Dejan Sarka, SQL Server MVP
Please reply only to the newsgroups.
"Thomas" <thomas-ggl-01@.data.iatn.net> wrote in message
news:4f2cac50.0311100913.21169934@.posting.google.com...
> We are trying to import our backups made on one SQL Server 2000
> machine to another SQL Server 2000 machine. The problem is the
> directory structures are different. After the import is done, I get
> errors trying to start SQL Server. I have to move the master database
> around. This makes me wonder what other things are in the wrong place?
> What other problems will we have? etc.
> I don't understand what the problem is exactly, shouldn't SQL Server
> import the database and put its files wherever the current 'data'
> directory is?
> The source server was originally running 7.0, which we upgraded in
> place to 2000. All the sql server directories, like data, repldata,
> ftdata, logs, etc are in our d:\database directory.
> On the destination server, it is a fresh install of SQL Server 2000. I
> tried to specify "d:\database" as our data directory when installing,
> but it insists on creating an MSSQL directory, and then putting all of
> its directories within that directory (argghhh!!).
> Any ideas?
> Thanks,
> Thomassql
machine to another SQL Server 2000 machine. The problem is the
directory structures are different. After the import is done, I get
errors trying to start SQL Server. I have to move the master database
around. This makes me wonder what other things are in the wrong place?
What other problems will we have? etc.
I don't understand what the problem is exactly, shouldn't SQL Server
import the database and put its files wherever the current 'data'
directory is?
The source server was originally running 7.0, which we upgraded in
place to 2000. All the sql server directories, like data, repldata,
ftdata, logs, etc are in our d:\database directory.
On the destination server, it is a fresh install of SQL Server 2000. I
tried to specify "d:\database" as our data directory when installing,
but it insists on creating an MSSQL directory, and then putting all of
its directories within that directory (argghhh!!).
Any ideas?
Thanks,
ThomasThomas,
you should use the MOVE option of the Restore command. Check for syntax in
Books OnLine.
--
Dejan Sarka, SQL Server MVP
Please reply only to the newsgroups.
"Thomas" <thomas-ggl-01@.data.iatn.net> wrote in message
news:4f2cac50.0311100913.21169934@.posting.google.com...
> We are trying to import our backups made on one SQL Server 2000
> machine to another SQL Server 2000 machine. The problem is the
> directory structures are different. After the import is done, I get
> errors trying to start SQL Server. I have to move the master database
> around. This makes me wonder what other things are in the wrong place?
> What other problems will we have? etc.
> I don't understand what the problem is exactly, shouldn't SQL Server
> import the database and put its files wherever the current 'data'
> directory is?
> The source server was originally running 7.0, which we upgraded in
> place to 2000. All the sql server directories, like data, repldata,
> ftdata, logs, etc are in our d:\database directory.
> On the destination server, it is a fresh install of SQL Server 2000. I
> tried to specify "d:\database" as our data directory when installing,
> but it insists on creating an MSSQL directory, and then putting all of
> its directories within that directory (argghhh!!).
> Any ideas?
> Thanks,
> Thomassql
Friday, March 23, 2012
Restoring SQL2000 DB to SQLExpress on different machine
Really having a problem...
I have a full backup in a file where RESTORE FILELISTONLY
FROM DISK = N'C:\Program Files\Microsoft SQL
Server\MSSQL. 1\MSSQL\Backup\DeviceTest_db_20060806020
0' shows the results...
DeviceTest E:\Microsoft SQL
Server\MSSQL\Data\DeviceTest. mdf D PRIMARY 27590656 35184372080640 1
0 0 000
00000-0000-0000-0000- 000000000000 0 0 0 512 1 NULL 1740000000
004500003 153EE
4C3-3307-4FCE-9B9B-3B79B81705D8 0 1
DeviceTest_log E:\Microsoft SQL
Server\MSSQL\Data\DeviceTest_log. ldf L NULL 13238272 35184372080640 2 0 0
00
000000-0000-0000-0000- 000000000000 0 0 0 512 0 NULL 0 00000000
-0000-0000-000
0-000000000000 0 0
When I try to restore this database on another machine with the following...
RESTORE DATABASE [DeviceTest]
FILE = N'DeviceTest'
FROM DISK = N'C:\Program Files\Microsoft SQL
Server\MSSQL. 1\MSSQL\Backup\DeviceTest_db_20060806020
0'
WITH FILE = 1, NORECOVERY,
MOVE N'DeviceTest' TO N'c:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\DeviceTest.mdf',
STATS = 10
it seems to work with the following output...
Processed 2632 pages for database 'DeviceTest', file 'DeviceTest' on file 1.
100 percent processed.
RESTORE DATABASE ... FILE=<name> successfully processed 2632 pages in 2.447
seconds (8.811 MB/sec).
BUT the newly restored database STAYS in the restoring state and I can't do
a thing with it! What is the secret that I'm missing? I've searched Books on
Line, this forum, product support, Q314546, and nothing seems to help.
Wishing for help...
Dave Gardner"DGardner" <DGardner@.discussions.microsoft.com> wrote in message
news:0916F23F-29B7-4460-A761-235D7A67A9C5@.microsoft.com...
> Really having a problem...
> I have a full backup in a file where RESTORE FILELISTONLY
> FROM DISK = N'C:\Program Files\Microsoft SQL
> Server\MSSQL. 1\MSSQL\Backup\DeviceTest_db_20060806020
0' shows the
> results...
> DeviceTest E:\Microsoft SQL
> Server\MSSQL\Data\DeviceTest.mdf D PRIMARY 27590656 35184372080640 1 0 0
> 00000000-0000-0000-0000-000000000000 0 0 0 512 1 NULL 1740000000004500003
> 153EE4C3-3307-4FCE-9B9B-3B79B81705D8 0 1
> DeviceTest_log E:\Microsoft SQL
> Server\MSSQL\Data\DeviceTest_log.ldf L NULL 13238272 35184372080640 2 0 0
> 00000000-0000-0000-0000-000000000000 0 0 0 512 0 NULL 0
> 00000000-0000-0000-0000-000000000000 0 0
> When I try to restore this database on another machine with the
> following...
> RESTORE DATABASE [DeviceTest]
> FILE = N'DeviceTest'
> FROM DISK = N'C:\Program Files\Microsoft SQL
> Server\MSSQL. 1\MSSQL\Backup\DeviceTest_db_20060806020
0'
> WITH FILE = 1, NORECOVERY,
> MOVE N'DeviceTest' TO N'c:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Data\DeviceTest.mdf',
> STATS = 10
> it seems to work with the following output...
> Processed 2632 pages for database 'DeviceTest', file 'DeviceTest' on file
> 1.
> 100 percent processed.
> RESTORE DATABASE ... FILE=<name> successfully processed 2632 pages in
> 2.447
> seconds (8.811 MB/sec).
> BUT the newly restored database STAYS in the restoring state and I can't
> do
> a thing with it! What is the secret that I'm missing? I've searched Books
> on
> Line, this forum, product support, Q314546, and nothing seems to help.
>
You left the database in restoring mode by not recovering it.
from BOL RESTORE (Transact-SQL)
Comparison of RECOVERY and NORECOVERY
Roll back is controlled by the RESTORE statement through the [ RECOVERY
|
NORECOVERY ] options:
NORECOVERY specifies that roll back not occur. This allows roll forward to
continue with the next statement in the sequence.
In this case, the restore sequence can restore other backups and roll them
forward.
RECOVERY (the default) indicates that roll back should be performed after
roll forward is completed for the current backup.
Recovering the database requires that the entire set of data being restored
(the roll forward set) is consistent with the database. If the roll forward
set has not been rolled forward far enough to be consistent with the
database and RECOVERY is specified, the Database Engine issues an error.
David|||"David Browne" wrote:
> You left the database in restoring mode by not recovering it.
> from BOL RESTORE (Transact-SQL)
> Comparison of RECOVERY and NORECOVERY
> Roll back is controlled by the RESTORE statement through the [ RECOVER
Y |
> NORECOVERY ] options:
> NORECOVERY specifies that roll back not occur. This allows roll forward to
> continue with the next statement in the sequence.
> In this case, the restore sequence can restore other backups and roll them
> forward.
>
> RECOVERY (the default) indicates that roll back should be performed after
> roll forward is completed for the current backup.
> Recovering the database requires that the entire set of data being restore
d
> (the roll forward set) is consistent with the database. If the roll forwar
d
> set has not been rolled forward far enough to be consistent with the
> database and RECOVERY is specified, the Database Engine issues an error.
> David
>
>
David,
Sorry, but that's not the problem. Been there, done that. Using RECOVERY or
NORECOVERY still keeps the database in RECOVERING status with no hope of
doing anything.
--Dave G.
I have a full backup in a file where RESTORE FILELISTONLY
FROM DISK = N'C:\Program Files\Microsoft SQL
Server\MSSQL. 1\MSSQL\Backup\DeviceTest_db_20060806020
0' shows the results...
DeviceTest E:\Microsoft SQL
Server\MSSQL\Data\DeviceTest. mdf D PRIMARY 27590656 35184372080640 1
0 0 000
00000-0000-0000-0000- 000000000000 0 0 0 512 1 NULL 1740000000
004500003 153EE
4C3-3307-4FCE-9B9B-3B79B81705D8 0 1
DeviceTest_log E:\Microsoft SQL
Server\MSSQL\Data\DeviceTest_log. ldf L NULL 13238272 35184372080640 2 0 0
00
000000-0000-0000-0000- 000000000000 0 0 0 512 0 NULL 0 00000000
-0000-0000-000
0-000000000000 0 0
When I try to restore this database on another machine with the following...
RESTORE DATABASE [DeviceTest]
FILE = N'DeviceTest'
FROM DISK = N'C:\Program Files\Microsoft SQL
Server\MSSQL. 1\MSSQL\Backup\DeviceTest_db_20060806020
0'
WITH FILE = 1, NORECOVERY,
MOVE N'DeviceTest' TO N'c:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\DeviceTest.mdf',
STATS = 10
it seems to work with the following output...
Processed 2632 pages for database 'DeviceTest', file 'DeviceTest' on file 1.
100 percent processed.
RESTORE DATABASE ... FILE=<name> successfully processed 2632 pages in 2.447
seconds (8.811 MB/sec).
BUT the newly restored database STAYS in the restoring state and I can't do
a thing with it! What is the secret that I'm missing? I've searched Books on
Line, this forum, product support, Q314546, and nothing seems to help.
Wishing for help...
Dave Gardner"DGardner" <DGardner@.discussions.microsoft.com> wrote in message
news:0916F23F-29B7-4460-A761-235D7A67A9C5@.microsoft.com...
> Really having a problem...
> I have a full backup in a file where RESTORE FILELISTONLY
> FROM DISK = N'C:\Program Files\Microsoft SQL
> Server\MSSQL. 1\MSSQL\Backup\DeviceTest_db_20060806020
0' shows the
> results...
> DeviceTest E:\Microsoft SQL
> Server\MSSQL\Data\DeviceTest.mdf D PRIMARY 27590656 35184372080640 1 0 0
> 00000000-0000-0000-0000-000000000000 0 0 0 512 1 NULL 1740000000004500003
> 153EE4C3-3307-4FCE-9B9B-3B79B81705D8 0 1
> DeviceTest_log E:\Microsoft SQL
> Server\MSSQL\Data\DeviceTest_log.ldf L NULL 13238272 35184372080640 2 0 0
> 00000000-0000-0000-0000-000000000000 0 0 0 512 0 NULL 0
> 00000000-0000-0000-0000-000000000000 0 0
> When I try to restore this database on another machine with the
> following...
> RESTORE DATABASE [DeviceTest]
> FILE = N'DeviceTest'
> FROM DISK = N'C:\Program Files\Microsoft SQL
> Server\MSSQL. 1\MSSQL\Backup\DeviceTest_db_20060806020
0'
> WITH FILE = 1, NORECOVERY,
> MOVE N'DeviceTest' TO N'c:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Data\DeviceTest.mdf',
> STATS = 10
> it seems to work with the following output...
> Processed 2632 pages for database 'DeviceTest', file 'DeviceTest' on file
> 1.
> 100 percent processed.
> RESTORE DATABASE ... FILE=<name> successfully processed 2632 pages in
> 2.447
> seconds (8.811 MB/sec).
> BUT the newly restored database STAYS in the restoring state and I can't
> do
> a thing with it! What is the secret that I'm missing? I've searched Books
> on
> Line, this forum, product support, Q314546, and nothing seems to help.
>
You left the database in restoring mode by not recovering it.
from BOL RESTORE (Transact-SQL)
Comparison of RECOVERY and NORECOVERY
Roll back is controlled by the RESTORE statement through the [ RECOVERY
|
NORECOVERY ] options:
NORECOVERY specifies that roll back not occur. This allows roll forward to
continue with the next statement in the sequence.
In this case, the restore sequence can restore other backups and roll them
forward.
RECOVERY (the default) indicates that roll back should be performed after
roll forward is completed for the current backup.
Recovering the database requires that the entire set of data being restored
(the roll forward set) is consistent with the database. If the roll forward
set has not been rolled forward far enough to be consistent with the
database and RECOVERY is specified, the Database Engine issues an error.
David|||"David Browne" wrote:
> You left the database in restoring mode by not recovering it.
> from BOL RESTORE (Transact-SQL)
> Comparison of RECOVERY and NORECOVERY
> Roll back is controlled by the RESTORE statement through the [ RECOVER
Y |
> NORECOVERY ] options:
> NORECOVERY specifies that roll back not occur. This allows roll forward to
> continue with the next statement in the sequence.
> In this case, the restore sequence can restore other backups and roll them
> forward.
>
> RECOVERY (the default) indicates that roll back should be performed after
> roll forward is completed for the current backup.
> Recovering the database requires that the entire set of data being restore
d
> (the roll forward set) is consistent with the database. If the roll forwar
d
> set has not been rolled forward far enough to be consistent with the
> database and RECOVERY is specified, the Database Engine issues an error.
> David
>
>
David,
Sorry, but that's not the problem. Been there, done that. Using RECOVERY or
NORECOVERY still keeps the database in RECOVERING status with no hope of
doing anything.
--Dave G.
Restoring SQL2000 DB to SQLExpress on different machine
Really having a problem...
I have a full backup in a file where RESTORE FILELISTONLY
FROM DISK = N'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Backup\DeviceTest_db_200608060200' shows the results...
DeviceTest E:\Microsoft SQL
Server\MSSQL\Data\DeviceTest.mdf D PRIMARY 27590656 35184372080640 1 0 0 00000000-0000-0000-0000-000000000000 0 0 0 512 1 NULL 1740000000004500003 153EE4C3-3307-4FCE-9B9B-3B79B81705D8 0 1
DeviceTest_log E:\Microsoft SQL
Server\MSSQL\Data\DeviceTest_log.ldf L NULL 13238272 35184372080640 2 0 0 00000000-0000-0000-0000-000000000000 0 0 0 512 0 NULL 0 00000000-0000-0000-0000-000000000000 0 0
When I try to restore this database on another machine with the following...
RESTORE DATABASE [DeviceTest]
FILE = N'DeviceTest'
FROM DISK = N'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Backup\DeviceTest_db_200608060200'
WITH FILE = 1, NORECOVERY,
MOVE N'DeviceTest' TO N'c:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\DeviceTest.mdf',
STATS = 10
it seems to work with the following output...
Processed 2632 pages for database 'DeviceTest', file 'DeviceTest' on file 1.
100 percent processed.
RESTORE DATABASE ... FILE=<name> successfully processed 2632 pages in 2.447
seconds (8.811 MB/sec).
BUT the newly restored database STAYS in the restoring state and I can't do
a thing with it! What is the secret that I'm missing? I've searched Books on
Line, this forum, product support, Q314546, and nothing seems to help.
Wishing for help...
Dave Gardner"DGardner" <DGardner@.discussions.microsoft.com> wrote in message
news:0916F23F-29B7-4460-A761-235D7A67A9C5@.microsoft.com...
> Really having a problem...
> I have a full backup in a file where RESTORE FILELISTONLY
> FROM DISK = N'C:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Backup\DeviceTest_db_200608060200' shows the
> results...
> DeviceTest E:\Microsoft SQL
> Server\MSSQL\Data\DeviceTest.mdf D PRIMARY 27590656 35184372080640 1 0 0
> 00000000-0000-0000-0000-000000000000 0 0 0 512 1 NULL 1740000000004500003
> 153EE4C3-3307-4FCE-9B9B-3B79B81705D8 0 1
> DeviceTest_log E:\Microsoft SQL
> Server\MSSQL\Data\DeviceTest_log.ldf L NULL 13238272 35184372080640 2 0 0
> 00000000-0000-0000-0000-000000000000 0 0 0 512 0 NULL 0
> 00000000-0000-0000-0000-000000000000 0 0
> When I try to restore this database on another machine with the
> following...
> RESTORE DATABASE [DeviceTest]
> FILE = N'DeviceTest'
> FROM DISK = N'C:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Backup\DeviceTest_db_200608060200'
> WITH FILE = 1, NORECOVERY,
> MOVE N'DeviceTest' TO N'c:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Data\DeviceTest.mdf',
> STATS = 10
> it seems to work with the following output...
> Processed 2632 pages for database 'DeviceTest', file 'DeviceTest' on file
> 1.
> 100 percent processed.
> RESTORE DATABASE ... FILE=<name> successfully processed 2632 pages in
> 2.447
> seconds (8.811 MB/sec).
> BUT the newly restored database STAYS in the restoring state and I can't
> do
> a thing with it! What is the secret that I'm missing? I've searched Books
> on
> Line, this forum, product support, Q314546, and nothing seems to help.
>
You left the database in restoring mode by not recovering it.
from BOL RESTORE (Transact-SQL)
Comparison of RECOVERY and NORECOVERY
Roll back is controlled by the RESTORE statement through the [ RECOVERY |
NORECOVERY ] options:
NORECOVERY specifies that roll back not occur. This allows roll forward to
continue with the next statement in the sequence.
In this case, the restore sequence can restore other backups and roll them
forward.
RECOVERY (the default) indicates that roll back should be performed after
roll forward is completed for the current backup.
Recovering the database requires that the entire set of data being restored
(the roll forward set) is consistent with the database. If the roll forward
set has not been rolled forward far enough to be consistent with the
database and RECOVERY is specified, the Database Engine issues an error.
David|||"David Browne" wrote:
> You left the database in restoring mode by not recovering it.
> from BOL RESTORE (Transact-SQL)
> Comparison of RECOVERY and NORECOVERY
> Roll back is controlled by the RESTORE statement through the [ RECOVERY |
> NORECOVERY ] options:
> NORECOVERY specifies that roll back not occur. This allows roll forward to
> continue with the next statement in the sequence.
> In this case, the restore sequence can restore other backups and roll them
> forward.
>
> RECOVERY (the default) indicates that roll back should be performed after
> roll forward is completed for the current backup.
> Recovering the database requires that the entire set of data being restored
> (the roll forward set) is consistent with the database. If the roll forward
> set has not been rolled forward far enough to be consistent with the
> database and RECOVERY is specified, the Database Engine issues an error.
> David
>
>
David,
Sorry, but that's not the problem. Been there, done that. Using RECOVERY or
NORECOVERY still keeps the database in RECOVERING status with no hope of
doing anything.
--Dave G.sql
I have a full backup in a file where RESTORE FILELISTONLY
FROM DISK = N'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Backup\DeviceTest_db_200608060200' shows the results...
DeviceTest E:\Microsoft SQL
Server\MSSQL\Data\DeviceTest.mdf D PRIMARY 27590656 35184372080640 1 0 0 00000000-0000-0000-0000-000000000000 0 0 0 512 1 NULL 1740000000004500003 153EE4C3-3307-4FCE-9B9B-3B79B81705D8 0 1
DeviceTest_log E:\Microsoft SQL
Server\MSSQL\Data\DeviceTest_log.ldf L NULL 13238272 35184372080640 2 0 0 00000000-0000-0000-0000-000000000000 0 0 0 512 0 NULL 0 00000000-0000-0000-0000-000000000000 0 0
When I try to restore this database on another machine with the following...
RESTORE DATABASE [DeviceTest]
FILE = N'DeviceTest'
FROM DISK = N'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Backup\DeviceTest_db_200608060200'
WITH FILE = 1, NORECOVERY,
MOVE N'DeviceTest' TO N'c:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\DeviceTest.mdf',
STATS = 10
it seems to work with the following output...
Processed 2632 pages for database 'DeviceTest', file 'DeviceTest' on file 1.
100 percent processed.
RESTORE DATABASE ... FILE=<name> successfully processed 2632 pages in 2.447
seconds (8.811 MB/sec).
BUT the newly restored database STAYS in the restoring state and I can't do
a thing with it! What is the secret that I'm missing? I've searched Books on
Line, this forum, product support, Q314546, and nothing seems to help.
Wishing for help...
Dave Gardner"DGardner" <DGardner@.discussions.microsoft.com> wrote in message
news:0916F23F-29B7-4460-A761-235D7A67A9C5@.microsoft.com...
> Really having a problem...
> I have a full backup in a file where RESTORE FILELISTONLY
> FROM DISK = N'C:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Backup\DeviceTest_db_200608060200' shows the
> results...
> DeviceTest E:\Microsoft SQL
> Server\MSSQL\Data\DeviceTest.mdf D PRIMARY 27590656 35184372080640 1 0 0
> 00000000-0000-0000-0000-000000000000 0 0 0 512 1 NULL 1740000000004500003
> 153EE4C3-3307-4FCE-9B9B-3B79B81705D8 0 1
> DeviceTest_log E:\Microsoft SQL
> Server\MSSQL\Data\DeviceTest_log.ldf L NULL 13238272 35184372080640 2 0 0
> 00000000-0000-0000-0000-000000000000 0 0 0 512 0 NULL 0
> 00000000-0000-0000-0000-000000000000 0 0
> When I try to restore this database on another machine with the
> following...
> RESTORE DATABASE [DeviceTest]
> FILE = N'DeviceTest'
> FROM DISK = N'C:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Backup\DeviceTest_db_200608060200'
> WITH FILE = 1, NORECOVERY,
> MOVE N'DeviceTest' TO N'c:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Data\DeviceTest.mdf',
> STATS = 10
> it seems to work with the following output...
> Processed 2632 pages for database 'DeviceTest', file 'DeviceTest' on file
> 1.
> 100 percent processed.
> RESTORE DATABASE ... FILE=<name> successfully processed 2632 pages in
> 2.447
> seconds (8.811 MB/sec).
> BUT the newly restored database STAYS in the restoring state and I can't
> do
> a thing with it! What is the secret that I'm missing? I've searched Books
> on
> Line, this forum, product support, Q314546, and nothing seems to help.
>
You left the database in restoring mode by not recovering it.
from BOL RESTORE (Transact-SQL)
Comparison of RECOVERY and NORECOVERY
Roll back is controlled by the RESTORE statement through the [ RECOVERY |
NORECOVERY ] options:
NORECOVERY specifies that roll back not occur. This allows roll forward to
continue with the next statement in the sequence.
In this case, the restore sequence can restore other backups and roll them
forward.
RECOVERY (the default) indicates that roll back should be performed after
roll forward is completed for the current backup.
Recovering the database requires that the entire set of data being restored
(the roll forward set) is consistent with the database. If the roll forward
set has not been rolled forward far enough to be consistent with the
database and RECOVERY is specified, the Database Engine issues an error.
David|||"David Browne" wrote:
> You left the database in restoring mode by not recovering it.
> from BOL RESTORE (Transact-SQL)
> Comparison of RECOVERY and NORECOVERY
> Roll back is controlled by the RESTORE statement through the [ RECOVERY |
> NORECOVERY ] options:
> NORECOVERY specifies that roll back not occur. This allows roll forward to
> continue with the next statement in the sequence.
> In this case, the restore sequence can restore other backups and roll them
> forward.
>
> RECOVERY (the default) indicates that roll back should be performed after
> roll forward is completed for the current backup.
> Recovering the database requires that the entire set of data being restored
> (the roll forward set) is consistent with the database. If the roll forward
> set has not been rolled forward far enough to be consistent with the
> database and RECOVERY is specified, the Database Engine issues an error.
> David
>
>
David,
Sorry, but that's not the problem. Been there, done that. Using RECOVERY or
NORECOVERY still keeps the database in RECOVERING status with no hope of
doing anything.
--Dave G.sql
Monday, March 12, 2012
Restoring Master on another machine...
We set up a test box and having been trying to move an entire instance
from our production server to this one. I have moved all my created
dbs using the RESTORE WITH MOVE. Now I am trying to move the Master,
Model, and msdb. This is where I am having trouble. On the production
box the dbs are stored on D:\mssql$instancename\data. On the test
server the data is stored in C:\Program Files\Microsoft SQL
Server\mssql$instancename\data. Also the server names are different.
So I finally got the Master from production to restore over the test's
copy. Now the instance will not start. I was trying to use the
following code in cmd line to connect to the instace so I could then
copy model and msdb:
from our production server to this one. I have moved all my created
dbs using the RESTORE WITH MOVE. Now I am trying to move the Master,
Model, and msdb. This is where I am having trouble. On the production
box the dbs are stored on D:\mssql$instancename\data. On the test
server the data is stored in C:\Program Files\Microsoft SQL
Server\mssql$instancename\data. Also the server names are different.
So I finally got the Master from production to restore over the test's
copy. Now the instance will not start. I was trying to use the
following code in cmd line to connect to the instace so I could then
copy model and msdb:
sqlservr -c -f -T3608 -T4022 -sINSTANCENAME
When this is trying to connect I read it is failing and saying that the
MDF and LDF may be corrupt or not there. The problem is it is now
trying to look in D:\mssql$instancename\data instead of C:\Program
Files\Microsoft SQL Server\mssql$instancename\data. Any ideas on how I
can change this, or if I have to reinstall the entire instace, not run
into this again?(murrayb3024@.gmail.com) writes:
> We set up a test box and having been trying to move an entire instance
> from our production server to this one. I have moved all my created
> dbs using the RESTORE WITH MOVE. Now I am trying to move the Master,
> Model, and msdb. This is where I am having trouble. On the production
> box the dbs are stored on D:\mssql$instancename\data. On the test
> server the data is stored in C:\Program Files\Microsoft SQL
> Server\mssql$instancename\data. Also the server names are different.
> So I finally got the Master from production to restore over the test's
> copy. Now the instance will not start. I was trying to use the
> following code in cmd line to connect to the instace so I could then
> copy model and msdb:
> sqlservr -c -f -T3608 -T4022 -sINSTANCENAME
> When this is trying to connect I read it is failing and saying that the
> MDF and LDF may be corrupt or not there. The problem is it is now
> trying to look in D:\mssql$instancename\data instead of C:\Program
> Files\Microsoft SQL Server\mssql$instancename\data. Any ideas on how I
> can change this, or if I have to reinstall the entire instace, not run
> into this again?
Before you set off, did you look at
http://support.microsoft.com/defaul...b;EN-US;224071?
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||The article is really useful
http://support.microsoft.com/defaul...kb;en-us;224071
Also attach & detach should save your lot of time.
Thanks
Ajay Rengunthwar
MCDBA
Friday, March 9, 2012
Restoring Master
We recently had a server go down completely and had to rebuild. One thing
that is different now on machine after restoring the databases is the
physical location. All databases are up and working fine, but after looking
at Master..sysdatabases I see some references that are not correct. I am not
sure if I should be concerned, but I just don't like seeing that the wrong
data.
If we run the following we see some database files that point to the
incorrect path, but if you look at the DB themselves it says the correct
place. My main concern is the master is one of them. Is there anything I
should do to correct this, what is the result of getting a wrong path for
FileName?
select *
from sysdatabasesBy "wrong" do you mean that the entries in master.dbo.sysdatabases are not
the same as in <your DB>.dbo.sysfiles? You may be able to correct things by
detaching and reattaching the databases.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Andy" <andyjax@.newsgroup.nospam> wrote in message
news:%232Nog4FuFHA.904@.tk2msftngp13.phx.gbl...
We recently had a server go down completely and had to rebuild. One thing
that is different now on machine after restoring the databases is the
physical location. All databases are up and working fine, but after looking
at Master..sysdatabases I see some references that are not correct. I am not
sure if I should be concerned, but I just don't like seeing that the wrong
data.
If we run the following we see some database files that point to the
incorrect path, but if you look at the DB themselves it says the correct
place. My main concern is the master is one of them. Is there anything I
should do to correct this, what is the result of getting a wrong path for
FileName?
select *
from sysdatabases|||Hi,
Before doing any thing just open the SQL Server Error log and look into the
entries during startup. If every thing
is good then just ignore. Incase if you have errors then detach the
databases which has got issues and then attach them back to
sql server.
Thanks
Hari
SQL Server MVP
"Andy" <andyjax@.newsgroup.nospam> wrote in message
news:%232Nog4FuFHA.904@.tk2msftngp13.phx.gbl...
> We recently had a server go down completely and had to rebuild. One thing
> that is different now on machine after restoring the databases is the
> physical location. All databases are up and working fine, but after
> looking at Master..sysdatabases I see some references that are not
> correct. I am not sure if I should be concerned, but I just don't like
> seeing that the wrong data.
> If we run the following we see some database files that point to the
> incorrect path, but if you look at the DB themselves it says the correct
> place. My main concern is the master is one of them. Is there anything I
> should do to correct this, what is the result of getting a wrong path for
> FileName?
> select *
> from sysdatabases
>
>|||Yes your description is what I mean. The main question is that MASTER is one
of them, so I do not believe I can detach it, since it needs itself to know
the information.
Here is what I have:
D:\MSSQL\data\master.mdf - new location, we had forgotten the SQL server
was installed incorrectly initially
D:\MSSQL\MSSQL\data\master.mdf - old path that is showing up now in
sysdatabases filename and in sysdevices.
Sysdevices shows wrong path for master and model, tempdb is fine because we
have not moved it.
Everything seems to be working just fine, but thought we would ask in case
something were to come up in the future to bite us.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eKJERLGuFHA.1472@.TK2MSFTNGP15.phx.gbl...
> By "wrong" do you mean that the entries in master.dbo.sysdatabases are not
> the same as in <your DB>.dbo.sysfiles? You may be able to correct things
> by
> detaching and reattaching the databases.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Andy" <andyjax@.newsgroup.nospam> wrote in message
> news:%232Nog4FuFHA.904@.tk2msftngp13.phx.gbl...
> We recently had a server go down completely and had to rebuild. One thing
> that is different now on machine after restoring the databases is the
> physical location. All databases are up and working fine, but after
> looking
> at Master..sysdatabases I see some references that are not correct. I am
> not
> sure if I should be concerned, but I just don't like seeing that the wrong
> data.
> If we run the following we see some database files that point to the
> incorrect path, but if you look at the DB themselves it says the correct
> place. My main concern is the master is one of them. Is there anything I
> should do to correct this, what is the result of getting a wrong path for
> FileName?
> select *
> from sysdatabases
>
>|||Let me clarify something more here.
We had to restore a system completely. So after installing SQL and restoring
master SQL would not start up. The reason was due to the looking for system
databases that did not exist at where MASTER thought they were. We simply
created a folder structure like the old structure and placed the system
databases there and SQL restarted. We then went through and performed a
restore with move to get the paths updated to new location.
"Andy" <andyjax@.newsgroup.nospam> wrote in message
news:eKmNq0HuFHA.4032@.TK2MSFTNGP15.phx.gbl...
> Yes your description is what I mean. The main question is that MASTER is
> one of them, so I do not believe I can detach it, since it needs itself to
> know the information.
> Here is what I have:
> D:\MSSQL\data\master.mdf - new location, we had forgotten the SQL
> server was installed incorrectly initially
> D:\MSSQL\MSSQL\data\master.mdf - old path that is showing up now in
> sysdatabases filename and in sysdevices.
>
> Sysdevices shows wrong path for master and model, tempdb is fine because
> we have not moved it.
>
> Everything seems to be working just fine, but thought we would ask in case
> something were to come up in the future to bite us.
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:eKJERLGuFHA.1472@.TK2MSFTNGP15.phx.gbl...
>|||Since you restored the old master DB, then this appears to be what you get.
It does not update the system database information. I wouldn't worry about
it. You could do surgery on the system tables - and accept the risks that
go with that.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Andy" <andyjax@.newsgroup.nospam> wrote in message
news:eKmNq0HuFHA.4032@.TK2MSFTNGP15.phx.gbl...
Yes your description is what I mean. The main question is that MASTER is one
of them, so I do not believe I can detach it, since it needs itself to know
the information.
Here is what I have:
D:\MSSQL\data\master.mdf - new location, we had forgotten the SQL server
was installed incorrectly initially
D:\MSSQL\MSSQL\data\master.mdf - old path that is showing up now in
sysdatabases filename and in sysdevices.
Sysdevices shows wrong path for master and model, tempdb is fine because we
have not moved it.
Everything seems to be working just fine, but thought we would ask in case
something were to come up in the future to bite us.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eKJERLGuFHA.1472@.TK2MSFTNGP15.phx.gbl...
> By "wrong" do you mean that the entries in master.dbo.sysdatabases are not
> the same as in <your DB>.dbo.sysfiles? You may be able to correct things
> by
> detaching and reattaching the databases.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Andy" <andyjax@.newsgroup.nospam> wrote in message
> news:%232Nog4FuFHA.904@.tk2msftngp13.phx.gbl...
> We recently had a server go down completely and had to rebuild. One thing
> that is different now on machine after restoring the databases is the
> physical location. All databases are up and working fine, but after
> looking
> at Master..sysdatabases I see some references that are not correct. I am
> not
> sure if I should be concerned, but I just don't like seeing that the wrong
> data.
> If we run the following we see some database files that point to the
> incorrect path, but if you look at the DB themselves it says the correct
> place. My main concern is the master is one of them. Is there anything I
> should do to correct this, what is the result of getting a wrong path for
> FileName?
> select *
> from sysdatabases
>
>
that is different now on machine after restoring the databases is the
physical location. All databases are up and working fine, but after looking
at Master..sysdatabases I see some references that are not correct. I am not
sure if I should be concerned, but I just don't like seeing that the wrong
data.
If we run the following we see some database files that point to the
incorrect path, but if you look at the DB themselves it says the correct
place. My main concern is the master is one of them. Is there anything I
should do to correct this, what is the result of getting a wrong path for
FileName?
select *
from sysdatabasesBy "wrong" do you mean that the entries in master.dbo.sysdatabases are not
the same as in <your DB>.dbo.sysfiles? You may be able to correct things by
detaching and reattaching the databases.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Andy" <andyjax@.newsgroup.nospam> wrote in message
news:%232Nog4FuFHA.904@.tk2msftngp13.phx.gbl...
We recently had a server go down completely and had to rebuild. One thing
that is different now on machine after restoring the databases is the
physical location. All databases are up and working fine, but after looking
at Master..sysdatabases I see some references that are not correct. I am not
sure if I should be concerned, but I just don't like seeing that the wrong
data.
If we run the following we see some database files that point to the
incorrect path, but if you look at the DB themselves it says the correct
place. My main concern is the master is one of them. Is there anything I
should do to correct this, what is the result of getting a wrong path for
FileName?
select *
from sysdatabases|||Hi,
Before doing any thing just open the SQL Server Error log and look into the
entries during startup. If every thing
is good then just ignore. Incase if you have errors then detach the
databases which has got issues and then attach them back to
sql server.
Thanks
Hari
SQL Server MVP
"Andy" <andyjax@.newsgroup.nospam> wrote in message
news:%232Nog4FuFHA.904@.tk2msftngp13.phx.gbl...
> We recently had a server go down completely and had to rebuild. One thing
> that is different now on machine after restoring the databases is the
> physical location. All databases are up and working fine, but after
> looking at Master..sysdatabases I see some references that are not
> correct. I am not sure if I should be concerned, but I just don't like
> seeing that the wrong data.
> If we run the following we see some database files that point to the
> incorrect path, but if you look at the DB themselves it says the correct
> place. My main concern is the master is one of them. Is there anything I
> should do to correct this, what is the result of getting a wrong path for
> FileName?
> select *
> from sysdatabases
>
>|||Yes your description is what I mean. The main question is that MASTER is one
of them, so I do not believe I can detach it, since it needs itself to know
the information.
Here is what I have:
D:\MSSQL\data\master.mdf - new location, we had forgotten the SQL server
was installed incorrectly initially
D:\MSSQL\MSSQL\data\master.mdf - old path that is showing up now in
sysdatabases filename and in sysdevices.
Sysdevices shows wrong path for master and model, tempdb is fine because we
have not moved it.
Everything seems to be working just fine, but thought we would ask in case
something were to come up in the future to bite us.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eKJERLGuFHA.1472@.TK2MSFTNGP15.phx.gbl...
> By "wrong" do you mean that the entries in master.dbo.sysdatabases are not
> the same as in <your DB>.dbo.sysfiles? You may be able to correct things
> by
> detaching and reattaching the databases.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Andy" <andyjax@.newsgroup.nospam> wrote in message
> news:%232Nog4FuFHA.904@.tk2msftngp13.phx.gbl...
> We recently had a server go down completely and had to rebuild. One thing
> that is different now on machine after restoring the databases is the
> physical location. All databases are up and working fine, but after
> looking
> at Master..sysdatabases I see some references that are not correct. I am
> not
> sure if I should be concerned, but I just don't like seeing that the wrong
> data.
> If we run the following we see some database files that point to the
> incorrect path, but if you look at the DB themselves it says the correct
> place. My main concern is the master is one of them. Is there anything I
> should do to correct this, what is the result of getting a wrong path for
> FileName?
> select *
> from sysdatabases
>
>|||Let me clarify something more here.
We had to restore a system completely. So after installing SQL and restoring
master SQL would not start up. The reason was due to the looking for system
databases that did not exist at where MASTER thought they were. We simply
created a folder structure like the old structure and placed the system
databases there and SQL restarted. We then went through and performed a
restore with move to get the paths updated to new location.
"Andy" <andyjax@.newsgroup.nospam> wrote in message
news:eKmNq0HuFHA.4032@.TK2MSFTNGP15.phx.gbl...
> Yes your description is what I mean. The main question is that MASTER is
> one of them, so I do not believe I can detach it, since it needs itself to
> know the information.
> Here is what I have:
> D:\MSSQL\data\master.mdf - new location, we had forgotten the SQL
> server was installed incorrectly initially
> D:\MSSQL\MSSQL\data\master.mdf - old path that is showing up now in
> sysdatabases filename and in sysdevices.
>
> Sysdevices shows wrong path for master and model, tempdb is fine because
> we have not moved it.
>
> Everything seems to be working just fine, but thought we would ask in case
> something were to come up in the future to bite us.
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:eKJERLGuFHA.1472@.TK2MSFTNGP15.phx.gbl...
>|||Since you restored the old master DB, then this appears to be what you get.
It does not update the system database information. I wouldn't worry about
it. You could do surgery on the system tables - and accept the risks that
go with that.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Andy" <andyjax@.newsgroup.nospam> wrote in message
news:eKmNq0HuFHA.4032@.TK2MSFTNGP15.phx.gbl...
Yes your description is what I mean. The main question is that MASTER is one
of them, so I do not believe I can detach it, since it needs itself to know
the information.
Here is what I have:
D:\MSSQL\data\master.mdf - new location, we had forgotten the SQL server
was installed incorrectly initially
D:\MSSQL\MSSQL\data\master.mdf - old path that is showing up now in
sysdatabases filename and in sysdevices.
Sysdevices shows wrong path for master and model, tempdb is fine because we
have not moved it.
Everything seems to be working just fine, but thought we would ask in case
something were to come up in the future to bite us.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eKJERLGuFHA.1472@.TK2MSFTNGP15.phx.gbl...
> By "wrong" do you mean that the entries in master.dbo.sysdatabases are not
> the same as in <your DB>.dbo.sysfiles? You may be able to correct things
> by
> detaching and reattaching the databases.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Andy" <andyjax@.newsgroup.nospam> wrote in message
> news:%232Nog4FuFHA.904@.tk2msftngp13.phx.gbl...
> We recently had a server go down completely and had to rebuild. One thing
> that is different now on machine after restoring the databases is the
> physical location. All databases are up and working fine, but after
> looking
> at Master..sysdatabases I see some references that are not correct. I am
> not
> sure if I should be concerned, but I just don't like seeing that the wrong
> data.
> If we run the following we see some database files that point to the
> incorrect path, but if you look at the DB themselves it says the correct
> place. My main concern is the master is one of them. Is there anything I
> should do to correct this, what is the result of getting a wrong path for
> FileName?
> select *
> from sysdatabases
>
>
Restoring Master
We recently had a server go down completely and had to rebuild. One thing
that is different now on machine after restoring the databases is the
physical location. All databases are up and working fine, but after looking
at Master..sysdatabases I see some references that are not correct. I am not
sure if I should be concerned, but I just don't like seeing that the wrong
data.
If we run the following we see some database files that point to the
incorrect path, but if you look at the DB themselves it says the correct
place. My main concern is the master is one of them. Is there anything I
should do to correct this, what is the result of getting a wrong path for
FileName?
select *
from sysdatabases
By "wrong" do you mean that the entries in master.dbo.sysdatabases are not
the same as in <your DB>.dbo.sysfiles? You may be able to correct things by
detaching and reattaching the databases.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Andy" <andyjax@.newsgroup.nospam> wrote in message
news:%232Nog4FuFHA.904@.tk2msftngp13.phx.gbl...
We recently had a server go down completely and had to rebuild. One thing
that is different now on machine after restoring the databases is the
physical location. All databases are up and working fine, but after looking
at Master..sysdatabases I see some references that are not correct. I am not
sure if I should be concerned, but I just don't like seeing that the wrong
data.
If we run the following we see some database files that point to the
incorrect path, but if you look at the DB themselves it says the correct
place. My main concern is the master is one of them. Is there anything I
should do to correct this, what is the result of getting a wrong path for
FileName?
select *
from sysdatabases
|||Hi,
Before doing any thing just open the SQL Server Error log and look into the
entries during startup. If every thing
is good then just ignore. Incase if you have errors then detach the
databases which has got issues and then attach them back to
sql server.
Thanks
Hari
SQL Server MVP
"Andy" <andyjax@.newsgroup.nospam> wrote in message
news:%232Nog4FuFHA.904@.tk2msftngp13.phx.gbl...
> We recently had a server go down completely and had to rebuild. One thing
> that is different now on machine after restoring the databases is the
> physical location. All databases are up and working fine, but after
> looking at Master..sysdatabases I see some references that are not
> correct. I am not sure if I should be concerned, but I just don't like
> seeing that the wrong data.
> If we run the following we see some database files that point to the
> incorrect path, but if you look at the DB themselves it says the correct
> place. My main concern is the master is one of them. Is there anything I
> should do to correct this, what is the result of getting a wrong path for
> FileName?
> select *
> from sysdatabases
>
>
|||Yes your description is what I mean. The main question is that MASTER is one
of them, so I do not believe I can detach it, since it needs itself to know
the information.
Here is what I have:
D:\MSSQL\data\master.mdf - new location, we had forgotten the SQL server
was installed incorrectly initially
D:\MSSQL\MSSQL\data\master.mdf - old path that is showing up now in
sysdatabases filename and in sysdevices.
Sysdevices shows wrong path for master and model, tempdb is fine because we
have not moved it.
Everything seems to be working just fine, but thought we would ask in case
something were to come up in the future to bite us.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eKJERLGuFHA.1472@.TK2MSFTNGP15.phx.gbl...
> By "wrong" do you mean that the entries in master.dbo.sysdatabases are not
> the same as in <your DB>.dbo.sysfiles? You may be able to correct things
> by
> detaching and reattaching the databases.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Andy" <andyjax@.newsgroup.nospam> wrote in message
> news:%232Nog4FuFHA.904@.tk2msftngp13.phx.gbl...
> We recently had a server go down completely and had to rebuild. One thing
> that is different now on machine after restoring the databases is the
> physical location. All databases are up and working fine, but after
> looking
> at Master..sysdatabases I see some references that are not correct. I am
> not
> sure if I should be concerned, but I just don't like seeing that the wrong
> data.
> If we run the following we see some database files that point to the
> incorrect path, but if you look at the DB themselves it says the correct
> place. My main concern is the master is one of them. Is there anything I
> should do to correct this, what is the result of getting a wrong path for
> FileName?
> select *
> from sysdatabases
>
>
|||Let me clarify something more here.
We had to restore a system completely. So after installing SQL and restoring
master SQL would not start up. The reason was due to the looking for system
databases that did not exist at where MASTER thought they were. We simply
created a folder structure like the old structure and placed the system
databases there and SQL restarted. We then went through and performed a
restore with move to get the paths updated to new location.
"Andy" <andyjax@.newsgroup.nospam> wrote in message
news:eKmNq0HuFHA.4032@.TK2MSFTNGP15.phx.gbl...
> Yes your description is what I mean. The main question is that MASTER is
> one of them, so I do not believe I can detach it, since it needs itself to
> know the information.
> Here is what I have:
> D:\MSSQL\data\master.mdf - new location, we had forgotten the SQL
> server was installed incorrectly initially
> D:\MSSQL\MSSQL\data\master.mdf - old path that is showing up now in
> sysdatabases filename and in sysdevices.
>
> Sysdevices shows wrong path for master and model, tempdb is fine because
> we have not moved it.
>
> Everything seems to be working just fine, but thought we would ask in case
> something were to come up in the future to bite us.
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:eKJERLGuFHA.1472@.TK2MSFTNGP15.phx.gbl...
>
|||Since you restored the old master DB, then this appears to be what you get.
It does not update the system database information. I wouldn't worry about
it. You could do surgery on the system tables - and accept the risks that
go with that.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Andy" <andyjax@.newsgroup.nospam> wrote in message
news:eKmNq0HuFHA.4032@.TK2MSFTNGP15.phx.gbl...
Yes your description is what I mean. The main question is that MASTER is one
of them, so I do not believe I can detach it, since it needs itself to know
the information.
Here is what I have:
D:\MSSQL\data\master.mdf - new location, we had forgotten the SQL server
was installed incorrectly initially
D:\MSSQL\MSSQL\data\master.mdf - old path that is showing up now in
sysdatabases filename and in sysdevices.
Sysdevices shows wrong path for master and model, tempdb is fine because we
have not moved it.
Everything seems to be working just fine, but thought we would ask in case
something were to come up in the future to bite us.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eKJERLGuFHA.1472@.TK2MSFTNGP15.phx.gbl...
> By "wrong" do you mean that the entries in master.dbo.sysdatabases are not
> the same as in <your DB>.dbo.sysfiles? You may be able to correct things
> by
> detaching and reattaching the databases.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Andy" <andyjax@.newsgroup.nospam> wrote in message
> news:%232Nog4FuFHA.904@.tk2msftngp13.phx.gbl...
> We recently had a server go down completely and had to rebuild. One thing
> that is different now on machine after restoring the databases is the
> physical location. All databases are up and working fine, but after
> looking
> at Master..sysdatabases I see some references that are not correct. I am
> not
> sure if I should be concerned, but I just don't like seeing that the wrong
> data.
> If we run the following we see some database files that point to the
> incorrect path, but if you look at the DB themselves it says the correct
> place. My main concern is the master is one of them. Is there anything I
> should do to correct this, what is the result of getting a wrong path for
> FileName?
> select *
> from sysdatabases
>
>
that is different now on machine after restoring the databases is the
physical location. All databases are up and working fine, but after looking
at Master..sysdatabases I see some references that are not correct. I am not
sure if I should be concerned, but I just don't like seeing that the wrong
data.
If we run the following we see some database files that point to the
incorrect path, but if you look at the DB themselves it says the correct
place. My main concern is the master is one of them. Is there anything I
should do to correct this, what is the result of getting a wrong path for
FileName?
select *
from sysdatabases
By "wrong" do you mean that the entries in master.dbo.sysdatabases are not
the same as in <your DB>.dbo.sysfiles? You may be able to correct things by
detaching and reattaching the databases.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Andy" <andyjax@.newsgroup.nospam> wrote in message
news:%232Nog4FuFHA.904@.tk2msftngp13.phx.gbl...
We recently had a server go down completely and had to rebuild. One thing
that is different now on machine after restoring the databases is the
physical location. All databases are up and working fine, but after looking
at Master..sysdatabases I see some references that are not correct. I am not
sure if I should be concerned, but I just don't like seeing that the wrong
data.
If we run the following we see some database files that point to the
incorrect path, but if you look at the DB themselves it says the correct
place. My main concern is the master is one of them. Is there anything I
should do to correct this, what is the result of getting a wrong path for
FileName?
select *
from sysdatabases
|||Hi,
Before doing any thing just open the SQL Server Error log and look into the
entries during startup. If every thing
is good then just ignore. Incase if you have errors then detach the
databases which has got issues and then attach them back to
sql server.
Thanks
Hari
SQL Server MVP
"Andy" <andyjax@.newsgroup.nospam> wrote in message
news:%232Nog4FuFHA.904@.tk2msftngp13.phx.gbl...
> We recently had a server go down completely and had to rebuild. One thing
> that is different now on machine after restoring the databases is the
> physical location. All databases are up and working fine, but after
> looking at Master..sysdatabases I see some references that are not
> correct. I am not sure if I should be concerned, but I just don't like
> seeing that the wrong data.
> If we run the following we see some database files that point to the
> incorrect path, but if you look at the DB themselves it says the correct
> place. My main concern is the master is one of them. Is there anything I
> should do to correct this, what is the result of getting a wrong path for
> FileName?
> select *
> from sysdatabases
>
>
|||Yes your description is what I mean. The main question is that MASTER is one
of them, so I do not believe I can detach it, since it needs itself to know
the information.
Here is what I have:
D:\MSSQL\data\master.mdf - new location, we had forgotten the SQL server
was installed incorrectly initially
D:\MSSQL\MSSQL\data\master.mdf - old path that is showing up now in
sysdatabases filename and in sysdevices.
Sysdevices shows wrong path for master and model, tempdb is fine because we
have not moved it.
Everything seems to be working just fine, but thought we would ask in case
something were to come up in the future to bite us.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eKJERLGuFHA.1472@.TK2MSFTNGP15.phx.gbl...
> By "wrong" do you mean that the entries in master.dbo.sysdatabases are not
> the same as in <your DB>.dbo.sysfiles? You may be able to correct things
> by
> detaching and reattaching the databases.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Andy" <andyjax@.newsgroup.nospam> wrote in message
> news:%232Nog4FuFHA.904@.tk2msftngp13.phx.gbl...
> We recently had a server go down completely and had to rebuild. One thing
> that is different now on machine after restoring the databases is the
> physical location. All databases are up and working fine, but after
> looking
> at Master..sysdatabases I see some references that are not correct. I am
> not
> sure if I should be concerned, but I just don't like seeing that the wrong
> data.
> If we run the following we see some database files that point to the
> incorrect path, but if you look at the DB themselves it says the correct
> place. My main concern is the master is one of them. Is there anything I
> should do to correct this, what is the result of getting a wrong path for
> FileName?
> select *
> from sysdatabases
>
>
|||Let me clarify something more here.
We had to restore a system completely. So after installing SQL and restoring
master SQL would not start up. The reason was due to the looking for system
databases that did not exist at where MASTER thought they were. We simply
created a folder structure like the old structure and placed the system
databases there and SQL restarted. We then went through and performed a
restore with move to get the paths updated to new location.
"Andy" <andyjax@.newsgroup.nospam> wrote in message
news:eKmNq0HuFHA.4032@.TK2MSFTNGP15.phx.gbl...
> Yes your description is what I mean. The main question is that MASTER is
> one of them, so I do not believe I can detach it, since it needs itself to
> know the information.
> Here is what I have:
> D:\MSSQL\data\master.mdf - new location, we had forgotten the SQL
> server was installed incorrectly initially
> D:\MSSQL\MSSQL\data\master.mdf - old path that is showing up now in
> sysdatabases filename and in sysdevices.
>
> Sysdevices shows wrong path for master and model, tempdb is fine because
> we have not moved it.
>
> Everything seems to be working just fine, but thought we would ask in case
> something were to come up in the future to bite us.
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:eKJERLGuFHA.1472@.TK2MSFTNGP15.phx.gbl...
>
|||Since you restored the old master DB, then this appears to be what you get.
It does not update the system database information. I wouldn't worry about
it. You could do surgery on the system tables - and accept the risks that
go with that.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Andy" <andyjax@.newsgroup.nospam> wrote in message
news:eKmNq0HuFHA.4032@.TK2MSFTNGP15.phx.gbl...
Yes your description is what I mean. The main question is that MASTER is one
of them, so I do not believe I can detach it, since it needs itself to know
the information.
Here is what I have:
D:\MSSQL\data\master.mdf - new location, we had forgotten the SQL server
was installed incorrectly initially
D:\MSSQL\MSSQL\data\master.mdf - old path that is showing up now in
sysdatabases filename and in sysdevices.
Sysdevices shows wrong path for master and model, tempdb is fine because we
have not moved it.
Everything seems to be working just fine, but thought we would ask in case
something were to come up in the future to bite us.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eKJERLGuFHA.1472@.TK2MSFTNGP15.phx.gbl...
> By "wrong" do you mean that the entries in master.dbo.sysdatabases are not
> the same as in <your DB>.dbo.sysfiles? You may be able to correct things
> by
> detaching and reattaching the databases.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Andy" <andyjax@.newsgroup.nospam> wrote in message
> news:%232Nog4FuFHA.904@.tk2msftngp13.phx.gbl...
> We recently had a server go down completely and had to rebuild. One thing
> that is different now on machine after restoring the databases is the
> physical location. All databases are up and working fine, but after
> looking
> at Master..sysdatabases I see some references that are not correct. I am
> not
> sure if I should be concerned, but I just don't like seeing that the wrong
> data.
> If we run the following we see some database files that point to the
> incorrect path, but if you look at the DB themselves it says the correct
> place. My main concern is the master is one of them. Is there anything I
> should do to correct this, what is the result of getting a wrong path for
> FileName?
> select *
> from sysdatabases
>
>
Restoring Master
We recently had a server go down completely and had to rebuild. One thing
that is different now on machine after restoring the databases is the
physical location. All databases are up and working fine, but after looking
at Master..sysdatabases I see some references that are not correct. I am not
sure if I should be concerned, but I just don't like seeing that the wrong
data.
If we run the following we see some database files that point to the
incorrect path, but if you look at the DB themselves it says the correct
place. My main concern is the master is one of them. Is there anything I
should do to correct this, what is the result of getting a wrong path for
FileName?
select *
from sysdatabasesBy "wrong" do you mean that the entries in master.dbo.sysdatabases are not
the same as in <your DB>.dbo.sysfiles? You may be able to correct things by
detaching and reattaching the databases.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Andy" <andyjax@.newsgroup.nospam> wrote in message
news:%232Nog4FuFHA.904@.tk2msftngp13.phx.gbl...
We recently had a server go down completely and had to rebuild. One thing
that is different now on machine after restoring the databases is the
physical location. All databases are up and working fine, but after looking
at Master..sysdatabases I see some references that are not correct. I am not
sure if I should be concerned, but I just don't like seeing that the wrong
data.
If we run the following we see some database files that point to the
incorrect path, but if you look at the DB themselves it says the correct
place. My main concern is the master is one of them. Is there anything I
should do to correct this, what is the result of getting a wrong path for
FileName?
select *
from sysdatabases|||Hi,
Before doing any thing just open the SQL Server Error log and look into the
entries during startup. If every thing
is good then just ignore. Incase if you have errors then detach the
databases which has got issues and then attach them back to
sql server.
Thanks
Hari
SQL Server MVP
"Andy" <andyjax@.newsgroup.nospam> wrote in message
news:%232Nog4FuFHA.904@.tk2msftngp13.phx.gbl...
> We recently had a server go down completely and had to rebuild. One thing
> that is different now on machine after restoring the databases is the
> physical location. All databases are up and working fine, but after
> looking at Master..sysdatabases I see some references that are not
> correct. I am not sure if I should be concerned, but I just don't like
> seeing that the wrong data.
> If we run the following we see some database files that point to the
> incorrect path, but if you look at the DB themselves it says the correct
> place. My main concern is the master is one of them. Is there anything I
> should do to correct this, what is the result of getting a wrong path for
> FileName?
> select *
> from sysdatabases
>
>|||Yes your description is what I mean. The main question is that MASTER is one
of them, so I do not believe I can detach it, since it needs itself to know
the information.
Here is what I have:
D:\MSSQL\data\master.mdf - new location, we had forgotten the SQL server
was installed incorrectly initially
D:\MSSQL\MSSQL\data\master.mdf - old path that is showing up now in
sysdatabases filename and in sysdevices.
Sysdevices shows wrong path for master and model, tempdb is fine because we
have not moved it.
Everything seems to be working just fine, but thought we would ask in case
something were to come up in the future to bite us.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eKJERLGuFHA.1472@.TK2MSFTNGP15.phx.gbl...
> By "wrong" do you mean that the entries in master.dbo.sysdatabases are not
> the same as in <your DB>.dbo.sysfiles? You may be able to correct things
> by
> detaching and reattaching the databases.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Andy" <andyjax@.newsgroup.nospam> wrote in message
> news:%232Nog4FuFHA.904@.tk2msftngp13.phx.gbl...
> We recently had a server go down completely and had to rebuild. One thing
> that is different now on machine after restoring the databases is the
> physical location. All databases are up and working fine, but after
> looking
> at Master..sysdatabases I see some references that are not correct. I am
> not
> sure if I should be concerned, but I just don't like seeing that the wrong
> data.
> If we run the following we see some database files that point to the
> incorrect path, but if you look at the DB themselves it says the correct
> place. My main concern is the master is one of them. Is there anything I
> should do to correct this, what is the result of getting a wrong path for
> FileName?
> select *
> from sysdatabases
>
>|||Let me clarify something more here.
We had to restore a system completely. So after installing SQL and restoring
master SQL would not start up. The reason was due to the looking for system
databases that did not exist at where MASTER thought they were. We simply
created a folder structure like the old structure and placed the system
databases there and SQL restarted. We then went through and performed a
restore with move to get the paths updated to new location.
"Andy" <andyjax@.newsgroup.nospam> wrote in message
news:eKmNq0HuFHA.4032@.TK2MSFTNGP15.phx.gbl...
> Yes your description is what I mean. The main question is that MASTER is
> one of them, so I do not believe I can detach it, since it needs itself to
> know the information.
> Here is what I have:
> D:\MSSQL\data\master.mdf - new location, we had forgotten the SQL
> server was installed incorrectly initially
> D:\MSSQL\MSSQL\data\master.mdf - old path that is showing up now in
> sysdatabases filename and in sysdevices.
>
> Sysdevices shows wrong path for master and model, tempdb is fine because
> we have not moved it.
>
> Everything seems to be working just fine, but thought we would ask in case
> something were to come up in the future to bite us.
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:eKJERLGuFHA.1472@.TK2MSFTNGP15.phx.gbl...
>> By "wrong" do you mean that the entries in master.dbo.sysdatabases are
>> not
>> the same as in <your DB>.dbo.sysfiles? You may be able to correct things
>> by
>> detaching and reattaching the databases.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada
>> www.pinpub.com
>> .
>> "Andy" <andyjax@.newsgroup.nospam> wrote in message
>> news:%232Nog4FuFHA.904@.tk2msftngp13.phx.gbl...
>> We recently had a server go down completely and had to rebuild. One thing
>> that is different now on machine after restoring the databases is the
>> physical location. All databases are up and working fine, but after
>> looking
>> at Master..sysdatabases I see some references that are not correct. I am
>> not
>> sure if I should be concerned, but I just don't like seeing that the
>> wrong
>> data.
>> If we run the following we see some database files that point to the
>> incorrect path, but if you look at the DB themselves it says the correct
>> place. My main concern is the master is one of them. Is there anything I
>> should do to correct this, what is the result of getting a wrong path for
>> FileName?
>> select *
>> from sysdatabases
>>
>>
>|||Since you restored the old master DB, then this appears to be what you get.
It does not update the system database information. I wouldn't worry about
it. You could do surgery on the system tables - and accept the risks that
go with that.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Andy" <andyjax@.newsgroup.nospam> wrote in message
news:eKmNq0HuFHA.4032@.TK2MSFTNGP15.phx.gbl...
Yes your description is what I mean. The main question is that MASTER is one
of them, so I do not believe I can detach it, since it needs itself to know
the information.
Here is what I have:
D:\MSSQL\data\master.mdf - new location, we had forgotten the SQL server
was installed incorrectly initially
D:\MSSQL\MSSQL\data\master.mdf - old path that is showing up now in
sysdatabases filename and in sysdevices.
Sysdevices shows wrong path for master and model, tempdb is fine because we
have not moved it.
Everything seems to be working just fine, but thought we would ask in case
something were to come up in the future to bite us.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eKJERLGuFHA.1472@.TK2MSFTNGP15.phx.gbl...
> By "wrong" do you mean that the entries in master.dbo.sysdatabases are not
> the same as in <your DB>.dbo.sysfiles? You may be able to correct things
> by
> detaching and reattaching the databases.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Andy" <andyjax@.newsgroup.nospam> wrote in message
> news:%232Nog4FuFHA.904@.tk2msftngp13.phx.gbl...
> We recently had a server go down completely and had to rebuild. One thing
> that is different now on machine after restoring the databases is the
> physical location. All databases are up and working fine, but after
> looking
> at Master..sysdatabases I see some references that are not correct. I am
> not
> sure if I should be concerned, but I just don't like seeing that the wrong
> data.
> If we run the following we see some database files that point to the
> incorrect path, but if you look at the DB themselves it says the correct
> place. My main concern is the master is one of them. Is there anything I
> should do to correct this, what is the result of getting a wrong path for
> FileName?
> select *
> from sysdatabases
>
>
that is different now on machine after restoring the databases is the
physical location. All databases are up and working fine, but after looking
at Master..sysdatabases I see some references that are not correct. I am not
sure if I should be concerned, but I just don't like seeing that the wrong
data.
If we run the following we see some database files that point to the
incorrect path, but if you look at the DB themselves it says the correct
place. My main concern is the master is one of them. Is there anything I
should do to correct this, what is the result of getting a wrong path for
FileName?
select *
from sysdatabasesBy "wrong" do you mean that the entries in master.dbo.sysdatabases are not
the same as in <your DB>.dbo.sysfiles? You may be able to correct things by
detaching and reattaching the databases.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Andy" <andyjax@.newsgroup.nospam> wrote in message
news:%232Nog4FuFHA.904@.tk2msftngp13.phx.gbl...
We recently had a server go down completely and had to rebuild. One thing
that is different now on machine after restoring the databases is the
physical location. All databases are up and working fine, but after looking
at Master..sysdatabases I see some references that are not correct. I am not
sure if I should be concerned, but I just don't like seeing that the wrong
data.
If we run the following we see some database files that point to the
incorrect path, but if you look at the DB themselves it says the correct
place. My main concern is the master is one of them. Is there anything I
should do to correct this, what is the result of getting a wrong path for
FileName?
select *
from sysdatabases|||Hi,
Before doing any thing just open the SQL Server Error log and look into the
entries during startup. If every thing
is good then just ignore. Incase if you have errors then detach the
databases which has got issues and then attach them back to
sql server.
Thanks
Hari
SQL Server MVP
"Andy" <andyjax@.newsgroup.nospam> wrote in message
news:%232Nog4FuFHA.904@.tk2msftngp13.phx.gbl...
> We recently had a server go down completely and had to rebuild. One thing
> that is different now on machine after restoring the databases is the
> physical location. All databases are up and working fine, but after
> looking at Master..sysdatabases I see some references that are not
> correct. I am not sure if I should be concerned, but I just don't like
> seeing that the wrong data.
> If we run the following we see some database files that point to the
> incorrect path, but if you look at the DB themselves it says the correct
> place. My main concern is the master is one of them. Is there anything I
> should do to correct this, what is the result of getting a wrong path for
> FileName?
> select *
> from sysdatabases
>
>|||Yes your description is what I mean. The main question is that MASTER is one
of them, so I do not believe I can detach it, since it needs itself to know
the information.
Here is what I have:
D:\MSSQL\data\master.mdf - new location, we had forgotten the SQL server
was installed incorrectly initially
D:\MSSQL\MSSQL\data\master.mdf - old path that is showing up now in
sysdatabases filename and in sysdevices.
Sysdevices shows wrong path for master and model, tempdb is fine because we
have not moved it.
Everything seems to be working just fine, but thought we would ask in case
something were to come up in the future to bite us.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eKJERLGuFHA.1472@.TK2MSFTNGP15.phx.gbl...
> By "wrong" do you mean that the entries in master.dbo.sysdatabases are not
> the same as in <your DB>.dbo.sysfiles? You may be able to correct things
> by
> detaching and reattaching the databases.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Andy" <andyjax@.newsgroup.nospam> wrote in message
> news:%232Nog4FuFHA.904@.tk2msftngp13.phx.gbl...
> We recently had a server go down completely and had to rebuild. One thing
> that is different now on machine after restoring the databases is the
> physical location. All databases are up and working fine, but after
> looking
> at Master..sysdatabases I see some references that are not correct. I am
> not
> sure if I should be concerned, but I just don't like seeing that the wrong
> data.
> If we run the following we see some database files that point to the
> incorrect path, but if you look at the DB themselves it says the correct
> place. My main concern is the master is one of them. Is there anything I
> should do to correct this, what is the result of getting a wrong path for
> FileName?
> select *
> from sysdatabases
>
>|||Let me clarify something more here.
We had to restore a system completely. So after installing SQL and restoring
master SQL would not start up. The reason was due to the looking for system
databases that did not exist at where MASTER thought they were. We simply
created a folder structure like the old structure and placed the system
databases there and SQL restarted. We then went through and performed a
restore with move to get the paths updated to new location.
"Andy" <andyjax@.newsgroup.nospam> wrote in message
news:eKmNq0HuFHA.4032@.TK2MSFTNGP15.phx.gbl...
> Yes your description is what I mean. The main question is that MASTER is
> one of them, so I do not believe I can detach it, since it needs itself to
> know the information.
> Here is what I have:
> D:\MSSQL\data\master.mdf - new location, we had forgotten the SQL
> server was installed incorrectly initially
> D:\MSSQL\MSSQL\data\master.mdf - old path that is showing up now in
> sysdatabases filename and in sysdevices.
>
> Sysdevices shows wrong path for master and model, tempdb is fine because
> we have not moved it.
>
> Everything seems to be working just fine, but thought we would ask in case
> something were to come up in the future to bite us.
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:eKJERLGuFHA.1472@.TK2MSFTNGP15.phx.gbl...
>> By "wrong" do you mean that the entries in master.dbo.sysdatabases are
>> not
>> the same as in <your DB>.dbo.sysfiles? You may be able to correct things
>> by
>> detaching and reattaching the databases.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada
>> www.pinpub.com
>> .
>> "Andy" <andyjax@.newsgroup.nospam> wrote in message
>> news:%232Nog4FuFHA.904@.tk2msftngp13.phx.gbl...
>> We recently had a server go down completely and had to rebuild. One thing
>> that is different now on machine after restoring the databases is the
>> physical location. All databases are up and working fine, but after
>> looking
>> at Master..sysdatabases I see some references that are not correct. I am
>> not
>> sure if I should be concerned, but I just don't like seeing that the
>> wrong
>> data.
>> If we run the following we see some database files that point to the
>> incorrect path, but if you look at the DB themselves it says the correct
>> place. My main concern is the master is one of them. Is there anything I
>> should do to correct this, what is the result of getting a wrong path for
>> FileName?
>> select *
>> from sysdatabases
>>
>>
>|||Since you restored the old master DB, then this appears to be what you get.
It does not update the system database information. I wouldn't worry about
it. You could do surgery on the system tables - and accept the risks that
go with that.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Andy" <andyjax@.newsgroup.nospam> wrote in message
news:eKmNq0HuFHA.4032@.TK2MSFTNGP15.phx.gbl...
Yes your description is what I mean. The main question is that MASTER is one
of them, so I do not believe I can detach it, since it needs itself to know
the information.
Here is what I have:
D:\MSSQL\data\master.mdf - new location, we had forgotten the SQL server
was installed incorrectly initially
D:\MSSQL\MSSQL\data\master.mdf - old path that is showing up now in
sysdatabases filename and in sysdevices.
Sysdevices shows wrong path for master and model, tempdb is fine because we
have not moved it.
Everything seems to be working just fine, but thought we would ask in case
something were to come up in the future to bite us.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eKJERLGuFHA.1472@.TK2MSFTNGP15.phx.gbl...
> By "wrong" do you mean that the entries in master.dbo.sysdatabases are not
> the same as in <your DB>.dbo.sysfiles? You may be able to correct things
> by
> detaching and reattaching the databases.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Andy" <andyjax@.newsgroup.nospam> wrote in message
> news:%232Nog4FuFHA.904@.tk2msftngp13.phx.gbl...
> We recently had a server go down completely and had to rebuild. One thing
> that is different now on machine after restoring the databases is the
> physical location. All databases are up and working fine, but after
> looking
> at Master..sysdatabases I see some references that are not correct. I am
> not
> sure if I should be concerned, but I just don't like seeing that the wrong
> data.
> If we run the following we see some database files that point to the
> incorrect path, but if you look at the DB themselves it says the correct
> place. My main concern is the master is one of them. Is there anything I
> should do to correct this, what is the result of getting a wrong path for
> FileName?
> select *
> from sysdatabases
>
>
Restoring in SQL 2005
Hi ,
I am trying to restore database , which is backed up from SQL 2005 ver 9.00.1399.06
to anothere machine have SQL 2005 ver 9.00.1399.00.
When I try to do a restore I am getting the following error!
Too many backup devices specified for backup or restore ; only 64 are allowed.
RESTORE HEADER ONLY is terminating abnormally . (Microsoft SQL Server, Error:3205)
Please give me some solution for this.
Did you got a solution. I am having the same problem when I move the backup from 1 server to another and try to restore|||Yes. I guess I was restoring on the wrong server. It was not 2005. Make sure you are on the right server. We cannot restore 2005 database in 2000. My was a user error
|||that fixed it for me too....I got fooled by a 2005 upgrade issue and was actually trying to restore a 2005 db to a 2000 db server.|||that fixed it for me too.... I was trying to restore backup from 2005 to a another machine with SQL server 2005, but we found that Management Studio was showing only sql server 2000. I Confirmed it by looking at the version Number. For 2005 it was 9.0.1399 and for 2000 it was 8.0.194|||Hi, I' m having the same problem, Im trying to restore a 2005 bkp into a another 2005, but i recive the same error have you resolve this?
thank very much and sorry about my english
|||I'm getting this same error going from 2005 to 2005. Definatly both 2005 versions - i installed them both myself.
Anyone have any ideas?
|||This is not an SSIS issue. Moving to the SQL Server Database Engine forum.|||the solution has already been discussed in the earlier post. You may be using SQLServer 2005Management studio but... the backup you have is from 2005 and u r trying to restore it in 2000 instance which is not possible.
Madhu
|||
Madhu K Nair wrote:
the solution has already been discussed in the earlier post. You may be using SQLServer 2005Management studio but... the backup you have is from 2005 and u r trying to restore it in 2000 instance which is not possible.
Madhu
I wish it was that simple.
I installed 2005 on a server at work and created the database in question. Then took a backup.
Then I installed 2005 on my laptop (using the same install program) and get this error when trying to restore the backup into a database on the laptop.
|||Actually, my bad. Didn't notice that the install program did not upgrade the existing sql default instance.|||http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=08.00.0760&EvtSrc=MSSQLServer&EvtID=3205&LinkId=20476|||ProdName=Microsoft+SQL+Server&ProdVer=08.00.0760&EvtSrc=MSSQLServer&EvtID=3205&LinkId=20476Restoring in SQL 2005
Hi ,
I am trying to restore database , which is backed up from SQL 2005 ver 9.00.1399.06
to anothere machine have SQL 2005 ver 9.00.1399.00.
When I try to do a restore I am getting the following error!
Too many backup devices specified for backup or restore ; only 64 are allowed.
RESTORE HEADER ONLY is terminating abnormally . (Microsoft SQL Server, Error:3205)
Please give me some solution for this.
Did you got a solution. I am having the same problem when I move the backup from 1 server to another and try to restore|||Yes. I guess I was restoring on the wrong server. It was not 2005. Make sure you are on the right server. We cannot restore 2005 database in 2000. My was a user error
|||that fixed it for me too....I got fooled by a 2005 upgrade issue and was actually trying to restore a 2005 db to a 2000 db server.|||that fixed it for me too.... I was trying to restore backup from 2005 to a another machine with SQL server 2005, but we found that Management Studio was showing only sql server 2000. I Confirmed it by looking at the version Number. For 2005 it was 9.0.1399 and for 2000 it was 8.0.194|||Hi, I' m having the same problem, Im trying to restore a 2005 bkp into a another 2005, but i recive the same error have you resolve this?
thank very much and sorry about my english
|||I'm getting this same error going from 2005 to 2005. Definatly both 2005 versions - i installed them both myself.
Anyone have any ideas?
|||This is not an SSIS issue. Moving to the SQL Server Database Engine forum.|||the solution has already been discussed in the earlier post. You may be using SQLServer 2005Management studio but... the backup you have is from 2005 and u r trying to restore it in 2000 instance which is not possible.
Madhu
|||
the solution has already been discussed in the earlier post. You may be using SQLServer 2005Management studio but... the backup you have is from 2005 and u r trying to restore it in 2000 instance which is not possible.
Madhu
I wish it was that simple.
I installed 2005 on a server at work and created the database in question. Then took a backup.
Then I installed 2005 on my laptop (using the same install program) and get this error when trying to restore the backup into a database on the laptop.
|||Actually, my bad. Didn't notice that the install program did not upgrade the existing sql default instance.|||http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=08.00.0760&EvtSrc=MSSQLServer&EvtID=3205&LinkId=20476|||
ProdName=Microsoft+SQL+Server&ProdVer=08.00.0760&EvtSrc=MSSQLServer&EvtID=3205&LinkId=20476
Restoring in SQL 2005
Hi ,
I am trying to restore database , which is backed up from SQL 2005 ver 9.00.1399.06
to anothere machine have SQL 2005 ver 9.00.1399.00.
When I try to do a restore I am getting the following error!
Too many backup devices specified for backup or restore ; only 64 are allowed.
RESTORE HEADER ONLY is terminating abnormally . (Microsoft SQL Server, Error:3205)
Please give me some solution for this.
Did you got a solution. I am having the same problem when I move the backup from 1 server to another and try to restore|||Yes. I guess I was restoring on the wrong server. It was not 2005. Make sure you are on the right server. We cannot restore 2005 database in 2000. My was a user error
|||that fixed it for me too....I got fooled by a 2005 upgrade issue and was actually trying to restore a 2005 db to a 2000 db server.|||that fixed it for me too.... I was trying to restore backup from 2005 to a another machine with SQL server 2005, but we found that Management Studio was showing only sql server 2000. I Confirmed it by looking at the version Number. For 2005 it was 9.0.1399 and for 2000 it was 8.0.194|||Hi, I' m having the same problem, Im trying to restore a 2005 bkp into a another 2005, but i recive the same error have you resolve this?
thank very much and sorry about my english
|||I'm getting this same error going from 2005 to 2005. Definatly both 2005 versions - i installed them both myself.
Anyone have any ideas?
|||This is not an SSIS issue. Moving to the SQL Server Database Engine forum.|||the solution has already been discussed in the earlier post. You may be using SQLServer 2005Management studio but... the backup you have is from 2005 and u r trying to restore it in 2000 instance which is not possible.
Madhu
|||
the solution has already been discussed in the earlier post. You may be using SQLServer 2005Management studio but... the backup you have is from 2005 and u r trying to restore it in 2000 instance which is not possible.
Madhu
I wish it was that simple.
I installed 2005 on a server at work and created the database in question. Then took a backup.
Then I installed 2005 on my laptop (using the same install program) and get this error when trying to restore the backup into a database on the laptop.
|||Actually, my bad. Didn't notice that the install program did not upgrade the existing sql default instance.|||http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=08.00.0760&EvtSrc=MSSQLServer&EvtID=3205&LinkId=20476|||ProdName=Microsoft+SQL+Server&ProdVer=08.00.0760&EvtSrc=MSSQLServer&EvtID=3205&LinkId=20476Restoring in SQL 2005
Hi ,
I am trying to restore database , which is backed up from SQL 2005 ver 9.00.1399.06
to anothere machine have SQL 2005 ver 9.00.1399.00.
When I try to do a restore I am getting the following error!
Too many backup devices specified for backup or restore ; only 64 are allowed.
RESTORE HEADER ONLY is terminating abnormally . (Microsoft SQL Server, Error:3205)
Please give me some solution for this.
Did you got a solution. I am having the same problem when I move the backup from 1 server to another and try to restore|||Yes. I guess I was restoring on the wrong server. It was not 2005. Make sure you are on the right server. We cannot restore 2005 database in 2000. My was a user error
|||that fixed it for me too....I got fooled by a 2005 upgrade issue and was actually trying to restore a 2005 db to a 2000 db server.|||that fixed it for me too.... I was trying to restore backup from 2005 to a another machine with SQL server 2005, but we found that Management Studio was showing only sql server 2000. I Confirmed it by looking at the version Number. For 2005 it was 9.0.1399 and for 2000 it was 8.0.194|||Hi, I' m having the same problem, Im trying to restore a 2005 bkp into a another 2005, but i recive the same error have you resolve this?
thank very much and sorry about my english
|||I'm getting this same error going from 2005 to 2005. Definatly both 2005 versions - i installed them both myself.
Anyone have any ideas?
|||This is not an SSIS issue. Moving to the SQL Server Database Engine forum.|||the solution has already been discussed in the earlier post. You may be using SQLServer 2005Management studio but... the backup you have is from 2005 and u r trying to restore it in 2000 instance which is not possible.
Madhu
|||
the solution has already been discussed in the earlier post. You may be using SQLServer 2005Management studio but... the backup you have is from 2005 and u r trying to restore it in 2000 instance which is not possible.
Madhu
I wish it was that simple.
I installed 2005 on a server at work and created the database in question. Then took a backup.
Then I installed 2005 on my laptop (using the same install program) and get this error when trying to restore the backup into a database on the laptop.
|||Actually, my bad. Didn't notice that the install program did not upgrade the existing sql default instance.|||http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=08.00.0760&EvtSrc=MSSQLServer&EvtID=3205&LinkId=20476|||
ProdName=Microsoft+SQL+Server&ProdVer=08.00.0760&EvtSrc=MSSQLServer&EvtID=3205&LinkId=20476
Restoring in SQL 2005
Hi ,
I am trying to restore database , which is backed up from SQL 2005 ver 9.00.1399.06
to anothere machine have SQL 2005 ver 9.00.1399.00.
When I try to do a restore I am getting the following error!
Too many backup devices specified for backup or restore ; only 64 are allowed.
RESTORE HEADER ONLY is terminating abnormally . (Microsoft SQL Server, Error:3205)
Please give me some solution for this.
Did you got a solution. I am having the same problem when I move the backup from 1 server to another and try to restore|||Yes. I guess I was restoring on the wrong server. It was not 2005. Make sure you are on the right server. We cannot restore 2005 database in 2000. My was a user error
|||that fixed it for me too....I got fooled by a 2005 upgrade issue and was actually trying to restore a 2005 db to a 2000 db server.|||that fixed it for me too.... I was trying to restore backup from 2005 to a another machine with SQL server 2005, but we found that Management Studio was showing only sql server 2000. I Confirmed it by looking at the version Number. For 2005 it was 9.0.1399 and for 2000 it was 8.0.194|||Hi, I' m having the same problem, Im trying to restore a 2005 bkp into a another 2005, but i recive the same error have you resolve this?
thank very much and sorry about my english
|||I'm getting this same error going from 2005 to 2005. Definatly both 2005 versions - i installed them both myself.
Anyone have any ideas?
|||This is not an SSIS issue. Moving to the SQL Server Database Engine forum.|||the solution has already been discussed in the earlier post. You may be using SQLServer 2005Management studio but... the backup you have is from 2005 and u r trying to restore it in 2000 instance which is not possible.
Madhu
|||
the solution has already been discussed in the earlier post. You may be using SQLServer 2005Management studio but... the backup you have is from 2005 and u r trying to restore it in 2000 instance which is not possible.
Madhu
I wish it was that simple.
I installed 2005 on a server at work and created the database in question. Then took a backup.
Then I installed 2005 on my laptop (using the same install program) and get this error when trying to restore the backup into a database on the laptop.
|||Actually, my bad. Didn't notice that the install program did not upgrade the existing sql default instance.|||http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=08.00.0760&EvtSrc=MSSQLServer&EvtID=3205&LinkId=20476|||
ProdName=Microsoft+SQL+Server&ProdVer=08.00.0760&EvtSrc=MSSQLServer&EvtID=3205&LinkId=20476
Saturday, February 25, 2012
Restoring DB
Hi,
>>How are you looking for the file in Management Studio? Are you using the Attach >>functionality?
I have created a DB on my local machine.
Now I would like to copy this on to another machine.
I stopped the service and then copied both the MDF file and the
log files into the same folder as in Data folder of the second machine.
When I run theStudio express I cannot find the MDF file.It does not show up in the
the DB folder or window.
When I try to create one it says that the DB exists.
What am I missing?
I tried doing a backup and restore but then the DB is not created on the second machine and
hence wont work.
Tnx
How are you looking for the file in Management Studio? Are you using the Attach functionality? If there is more than one SQL Server running on the computer, there will be more than one Data directory, so it's possible that you've put the files in the wrong directory.
Let us know the exact steps you're using to attach the database on the new machine.
Regards,
Mike Wachal
SQL Express team
-
Mark the best posts as Answers!
>>How are you looking for the file in Management Studio? Are you using the Attach >>functionality?
I stoped the service on the first machine and then went into the folder:
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\
and copied both the DB and the Log files on to my CD.
I stoped the service on the second machine and then went into the folder.
copied them both into the second machine's :
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
There is only one SQL server installed on both the machines'.
Tnx
|||
Hi,
great you found the button for making fonts real big ! :-)
As Mike said you have to attach the database for before you can see it, use the Sp_attach procedure for this or use the graphical UI (SQL Server Management Studio Express) for that, I don′t know if there is a possibility to use this function for attaching otherwise sp_attachdb is quite easy to code in TSQL, there are samples on the BOL for that.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
Restoring datbase on different machine
Hi All,
I am using SQL serve 2000. My client has sent me one database. I want to restore here. How do I restore the database which has been sent to me.
Vinihi vini,
what do u mean by the client has sent u a database? I mean is it a database backup or the database file(mdf)??
If it is a backup of the database then you can create a empty database and restore the database backup on this new database.
If it is a database file (mdf) then attach the database to the database server.
regards,
harsh.|||To restore a dump from other Server , you don't have to create an empty database !! Use RESTORE COMMAND WITH MOVE option (See BOL) or USe Enterprise manager , Right click any existing database : Click Restore : Change the name to New Db , In Options tab , amke sure the Physical File Path is Correct matching to the Server you are restoring , Click Force Restore and Hit OK .
For MDF Files If you do not have Log File with it , sp_Attach_singly_file might work but you are likely to loose the transactions which are uncommited and sitting on MDF file ..
I am using SQL serve 2000. My client has sent me one database. I want to restore here. How do I restore the database which has been sent to me.
Vinihi vini,
what do u mean by the client has sent u a database? I mean is it a database backup or the database file(mdf)??
If it is a backup of the database then you can create a empty database and restore the database backup on this new database.
If it is a database file (mdf) then attach the database to the database server.
regards,
harsh.|||To restore a dump from other Server , you don't have to create an empty database !! Use RESTORE COMMAND WITH MOVE option (See BOL) or USe Enterprise manager , Right click any existing database : Click Restore : Change the name to New Db , In Options tab , amke sure the Physical File Path is Correct matching to the Server you are restoring , Click Force Restore and Hit OK .
For MDF Files If you do not have Log File with it , sp_Attach_singly_file might work but you are likely to loose the transactions which are uncommited and sitting on MDF file ..
Restoring databases
This weekend I have to restore a backup from one SQL server 2000 machine to
another. I am doing this simply because we are updating the hardware, so
when the new machine has been built it will have the same network name as
the original box. The current box is running Windows 2000 Server with SQL
2000 sp2, the new box will be Windows 2003 and SQL 2000 sp3a. There are 8
small databases totaling 1.5Gb. My question is do I need to restore any of
the SQL databases? msdb? pubs? I normally do this for SAP databases where I
only have to restore the SAP database and nothing else. Can anbody enlighten
me?
GavYou don't actually need to restore any of you system
databases unless you have modified or added code in them
(which is a bit dangeous anyway).
In your text you said the pubs database, thats not a
system database but a user database, the answer is it
depends if you have changed it.
BTW the best way of doing it is not to restore them, but
to detach the database and copy over the log and data file
onto the new server then re-attach them. It will save you
a bit of time ;)
Peter
"Adam and Eve had many advantages but the principal one
was that they escaped teething."
Mark Twain
>--Original Message--
>This weekend I have to restore a backup from one SQL
server 2000 machine to
>another. I am doing this simply because we are updating
the hardware, so
>when the new machine has been built it will have the same
network name as
>the original box. The current box is running Windows 2000
Server with SQL
>2000 sp2, the new box will be Windows 2003 and SQL 2000
sp3a. There are 8
>small databases totaling 1.5Gb. My question is do I need
to restore any of
>the SQL databases? msdb? pubs? I normally do this for SAP
databases where I
>only have to restore the SAP database and nothing else.
Can anbody enlighten
>me?
>Gav
>
>.
>|||"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:2f1d01c520d6$32bc7970$a501280a@.phx.gbl...
> You don't actually need to restore any of you system
> databases unless you have modified or added code in them
> (which is a bit dangeous anyway).
> In your text you said the pubs database, thats not a
> system database but a user database, the answer is it
> depends if you have changed it.
> BTW the best way of doing it is not to restore them, but
> to detach the database and copy over the log and data file
> onto the new server then re-attach them. It will save you
> a bit of time ;)
> Peter
> "Adam and Eve had many advantages but the principal one
> was that they escaped teething."
> Mark Twain
>
> >--Original Message--
> >This weekend I have to restore a backup from one SQL
> server 2000 machine to
> >another. I am doing this simply because we are updating
> the hardware, so
> >when the new machine has been built it will have the same
> network name as
> >the original box. The current box is running Windows 2000
> Server with SQL
> >2000 sp2, the new box will be Windows 2003 and SQL 2000
> sp3a. There are 8
> >small databases totaling 1.5Gb. My question is do I need
> to restore any of
> >the SQL databases? msdb? pubs? I normally do this for SAP
> databases where I
> >only have to restore the SAP database and nothing else.
> Can anbody enlighten
> >me?
> >
> >Gav
> >
> >
> >.
> >
What about any users that are defined in Logins? Where are they stored?
Thought about detaching the databases but they are on local storage and both
machines have the same name so without changing server names we cannot have
them both on the network at the same time. Restore takes no time at all so
its probably less hassle.
Just read up on the other databases, doh, didn't realise pub was just a
sample like northwind. :o)
Gav
another. I am doing this simply because we are updating the hardware, so
when the new machine has been built it will have the same network name as
the original box. The current box is running Windows 2000 Server with SQL
2000 sp2, the new box will be Windows 2003 and SQL 2000 sp3a. There are 8
small databases totaling 1.5Gb. My question is do I need to restore any of
the SQL databases? msdb? pubs? I normally do this for SAP databases where I
only have to restore the SAP database and nothing else. Can anbody enlighten
me?
GavYou don't actually need to restore any of you system
databases unless you have modified or added code in them
(which is a bit dangeous anyway).
In your text you said the pubs database, thats not a
system database but a user database, the answer is it
depends if you have changed it.
BTW the best way of doing it is not to restore them, but
to detach the database and copy over the log and data file
onto the new server then re-attach them. It will save you
a bit of time ;)
Peter
"Adam and Eve had many advantages but the principal one
was that they escaped teething."
Mark Twain
>--Original Message--
>This weekend I have to restore a backup from one SQL
server 2000 machine to
>another. I am doing this simply because we are updating
the hardware, so
>when the new machine has been built it will have the same
network name as
>the original box. The current box is running Windows 2000
Server with SQL
>2000 sp2, the new box will be Windows 2003 and SQL 2000
sp3a. There are 8
>small databases totaling 1.5Gb. My question is do I need
to restore any of
>the SQL databases? msdb? pubs? I normally do this for SAP
databases where I
>only have to restore the SAP database and nothing else.
Can anbody enlighten
>me?
>Gav
>
>.
>|||"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:2f1d01c520d6$32bc7970$a501280a@.phx.gbl...
> You don't actually need to restore any of you system
> databases unless you have modified or added code in them
> (which is a bit dangeous anyway).
> In your text you said the pubs database, thats not a
> system database but a user database, the answer is it
> depends if you have changed it.
> BTW the best way of doing it is not to restore them, but
> to detach the database and copy over the log and data file
> onto the new server then re-attach them. It will save you
> a bit of time ;)
> Peter
> "Adam and Eve had many advantages but the principal one
> was that they escaped teething."
> Mark Twain
>
> >--Original Message--
> >This weekend I have to restore a backup from one SQL
> server 2000 machine to
> >another. I am doing this simply because we are updating
> the hardware, so
> >when the new machine has been built it will have the same
> network name as
> >the original box. The current box is running Windows 2000
> Server with SQL
> >2000 sp2, the new box will be Windows 2003 and SQL 2000
> sp3a. There are 8
> >small databases totaling 1.5Gb. My question is do I need
> to restore any of
> >the SQL databases? msdb? pubs? I normally do this for SAP
> databases where I
> >only have to restore the SAP database and nothing else.
> Can anbody enlighten
> >me?
> >
> >Gav
> >
> >
> >.
> >
What about any users that are defined in Logins? Where are they stored?
Thought about detaching the databases but they are on local storage and both
machines have the same name so without changing server names we cannot have
them both on the network at the same time. Restore takes no time at all so
its probably less hassle.
Just read up on the other databases, doh, didn't realise pub was just a
sample like northwind. :o)
Gav
Tuesday, February 21, 2012
restoring database with FTS to another machine
Hi,
I would like to help me to restore the FTS database in another machine. The
problem is that in the main server the drive letter is different than in the
one where I want to restore, in the production(main) server drive in M, in
the test server I can put it in D, for instance. Can you please tell me the
best or easiest way of restoring this database?
Thanks a lot,
BaniSQL
I tried this to change the path in another server where I restored my db:
DECLARE @.path_s VARCHAR(100)
SELECT @.path_s = 'D:\Fulltext' -- CHANGE HERE IF WANTED
IF @.path_s = ''
BEGIN
SELECT @.path_s = LEFT(filename, LEN(RTRIM(filename)) - CHARINDEX('\',
REVERSE(RTRIM(filename))))
FROM sysfiles WHERE fileid = 1
END
UPDATE dbo.sysfulltextcatalogs SET path = @.path_s
GO
Any other solution from your side ?
Thanks,
BaniSQL
"BaniSQL" wrote:
> Hi,
> I would like to help me to restore the FTS database in another machine. The
> problem is that in the main server the drive letter is different than in the
> one where I want to restore, in the production(main) server drive in M, in
> the test server I can put it in D, for instance. Can you please tell me the
> best or easiest way of restoring this database?
> Thanks a lot,
> BaniSQL
I would like to help me to restore the FTS database in another machine. The
problem is that in the main server the drive letter is different than in the
one where I want to restore, in the production(main) server drive in M, in
the test server I can put it in D, for instance. Can you please tell me the
best or easiest way of restoring this database?
Thanks a lot,
BaniSQL
I tried this to change the path in another server where I restored my db:
DECLARE @.path_s VARCHAR(100)
SELECT @.path_s = 'D:\Fulltext' -- CHANGE HERE IF WANTED
IF @.path_s = ''
BEGIN
SELECT @.path_s = LEFT(filename, LEN(RTRIM(filename)) - CHARINDEX('\',
REVERSE(RTRIM(filename))))
FROM sysfiles WHERE fileid = 1
END
UPDATE dbo.sysfulltextcatalogs SET path = @.path_s
GO
Any other solution from your side ?
Thanks,
BaniSQL
"BaniSQL" wrote:
> Hi,
> I would like to help me to restore the FTS database in another machine. The
> problem is that in the main server the drive letter is different than in the
> one where I want to restore, in the production(main) server drive in M, in
> the test server I can put it in D, for instance. Can you please tell me the
> best or easiest way of restoring this database?
> Thanks a lot,
> BaniSQL
restoring database onto a different machine
Hi,
Every time I have to copy a database from one machine to another I get stuck
in a problem with login IDs. Here's what happens:
On machine A I have a database called D. there is a user called U that maps
to a login called U (no NTLM). U owns all the tables in database D, and D is
the default database for U. On machine B there is also a login called U.
The machines are not on the same network so I backup the database to a flat
file and restore it onto machine B
Now if I log onto machine B as user U, I do not have permission to change
into the newly restored database D. I presume this is because user U on
machine B is different to user U on machine A, and the database is owned by
user U on machine A.
So I log into machine B as 'sa' and I try to add user U to the database D.
This fails because there is already a user called U on the database. I
cannot remove the existing user called U because it owns all the tables. So
it seems I am in a catch 22 situation.
I have tried sp_fix_users_login which looks like it should do the right
thing but to no avail.
The only way I can get working is create a new user called something other
than U, add this to the database, and then use sp_changeobjectowner to
change ownership of all the tables to the new user, which is a right PITA.
I have tried to keep it as simple as possible (only 1 user per database,
only 1 database per user, login name same as user name, etc etc) but I am
still stuck. Any ideas
TIA
AndyI suspect that you mean sp_change_users_login not sp_fix_users_login. I
recommend that you have dbo own the database objects. You can grant the
appropriate rights on the tables (or stored procedures) to the other
database users.
Keith
"Andy Fish" <ajfish@.blueyonder.co.uk> wrote in message
news:uZx%237PJKFHA.3788@.tk2msftngp13.phx.gbl...
> Hi,
> Every time I have to copy a database from one machine to another I get
stuck
> in a problem with login IDs. Here's what happens:
> On machine A I have a database called D. there is a user called U that
maps
> to a login called U (no NTLM). U owns all the tables in database D, and D
is
> the default database for U. On machine B there is also a login called U.
> The machines are not on the same network so I backup the database to a
flat
> file and restore it onto machine B
> Now if I log onto machine B as user U, I do not have permission to change
> into the newly restored database D. I presume this is because user U on
> machine B is different to user U on machine A, and the database is owned
by
> user U on machine A.
> So I log into machine B as 'sa' and I try to add user U to the database D.
> This fails because there is already a user called U on the database. I
> cannot remove the existing user called U because it owns all the tables.
So
> it seems I am in a catch 22 situation.
> I have tried sp_fix_users_login which looks like it should do the right
> thing but to no avail.
> The only way I can get working is create a new user called something other
> than U, add this to the database, and then use sp_changeobjectowner to
> change ownership of all the tables to the new user, which is a right PITA.
> I have tried to keep it as simple as possible (only 1 user per database,
> only 1 database per user, login name same as user name, etc etc) but I am
> still stuck. Any ideas
> TIA
> Andy
>|||There is a way to create a Login on one server with the GUID of another
login on another server.
This should get rid of the need to recreate the login everytime a restore is
done.
I cannot recollect how I used to do it as it is a year since I last worked
on DB administration.
You might want to check with BOL.
Gopi
"Andy Fish" <ajfish@.blueyonder.co.uk> wrote in message
news:uZx%237PJKFHA.3788@.tk2msftngp13.phx.gbl...
> Hi,
> Every time I have to copy a database from one machine to another I get
> stuck in a problem with login IDs. Here's what happens:
> On machine A I have a database called D. there is a user called U that
> maps to a login called U (no NTLM). U owns all the tables in database D,
> and D is the default database for U. On machine B there is also a login
> called U.
> The machines are not on the same network so I backup the database to a
> flat file and restore it onto machine B
> Now if I log onto machine B as user U, I do not have permission to change
> into the newly restored database D. I presume this is because user U on
> machine B is different to user U on machine A, and the database is owned
> by user U on machine A.
> So I log into machine B as 'sa' and I try to add user U to the database D.
> This fails because there is already a user called U on the database. I
> cannot remove the existing user called U because it owns all the tables.
> So it seems I am in a catch 22 situation.
> I have tried sp_fix_users_login which looks like it should do the right
> thing but to no avail.
> The only way I can get working is create a new user called something other
> than U, add this to the database, and then use sp_changeobjectowner to
> change ownership of all the tables to the new user, which is a right PITA.
> I have tried to keep it as simple as possible (only 1 user per database,
> only 1 database per user, login name same as user name, etc etc) but I am
> still stuck. Any ideas
> TIA
> Andy
>|||HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://www.support.microsoft.com/?id=246133
Keith
"rgn" <gopinathr@.healthasyst.com> wrote in message
news:O5emurJKFHA.1096@.tk2msftngp13.phx.gbl...
> There is a way to create a Login on one server with the GUID of another
> login on another server.
> This should get rid of the need to recreate the login everytime a restore
is
> done.
> I cannot recollect how I used to do it as it is a year since I last worked
> on DB administration.
> You might want to check with BOL.
> Gopi
>
> "Andy Fish" <ajfish@.blueyonder.co.uk> wrote in message
> news:uZx%237PJKFHA.3788@.tk2msftngp13.phx.gbl...
change[vbcol=seagreen]
D.[vbcol=seagreen]
other[vbcol=seagreen]
PITA.[vbcol=seagreen]
am[vbcol=seagreen]
>|||Thanks for that link Keith, it had links to some other very useful articles.
It turned out to be fairly simple. When I was running sp_change_users_login
as sa, I was still in the master database. Changing to the new database and
then running:
exec sp_change_users_login 'Auto_Fix', 'U'
worked a treat !!
Andy
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:eLHBNzJKFHA.484@.TK2MSFTNGP15.phx.gbl...
> HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
> http://www.support.microsoft.com/?id=246133
> --
> Keith
>
> "rgn" <gopinathr@.healthasyst.com> wrote in message
> news:O5emurJKFHA.1096@.tk2msftngp13.phx.gbl...
> is
> change
> D.
> other
> PITA.
> am
>|||Excellent. I am glad that you figured out how to fix the issue. Going
forward if you create your logins based on the script of one server the SID
will match across all of your servers and that should allow you to restore
across servers and not have to execute sp_change_users_login at all.
Keith
"Andy Fish" <ajfish@.blueyonder.co.uk> wrote in message
news:O%23K2jgKKFHA.2920@.TK2MSFTNGP10.phx.gbl...
> Thanks for that link Keith, it had links to some other very useful
articles.
> It turned out to be fairly simple. When I was running
sp_change_users_login
> as sa, I was still in the master database. Changing to the new database
and
> then running:
> exec sp_change_users_login 'Auto_Fix', 'U'
> worked a treat !!
> Andy
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
> news:eLHBNzJKFHA.484@.TK2MSFTNGP15.phx.gbl...
restore[vbcol=seagreen]
get[vbcol=seagreen]
that[vbcol=seagreen]
login[vbcol=seagreen]
a[vbcol=seagreen]
on[vbcol=seagreen]
database[vbcol=seagreen]
I[vbcol=seagreen]
right[vbcol=seagreen]
to[vbcol=seagreen]
I[vbcol=seagreen]
>
Every time I have to copy a database from one machine to another I get stuck
in a problem with login IDs. Here's what happens:
On machine A I have a database called D. there is a user called U that maps
to a login called U (no NTLM). U owns all the tables in database D, and D is
the default database for U. On machine B there is also a login called U.
The machines are not on the same network so I backup the database to a flat
file and restore it onto machine B
Now if I log onto machine B as user U, I do not have permission to change
into the newly restored database D. I presume this is because user U on
machine B is different to user U on machine A, and the database is owned by
user U on machine A.
So I log into machine B as 'sa' and I try to add user U to the database D.
This fails because there is already a user called U on the database. I
cannot remove the existing user called U because it owns all the tables. So
it seems I am in a catch 22 situation.
I have tried sp_fix_users_login which looks like it should do the right
thing but to no avail.
The only way I can get working is create a new user called something other
than U, add this to the database, and then use sp_changeobjectowner to
change ownership of all the tables to the new user, which is a right PITA.
I have tried to keep it as simple as possible (only 1 user per database,
only 1 database per user, login name same as user name, etc etc) but I am
still stuck. Any ideas
TIA
AndyI suspect that you mean sp_change_users_login not sp_fix_users_login. I
recommend that you have dbo own the database objects. You can grant the
appropriate rights on the tables (or stored procedures) to the other
database users.
Keith
"Andy Fish" <ajfish@.blueyonder.co.uk> wrote in message
news:uZx%237PJKFHA.3788@.tk2msftngp13.phx.gbl...
> Hi,
> Every time I have to copy a database from one machine to another I get
stuck
> in a problem with login IDs. Here's what happens:
> On machine A I have a database called D. there is a user called U that
maps
> to a login called U (no NTLM). U owns all the tables in database D, and D
is
> the default database for U. On machine B there is also a login called U.
> The machines are not on the same network so I backup the database to a
flat
> file and restore it onto machine B
> Now if I log onto machine B as user U, I do not have permission to change
> into the newly restored database D. I presume this is because user U on
> machine B is different to user U on machine A, and the database is owned
by
> user U on machine A.
> So I log into machine B as 'sa' and I try to add user U to the database D.
> This fails because there is already a user called U on the database. I
> cannot remove the existing user called U because it owns all the tables.
So
> it seems I am in a catch 22 situation.
> I have tried sp_fix_users_login which looks like it should do the right
> thing but to no avail.
> The only way I can get working is create a new user called something other
> than U, add this to the database, and then use sp_changeobjectowner to
> change ownership of all the tables to the new user, which is a right PITA.
> I have tried to keep it as simple as possible (only 1 user per database,
> only 1 database per user, login name same as user name, etc etc) but I am
> still stuck. Any ideas
> TIA
> Andy
>|||There is a way to create a Login on one server with the GUID of another
login on another server.
This should get rid of the need to recreate the login everytime a restore is
done.
I cannot recollect how I used to do it as it is a year since I last worked
on DB administration.
You might want to check with BOL.
Gopi
"Andy Fish" <ajfish@.blueyonder.co.uk> wrote in message
news:uZx%237PJKFHA.3788@.tk2msftngp13.phx.gbl...
> Hi,
> Every time I have to copy a database from one machine to another I get
> stuck in a problem with login IDs. Here's what happens:
> On machine A I have a database called D. there is a user called U that
> maps to a login called U (no NTLM). U owns all the tables in database D,
> and D is the default database for U. On machine B there is also a login
> called U.
> The machines are not on the same network so I backup the database to a
> flat file and restore it onto machine B
> Now if I log onto machine B as user U, I do not have permission to change
> into the newly restored database D. I presume this is because user U on
> machine B is different to user U on machine A, and the database is owned
> by user U on machine A.
> So I log into machine B as 'sa' and I try to add user U to the database D.
> This fails because there is already a user called U on the database. I
> cannot remove the existing user called U because it owns all the tables.
> So it seems I am in a catch 22 situation.
> I have tried sp_fix_users_login which looks like it should do the right
> thing but to no avail.
> The only way I can get working is create a new user called something other
> than U, add this to the database, and then use sp_changeobjectowner to
> change ownership of all the tables to the new user, which is a right PITA.
> I have tried to keep it as simple as possible (only 1 user per database,
> only 1 database per user, login name same as user name, etc etc) but I am
> still stuck. Any ideas
> TIA
> Andy
>|||HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://www.support.microsoft.com/?id=246133
Keith
"rgn" <gopinathr@.healthasyst.com> wrote in message
news:O5emurJKFHA.1096@.tk2msftngp13.phx.gbl...
> There is a way to create a Login on one server with the GUID of another
> login on another server.
> This should get rid of the need to recreate the login everytime a restore
is
> done.
> I cannot recollect how I used to do it as it is a year since I last worked
> on DB administration.
> You might want to check with BOL.
> Gopi
>
> "Andy Fish" <ajfish@.blueyonder.co.uk> wrote in message
> news:uZx%237PJKFHA.3788@.tk2msftngp13.phx.gbl...
change[vbcol=seagreen]
D.[vbcol=seagreen]
other[vbcol=seagreen]
PITA.[vbcol=seagreen]
am[vbcol=seagreen]
>|||Thanks for that link Keith, it had links to some other very useful articles.
It turned out to be fairly simple. When I was running sp_change_users_login
as sa, I was still in the master database. Changing to the new database and
then running:
exec sp_change_users_login 'Auto_Fix', 'U'
worked a treat !!
Andy
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:eLHBNzJKFHA.484@.TK2MSFTNGP15.phx.gbl...
> HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
> http://www.support.microsoft.com/?id=246133
> --
> Keith
>
> "rgn" <gopinathr@.healthasyst.com> wrote in message
> news:O5emurJKFHA.1096@.tk2msftngp13.phx.gbl...
> is
> change
> D.
> other
> PITA.
> am
>|||Excellent. I am glad that you figured out how to fix the issue. Going
forward if you create your logins based on the script of one server the SID
will match across all of your servers and that should allow you to restore
across servers and not have to execute sp_change_users_login at all.
Keith
"Andy Fish" <ajfish@.blueyonder.co.uk> wrote in message
news:O%23K2jgKKFHA.2920@.TK2MSFTNGP10.phx.gbl...
> Thanks for that link Keith, it had links to some other very useful
articles.
> It turned out to be fairly simple. When I was running
sp_change_users_login
> as sa, I was still in the master database. Changing to the new database
and
> then running:
> exec sp_change_users_login 'Auto_Fix', 'U'
> worked a treat !!
> Andy
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
> news:eLHBNzJKFHA.484@.TK2MSFTNGP15.phx.gbl...
restore[vbcol=seagreen]
get[vbcol=seagreen]
that[vbcol=seagreen]
login[vbcol=seagreen]
a[vbcol=seagreen]
on[vbcol=seagreen]
database[vbcol=seagreen]
I[vbcol=seagreen]
right[vbcol=seagreen]
to[vbcol=seagreen]
I[vbcol=seagreen]
>
restoring database onto a different machine
Hi,
Every time I have to copy a database from one machine to another I get stuck
in a problem with login IDs. Here's what happens:
On machine A I have a database called D. there is a user called U that maps
to a login called U (no NTLM). U owns all the tables in database D, and D is
the default database for U. On machine B there is also a login called U.
The machines are not on the same network so I backup the database to a flat
file and restore it onto machine B
Now if I log onto machine B as user U, I do not have permission to change
into the newly restored database D. I presume this is because user U on
machine B is different to user U on machine A, and the database is owned by
user U on machine A.
So I log into machine B as 'sa' and I try to add user U to the database D.
This fails because there is already a user called U on the database. I
cannot remove the existing user called U because it owns all the tables. So
it seems I am in a catch 22 situation.
I have tried sp_fix_users_login which looks like it should do the right
thing but to no avail.
The only way I can get working is create a new user called something other
than U, add this to the database, and then use sp_changeobjectowner to
change ownership of all the tables to the new user, which is a right PITA.
I have tried to keep it as simple as possible (only 1 user per database,
only 1 database per user, login name same as user name, etc etc) but I am
still stuck. Any ideas
TIA
Andy
I suspect that you mean sp_change_users_login not sp_fix_users_login. I
recommend that you have dbo own the database objects. You can grant the
appropriate rights on the tables (or stored procedures) to the other
database users.
Keith
"Andy Fish" <ajfish@.blueyonder.co.uk> wrote in message
news:uZx%237PJKFHA.3788@.tk2msftngp13.phx.gbl...
> Hi,
> Every time I have to copy a database from one machine to another I get
stuck
> in a problem with login IDs. Here's what happens:
> On machine A I have a database called D. there is a user called U that
maps
> to a login called U (no NTLM). U owns all the tables in database D, and D
is
> the default database for U. On machine B there is also a login called U.
> The machines are not on the same network so I backup the database to a
flat
> file and restore it onto machine B
> Now if I log onto machine B as user U, I do not have permission to change
> into the newly restored database D. I presume this is because user U on
> machine B is different to user U on machine A, and the database is owned
by
> user U on machine A.
> So I log into machine B as 'sa' and I try to add user U to the database D.
> This fails because there is already a user called U on the database. I
> cannot remove the existing user called U because it owns all the tables.
So
> it seems I am in a catch 22 situation.
> I have tried sp_fix_users_login which looks like it should do the right
> thing but to no avail.
> The only way I can get working is create a new user called something other
> than U, add this to the database, and then use sp_changeobjectowner to
> change ownership of all the tables to the new user, which is a right PITA.
> I have tried to keep it as simple as possible (only 1 user per database,
> only 1 database per user, login name same as user name, etc etc) but I am
> still stuck. Any ideas
> TIA
> Andy
>
|||There is a way to create a Login on one server with the GUID of another
login on another server.
This should get rid of the need to recreate the login everytime a restore is
done.
I cannot recollect how I used to do it as it is a year since I last worked
on DB administration.
You might want to check with BOL.
Gopi
"Andy Fish" <ajfish@.blueyonder.co.uk> wrote in message
news:uZx%237PJKFHA.3788@.tk2msftngp13.phx.gbl...
> Hi,
> Every time I have to copy a database from one machine to another I get
> stuck in a problem with login IDs. Here's what happens:
> On machine A I have a database called D. there is a user called U that
> maps to a login called U (no NTLM). U owns all the tables in database D,
> and D is the default database for U. On machine B there is also a login
> called U.
> The machines are not on the same network so I backup the database to a
> flat file and restore it onto machine B
> Now if I log onto machine B as user U, I do not have permission to change
> into the newly restored database D. I presume this is because user U on
> machine B is different to user U on machine A, and the database is owned
> by user U on machine A.
> So I log into machine B as 'sa' and I try to add user U to the database D.
> This fails because there is already a user called U on the database. I
> cannot remove the existing user called U because it owns all the tables.
> So it seems I am in a catch 22 situation.
> I have tried sp_fix_users_login which looks like it should do the right
> thing but to no avail.
> The only way I can get working is create a new user called something other
> than U, add this to the database, and then use sp_changeobjectowner to
> change ownership of all the tables to the new user, which is a right PITA.
> I have tried to keep it as simple as possible (only 1 user per database,
> only 1 database per user, login name same as user name, etc etc) but I am
> still stuck. Any ideas
> TIA
> Andy
>
|||HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://www.support.microsoft.com/?id=246133
Keith
"rgn" <gopinathr@.healthasyst.com> wrote in message
news:O5emurJKFHA.1096@.tk2msftngp13.phx.gbl...
> There is a way to create a Login on one server with the GUID of another
> login on another server.
> This should get rid of the need to recreate the login everytime a restore
is[vbcol=seagreen]
> done.
> I cannot recollect how I used to do it as it is a year since I last worked
> on DB administration.
> You might want to check with BOL.
> Gopi
>
> "Andy Fish" <ajfish@.blueyonder.co.uk> wrote in message
> news:uZx%237PJKFHA.3788@.tk2msftngp13.phx.gbl...
change[vbcol=seagreen]
D.[vbcol=seagreen]
other[vbcol=seagreen]
PITA.[vbcol=seagreen]
am
>
|||Thanks for that link Keith, it had links to some other very useful articles.
It turned out to be fairly simple. When I was running sp_change_users_login
as sa, I was still in the master database. Changing to the new database and
then running:
exec sp_change_users_login 'Auto_Fix', 'U'
worked a treat !!
Andy
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:eLHBNzJKFHA.484@.TK2MSFTNGP15.phx.gbl...
> HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
> http://www.support.microsoft.com/?id=246133
> --
> Keith
>
> "rgn" <gopinathr@.healthasyst.com> wrote in message
> news:O5emurJKFHA.1096@.tk2msftngp13.phx.gbl...
> is
> change
> D.
> other
> PITA.
> am
>
|||Excellent. I am glad that you figured out how to fix the issue. Going
forward if you create your logins based on the script of one server the SID
will match across all of your servers and that should allow you to restore
across servers and not have to execute sp_change_users_login at all.
Keith
"Andy Fish" <ajfish@.blueyonder.co.uk> wrote in message
news:O%23K2jgKKFHA.2920@.TK2MSFTNGP10.phx.gbl...
> Thanks for that link Keith, it had links to some other very useful
articles.
> It turned out to be fairly simple. When I was running
sp_change_users_login
> as sa, I was still in the master database. Changing to the new database
and[vbcol=seagreen]
> then running:
> exec sp_change_users_login 'Auto_Fix', 'U'
> worked a treat !!
> Andy
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
> news:eLHBNzJKFHA.484@.TK2MSFTNGP15.phx.gbl...
restore[vbcol=seagreen]
get[vbcol=seagreen]
that[vbcol=seagreen]
login[vbcol=seagreen]
a[vbcol=seagreen]
on[vbcol=seagreen]
database[vbcol=seagreen]
I[vbcol=seagreen]
right[vbcol=seagreen]
to[vbcol=seagreen]
I
>
Every time I have to copy a database from one machine to another I get stuck
in a problem with login IDs. Here's what happens:
On machine A I have a database called D. there is a user called U that maps
to a login called U (no NTLM). U owns all the tables in database D, and D is
the default database for U. On machine B there is also a login called U.
The machines are not on the same network so I backup the database to a flat
file and restore it onto machine B
Now if I log onto machine B as user U, I do not have permission to change
into the newly restored database D. I presume this is because user U on
machine B is different to user U on machine A, and the database is owned by
user U on machine A.
So I log into machine B as 'sa' and I try to add user U to the database D.
This fails because there is already a user called U on the database. I
cannot remove the existing user called U because it owns all the tables. So
it seems I am in a catch 22 situation.
I have tried sp_fix_users_login which looks like it should do the right
thing but to no avail.
The only way I can get working is create a new user called something other
than U, add this to the database, and then use sp_changeobjectowner to
change ownership of all the tables to the new user, which is a right PITA.
I have tried to keep it as simple as possible (only 1 user per database,
only 1 database per user, login name same as user name, etc etc) but I am
still stuck. Any ideas
TIA
Andy
I suspect that you mean sp_change_users_login not sp_fix_users_login. I
recommend that you have dbo own the database objects. You can grant the
appropriate rights on the tables (or stored procedures) to the other
database users.
Keith
"Andy Fish" <ajfish@.blueyonder.co.uk> wrote in message
news:uZx%237PJKFHA.3788@.tk2msftngp13.phx.gbl...
> Hi,
> Every time I have to copy a database from one machine to another I get
stuck
> in a problem with login IDs. Here's what happens:
> On machine A I have a database called D. there is a user called U that
maps
> to a login called U (no NTLM). U owns all the tables in database D, and D
is
> the default database for U. On machine B there is also a login called U.
> The machines are not on the same network so I backup the database to a
flat
> file and restore it onto machine B
> Now if I log onto machine B as user U, I do not have permission to change
> into the newly restored database D. I presume this is because user U on
> machine B is different to user U on machine A, and the database is owned
by
> user U on machine A.
> So I log into machine B as 'sa' and I try to add user U to the database D.
> This fails because there is already a user called U on the database. I
> cannot remove the existing user called U because it owns all the tables.
So
> it seems I am in a catch 22 situation.
> I have tried sp_fix_users_login which looks like it should do the right
> thing but to no avail.
> The only way I can get working is create a new user called something other
> than U, add this to the database, and then use sp_changeobjectowner to
> change ownership of all the tables to the new user, which is a right PITA.
> I have tried to keep it as simple as possible (only 1 user per database,
> only 1 database per user, login name same as user name, etc etc) but I am
> still stuck. Any ideas
> TIA
> Andy
>
|||There is a way to create a Login on one server with the GUID of another
login on another server.
This should get rid of the need to recreate the login everytime a restore is
done.
I cannot recollect how I used to do it as it is a year since I last worked
on DB administration.
You might want to check with BOL.
Gopi
"Andy Fish" <ajfish@.blueyonder.co.uk> wrote in message
news:uZx%237PJKFHA.3788@.tk2msftngp13.phx.gbl...
> Hi,
> Every time I have to copy a database from one machine to another I get
> stuck in a problem with login IDs. Here's what happens:
> On machine A I have a database called D. there is a user called U that
> maps to a login called U (no NTLM). U owns all the tables in database D,
> and D is the default database for U. On machine B there is also a login
> called U.
> The machines are not on the same network so I backup the database to a
> flat file and restore it onto machine B
> Now if I log onto machine B as user U, I do not have permission to change
> into the newly restored database D. I presume this is because user U on
> machine B is different to user U on machine A, and the database is owned
> by user U on machine A.
> So I log into machine B as 'sa' and I try to add user U to the database D.
> This fails because there is already a user called U on the database. I
> cannot remove the existing user called U because it owns all the tables.
> So it seems I am in a catch 22 situation.
> I have tried sp_fix_users_login which looks like it should do the right
> thing but to no avail.
> The only way I can get working is create a new user called something other
> than U, add this to the database, and then use sp_changeobjectowner to
> change ownership of all the tables to the new user, which is a right PITA.
> I have tried to keep it as simple as possible (only 1 user per database,
> only 1 database per user, login name same as user name, etc etc) but I am
> still stuck. Any ideas
> TIA
> Andy
>
|||HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://www.support.microsoft.com/?id=246133
Keith
"rgn" <gopinathr@.healthasyst.com> wrote in message
news:O5emurJKFHA.1096@.tk2msftngp13.phx.gbl...
> There is a way to create a Login on one server with the GUID of another
> login on another server.
> This should get rid of the need to recreate the login everytime a restore
is[vbcol=seagreen]
> done.
> I cannot recollect how I used to do it as it is a year since I last worked
> on DB administration.
> You might want to check with BOL.
> Gopi
>
> "Andy Fish" <ajfish@.blueyonder.co.uk> wrote in message
> news:uZx%237PJKFHA.3788@.tk2msftngp13.phx.gbl...
change[vbcol=seagreen]
D.[vbcol=seagreen]
other[vbcol=seagreen]
PITA.[vbcol=seagreen]
am
>
|||Thanks for that link Keith, it had links to some other very useful articles.
It turned out to be fairly simple. When I was running sp_change_users_login
as sa, I was still in the master database. Changing to the new database and
then running:
exec sp_change_users_login 'Auto_Fix', 'U'
worked a treat !!
Andy
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:eLHBNzJKFHA.484@.TK2MSFTNGP15.phx.gbl...
> HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
> http://www.support.microsoft.com/?id=246133
> --
> Keith
>
> "rgn" <gopinathr@.healthasyst.com> wrote in message
> news:O5emurJKFHA.1096@.tk2msftngp13.phx.gbl...
> is
> change
> D.
> other
> PITA.
> am
>
|||Excellent. I am glad that you figured out how to fix the issue. Going
forward if you create your logins based on the script of one server the SID
will match across all of your servers and that should allow you to restore
across servers and not have to execute sp_change_users_login at all.
Keith
"Andy Fish" <ajfish@.blueyonder.co.uk> wrote in message
news:O%23K2jgKKFHA.2920@.TK2MSFTNGP10.phx.gbl...
> Thanks for that link Keith, it had links to some other very useful
articles.
> It turned out to be fairly simple. When I was running
sp_change_users_login
> as sa, I was still in the master database. Changing to the new database
and[vbcol=seagreen]
> then running:
> exec sp_change_users_login 'Auto_Fix', 'U'
> worked a treat !!
> Andy
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
> news:eLHBNzJKFHA.484@.TK2MSFTNGP15.phx.gbl...
restore[vbcol=seagreen]
get[vbcol=seagreen]
that[vbcol=seagreen]
login[vbcol=seagreen]
a[vbcol=seagreen]
on[vbcol=seagreen]
database[vbcol=seagreen]
I[vbcol=seagreen]
right[vbcol=seagreen]
to[vbcol=seagreen]
I
>
restoring database onto a different machine
Hi,
Every time I have to copy a database from one machine to another I get stuck
in a problem with login IDs. Here's what happens:
On machine A I have a database called D. there is a user called U that maps
to a login called U (no NTLM). U owns all the tables in database D, and D is
the default database for U. On machine B there is also a login called U.
The machines are not on the same network so I backup the database to a flat
file and restore it onto machine B
Now if I log onto machine B as user U, I do not have permission to change
into the newly restored database D. I presume this is because user U on
machine B is different to user U on machine A, and the database is owned by
user U on machine A.
So I log into machine B as 'sa' and I try to add user U to the database D.
This fails because there is already a user called U on the database. I
cannot remove the existing user called U because it owns all the tables. So
it seems I am in a catch 22 situation.
I have tried sp_fix_users_login which looks like it should do the right
thing but to no avail.
The only way I can get working is create a new user called something other
than U, add this to the database, and then use sp_changeobjectowner to
change ownership of all the tables to the new user, which is a right PITA.
I have tried to keep it as simple as possible (only 1 user per database,
only 1 database per user, login name same as user name, etc etc) but I am
still stuck. Any ideas
TIA
AndyI suspect that you mean sp_change_users_login not sp_fix_users_login. I
recommend that you have dbo own the database objects. You can grant the
appropriate rights on the tables (or stored procedures) to the other
database users.
--
Keith
"Andy Fish" <ajfish@.blueyonder.co.uk> wrote in message
news:uZx%237PJKFHA.3788@.tk2msftngp13.phx.gbl...
> Hi,
> Every time I have to copy a database from one machine to another I get
stuck
> in a problem with login IDs. Here's what happens:
> On machine A I have a database called D. there is a user called U that
maps
> to a login called U (no NTLM). U owns all the tables in database D, and D
is
> the default database for U. On machine B there is also a login called U.
> The machines are not on the same network so I backup the database to a
flat
> file and restore it onto machine B
> Now if I log onto machine B as user U, I do not have permission to change
> into the newly restored database D. I presume this is because user U on
> machine B is different to user U on machine A, and the database is owned
by
> user U on machine A.
> So I log into machine B as 'sa' and I try to add user U to the database D.
> This fails because there is already a user called U on the database. I
> cannot remove the existing user called U because it owns all the tables.
So
> it seems I am in a catch 22 situation.
> I have tried sp_fix_users_login which looks like it should do the right
> thing but to no avail.
> The only way I can get working is create a new user called something other
> than U, add this to the database, and then use sp_changeobjectowner to
> change ownership of all the tables to the new user, which is a right PITA.
> I have tried to keep it as simple as possible (only 1 user per database,
> only 1 database per user, login name same as user name, etc etc) but I am
> still stuck. Any ideas
> TIA
> Andy
>|||There is a way to create a Login on one server with the GUID of another
login on another server.
This should get rid of the need to recreate the login everytime a restore is
done.
I cannot recollect how I used to do it as it is a year since I last worked
on DB administration.
You might want to check with BOL.
Gopi
"Andy Fish" <ajfish@.blueyonder.co.uk> wrote in message
news:uZx%237PJKFHA.3788@.tk2msftngp13.phx.gbl...
> Hi,
> Every time I have to copy a database from one machine to another I get
> stuck in a problem with login IDs. Here's what happens:
> On machine A I have a database called D. there is a user called U that
> maps to a login called U (no NTLM). U owns all the tables in database D,
> and D is the default database for U. On machine B there is also a login
> called U.
> The machines are not on the same network so I backup the database to a
> flat file and restore it onto machine B
> Now if I log onto machine B as user U, I do not have permission to change
> into the newly restored database D. I presume this is because user U on
> machine B is different to user U on machine A, and the database is owned
> by user U on machine A.
> So I log into machine B as 'sa' and I try to add user U to the database D.
> This fails because there is already a user called U on the database. I
> cannot remove the existing user called U because it owns all the tables.
> So it seems I am in a catch 22 situation.
> I have tried sp_fix_users_login which looks like it should do the right
> thing but to no avail.
> The only way I can get working is create a new user called something other
> than U, add this to the database, and then use sp_changeobjectowner to
> change ownership of all the tables to the new user, which is a right PITA.
> I have tried to keep it as simple as possible (only 1 user per database,
> only 1 database per user, login name same as user name, etc etc) but I am
> still stuck. Any ideas
> TIA
> Andy
>|||HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://www.support.microsoft.com/?id=246133
--
Keith
"rgn" <gopinathr@.healthasyst.com> wrote in message
news:O5emurJKFHA.1096@.tk2msftngp13.phx.gbl...
> There is a way to create a Login on one server with the GUID of another
> login on another server.
> This should get rid of the need to recreate the login everytime a restore
is
> done.
> I cannot recollect how I used to do it as it is a year since I last worked
> on DB administration.
> You might want to check with BOL.
> Gopi
>
> "Andy Fish" <ajfish@.blueyonder.co.uk> wrote in message
> news:uZx%237PJKFHA.3788@.tk2msftngp13.phx.gbl...
> > Hi,
> >
> > Every time I have to copy a database from one machine to another I get
> > stuck in a problem with login IDs. Here's what happens:
> >
> > On machine A I have a database called D. there is a user called U that
> > maps to a login called U (no NTLM). U owns all the tables in database D,
> > and D is the default database for U. On machine B there is also a login
> > called U.
> >
> > The machines are not on the same network so I backup the database to a
> > flat file and restore it onto machine B
> >
> > Now if I log onto machine B as user U, I do not have permission to
change
> > into the newly restored database D. I presume this is because user U on
> > machine B is different to user U on machine A, and the database is owned
> > by user U on machine A.
> >
> > So I log into machine B as 'sa' and I try to add user U to the database
D.
> > This fails because there is already a user called U on the database. I
> > cannot remove the existing user called U because it owns all the tables.
> > So it seems I am in a catch 22 situation.
> >
> > I have tried sp_fix_users_login which looks like it should do the right
> > thing but to no avail.
> >
> > The only way I can get working is create a new user called something
other
> > than U, add this to the database, and then use sp_changeobjectowner to
> > change ownership of all the tables to the new user, which is a right
PITA.
> >
> > I have tried to keep it as simple as possible (only 1 user per database,
> > only 1 database per user, login name same as user name, etc etc) but I
am
> > still stuck. Any ideas
> >
> > TIA
> >
> > Andy
> >
> >
>|||Thanks for that link Keith, it had links to some other very useful articles.
It turned out to be fairly simple. When I was running sp_change_users_login
as sa, I was still in the master database. Changing to the new database and
then running:
exec sp_change_users_login 'Auto_Fix', 'U'
worked a treat !!
Andy
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:eLHBNzJKFHA.484@.TK2MSFTNGP15.phx.gbl...
> HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
> http://www.support.microsoft.com/?id=246133
> --
> Keith
>
> "rgn" <gopinathr@.healthasyst.com> wrote in message
> news:O5emurJKFHA.1096@.tk2msftngp13.phx.gbl...
>> There is a way to create a Login on one server with the GUID of another
>> login on another server.
>> This should get rid of the need to recreate the login everytime a restore
> is
>> done.
>> I cannot recollect how I used to do it as it is a year since I last
>> worked
>> on DB administration.
>> You might want to check with BOL.
>> Gopi
>>
>> "Andy Fish" <ajfish@.blueyonder.co.uk> wrote in message
>> news:uZx%237PJKFHA.3788@.tk2msftngp13.phx.gbl...
>> > Hi,
>> >
>> > Every time I have to copy a database from one machine to another I get
>> > stuck in a problem with login IDs. Here's what happens:
>> >
>> > On machine A I have a database called D. there is a user called U that
>> > maps to a login called U (no NTLM). U owns all the tables in database
>> > D,
>> > and D is the default database for U. On machine B there is also a login
>> > called U.
>> >
>> > The machines are not on the same network so I backup the database to a
>> > flat file and restore it onto machine B
>> >
>> > Now if I log onto machine B as user U, I do not have permission to
> change
>> > into the newly restored database D. I presume this is because user U on
>> > machine B is different to user U on machine A, and the database is
>> > owned
>> > by user U on machine A.
>> >
>> > So I log into machine B as 'sa' and I try to add user U to the database
> D.
>> > This fails because there is already a user called U on the database. I
>> > cannot remove the existing user called U because it owns all the
>> > tables.
>> > So it seems I am in a catch 22 situation.
>> >
>> > I have tried sp_fix_users_login which looks like it should do the right
>> > thing but to no avail.
>> >
>> > The only way I can get working is create a new user called something
> other
>> > than U, add this to the database, and then use sp_changeobjectowner to
>> > change ownership of all the tables to the new user, which is a right
> PITA.
>> >
>> > I have tried to keep it as simple as possible (only 1 user per
>> > database,
>> > only 1 database per user, login name same as user name, etc etc) but I
> am
>> > still stuck. Any ideas
>> >
>> > TIA
>> >
>> > Andy
>> >
>> >
>>
>|||Excellent. I am glad that you figured out how to fix the issue. Going
forward if you create your logins based on the script of one server the SID
will match across all of your servers and that should allow you to restore
across servers and not have to execute sp_change_users_login at all.
--
Keith
"Andy Fish" <ajfish@.blueyonder.co.uk> wrote in message
news:O%23K2jgKKFHA.2920@.TK2MSFTNGP10.phx.gbl...
> Thanks for that link Keith, it had links to some other very useful
articles.
> It turned out to be fairly simple. When I was running
sp_change_users_login
> as sa, I was still in the master database. Changing to the new database
and
> then running:
> exec sp_change_users_login 'Auto_Fix', 'U'
> worked a treat !!
> Andy
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
> news:eLHBNzJKFHA.484@.TK2MSFTNGP15.phx.gbl...
> > HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
> > http://www.support.microsoft.com/?id=246133
> >
> > --
> > Keith
> >
> >
> > "rgn" <gopinathr@.healthasyst.com> wrote in message
> > news:O5emurJKFHA.1096@.tk2msftngp13.phx.gbl...
> >> There is a way to create a Login on one server with the GUID of another
> >> login on another server.
> >> This should get rid of the need to recreate the login everytime a
restore
> > is
> >> done.
> >>
> >> I cannot recollect how I used to do it as it is a year since I last
> >> worked
> >> on DB administration.
> >>
> >> You might want to check with BOL.
> >>
> >> Gopi
> >>
> >>
> >> "Andy Fish" <ajfish@.blueyonder.co.uk> wrote in message
> >> news:uZx%237PJKFHA.3788@.tk2msftngp13.phx.gbl...
> >> > Hi,
> >> >
> >> > Every time I have to copy a database from one machine to another I
get
> >> > stuck in a problem with login IDs. Here's what happens:
> >> >
> >> > On machine A I have a database called D. there is a user called U
that
> >> > maps to a login called U (no NTLM). U owns all the tables in database
> >> > D,
> >> > and D is the default database for U. On machine B there is also a
login
> >> > called U.
> >> >
> >> > The machines are not on the same network so I backup the database to
a
> >> > flat file and restore it onto machine B
> >> >
> >> > Now if I log onto machine B as user U, I do not have permission to
> > change
> >> > into the newly restored database D. I presume this is because user U
on
> >> > machine B is different to user U on machine A, and the database is
> >> > owned
> >> > by user U on machine A.
> >> >
> >> > So I log into machine B as 'sa' and I try to add user U to the
database
> > D.
> >> > This fails because there is already a user called U on the database.
I
> >> > cannot remove the existing user called U because it owns all the
> >> > tables.
> >> > So it seems I am in a catch 22 situation.
> >> >
> >> > I have tried sp_fix_users_login which looks like it should do the
right
> >> > thing but to no avail.
> >> >
> >> > The only way I can get working is create a new user called something
> > other
> >> > than U, add this to the database, and then use sp_changeobjectowner
to
> >> > change ownership of all the tables to the new user, which is a right
> > PITA.
> >> >
> >> > I have tried to keep it as simple as possible (only 1 user per
> >> > database,
> >> > only 1 database per user, login name same as user name, etc etc) but
I
> > am
> >> > still stuck. Any ideas
> >> >
> >> > TIA
> >> >
> >> > Andy
> >> >
> >> >
> >>
> >>
> >
>
Every time I have to copy a database from one machine to another I get stuck
in a problem with login IDs. Here's what happens:
On machine A I have a database called D. there is a user called U that maps
to a login called U (no NTLM). U owns all the tables in database D, and D is
the default database for U. On machine B there is also a login called U.
The machines are not on the same network so I backup the database to a flat
file and restore it onto machine B
Now if I log onto machine B as user U, I do not have permission to change
into the newly restored database D. I presume this is because user U on
machine B is different to user U on machine A, and the database is owned by
user U on machine A.
So I log into machine B as 'sa' and I try to add user U to the database D.
This fails because there is already a user called U on the database. I
cannot remove the existing user called U because it owns all the tables. So
it seems I am in a catch 22 situation.
I have tried sp_fix_users_login which looks like it should do the right
thing but to no avail.
The only way I can get working is create a new user called something other
than U, add this to the database, and then use sp_changeobjectowner to
change ownership of all the tables to the new user, which is a right PITA.
I have tried to keep it as simple as possible (only 1 user per database,
only 1 database per user, login name same as user name, etc etc) but I am
still stuck. Any ideas
TIA
AndyI suspect that you mean sp_change_users_login not sp_fix_users_login. I
recommend that you have dbo own the database objects. You can grant the
appropriate rights on the tables (or stored procedures) to the other
database users.
--
Keith
"Andy Fish" <ajfish@.blueyonder.co.uk> wrote in message
news:uZx%237PJKFHA.3788@.tk2msftngp13.phx.gbl...
> Hi,
> Every time I have to copy a database from one machine to another I get
stuck
> in a problem with login IDs. Here's what happens:
> On machine A I have a database called D. there is a user called U that
maps
> to a login called U (no NTLM). U owns all the tables in database D, and D
is
> the default database for U. On machine B there is also a login called U.
> The machines are not on the same network so I backup the database to a
flat
> file and restore it onto machine B
> Now if I log onto machine B as user U, I do not have permission to change
> into the newly restored database D. I presume this is because user U on
> machine B is different to user U on machine A, and the database is owned
by
> user U on machine A.
> So I log into machine B as 'sa' and I try to add user U to the database D.
> This fails because there is already a user called U on the database. I
> cannot remove the existing user called U because it owns all the tables.
So
> it seems I am in a catch 22 situation.
> I have tried sp_fix_users_login which looks like it should do the right
> thing but to no avail.
> The only way I can get working is create a new user called something other
> than U, add this to the database, and then use sp_changeobjectowner to
> change ownership of all the tables to the new user, which is a right PITA.
> I have tried to keep it as simple as possible (only 1 user per database,
> only 1 database per user, login name same as user name, etc etc) but I am
> still stuck. Any ideas
> TIA
> Andy
>|||There is a way to create a Login on one server with the GUID of another
login on another server.
This should get rid of the need to recreate the login everytime a restore is
done.
I cannot recollect how I used to do it as it is a year since I last worked
on DB administration.
You might want to check with BOL.
Gopi
"Andy Fish" <ajfish@.blueyonder.co.uk> wrote in message
news:uZx%237PJKFHA.3788@.tk2msftngp13.phx.gbl...
> Hi,
> Every time I have to copy a database from one machine to another I get
> stuck in a problem with login IDs. Here's what happens:
> On machine A I have a database called D. there is a user called U that
> maps to a login called U (no NTLM). U owns all the tables in database D,
> and D is the default database for U. On machine B there is also a login
> called U.
> The machines are not on the same network so I backup the database to a
> flat file and restore it onto machine B
> Now if I log onto machine B as user U, I do not have permission to change
> into the newly restored database D. I presume this is because user U on
> machine B is different to user U on machine A, and the database is owned
> by user U on machine A.
> So I log into machine B as 'sa' and I try to add user U to the database D.
> This fails because there is already a user called U on the database. I
> cannot remove the existing user called U because it owns all the tables.
> So it seems I am in a catch 22 situation.
> I have tried sp_fix_users_login which looks like it should do the right
> thing but to no avail.
> The only way I can get working is create a new user called something other
> than U, add this to the database, and then use sp_changeobjectowner to
> change ownership of all the tables to the new user, which is a right PITA.
> I have tried to keep it as simple as possible (only 1 user per database,
> only 1 database per user, login name same as user name, etc etc) but I am
> still stuck. Any ideas
> TIA
> Andy
>|||HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://www.support.microsoft.com/?id=246133
--
Keith
"rgn" <gopinathr@.healthasyst.com> wrote in message
news:O5emurJKFHA.1096@.tk2msftngp13.phx.gbl...
> There is a way to create a Login on one server with the GUID of another
> login on another server.
> This should get rid of the need to recreate the login everytime a restore
is
> done.
> I cannot recollect how I used to do it as it is a year since I last worked
> on DB administration.
> You might want to check with BOL.
> Gopi
>
> "Andy Fish" <ajfish@.blueyonder.co.uk> wrote in message
> news:uZx%237PJKFHA.3788@.tk2msftngp13.phx.gbl...
> > Hi,
> >
> > Every time I have to copy a database from one machine to another I get
> > stuck in a problem with login IDs. Here's what happens:
> >
> > On machine A I have a database called D. there is a user called U that
> > maps to a login called U (no NTLM). U owns all the tables in database D,
> > and D is the default database for U. On machine B there is also a login
> > called U.
> >
> > The machines are not on the same network so I backup the database to a
> > flat file and restore it onto machine B
> >
> > Now if I log onto machine B as user U, I do not have permission to
change
> > into the newly restored database D. I presume this is because user U on
> > machine B is different to user U on machine A, and the database is owned
> > by user U on machine A.
> >
> > So I log into machine B as 'sa' and I try to add user U to the database
D.
> > This fails because there is already a user called U on the database. I
> > cannot remove the existing user called U because it owns all the tables.
> > So it seems I am in a catch 22 situation.
> >
> > I have tried sp_fix_users_login which looks like it should do the right
> > thing but to no avail.
> >
> > The only way I can get working is create a new user called something
other
> > than U, add this to the database, and then use sp_changeobjectowner to
> > change ownership of all the tables to the new user, which is a right
PITA.
> >
> > I have tried to keep it as simple as possible (only 1 user per database,
> > only 1 database per user, login name same as user name, etc etc) but I
am
> > still stuck. Any ideas
> >
> > TIA
> >
> > Andy
> >
> >
>|||Thanks for that link Keith, it had links to some other very useful articles.
It turned out to be fairly simple. When I was running sp_change_users_login
as sa, I was still in the master database. Changing to the new database and
then running:
exec sp_change_users_login 'Auto_Fix', 'U'
worked a treat !!
Andy
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:eLHBNzJKFHA.484@.TK2MSFTNGP15.phx.gbl...
> HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
> http://www.support.microsoft.com/?id=246133
> --
> Keith
>
> "rgn" <gopinathr@.healthasyst.com> wrote in message
> news:O5emurJKFHA.1096@.tk2msftngp13.phx.gbl...
>> There is a way to create a Login on one server with the GUID of another
>> login on another server.
>> This should get rid of the need to recreate the login everytime a restore
> is
>> done.
>> I cannot recollect how I used to do it as it is a year since I last
>> worked
>> on DB administration.
>> You might want to check with BOL.
>> Gopi
>>
>> "Andy Fish" <ajfish@.blueyonder.co.uk> wrote in message
>> news:uZx%237PJKFHA.3788@.tk2msftngp13.phx.gbl...
>> > Hi,
>> >
>> > Every time I have to copy a database from one machine to another I get
>> > stuck in a problem with login IDs. Here's what happens:
>> >
>> > On machine A I have a database called D. there is a user called U that
>> > maps to a login called U (no NTLM). U owns all the tables in database
>> > D,
>> > and D is the default database for U. On machine B there is also a login
>> > called U.
>> >
>> > The machines are not on the same network so I backup the database to a
>> > flat file and restore it onto machine B
>> >
>> > Now if I log onto machine B as user U, I do not have permission to
> change
>> > into the newly restored database D. I presume this is because user U on
>> > machine B is different to user U on machine A, and the database is
>> > owned
>> > by user U on machine A.
>> >
>> > So I log into machine B as 'sa' and I try to add user U to the database
> D.
>> > This fails because there is already a user called U on the database. I
>> > cannot remove the existing user called U because it owns all the
>> > tables.
>> > So it seems I am in a catch 22 situation.
>> >
>> > I have tried sp_fix_users_login which looks like it should do the right
>> > thing but to no avail.
>> >
>> > The only way I can get working is create a new user called something
> other
>> > than U, add this to the database, and then use sp_changeobjectowner to
>> > change ownership of all the tables to the new user, which is a right
> PITA.
>> >
>> > I have tried to keep it as simple as possible (only 1 user per
>> > database,
>> > only 1 database per user, login name same as user name, etc etc) but I
> am
>> > still stuck. Any ideas
>> >
>> > TIA
>> >
>> > Andy
>> >
>> >
>>
>|||Excellent. I am glad that you figured out how to fix the issue. Going
forward if you create your logins based on the script of one server the SID
will match across all of your servers and that should allow you to restore
across servers and not have to execute sp_change_users_login at all.
--
Keith
"Andy Fish" <ajfish@.blueyonder.co.uk> wrote in message
news:O%23K2jgKKFHA.2920@.TK2MSFTNGP10.phx.gbl...
> Thanks for that link Keith, it had links to some other very useful
articles.
> It turned out to be fairly simple. When I was running
sp_change_users_login
> as sa, I was still in the master database. Changing to the new database
and
> then running:
> exec sp_change_users_login 'Auto_Fix', 'U'
> worked a treat !!
> Andy
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
> news:eLHBNzJKFHA.484@.TK2MSFTNGP15.phx.gbl...
> > HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
> > http://www.support.microsoft.com/?id=246133
> >
> > --
> > Keith
> >
> >
> > "rgn" <gopinathr@.healthasyst.com> wrote in message
> > news:O5emurJKFHA.1096@.tk2msftngp13.phx.gbl...
> >> There is a way to create a Login on one server with the GUID of another
> >> login on another server.
> >> This should get rid of the need to recreate the login everytime a
restore
> > is
> >> done.
> >>
> >> I cannot recollect how I used to do it as it is a year since I last
> >> worked
> >> on DB administration.
> >>
> >> You might want to check with BOL.
> >>
> >> Gopi
> >>
> >>
> >> "Andy Fish" <ajfish@.blueyonder.co.uk> wrote in message
> >> news:uZx%237PJKFHA.3788@.tk2msftngp13.phx.gbl...
> >> > Hi,
> >> >
> >> > Every time I have to copy a database from one machine to another I
get
> >> > stuck in a problem with login IDs. Here's what happens:
> >> >
> >> > On machine A I have a database called D. there is a user called U
that
> >> > maps to a login called U (no NTLM). U owns all the tables in database
> >> > D,
> >> > and D is the default database for U. On machine B there is also a
login
> >> > called U.
> >> >
> >> > The machines are not on the same network so I backup the database to
a
> >> > flat file and restore it onto machine B
> >> >
> >> > Now if I log onto machine B as user U, I do not have permission to
> > change
> >> > into the newly restored database D. I presume this is because user U
on
> >> > machine B is different to user U on machine A, and the database is
> >> > owned
> >> > by user U on machine A.
> >> >
> >> > So I log into machine B as 'sa' and I try to add user U to the
database
> > D.
> >> > This fails because there is already a user called U on the database.
I
> >> > cannot remove the existing user called U because it owns all the
> >> > tables.
> >> > So it seems I am in a catch 22 situation.
> >> >
> >> > I have tried sp_fix_users_login which looks like it should do the
right
> >> > thing but to no avail.
> >> >
> >> > The only way I can get working is create a new user called something
> > other
> >> > than U, add this to the database, and then use sp_changeobjectowner
to
> >> > change ownership of all the tables to the new user, which is a right
> > PITA.
> >> >
> >> > I have tried to keep it as simple as possible (only 1 user per
> >> > database,
> >> > only 1 database per user, login name same as user name, etc etc) but
I
> > am
> >> > still stuck. Any ideas
> >> >
> >> > TIA
> >> >
> >> > Andy
> >> >
> >> >
> >>
> >>
> >
>
Subscribe to:
Posts (Atom)