store data in different server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

store data in different server

Author

Topic

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2005-01-18 : 09:24:26

I was told that the SQL Server needs to be empty in order to add more disk space but, that process will
be performed during the day so users need to still be able to access the databases. Is it possible to
move the data, and log files to a server where the SQL is not running???

The person that is working with this did the following
detach the DBs
move the files
tried to reattach the DBs pointing to the new location and received a message saying
"Network device not supported for database file"

.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.
A candle loses nothing by lighting another candle

nr
SQLTeam MVY

12543 Posts

Posted - 2005-01-18 : 10:26:27

>> I was told that the SQL Server needs to be empty in order to add more disk space
What do you mean by that?

Before detaching the db did you take a backup and test it? Otherwise you can lose the database.
I would not advise moving a db like this. Better to bacup and restore then you can leave the original database on-line until you make sure the restore works.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2005-01-18 : 11:39:26

furtunately he just tried it with test databases. Thanx for the information.

Just to make sure is this what you are suggesting
for DB1

Make Backup
Restore with different name (DB2)
test DB2
if it works make sure there are no connections to DB1 and detach DB1
- Restore previous backup as DB1 specifing the new location
- keep working online with data files in new location
else

.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.
A candle loses nothing by lighting another candle

nr
SQLTeam MVY

12543 Posts

Posted - 2005-01-18 : 12:03:20

I would
Stop system
backup database
test restore on another server (or just with another name o same server)
detach database
rename previously restored database if on same server or restore with correct name.

You can shorten the down time by using log backups or diffs.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2005-01-18 : 12:41:41

Thanx Nigel

.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.
A candle loses nothing by lighting another candle

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-01-18 : 15:50:27

It sounds like they tried to move the database files to a different server and attach them on a network share. SQL Server 2000 or before does not support this.

quote:
Originally posted by tuenty

I was told that the SQL Server needs to be empty in order to add more disk space but, that process will
be performed during the day so users need to still be able to access the databases. Is it possible to
move the data, and log files to a server where the SQL is not running???

The person that is working with this did the following
detach the DBs
move the files
tried to reattach the DBs pointing to the new location and received a message saying
"Network device not supported for database file"

.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.
A candle loses nothing by lighting another candle

Codo Ergo Sum

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2005-01-19 : 11:39:49

Nigel

I tried what you said with the restore wizard and it gave me the same error message and it said
that if I wanted to move the file I had to specify a With Move so I searched BOL and found the
syntax but I'm not sure what I'm supposed to put in the 'logical_file_name'

Ok lets say I want to move the test DB named dbTestToBeMoved to a new location
new location = '\\NewServer\SQLFolder\Microsoft SQL Server\MSSQL\Data\dbTestToBeMoved_Data.mdf'
old location = '\\SQLServer\Microsoft SQL Server\MSSQL\Data\dbTestToBeMoved_Data.mdf'
but I want to do it with the current Life data
Last Life backup = '\\SQLServer\Microsoft SQL Server\MSSQL\BACKUP\ToBeMoved\ToBeMoved_db_200501190200.BAK'

I constructed the following SQL statement

RESTORE DATABASE dbTestToBeMoved 
FROM \\SQLServer\Microsoft SQL Server\MSSQL\BACKUP\ToBeMoved\ToBeMoved_db_200501190200.BAK 
WITH MOVE 'logical_file_name' 
     TO '\\NewServer\SQLFolder\Microsoft SQL Server\MSSQL\Data\dbTestToBeMoved_Data.mdf'

With what I'm supposed to substitute 'logical_file_name'

.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.
A candle loses nothing by lighting another candle

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources