I finally figured out how to do this when the source server has its master,
model, msdb, and tempdb databases in a different location than the target
server.
You may have to interpret some of the file names and locations here:
If you are going to restore system databases from a source who’s data
directory path is different from this computer, use the following procedure:
1.WARNING you must be able to detach and reattach the model and msdb
databases, therefore, you should:
oOpen enterprise manager
oRestore the model database from the source server
oRestore the msdb database from the source server
2.Stop the MSSQL service.
3.Go to Start menu and open a command prompt.
4.Change to directory C:\Program files\ Microsoft SQL Server\MSSQL\binn
5.Start sql server with: sqlservr –m –c -f (this brings the system up in
Single User Mode; if you add -T3608 (don’t use a lowercase t) so SQL Server
does not recover any database except the master database, the restore
database master command will complain about not having a tempdb).
6.Go to Start menu and open a second command prompt.
7.Change to directory where the backup files are (e.g. E:\ MSSQL\BACKUP).
8.Type (case sensitive): osql –S serverName -E
oThis logs in to the specified server and logs in with the Windows user
currently logged in.
oYou should see a command prompt that looks like: >1
9.Type:
restore database master
from disk='e:\mssql\backup\from_prod\master_bk.bak'
with move 'master' to 'e:\mssql\data\master.mdf', move 'mastlog' to
'e:\mssql\data\mastlog.ldf'
(the ‘with move’ part is necessary because the location of the files is
changing)
go
oWhen that completes, both the osql session and the sqlservr session will
be shut down. You should see:
The master database has been successfully restored. Shutting down SQL Server
SQL Server is terminating this process.
10.to move the model database, start sql server with sqlservr -m -c -f -T3608
11.in the other window, run osql –S serverName –E again and type
use master
go
sp_detach_db 'model'
go
sp_attach_single_file_db 'model','e:\mssql\data\model.mdf'
go
sp_detach_db 'msdb'
go
exit
12.Stop the server with Ctrl+C
13.Restart the server with sqlservr -m -c –f
14.in the other window, run osql –S serverName –E again and type
use master
go
sp_attach_single_file_db 'msdb','e:\mssql\data\msdbdata.mdf'
go
Alter database tempdb modify file (name = tempdev, filename =
'E:\MSSQL\Datatempdb.mdf')
go
{you will see: File 'tempdev' modified in sysaltfiles. Delete old file after
restarting SQLServer.}
Alter database tempdb modify file (name = templog, filename =
'E:\MSSQL\Datatemplog.ldf')
go
{you will see: File 'templog' modified in sysaltfiles. Delete old file after
restarting SQL Server.}
exit
oyou will not actually have to delete any files because they are already in
the correct place. The master database just had to be modified so that it
would know that.
15.Stop the server with Ctrl+C
oYou should now be able to restart the sql cluster resources (or the sql
server service if not using a cluster)
16.start the normal sql service and test by opening Enterprise Manager and
connecting to the server. If it hangs up, you might just have to restart the
service again.
oNote: On a fresh restore the user databases will be listed as Suspect.
You must now restore each of the user databases to the correct file location.
This can easily be done with Enterprise Manager (assuming it doesn’t hang up
or take forever for the restore database dialog box to come up, which usually
is the case, actually).
I forgot to mention that if the server name is different, you are going to
have problems with your scheduled jobs. Run this script to fix:
'replace the newName with the name of the new server and the oldName with
the name of the old server.
use msdb
update sysjobs
set sysjobs.originating_server = 'newName'
where sysjobs.originating_server = 'oldName'
select * from msdb..sysjobs order by name --to verify
Oh, and by the way, your backup "devices" will still be set to the same
location and thus will need to be recreated.
Showing posts with label location. Show all posts
Showing posts with label location. Show all posts
Friday, March 23, 2012
restore system databases to server with different/alternate data p
I finally figured out how to do this when the source server has its master,
model, msdb, and tempdb databases in a different location than the target
server.
You may have to interpret some of the file names and locations here:
If you are going to restore system databases from a source who’s data
directory path is different from this computer, use the following procedure:
1. WARNING you must be able to detach and reattach the model and msdb
databases, therefore, you should:
o Open enterprise manager
o Restore the model database from the source server
o Restore the msdb database from the source server
2. Stop the MSSQL service.
3. Go to Start menu and open a command prompt.
4. Change to directory C:\Program files\ Microsoft SQL Server\MSSQL\binn
5. Start sql server with: sqlservr –m –c -f (this brings the system up i
n
Single User Mode; if you add -T3608 (don’t use a lowercase t) so SQL Serve
r
does not recover any database except the master database, the restore
database master command will complain about not having a tempdb).
6. Go to Start menu and open a second command prompt.
7. Change to directory where the backup files are (e.g. E:\ MSSQL\BACKUP).
8. Type (case sensitive): osql –S serverName -E
o This logs in to the specified server and logs in with the Windows user
currently logged in.
o You should see a command prompt that looks like: >1
9. Type:
restore database master
from disk='e:\mssql\backup\from_prod\master_b
k.bak'
with move 'master' to 'e:\mssql\data\master.mdf', move 'mastlog' to
'e:\mssql\data\mastlog.ldf'
(the ‘with move’ part is necessary because the location of the files is
changing)
go
o When that completes, both the osql session and the sqlservr session will
be shut down. You should see:
The master database has been successfully restored. Shutting down SQL Server
SQL Server is terminating this process.
10. to move the model database, start sql server with sqlservr -m -c -f -T36
08
11. in the other window, run osql –S serverName –E again and type
use master
go
sp_detach_db 'model'
go
sp_attach_single_file_db 'model','e:\mssql\data\model.mdf'
go
sp_detach_db 'msdb'
go
exit
12. Stop the server with Ctrl+C
13. Restart the server with sqlservr -m -c –f
14. in the other window, run osql –S serverName –E again and type
use master
go
sp_attach_single_file_db 'msdb','e:\mssql\data\msdbdata.mdf'
go
Alter database tempdb modify file (name = tempdev, filename =
'E:\MSSQL\Datatempdb.mdf')
go
{you will see: File 'tempdev' modified in sysaltfiles. Delete old file
after
restarting SQLServer.}
Alter database tempdb modify file (name = templog, filename =
'E:\MSSQL\Datatemplog.ldf')
go
{you will see: File 'templog' modified in sysaltfiles. Delete old file
after
restarting SQL Server.}
exit
o you will not actually have to delete any files because they are already in
the correct place. The master database just had to be modified so that it
would know that.
15. Stop the server with Ctrl+C
o You should now be able to restart the sql cluster resources (or the sql
server service if not using a cluster)
16. start the normal sql service and test by opening Enterprise Manager and
connecting to the server. If it hangs up, you might just have to restart the
service again.
o Note: On a fresh restore the user databases will be listed as Suspect.
You must now restore each of the user databases to the correct file location
.
This can easily be done with Enterprise Manager (assuming it doesn’t hang
up
or take forever for the restore database dialog box to come up, which usuall
y
is the case, actually).I forgot to mention that if the server name is different, you are going to
have problems with your scheduled jobs. Run this script to fix:
'replace the newName with the name of the new server and the oldName with
the name of the old server.
use msdb
update sysjobs
set sysjobs.originating_server = 'newName'
where sysjobs.originating_server = 'oldName'
select * from msdb..sysjobs order by name --to verify
Oh, and by the way, your backup "devices" will still be set to the same
location and thus will need to be recreated.
model, msdb, and tempdb databases in a different location than the target
server.
You may have to interpret some of the file names and locations here:
If you are going to restore system databases from a source who’s data
directory path is different from this computer, use the following procedure:
1. WARNING you must be able to detach and reattach the model and msdb
databases, therefore, you should:
o Open enterprise manager
o Restore the model database from the source server
o Restore the msdb database from the source server
2. Stop the MSSQL service.
3. Go to Start menu and open a command prompt.
4. Change to directory C:\Program files\ Microsoft SQL Server\MSSQL\binn
5. Start sql server with: sqlservr –m –c -f (this brings the system up i
n
Single User Mode; if you add -T3608 (don’t use a lowercase t) so SQL Serve
r
does not recover any database except the master database, the restore
database master command will complain about not having a tempdb).
6. Go to Start menu and open a second command prompt.
7. Change to directory where the backup files are (e.g. E:\ MSSQL\BACKUP).
8. Type (case sensitive): osql –S serverName -E
o This logs in to the specified server and logs in with the Windows user
currently logged in.
o You should see a command prompt that looks like: >1
9. Type:
restore database master
from disk='e:\mssql\backup\from_prod\master_b
k.bak'
with move 'master' to 'e:\mssql\data\master.mdf', move 'mastlog' to
'e:\mssql\data\mastlog.ldf'
(the ‘with move’ part is necessary because the location of the files is
changing)
go
o When that completes, both the osql session and the sqlservr session will
be shut down. You should see:
The master database has been successfully restored. Shutting down SQL Server
SQL Server is terminating this process.
10. to move the model database, start sql server with sqlservr -m -c -f -T36
08
11. in the other window, run osql –S serverName –E again and type
use master
go
sp_detach_db 'model'
go
sp_attach_single_file_db 'model','e:\mssql\data\model.mdf'
go
sp_detach_db 'msdb'
go
exit
12. Stop the server with Ctrl+C
13. Restart the server with sqlservr -m -c –f
14. in the other window, run osql –S serverName –E again and type
use master
go
sp_attach_single_file_db 'msdb','e:\mssql\data\msdbdata.mdf'
go
Alter database tempdb modify file (name = tempdev, filename =
'E:\MSSQL\Datatempdb.mdf')
go
{you will see: File 'tempdev' modified in sysaltfiles. Delete old file
after
restarting SQLServer.}
Alter database tempdb modify file (name = templog, filename =
'E:\MSSQL\Datatemplog.ldf')
go
{you will see: File 'templog' modified in sysaltfiles. Delete old file
after
restarting SQL Server.}
exit
o you will not actually have to delete any files because they are already in
the correct place. The master database just had to be modified so that it
would know that.
15. Stop the server with Ctrl+C
o You should now be able to restart the sql cluster resources (or the sql
server service if not using a cluster)
16. start the normal sql service and test by opening Enterprise Manager and
connecting to the server. If it hangs up, you might just have to restart the
service again.
o Note: On a fresh restore the user databases will be listed as Suspect.
You must now restore each of the user databases to the correct file location
.
This can easily be done with Enterprise Manager (assuming it doesn’t hang
up
or take forever for the restore database dialog box to come up, which usuall
y
is the case, actually).I forgot to mention that if the server name is different, you are going to
have problems with your scheduled jobs. Run this script to fix:
'replace the newName with the name of the new server and the oldName with
the name of the old server.
use msdb
update sysjobs
set sysjobs.originating_server = 'newName'
where sysjobs.originating_server = 'oldName'
select * from msdb..sysjobs order by name --to verify
Oh, and by the way, your backup "devices" will still be set to the same
location and thus will need to be recreated.
restore system databases to server with different/alternate data p
I finally figured out how to do this when the source server has its master,
model, msdb, and tempdb databases in a different location than the target
server.
You may have to interpret some of the file names and locations here:
If you are going to restore system databases from a source whoâ's data
directory path is different from this computer, use the following procedure:
1. WARNING you must be able to detach and reattach the model and msdb
databases, therefore, you should:
o Open enterprise manager
o Restore the model database from the source server
o Restore the msdb database from the source server
2. Stop the MSSQL service.
3. Go to Start menu and open a command prompt.
4. Change to directory C:\Program files\ Microsoft SQL Server\MSSQL\binn
5. Start sql server with: sqlservr â'm â'c -f (this brings the system up in
Single User Mode; if you add -T3608 (donâ't use a lowercase t) so SQL Server
does not recover any database except the master database, the restore
database master command will complain about not having a tempdb).
6. Go to Start menu and open a second command prompt.
7. Change to directory where the backup files are (e.g. E:\ MSSQL\BACKUP).
8. Type (case sensitive): osql â'S serverName -E
o This logs in to the specified server and logs in with the Windows user
currently logged in.
o You should see a command prompt that looks like: >1
9. Type:
restore database master
from disk='e:\mssql\backup\from_prod\master_bk.bak'
with move 'master' to 'e:\mssql\data\master.mdf', move 'mastlog' to
'e:\mssql\data\mastlog.ldf'
(the â'with moveâ' part is necessary because the location of the files is
changing)
go
o When that completes, both the osql session and the sqlservr session will
be shut down. You should see:
The master database has been successfully restored. Shutting down SQL Server
SQL Server is terminating this process.
10. to move the model database, start sql server with sqlservr -m -c -f -T3608
11. in the other window, run osql â'S serverName â'E again and type
use master
go
sp_detach_db 'model'
go
sp_attach_single_file_db 'model','e:\mssql\data\model.mdf'
go
sp_detach_db 'msdb'
go
exit
12. Stop the server with Ctrl+C
13. Restart the server with sqlservr -m -c â'f
14. in the other window, run osql â'S serverName â'E again and type
use master
go
sp_attach_single_file_db 'msdb','e:\mssql\data\msdbdata.mdf'
go
Alter database tempdb modify file (name = tempdev, filename = 'E:\MSSQL\Datatempdb.mdf')
go
{you will see: File 'tempdev' modified in sysaltfiles. Delete old file after
restarting SQLServer.}
Alter database tempdb modify file (name = templog, filename = 'E:\MSSQL\Datatemplog.ldf')
go
{you will see: File 'templog' modified in sysaltfiles. Delete old file after
restarting SQL Server.}
exit
o you will not actually have to delete any files because they are already in
the correct place. The master database just had to be modified so that it
would know that.
15. Stop the server with Ctrl+C
o You should now be able to restart the sql cluster resources (or the sql
server service if not using a cluster)
16. start the normal sql service and test by opening Enterprise Manager and
connecting to the server. If it hangs up, you might just have to restart the
service again.
o Note: On a fresh restore the user databases will be listed as Suspect.
You must now restore each of the user databases to the correct file location.
This can easily be done with Enterprise Manager (assuming it doesnâ't hang up
or take forever for the restore database dialog box to come up, which usually
is the case, actually).I forgot to mention that if the server name is different, you are going to
have problems with your scheduled jobs. Run this script to fix:
'replace the newName with the name of the new server and the oldName with
the name of the old server.
use msdb
update sysjobs
set sysjobs.originating_server = 'newName'
where sysjobs.originating_server = 'oldName'
select * from msdb..sysjobs order by name --to verify
Oh, and by the way, your backup "devices" will still be set to the same
location and thus will need to be recreated.
model, msdb, and tempdb databases in a different location than the target
server.
You may have to interpret some of the file names and locations here:
If you are going to restore system databases from a source whoâ's data
directory path is different from this computer, use the following procedure:
1. WARNING you must be able to detach and reattach the model and msdb
databases, therefore, you should:
o Open enterprise manager
o Restore the model database from the source server
o Restore the msdb database from the source server
2. Stop the MSSQL service.
3. Go to Start menu and open a command prompt.
4. Change to directory C:\Program files\ Microsoft SQL Server\MSSQL\binn
5. Start sql server with: sqlservr â'm â'c -f (this brings the system up in
Single User Mode; if you add -T3608 (donâ't use a lowercase t) so SQL Server
does not recover any database except the master database, the restore
database master command will complain about not having a tempdb).
6. Go to Start menu and open a second command prompt.
7. Change to directory where the backup files are (e.g. E:\ MSSQL\BACKUP).
8. Type (case sensitive): osql â'S serverName -E
o This logs in to the specified server and logs in with the Windows user
currently logged in.
o You should see a command prompt that looks like: >1
9. Type:
restore database master
from disk='e:\mssql\backup\from_prod\master_bk.bak'
with move 'master' to 'e:\mssql\data\master.mdf', move 'mastlog' to
'e:\mssql\data\mastlog.ldf'
(the â'with moveâ' part is necessary because the location of the files is
changing)
go
o When that completes, both the osql session and the sqlservr session will
be shut down. You should see:
The master database has been successfully restored. Shutting down SQL Server
SQL Server is terminating this process.
10. to move the model database, start sql server with sqlservr -m -c -f -T3608
11. in the other window, run osql â'S serverName â'E again and type
use master
go
sp_detach_db 'model'
go
sp_attach_single_file_db 'model','e:\mssql\data\model.mdf'
go
sp_detach_db 'msdb'
go
exit
12. Stop the server with Ctrl+C
13. Restart the server with sqlservr -m -c â'f
14. in the other window, run osql â'S serverName â'E again and type
use master
go
sp_attach_single_file_db 'msdb','e:\mssql\data\msdbdata.mdf'
go
Alter database tempdb modify file (name = tempdev, filename = 'E:\MSSQL\Datatempdb.mdf')
go
{you will see: File 'tempdev' modified in sysaltfiles. Delete old file after
restarting SQLServer.}
Alter database tempdb modify file (name = templog, filename = 'E:\MSSQL\Datatemplog.ldf')
go
{you will see: File 'templog' modified in sysaltfiles. Delete old file after
restarting SQL Server.}
exit
o you will not actually have to delete any files because they are already in
the correct place. The master database just had to be modified so that it
would know that.
15. Stop the server with Ctrl+C
o You should now be able to restart the sql cluster resources (or the sql
server service if not using a cluster)
16. start the normal sql service and test by opening Enterprise Manager and
connecting to the server. If it hangs up, you might just have to restart the
service again.
o Note: On a fresh restore the user databases will be listed as Suspect.
You must now restore each of the user databases to the correct file location.
This can easily be done with Enterprise Manager (assuming it doesnâ't hang up
or take forever for the restore database dialog box to come up, which usually
is the case, actually).I forgot to mention that if the server name is different, you are going to
have problems with your scheduled jobs. Run this script to fix:
'replace the newName with the name of the new server and the oldName with
the name of the old server.
use msdb
update sysjobs
set sysjobs.originating_server = 'newName'
where sysjobs.originating_server = 'oldName'
select * from msdb..sysjobs order by name --to verify
Oh, and by the way, your backup "devices" will still be set to the same
location and thus will need to be recreated.
Wednesday, March 7, 2012
Restore new server different database name not working
I have a database on my new server:
tempFTS
From Properties:
FileName: tempFTS_Data Location: C:\Program Files\Microsoft Sql
Server\MSSQL\Data\tempFTS_DATA.MDF
Log: tempFTS_Log Location: C:\Program Files\Microsoft
Sql Server\MSSQL\Data\tempFTS_LOG.LDF
Backup from old Server:
FTS_db_200510180200.BAK
Right-Click tempFTS database and choose Restore Database
Select From Device
Select "Select Devices..."
Select Add
Double click on FTS_db_200510180200.BAK
Select OK
Go to Options and Displayed:
FTS_log d:\Microsoft SQL Server\MSSQL\data\FTS.ldf
FTS_dat d:\Microsoft SQL Server\MSSQL\data\FTS.mdf
This is where it originally was.
I now want to change the name to tempFTS
So I change the Logical fileNames to (AS shown above from the properties of
tempFTS:
tempFTS_Log
tempFTS_Data
I change the Physical names to where I want the new files to be located
C:\Program Files\Microsoft Sql Server\MSSQL\Data\tempFTS_DATA.MDF
C:\Program Files\Microsoft Sql Server\MSSQL\Data\tempFTS_LOG.LDF
Force Restore (just in case)
I now get the error:
Logical file 'tempFTS_Log', not part of the database "tempFTS".
I then tried to put the Logical names back to FTS_log and FTS_dat to see
what would happen there:
Now it tries to overwrite the FTS_Data.mdf, which is the old database that I
am restoring, but I want to put into TempFTS first to look at a couple of
tables.
What is the point of changing the Physical file names, if it is not going to
use them?
What did I do wrong?
Thanks,
Tom
Ok,
I got it to work.
This time it worked correctly. I am not sure why it wasn't working before
but I had to leave the Logical File Names the same as it found on the device
and change the Physical name to the folder and name that were in the new
Database (tempFTS).
It is just confusing in the way the window is laid out.
Where it says
Restore Database as:
Logical File Name --> really should say something like "Logical File Name
From Device". That was what was confusing. I thought I tried that before
and it didn't work.
So the left column (Logical File Name) is from the Device and the Move to
Physical File is where your new Database is pointing at (I think).
Tom
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:OAkV7%23D1FHA.3820@.TK2MSFTNGP10.phx.gbl...
>I have a database on my new server:
> tempFTS
> From Properties:
> FileName: tempFTS_Data Location: C:\Program Files\Microsoft Sql
> Server\MSSQL\Data\tempFTS_DATA.MDF
> Log: tempFTS_Log Location: C:\Program Files\Microsoft
> Sql Server\MSSQL\Data\tempFTS_LOG.LDF
> Backup from old Server:
> FTS_db_200510180200.BAK
> Right-Click tempFTS database and choose Restore Database
> Select From Device
> Select "Select Devices..."
> Select Add
> Double click on FTS_db_200510180200.BAK
> Select OK
> Go to Options and Displayed:
> FTS_log d:\Microsoft SQL Server\MSSQL\data\FTS.ldf
> FTS_dat d:\Microsoft SQL Server\MSSQL\data\FTS.mdf
> This is where it originally was.
> I now want to change the name to tempFTS
> So I change the Logical fileNames to (AS shown above from the properties
> of tempFTS:
> tempFTS_Log
> tempFTS_Data
> I change the Physical names to where I want the new files to be located
> C:\Program Files\Microsoft Sql Server\MSSQL\Data\tempFTS_DATA.MDF
> C:\Program Files\Microsoft Sql Server\MSSQL\Data\tempFTS_LOG.LDF
> Force Restore (just in case)
> I now get the error:
> Logical file 'tempFTS_Log', not part of the database "tempFTS".
> I then tried to put the Logical names back to FTS_log and FTS_dat to see
> what would happen there:
> Now it tries to overwrite the FTS_Data.mdf, which is the old database that
> I am restoring, but I want to put into TempFTS first to look at a couple
> of tables.
> What is the point of changing the Physical file names, if it is not going
> to use them?
> What did I do wrong?
> Thanks,
> Tom
>
tempFTS
From Properties:
FileName: tempFTS_Data Location: C:\Program Files\Microsoft Sql
Server\MSSQL\Data\tempFTS_DATA.MDF
Log: tempFTS_Log Location: C:\Program Files\Microsoft
Sql Server\MSSQL\Data\tempFTS_LOG.LDF
Backup from old Server:
FTS_db_200510180200.BAK
Right-Click tempFTS database and choose Restore Database
Select From Device
Select "Select Devices..."
Select Add
Double click on FTS_db_200510180200.BAK
Select OK
Go to Options and Displayed:
FTS_log d:\Microsoft SQL Server\MSSQL\data\FTS.ldf
FTS_dat d:\Microsoft SQL Server\MSSQL\data\FTS.mdf
This is where it originally was.
I now want to change the name to tempFTS
So I change the Logical fileNames to (AS shown above from the properties of
tempFTS:
tempFTS_Log
tempFTS_Data
I change the Physical names to where I want the new files to be located
C:\Program Files\Microsoft Sql Server\MSSQL\Data\tempFTS_DATA.MDF
C:\Program Files\Microsoft Sql Server\MSSQL\Data\tempFTS_LOG.LDF
Force Restore (just in case)
I now get the error:
Logical file 'tempFTS_Log', not part of the database "tempFTS".
I then tried to put the Logical names back to FTS_log and FTS_dat to see
what would happen there:
Now it tries to overwrite the FTS_Data.mdf, which is the old database that I
am restoring, but I want to put into TempFTS first to look at a couple of
tables.
What is the point of changing the Physical file names, if it is not going to
use them?
What did I do wrong?
Thanks,
Tom
Ok,
I got it to work.
This time it worked correctly. I am not sure why it wasn't working before
but I had to leave the Logical File Names the same as it found on the device
and change the Physical name to the folder and name that were in the new
Database (tempFTS).
It is just confusing in the way the window is laid out.
Where it says
Restore Database as:
Logical File Name --> really should say something like "Logical File Name
From Device". That was what was confusing. I thought I tried that before
and it didn't work.
So the left column (Logical File Name) is from the Device and the Move to
Physical File is where your new Database is pointing at (I think).
Tom
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:OAkV7%23D1FHA.3820@.TK2MSFTNGP10.phx.gbl...
>I have a database on my new server:
> tempFTS
> From Properties:
> FileName: tempFTS_Data Location: C:\Program Files\Microsoft Sql
> Server\MSSQL\Data\tempFTS_DATA.MDF
> Log: tempFTS_Log Location: C:\Program Files\Microsoft
> Sql Server\MSSQL\Data\tempFTS_LOG.LDF
> Backup from old Server:
> FTS_db_200510180200.BAK
> Right-Click tempFTS database and choose Restore Database
> Select From Device
> Select "Select Devices..."
> Select Add
> Double click on FTS_db_200510180200.BAK
> Select OK
> Go to Options and Displayed:
> FTS_log d:\Microsoft SQL Server\MSSQL\data\FTS.ldf
> FTS_dat d:\Microsoft SQL Server\MSSQL\data\FTS.mdf
> This is where it originally was.
> I now want to change the name to tempFTS
> So I change the Logical fileNames to (AS shown above from the properties
> of tempFTS:
> tempFTS_Log
> tempFTS_Data
> I change the Physical names to where I want the new files to be located
> C:\Program Files\Microsoft Sql Server\MSSQL\Data\tempFTS_DATA.MDF
> C:\Program Files\Microsoft Sql Server\MSSQL\Data\tempFTS_LOG.LDF
> Force Restore (just in case)
> I now get the error:
> Logical file 'tempFTS_Log', not part of the database "tempFTS".
> I then tried to put the Logical names back to FTS_log and FTS_dat to see
> what would happen there:
> Now it tries to overwrite the FTS_Data.mdf, which is the old database that
> I am restoring, but I want to put into TempFTS first to look at a couple
> of tables.
> What is the point of changing the Physical file names, if it is not going
> to use them?
> What did I do wrong?
> Thanks,
> Tom
>
Restore new server different database name not working
I have a database on my new server:
tempFTS
From Properties:
FileName: tempFTS_Data Location: C:\Program Files\Microsoft Sql
Server\MSSQL\Data\tempFTS_DATA.MDF
Log: tempFTS_Log Location: C:\Program Files\Microsoft
Sql Server\MSSQL\Data\tempFTS_LOG.LDF
Backup from old Server:
FTS_db_200510180200.BAK
Right-Click tempFTS database and choose Restore Database
Select From Device
Select "Select Devices..."
Select Add
Double click on FTS_db_200510180200.BAK
Select OK
Go to Options and Displayed:
FTS_log d:\Microsoft SQL Server\MSSQL\data\FTS.ldf
FTS_dat d:\Microsoft SQL Server\MSSQL\data\FTS.mdf
This is where it originally was.
I now want to change the name to tempFTS
So I change the Logical fileNames to (AS shown above from the properties of
tempFTS:
tempFTS_Log
tempFTS_Data
I change the Physical names to where I want the new files to be located
C:\Program Files\Microsoft Sql Server\MSSQL\Data\tempFTS_DATA.MDF
C:\Program Files\Microsoft Sql Server\MSSQL\Data\tempFTS_LOG.LDF
Force Restore (just in case)
I now get the error:
Logical file 'tempFTS_Log', not part of the database "tempFTS".
I then tried to put the Logical names back to FTS_log and FTS_dat to see
what would happen there:
Now it tries to overwrite the FTS_Data.mdf, which is the old database that I
am restoring, but I want to put into TempFTS first to look at a couple of
tables.
What is the point of changing the Physical file names, if it is not going to
use them?
What did I do wrong?
Thanks,
TomOk,
I got it to work.
This time it worked correctly. I am not sure why it wasn't working before
but I had to leave the Logical File Names the same as it found on the device
and change the Physical name to the folder and name that were in the new
Database (tempFTS).
It is just confusing in the way the window is laid out.
Where it says
Restore Database as:
Logical File Name --> really should say something like "Logical File Name
From Device". That was what was confusing. I thought I tried that before
and it didn't work.
So the left column (Logical File Name) is from the Device and the Move to
Physical File is where your new Database is pointing at (I think).
Tom
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:OAkV7%23D1FHA.3820@.TK2MSFTNGP10.phx.gbl...
>I have a database on my new server:
> tempFTS
> From Properties:
> FileName: tempFTS_Data Location: C:\Program Files\Microsoft Sql
> Server\MSSQL\Data\tempFTS_DATA.MDF
> Log: tempFTS_Log Location: C:\Program Files\Microsoft
> Sql Server\MSSQL\Data\tempFTS_LOG.LDF
> Backup from old Server:
> FTS_db_200510180200.BAK
> Right-Click tempFTS database and choose Restore Database
> Select From Device
> Select "Select Devices..."
> Select Add
> Double click on FTS_db_200510180200.BAK
> Select OK
> Go to Options and Displayed:
> FTS_log d:\Microsoft SQL Server\MSSQL\data\FTS.ldf
> FTS_dat d:\Microsoft SQL Server\MSSQL\data\FTS.mdf
> This is where it originally was.
> I now want to change the name to tempFTS
> So I change the Logical fileNames to (AS shown above from the properties
> of tempFTS:
> tempFTS_Log
> tempFTS_Data
> I change the Physical names to where I want the new files to be located
> C:\Program Files\Microsoft Sql Server\MSSQL\Data\tempFTS_DATA.MDF
> C:\Program Files\Microsoft Sql Server\MSSQL\Data\tempFTS_LOG.LDF
> Force Restore (just in case)
> I now get the error:
> Logical file 'tempFTS_Log', not part of the database "tempFTS".
> I then tried to put the Logical names back to FTS_log and FTS_dat to see
> what would happen there:
> Now it tries to overwrite the FTS_Data.mdf, which is the old database that
> I am restoring, but I want to put into TempFTS first to look at a couple
> of tables.
> What is the point of changing the Physical file names, if it is not going
> to use them?
> What did I do wrong?
> Thanks,
> Tom
>
tempFTS
From Properties:
FileName: tempFTS_Data Location: C:\Program Files\Microsoft Sql
Server\MSSQL\Data\tempFTS_DATA.MDF
Log: tempFTS_Log Location: C:\Program Files\Microsoft
Sql Server\MSSQL\Data\tempFTS_LOG.LDF
Backup from old Server:
FTS_db_200510180200.BAK
Right-Click tempFTS database and choose Restore Database
Select From Device
Select "Select Devices..."
Select Add
Double click on FTS_db_200510180200.BAK
Select OK
Go to Options and Displayed:
FTS_log d:\Microsoft SQL Server\MSSQL\data\FTS.ldf
FTS_dat d:\Microsoft SQL Server\MSSQL\data\FTS.mdf
This is where it originally was.
I now want to change the name to tempFTS
So I change the Logical fileNames to (AS shown above from the properties of
tempFTS:
tempFTS_Log
tempFTS_Data
I change the Physical names to where I want the new files to be located
C:\Program Files\Microsoft Sql Server\MSSQL\Data\tempFTS_DATA.MDF
C:\Program Files\Microsoft Sql Server\MSSQL\Data\tempFTS_LOG.LDF
Force Restore (just in case)
I now get the error:
Logical file 'tempFTS_Log', not part of the database "tempFTS".
I then tried to put the Logical names back to FTS_log and FTS_dat to see
what would happen there:
Now it tries to overwrite the FTS_Data.mdf, which is the old database that I
am restoring, but I want to put into TempFTS first to look at a couple of
tables.
What is the point of changing the Physical file names, if it is not going to
use them?
What did I do wrong?
Thanks,
TomOk,
I got it to work.
This time it worked correctly. I am not sure why it wasn't working before
but I had to leave the Logical File Names the same as it found on the device
and change the Physical name to the folder and name that were in the new
Database (tempFTS).
It is just confusing in the way the window is laid out.
Where it says
Restore Database as:
Logical File Name --> really should say something like "Logical File Name
From Device". That was what was confusing. I thought I tried that before
and it didn't work.
So the left column (Logical File Name) is from the Device and the Move to
Physical File is where your new Database is pointing at (I think).
Tom
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:OAkV7%23D1FHA.3820@.TK2MSFTNGP10.phx.gbl...
>I have a database on my new server:
> tempFTS
> From Properties:
> FileName: tempFTS_Data Location: C:\Program Files\Microsoft Sql
> Server\MSSQL\Data\tempFTS_DATA.MDF
> Log: tempFTS_Log Location: C:\Program Files\Microsoft
> Sql Server\MSSQL\Data\tempFTS_LOG.LDF
> Backup from old Server:
> FTS_db_200510180200.BAK
> Right-Click tempFTS database and choose Restore Database
> Select From Device
> Select "Select Devices..."
> Select Add
> Double click on FTS_db_200510180200.BAK
> Select OK
> Go to Options and Displayed:
> FTS_log d:\Microsoft SQL Server\MSSQL\data\FTS.ldf
> FTS_dat d:\Microsoft SQL Server\MSSQL\data\FTS.mdf
> This is where it originally was.
> I now want to change the name to tempFTS
> So I change the Logical fileNames to (AS shown above from the properties
> of tempFTS:
> tempFTS_Log
> tempFTS_Data
> I change the Physical names to where I want the new files to be located
> C:\Program Files\Microsoft Sql Server\MSSQL\Data\tempFTS_DATA.MDF
> C:\Program Files\Microsoft Sql Server\MSSQL\Data\tempFTS_LOG.LDF
> Force Restore (just in case)
> I now get the error:
> Logical file 'tempFTS_Log', not part of the database "tempFTS".
> I then tried to put the Logical names back to FTS_log and FTS_dat to see
> what would happen there:
> Now it tries to overwrite the FTS_Data.mdf, which is the old database that
> I am restoring, but I want to put into TempFTS first to look at a couple
> of tables.
> What is the point of changing the Physical file names, if it is not going
> to use them?
> What did I do wrong?
> Thanks,
> Tom
>
Restore new server different database name not working
I have a database on my new server:
tempFTS
From Properties:
FileName: tempFTS_Data Location: C:\Program Files\Microsoft Sql
Server\MSSQL\Data\tempFTS_DATA.MDF
Log: tempFTS_Log Location: C:\Program Files\Microsoft
Sql Server\MSSQL\Data\tempFTS_LOG.LDF
Backup from old Server:
FTS_db_200510180200.BAK
Right-Click tempFTS database and choose Restore Database
Select From Device
Select "Select Devices..."
Select Add
Double click on FTS_db_200510180200.BAK
Select OK
Go to Options and Displayed:
FTS_log d:\Microsoft SQL Server\MSSQL\data\FTS.ldf
FTS_dat d:\Microsoft SQL Server\MSSQL\data\FTS.mdf
This is where it originally was.
I now want to change the name to tempFTS
So I change the Logical fileNames to (AS shown above from the properties of
tempFTS:
tempFTS_Log
tempFTS_Data
I change the Physical names to where I want the new files to be located
C:\Program Files\Microsoft Sql Server\MSSQL\Data\tempFTS_DATA.MDF
C:\Program Files\Microsoft Sql Server\MSSQL\Data\tempFTS_LOG.LDF
Force Restore (just in case)
I now get the error:
Logical file 'tempFTS_Log', not part of the database "tempFTS".
I then tried to put the Logical names back to FTS_log and FTS_dat to see
what would happen there:
Now it tries to overwrite the FTS_Data.mdf, which is the old database that I
am restoring, but I want to put into TempFTS first to look at a couple of
tables.
What is the point of changing the Physical file names, if it is not going to
use them?
What did I do wrong?
Thanks,
TomOk,
I got it to work.
This time it worked correctly. I am not sure why it wasn't working before
but I had to leave the Logical File Names the same as it found on the device
and change the Physical name to the folder and name that were in the new
Database (tempFTS).
It is just confusing in the way the window is laid out.
Where it says
Restore Database as:
Logical File Name --> really should say something like "Logical File Name
From Device". That was what was confusing. I thought I tried that before
and it didn't work.
So the left column (Logical File Name) is from the Device and the Move to
Physical File is where your new Database is pointing at (I think).
Tom
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:OAkV7%23D1FHA.3820@.TK2MSFTNGP10.phx.gbl...
>I have a database on my new server:
> tempFTS
> From Properties:
> FileName: tempFTS_Data Location: C:\Program Files\Microsoft Sql
> Server\MSSQL\Data\tempFTS_DATA.MDF
> Log: tempFTS_Log Location: C:\Program Files\Microsoft
> Sql Server\MSSQL\Data\tempFTS_LOG.LDF
> Backup from old Server:
> FTS_db_200510180200.BAK
> Right-Click tempFTS database and choose Restore Database
> Select From Device
> Select "Select Devices..."
> Select Add
> Double click on FTS_db_200510180200.BAK
> Select OK
> Go to Options and Displayed:
> FTS_log d:\Microsoft SQL Server\MSSQL\data\FTS.ldf
> FTS_dat d:\Microsoft SQL Server\MSSQL\data\FTS.mdf
> This is where it originally was.
> I now want to change the name to tempFTS
> So I change the Logical fileNames to (AS shown above from the properties
> of tempFTS:
> tempFTS_Log
> tempFTS_Data
> I change the Physical names to where I want the new files to be located
> C:\Program Files\Microsoft Sql Server\MSSQL\Data\tempFTS_DATA.MDF
> C:\Program Files\Microsoft Sql Server\MSSQL\Data\tempFTS_LOG.LDF
> Force Restore (just in case)
> I now get the error:
> Logical file 'tempFTS_Log', not part of the database "tempFTS".
> I then tried to put the Logical names back to FTS_log and FTS_dat to see
> what would happen there:
> Now it tries to overwrite the FTS_Data.mdf, which is the old database that
> I am restoring, but I want to put into TempFTS first to look at a couple
> of tables.
> What is the point of changing the Physical file names, if it is not going
> to use them?
> What did I do wrong?
> Thanks,
> Tom
>
tempFTS
From Properties:
FileName: tempFTS_Data Location: C:\Program Files\Microsoft Sql
Server\MSSQL\Data\tempFTS_DATA.MDF
Log: tempFTS_Log Location: C:\Program Files\Microsoft
Sql Server\MSSQL\Data\tempFTS_LOG.LDF
Backup from old Server:
FTS_db_200510180200.BAK
Right-Click tempFTS database and choose Restore Database
Select From Device
Select "Select Devices..."
Select Add
Double click on FTS_db_200510180200.BAK
Select OK
Go to Options and Displayed:
FTS_log d:\Microsoft SQL Server\MSSQL\data\FTS.ldf
FTS_dat d:\Microsoft SQL Server\MSSQL\data\FTS.mdf
This is where it originally was.
I now want to change the name to tempFTS
So I change the Logical fileNames to (AS shown above from the properties of
tempFTS:
tempFTS_Log
tempFTS_Data
I change the Physical names to where I want the new files to be located
C:\Program Files\Microsoft Sql Server\MSSQL\Data\tempFTS_DATA.MDF
C:\Program Files\Microsoft Sql Server\MSSQL\Data\tempFTS_LOG.LDF
Force Restore (just in case)
I now get the error:
Logical file 'tempFTS_Log', not part of the database "tempFTS".
I then tried to put the Logical names back to FTS_log and FTS_dat to see
what would happen there:
Now it tries to overwrite the FTS_Data.mdf, which is the old database that I
am restoring, but I want to put into TempFTS first to look at a couple of
tables.
What is the point of changing the Physical file names, if it is not going to
use them?
What did I do wrong?
Thanks,
TomOk,
I got it to work.
This time it worked correctly. I am not sure why it wasn't working before
but I had to leave the Logical File Names the same as it found on the device
and change the Physical name to the folder and name that were in the new
Database (tempFTS).
It is just confusing in the way the window is laid out.
Where it says
Restore Database as:
Logical File Name --> really should say something like "Logical File Name
From Device". That was what was confusing. I thought I tried that before
and it didn't work.
So the left column (Logical File Name) is from the Device and the Move to
Physical File is where your new Database is pointing at (I think).
Tom
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:OAkV7%23D1FHA.3820@.TK2MSFTNGP10.phx.gbl...
>I have a database on my new server:
> tempFTS
> From Properties:
> FileName: tempFTS_Data Location: C:\Program Files\Microsoft Sql
> Server\MSSQL\Data\tempFTS_DATA.MDF
> Log: tempFTS_Log Location: C:\Program Files\Microsoft
> Sql Server\MSSQL\Data\tempFTS_LOG.LDF
> Backup from old Server:
> FTS_db_200510180200.BAK
> Right-Click tempFTS database and choose Restore Database
> Select From Device
> Select "Select Devices..."
> Select Add
> Double click on FTS_db_200510180200.BAK
> Select OK
> Go to Options and Displayed:
> FTS_log d:\Microsoft SQL Server\MSSQL\data\FTS.ldf
> FTS_dat d:\Microsoft SQL Server\MSSQL\data\FTS.mdf
> This is where it originally was.
> I now want to change the name to tempFTS
> So I change the Logical fileNames to (AS shown above from the properties
> of tempFTS:
> tempFTS_Log
> tempFTS_Data
> I change the Physical names to where I want the new files to be located
> C:\Program Files\Microsoft Sql Server\MSSQL\Data\tempFTS_DATA.MDF
> C:\Program Files\Microsoft Sql Server\MSSQL\Data\tempFTS_LOG.LDF
> Force Restore (just in case)
> I now get the error:
> Logical file 'tempFTS_Log', not part of the database "tempFTS".
> I then tried to put the Logical names back to FTS_log and FTS_dat to see
> what would happen there:
> Now it tries to overwrite the FTS_Data.mdf, which is the old database that
> I am restoring, but I want to put into TempFTS first to look at a couple
> of tables.
> What is the point of changing the Physical file names, if it is not going
> to use them?
> What did I do wrong?
> Thanks,
> Tom
>
Saturday, February 25, 2012
Restore MDF and NDF to a single DB on a new location
I would like to combine my .MDF and .NDF into a single file when I perform a
restore from a .BAK file. Is there a way of doing such task. The restore
will be done to a new database on a different SQL server which will only have
a .MDF.
environment:SQL 2000
o.s. Windows 2000.
Thank you,
Max
No you must restore it and then use the DBCC SHRINKFILE with the EMPTYFILE
option. See BOL for more details.
Andrew J. Kelly SQL MVP
"Max" <Max@.discussions.microsoft.com> wrote in message
news:0614BF32-7F21-4694-9B53-9EBB83F2D7BE@.microsoft.com...
>I would like to combine my .MDF and .NDF into a single file when I perform
>a
> restore from a .BAK file. Is there a way of doing such task. The restore
> will be done to a new database on a different SQL server which will only
> have
> a .MDF.
> environment:SQL 2000
> o.s. Windows 2000.
> Thank you,
> Max
restore from a .BAK file. Is there a way of doing such task. The restore
will be done to a new database on a different SQL server which will only have
a .MDF.
environment:SQL 2000
o.s. Windows 2000.
Thank you,
Max
No you must restore it and then use the DBCC SHRINKFILE with the EMPTYFILE
option. See BOL for more details.
Andrew J. Kelly SQL MVP
"Max" <Max@.discussions.microsoft.com> wrote in message
news:0614BF32-7F21-4694-9B53-9EBB83F2D7BE@.microsoft.com...
>I would like to combine my .MDF and .NDF into a single file when I perform
>a
> restore from a .BAK file. Is there a way of doing such task. The restore
> will be done to a new database on a different SQL server which will only
> have
> a .MDF.
> environment:SQL 2000
> o.s. Windows 2000.
> Thank you,
> Max
Restore MDF and NDF to a single DB on a new location
I would like to combine my .MDF and .NDF into a single file when I perform a
restore from a .BAK file. Is there a way of doing such task. The restore
will be done to a new database on a different SQL server which will only hav
e
a .MDF.
environment:SQL 2000
o.s. Windows 2000.
Thank you,
MaxNo you must restore it and then use the DBCC SHRINKFILE with the EMPTYFILE
option. See BOL for more details.
Andrew J. Kelly SQL MVP
"Max" <Max@.discussions.microsoft.com> wrote in message
news:0614BF32-7F21-4694-9B53-9EBB83F2D7BE@.microsoft.com...
>I would like to combine my .MDF and .NDF into a single file when I perform
>a
> restore from a .BAK file. Is there a way of doing such task. The restore
> will be done to a new database on a different SQL server which will only
> have
> a .MDF.
> environment:SQL 2000
> o.s. Windows 2000.
> Thank you,
> Max
restore from a .BAK file. Is there a way of doing such task. The restore
will be done to a new database on a different SQL server which will only hav
e
a .MDF.
environment:SQL 2000
o.s. Windows 2000.
Thank you,
MaxNo you must restore it and then use the DBCC SHRINKFILE with the EMPTYFILE
option. See BOL for more details.
Andrew J. Kelly SQL MVP
"Max" <Max@.discussions.microsoft.com> wrote in message
news:0614BF32-7F21-4694-9B53-9EBB83F2D7BE@.microsoft.com...
>I would like to combine my .MDF and .NDF into a single file when I perform
>a
> restore from a .BAK file. Is there a way of doing such task. The restore
> will be done to a new database on a different SQL server which will only
> have
> a .MDF.
> environment:SQL 2000
> o.s. Windows 2000.
> Thank you,
> Max
Restore MDF and NDF to a single DB on a new location
I would like to combine my .MDF and .NDF into a single file when I perform a
restore from a .BAK file. Is there a way of doing such task. The restore
will be done to a new database on a different SQL server which will only have
a .MDF.
environment:SQL 2000
o.s. Windows 2000.
Thank you,
MaxNo you must restore it and then use the DBCC SHRINKFILE with the EMPTYFILE
option. See BOL for more details.
--
Andrew J. Kelly SQL MVP
"Max" <Max@.discussions.microsoft.com> wrote in message
news:0614BF32-7F21-4694-9B53-9EBB83F2D7BE@.microsoft.com...
>I would like to combine my .MDF and .NDF into a single file when I perform
>a
> restore from a .BAK file. Is there a way of doing such task. The restore
> will be done to a new database on a different SQL server which will only
> have
> a .MDF.
> environment:SQL 2000
> o.s. Windows 2000.
> Thank you,
> Max
restore from a .BAK file. Is there a way of doing such task. The restore
will be done to a new database on a different SQL server which will only have
a .MDF.
environment:SQL 2000
o.s. Windows 2000.
Thank you,
MaxNo you must restore it and then use the DBCC SHRINKFILE with the EMPTYFILE
option. See BOL for more details.
--
Andrew J. Kelly SQL MVP
"Max" <Max@.discussions.microsoft.com> wrote in message
news:0614BF32-7F21-4694-9B53-9EBB83F2D7BE@.microsoft.com...
>I would like to combine my .MDF and .NDF into a single file when I perform
>a
> restore from a .BAK file. Is there a way of doing such task. The restore
> will be done to a new database on a different SQL server which will only
> have
> a .MDF.
> environment:SQL 2000
> o.s. Windows 2000.
> Thank you,
> Max
Monday, February 20, 2012
Restore master backup from different server causes file location problems
Hi,
I have just restored a backup of our production master database to a
development PC as a test and now SQL Server won't start. This is
because it cannot open the devices for the various databases. Fair
enough, I understand why that is - we have system dbs (model, msdb) on
D: drive in production while they are on C: drive on dev PC.
My questions are: -
1. Is it possible to repair this by somehow updating the server's links
to where the database files are? I know you can specify master db/log
files' location on command line, but not the other system DBs.
2. Is it only the device activation error for the system databases that
prevent SQL Server restarting or do all user database files need to
also be in their correct location?
I can't get SQL up and running because there's no D: drive on the dev
PC, so I can't move the files into the correct location for SQL Server
to find them on startup...
Rgds,
Andrew Duncan
SQL Server will start once the system databases are set
correctly. The following articles have information on
changing the file locations for system databases:
Moving SQL Server databases to a new location with
Detach/Attach
http://support.microsoft.com/?id=224071
INF: Moving SQL Server 7.0 Databases to a New Server with
BACKUP and RESTORE
http://support.microsoft.com/?id=304692
-Sue
On 16 Mar 2005 00:10:03 -0800, andrew@.duncanhcmc.com wrote:
>Hi,
>I have just restored a backup of our production master database to a
>development PC as a test and now SQL Server won't start. This is
>because it cannot open the devices for the various databases. Fair
>enough, I understand why that is - we have system dbs (model, msdb) on
>D: drive in production while they are on C: drive on dev PC.
>My questions are: -
>1. Is it possible to repair this by somehow updating the server's links
>to where the database files are? I know you can specify master db/log
>files' location on command line, but not the other system DBs.
>2. Is it only the device activation error for the system databases that
>prevent SQL Server restarting or do all user database files need to
>also be in their correct location?
>I can't get SQL up and running because there's no D: drive on the dev
>PC, so I can't move the files into the correct location for SQL Server
>to find them on startup...
>Rgds,
>Andrew Duncan
|||Thanks Sue,
I actually had this article but for some reason assumed I couldn't move
the files unless they were already in a valid location!
One point not mentioned in the article was I had to start sqlservr with
-f flag due to tempdb files not being found. Only then could I perform
the ALTER DATABASE commands to move tempdb.
Thanks again for response!
|||The -f flag is mentioned several times in the second
article. It's a more inclusive article but there are a few
differences with SQL 7 and 2000 and the first article is
what MS has for SQL Server 2000. I'd prefer that they just
update the second article on using backup and restore with
SQL Server 2000.
-Sue
On 16 Mar 2005 18:17:38 -0800, andrew@.duncanhcmc.com wrote:
>Thanks Sue,
>I actually had this article but for some reason assumed I couldn't move
>the files unless they were already in a valid location!
>One point not mentioned in the article was I had to start sqlservr with
>-f flag due to tempdb files not being found. Only then could I perform
>the ALTER DATABASE commands to move tempdb.
>Thanks again for response!
I have just restored a backup of our production master database to a
development PC as a test and now SQL Server won't start. This is
because it cannot open the devices for the various databases. Fair
enough, I understand why that is - we have system dbs (model, msdb) on
D: drive in production while they are on C: drive on dev PC.
My questions are: -
1. Is it possible to repair this by somehow updating the server's links
to where the database files are? I know you can specify master db/log
files' location on command line, but not the other system DBs.
2. Is it only the device activation error for the system databases that
prevent SQL Server restarting or do all user database files need to
also be in their correct location?
I can't get SQL up and running because there's no D: drive on the dev
PC, so I can't move the files into the correct location for SQL Server
to find them on startup...
Rgds,
Andrew Duncan
SQL Server will start once the system databases are set
correctly. The following articles have information on
changing the file locations for system databases:
Moving SQL Server databases to a new location with
Detach/Attach
http://support.microsoft.com/?id=224071
INF: Moving SQL Server 7.0 Databases to a New Server with
BACKUP and RESTORE
http://support.microsoft.com/?id=304692
-Sue
On 16 Mar 2005 00:10:03 -0800, andrew@.duncanhcmc.com wrote:
>Hi,
>I have just restored a backup of our production master database to a
>development PC as a test and now SQL Server won't start. This is
>because it cannot open the devices for the various databases. Fair
>enough, I understand why that is - we have system dbs (model, msdb) on
>D: drive in production while they are on C: drive on dev PC.
>My questions are: -
>1. Is it possible to repair this by somehow updating the server's links
>to where the database files are? I know you can specify master db/log
>files' location on command line, but not the other system DBs.
>2. Is it only the device activation error for the system databases that
>prevent SQL Server restarting or do all user database files need to
>also be in their correct location?
>I can't get SQL up and running because there's no D: drive on the dev
>PC, so I can't move the files into the correct location for SQL Server
>to find them on startup...
>Rgds,
>Andrew Duncan
|||Thanks Sue,
I actually had this article but for some reason assumed I couldn't move
the files unless they were already in a valid location!
One point not mentioned in the article was I had to start sqlservr with
-f flag due to tempdb files not being found. Only then could I perform
the ALTER DATABASE commands to move tempdb.
Thanks again for response!
|||The -f flag is mentioned several times in the second
article. It's a more inclusive article but there are a few
differences with SQL 7 and 2000 and the first article is
what MS has for SQL Server 2000. I'd prefer that they just
update the second article on using backup and restore with
SQL Server 2000.
-Sue
On 16 Mar 2005 18:17:38 -0800, andrew@.duncanhcmc.com wrote:
>Thanks Sue,
>I actually had this article but for some reason assumed I couldn't move
>the files unless they were already in a valid location!
>One point not mentioned in the article was I had to start sqlservr with
>-f flag due to tempdb files not being found. Only then could I perform
>the ALTER DATABASE commands to move tempdb.
>Thanks again for response!
Restore master backup from different server causes file location problems
Hi,
I have just restored a backup of our production master database to a
development PC as a test and now SQL Server won't start. This is
because it cannot open the devices for the various databases. Fair
enough, I understand why that is - we have system dbs (model, msdb) on
D: drive in production while they are on C: drive on dev PC.
My questions are: -
1. Is it possible to repair this by somehow updating the server's links
to where the database files are? I know you can specify master db/log
files' location on command line, but not the other system DBs.
2. Is it only the device activation error for the system databases that
prevent SQL Server restarting or do all user database files need to
also be in their correct location?
I can't get SQL up and running because there's no D: drive on the dev
PC, so I can't move the files into the correct location for SQL Server
to find them on startup...
Rgds,
Andrew DuncanSQL Server will start once the system databases are set
correctly. The following articles have information on
changing the file locations for system databases:
Moving SQL Server databases to a new location with
Detach/Attach
http://support.microsoft.com/?id=224071
INF: Moving SQL Server 7.0 Databases to a New Server with
BACKUP and RESTORE
http://support.microsoft.com/?id=304692
-Sue
On 16 Mar 2005 00:10:03 -0800, andrew@.duncanhcmc.com wrote:
>Hi,
>I have just restored a backup of our production master database to a
>development PC as a test and now SQL Server won't start. This is
>because it cannot open the devices for the various databases. Fair
>enough, I understand why that is - we have system dbs (model, msdb) on
>D: drive in production while they are on C: drive on dev PC.
>My questions are: -
>1. Is it possible to repair this by somehow updating the server's links
>to where the database files are? I know you can specify master db/log
>files' location on command line, but not the other system DBs.
>2. Is it only the device activation error for the system databases that
>prevent SQL Server restarting or do all user database files need to
>also be in their correct location?
>I can't get SQL up and running because there's no D: drive on the dev
>PC, so I can't move the files into the correct location for SQL Server
>to find them on startup...
>Rgds,
>Andrew Duncan|||Thanks Sue,
I actually had this article but for some reason assumed I couldn't move
the files unless they were already in a valid location!
One point not mentioned in the article was I had to start sqlservr with
-f flag due to tempdb files not being found. Only then could I perform
the ALTER DATABASE commands to move tempdb.
Thanks again for response!|||The -f flag is mentioned several times in the second
article. It's a more inclusive article but there are a few
differences with SQL 7 and 2000 and the first article is
what MS has for SQL Server 2000. I'd prefer that they just
update the second article on using backup and restore with
SQL Server 2000.
-Sue
On 16 Mar 2005 18:17:38 -0800, andrew@.duncanhcmc.com wrote:
>Thanks Sue,
>I actually had this article but for some reason assumed I couldn't move
>the files unless they were already in a valid location!
>One point not mentioned in the article was I had to start sqlservr with
>-f flag due to tempdb files not being found. Only then could I perform
>the ALTER DATABASE commands to move tempdb.
>Thanks again for response!
I have just restored a backup of our production master database to a
development PC as a test and now SQL Server won't start. This is
because it cannot open the devices for the various databases. Fair
enough, I understand why that is - we have system dbs (model, msdb) on
D: drive in production while they are on C: drive on dev PC.
My questions are: -
1. Is it possible to repair this by somehow updating the server's links
to where the database files are? I know you can specify master db/log
files' location on command line, but not the other system DBs.
2. Is it only the device activation error for the system databases that
prevent SQL Server restarting or do all user database files need to
also be in their correct location?
I can't get SQL up and running because there's no D: drive on the dev
PC, so I can't move the files into the correct location for SQL Server
to find them on startup...
Rgds,
Andrew DuncanSQL Server will start once the system databases are set
correctly. The following articles have information on
changing the file locations for system databases:
Moving SQL Server databases to a new location with
Detach/Attach
http://support.microsoft.com/?id=224071
INF: Moving SQL Server 7.0 Databases to a New Server with
BACKUP and RESTORE
http://support.microsoft.com/?id=304692
-Sue
On 16 Mar 2005 00:10:03 -0800, andrew@.duncanhcmc.com wrote:
>Hi,
>I have just restored a backup of our production master database to a
>development PC as a test and now SQL Server won't start. This is
>because it cannot open the devices for the various databases. Fair
>enough, I understand why that is - we have system dbs (model, msdb) on
>D: drive in production while they are on C: drive on dev PC.
>My questions are: -
>1. Is it possible to repair this by somehow updating the server's links
>to where the database files are? I know you can specify master db/log
>files' location on command line, but not the other system DBs.
>2. Is it only the device activation error for the system databases that
>prevent SQL Server restarting or do all user database files need to
>also be in their correct location?
>I can't get SQL up and running because there's no D: drive on the dev
>PC, so I can't move the files into the correct location for SQL Server
>to find them on startup...
>Rgds,
>Andrew Duncan|||Thanks Sue,
I actually had this article but for some reason assumed I couldn't move
the files unless they were already in a valid location!
One point not mentioned in the article was I had to start sqlservr with
-f flag due to tempdb files not being found. Only then could I perform
the ALTER DATABASE commands to move tempdb.
Thanks again for response!|||The -f flag is mentioned several times in the second
article. It's a more inclusive article but there are a few
differences with SQL 7 and 2000 and the first article is
what MS has for SQL Server 2000. I'd prefer that they just
update the second article on using backup and restore with
SQL Server 2000.
-Sue
On 16 Mar 2005 18:17:38 -0800, andrew@.duncanhcmc.com wrote:
>Thanks Sue,
>I actually had this article but for some reason assumed I couldn't move
>the files unless they were already in a valid location!
>One point not mentioned in the article was I had to start sqlservr with
>-f flag due to tempdb files not being found. Only then could I perform
>the ALTER DATABASE commands to move tempdb.
>Thanks again for response!
Restore master backup from different server causes file location problems
Hi,
I have just restored a backup of our production master database to a
development PC as a test and now SQL Server won't start. This is
because it cannot open the devices for the various databases. Fair
enough, I understand why that is - we have system dbs (model, msdb) on
D: drive in production while they are on C: drive on dev PC.
My questions are: -
1. Is it possible to repair this by somehow updating the server's links
to where the database files are? I know you can specify master db/log
files' location on command line, but not the other system DBs.
2. Is it only the device activation error for the system databases that
prevent SQL Server restarting or do all user database files need to
also be in their correct location?
I can't get SQL up and running because there's no D: drive on the dev
PC, so I can't move the files into the correct location for SQL Server
to find them on startup...
Rgds,
Andrew DuncanSQL Server will start once the system databases are set
correctly. The following articles have information on
changing the file locations for system databases:
Moving SQL Server databases to a new location with
Detach/Attach
http://support.microsoft.com/?id=224071
INF: Moving SQL Server 7.0 Databases to a New Server with
BACKUP and RESTORE
http://support.microsoft.com/?id=304692
-Sue
On 16 Mar 2005 00:10:03 -0800, andrew@.duncanhcmc.com wrote:
>Hi,
>I have just restored a backup of our production master database to a
>development PC as a test and now SQL Server won't start. This is
>because it cannot open the devices for the various databases. Fair
>enough, I understand why that is - we have system dbs (model, msdb) on
>D: drive in production while they are on C: drive on dev PC.
>My questions are: -
>1. Is it possible to repair this by somehow updating the server's links
>to where the database files are? I know you can specify master db/log
>files' location on command line, but not the other system DBs.
>2. Is it only the device activation error for the system databases that
>prevent SQL Server restarting or do all user database files need to
>also be in their correct location?
>I can't get SQL up and running because there's no D: drive on the dev
>PC, so I can't move the files into the correct location for SQL Server
>to find them on startup...
>Rgds,
>Andrew Duncan|||Thanks Sue,
I actually had this article but for some reason assumed I couldn't move
the files unless they were already in a valid location!
One point not mentioned in the article was I had to start sqlservr with
-f flag due to tempdb files not being found. Only then could I perform
the ALTER DATABASE commands to move tempdb.
Thanks again for response!|||The -f flag is mentioned several times in the second
article. It's a more inclusive article but there are a few
differences with SQL 7 and 2000 and the first article is
what MS has for SQL Server 2000. I'd prefer that they just
update the second article on using backup and restore with
SQL Server 2000.
-Sue
On 16 Mar 2005 18:17:38 -0800, andrew@.duncanhcmc.com wrote:
>Thanks Sue,
>I actually had this article but for some reason assumed I couldn't move
>the files unless they were already in a valid location!
>One point not mentioned in the article was I had to start sqlservr with
>-f flag due to tempdb files not being found. Only then could I perform
>the ALTER DATABASE commands to move tempdb.
>Thanks again for response!
I have just restored a backup of our production master database to a
development PC as a test and now SQL Server won't start. This is
because it cannot open the devices for the various databases. Fair
enough, I understand why that is - we have system dbs (model, msdb) on
D: drive in production while they are on C: drive on dev PC.
My questions are: -
1. Is it possible to repair this by somehow updating the server's links
to where the database files are? I know you can specify master db/log
files' location on command line, but not the other system DBs.
2. Is it only the device activation error for the system databases that
prevent SQL Server restarting or do all user database files need to
also be in their correct location?
I can't get SQL up and running because there's no D: drive on the dev
PC, so I can't move the files into the correct location for SQL Server
to find them on startup...
Rgds,
Andrew DuncanSQL Server will start once the system databases are set
correctly. The following articles have information on
changing the file locations for system databases:
Moving SQL Server databases to a new location with
Detach/Attach
http://support.microsoft.com/?id=224071
INF: Moving SQL Server 7.0 Databases to a New Server with
BACKUP and RESTORE
http://support.microsoft.com/?id=304692
-Sue
On 16 Mar 2005 00:10:03 -0800, andrew@.duncanhcmc.com wrote:
>Hi,
>I have just restored a backup of our production master database to a
>development PC as a test and now SQL Server won't start. This is
>because it cannot open the devices for the various databases. Fair
>enough, I understand why that is - we have system dbs (model, msdb) on
>D: drive in production while they are on C: drive on dev PC.
>My questions are: -
>1. Is it possible to repair this by somehow updating the server's links
>to where the database files are? I know you can specify master db/log
>files' location on command line, but not the other system DBs.
>2. Is it only the device activation error for the system databases that
>prevent SQL Server restarting or do all user database files need to
>also be in their correct location?
>I can't get SQL up and running because there's no D: drive on the dev
>PC, so I can't move the files into the correct location for SQL Server
>to find them on startup...
>Rgds,
>Andrew Duncan|||Thanks Sue,
I actually had this article but for some reason assumed I couldn't move
the files unless they were already in a valid location!
One point not mentioned in the article was I had to start sqlservr with
-f flag due to tempdb files not being found. Only then could I perform
the ALTER DATABASE commands to move tempdb.
Thanks again for response!|||The -f flag is mentioned several times in the second
article. It's a more inclusive article but there are a few
differences with SQL 7 and 2000 and the first article is
what MS has for SQL Server 2000. I'd prefer that they just
update the second article on using backup and restore with
SQL Server 2000.
-Sue
On 16 Mar 2005 18:17:38 -0800, andrew@.duncanhcmc.com wrote:
>Thanks Sue,
>I actually had this article but for some reason assumed I couldn't move
>the files unless they were already in a valid location!
>One point not mentioned in the article was I had to start sqlservr with
>-f flag due to tempdb files not being found. Only then could I perform
>the ALTER DATABASE commands to move tempdb.
>Thanks again for response!
Subscribe to:
Posts (Atom)