How to move db to another server?

We need to move one db to another server to save diskspace on MS SQL server. ie. move only db & transaction log files from server1 to y:\dbfiles\ folder where y: is \\server2\Remote.
MSSQL is not installed on server2. So do I need to set any permissions on y:\dbfiles or any special setting for it? Plz reply.

If your only intention is to save diskspace by transferring entire dabase to another server and not want to use this database on other server. Then you don't have to bother about sql server and use the first part of this reply to save your diskspace: steps:

1. Enter sql server enterprie manager

2. Right click on the database you want to transfer

3. Choose all tasks -> backup database

4. In the dialog box "select-database complete" radio button and in the name textbox enter the appropriate backup name.

5.in the destination section click add to select file where to save the database backup.

6. Choose filename radiobutton and clilck on eclipse button to chhose destination folder
and filename for the backup. If file name dosn't appear when you click ok then go to end of the textbox and append a filename

7. Click on ok

8. In overwrite section choose append media or overwrite media according to your plan.

9. Now click on ok.

10. Find the backup file in the destination folder and move it to another server.

11. Delete the database from first server using enterprise manager. Make sure u uncheck the option of delete backup and restore history now the the first server doesn't have the the database.

If you want to use this database for further transaction then make sure you install the sql server on the second server and follow the steps given below to restore the database.
1.open the sql enterprise manager-> unfold the server instance-> right click on the database folder-> choose all tasks->choose restore database

2.in the dialog box choose give a name for the database to restore e.g. Mydb. In the restore section choose from device.

3. In the parameter section click on select device and select the backup file by clicking add and click ok twice.

4. Choose restore bckup set and choose database-complete

5. Now click ok. Your database will be restore and now you are ready serve the database.

If this reply doesn't serve your purpose then please do inform me. If you have any suggestions that is welcome.--> bhaskar jha

happy programming

Hi bhaskar jha,

Thanks for your interest. I think, I failed to explain my actual purpose in the post. What I trying to do is keep only the live database files on another server to save diskspace on current sql server. ie. place live database files on a network share. So that SQL server & Database on same system but only database files on a networkshare on another system as the growth of db will not reduce the free space on SQL server. I think now you are clear about my problem.. plz help..

Hi
here is the another solution..

First, you have to make sure that no one else but the administator is currently using this database.

Next, create a backup of the database. For this open query analyzer and type down following command to add dump device for backup :

sp_addumpdevice 'disk','mybackup','c:\backup.dat'
go

now backup the database

backup database 'mydatabase' to mybackup
go

here mydatabase is the name of database and backup is the name of dump device we created earlier. After the backup now restore the database to use further

--determine the number of the files to restore

restore filelistonly from mybackup
go

--now restore the database

restore database mydatabase from mybackup with norecovery,
move mydatabase_data to server\\path\mydata.mdf,
move mydatabase_sec to server\\path\mydatasec.ndf
go

after the restoration process refersh the server and checkout the location using:

sp_helpfile 'mydatabase_data'
go

if there is log to recover then recover it through

restore log mydatabase from mylog with recovery
go

make sure the current database is master .

i think that will solve your problem. Please do reply with what ever your take on this solution.

[Quote]happy programming....[/quote]

First import db from ms sql serevr to ms access file one machine and copy the same database in another machine through network. To copy data from sql to excel. You better to use dts import/export wizard. Thanking you, d.venkateswarlu