CODEWITHSUNDEEP
sqlsql-serverdatabase-administrationdatabase-restoresql-server-2017
mHelpMe
mHelpMe

Reputation: 6668

Restore database using a bak file from another computer

I have a SQL Server database on my PC. I backed up my database so now I have a .bak file.

I recently brought a new PC and want to move the database from my old PC to this PC.

I go to restore database & under source click on device. I then have the backup media type as file & then click Add. I browse to where my .bak file is. However when I browse to this directory no files show up which I don't understand why?

Can I not just copy over the backup file from my old PC to my new one? What am I missing?

Upvotes: 1

Views: 1860

Answers (1)

Ed Callahan
Ed Callahan

Reputation: 189

I do it manually. First, find out where your new server keeps it's database (mdf) and log (ldf) files

SELECT name, physical_name FROM sys.master_files

Then, find out what the logical names of those files are in your backup

restore FILELISTONLY FROM DISK='c:\dir\file.bak'

Finally, use combine that info to do the restore:

RESTORE DATABASE DataBaseToCreate
    FROM DISK = 'c:\dir\file.bak' 
    WITH REPLACE, 
    MOVE 'DBLogicalName' TO  'C:\DB_DIR\DataBaseToCreate.mdf',
    MOVE 'LogLogicalName' TO  'C:\LOG_DIR\DatabaseToCreate.ldf'

Upvotes: 2

Related Questions