Restoring a Backup on other server

Question

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

gsvirdi SSC Veteran Points: 226 More actions · Answer 1

STEP I:

Open Query analyzer for the source server and run the following command:

BACKUP DATABASE aaa

TO DISK='F:\MSSQL\backups\aaa_migrate.bak'

STEP II:

Move the *.bak file to server yyyy

Migrate all your logins to yyyy

STEP III:

Open Query analyzer on the desitination server and run the following command:

RESTORE FILELISTONLY

FROM DISK='This directory is the location to which you copied the .bak file\aaa_migrate.bak'

FILELISTONLY returns a result set with a list of database and log files contained in the *.bak file.

Once you know the logical database & log file names, you can execute the following

RESTORE DATABASE aaa

FROM DISK='the .bak file directory\aaa_migrate.bak'

WITH MOVE 'aaa_data' TO 'F:\MSSQL\Data\aaa_Data.mdf'

MOVE 'aaa_log' TO 'F:\MSSQL\Data\aaa_Log.ldf'

STEP IV:

Once restore is complete re-link orphaned users in the database by using sp_change_users_login

HTH,

Gurbaksh

Dave Hulse Newbie Points: 1 More actions · Answer 2

Or use SQL enterprise manager backup and restore for steps I and III