locked
SQL Server 2012 restore back up of a database to a different computer RRS feed

  • Question

  • How best to restore a back up of a database to a different computer. Can we simply copy the back-up files (.bak) and paste/move it to the default back up folder of the other computer and restore to the new environment. How best to do it?
    Monday, January 25, 2016 8:05 AM

Answers

All replies

  • I think the best way is backup database move backup file yo new Server and Restore database to new server(generally with move parametre).


    SQL Server 2012 Kitabımı incelediniz mi?

    Monday, January 25, 2016 8:11 AM
  • How best  to move the back-up files to new server. After copying the back-up files .bak and trying to paste it in the back up folder of the new server it gives an error operation cannot be done!!
    Monday, January 25, 2016 8:22 AM
  • source and destination server have the same SQL Server version? 

    If destination server has newer version of sql server you can not use backup/restore or attach/deattach.

    You should script out your database and run it on destion server.


    SQL Server 2012 Kitabımı incelediniz mi?

    Monday, January 25, 2016 8:28 AM
  • Good Day,

    Copy the backup file to any location on other computer. Restore the backup either through the SQL management studio with relocate all files to folder option or by using the T-SQL QUERY

    RESTORE DATABASE MyAdvWorks
       FROM disk = 'backuplocation\AdventureWorks2012.bak'
       WITH RECOVERY,
       MOVE 'AdventureWorks2012_Data' TO 'D:\MyData\MyAdvWorks_Data.mdf', 
       MOVE 'AdventureWorks2012_Log' TO 'F:\MyLog\MyAdvWorks_Log.ldf';
    GO

    The below T-Sql query will find the name and physical name for the database in the existing server.

    use MyAdvWorks;   
    go
    select name,physical_name from sys.database_files;


    https://msdn.microsoft.com/en-us/library/ms177429(v=sql.110).aspx

    https://msdn.microsoft.com/en-us/library/ms186390(v=sql.110).aspx

    Thanks,

    SKB

     





    • Edited by SKB7 Monday, January 25, 2016 8:44 AM
    • Proposed as answer by Shanky_621MVP Monday, January 25, 2016 8:57 AM
    Monday, January 25, 2016 8:33 AM
  • >>Can we simply copy the back-up files (.bak) and paste/move it to the default back up folder of the other computer >>>and restore to the new environment.

    Yes, you can but also you can directly backup the database to another computer

    BACKUP DATABASE dbname TO DISK = 
        N'\\SERVER\SHAREDFOLDER\dbname.BAK' 


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Proposed as answer by Shanky_621MVP Monday, January 25, 2016 8:57 AM
    • Marked as answer by geeam Tuesday, January 26, 2016 11:04 AM
    Monday, January 25, 2016 8:35 AM
    Answerer
  • Paste? Are you talking about Windows Explorer?

    You don't have to copy the file into the backup folder, but you can put the backup file anywhere the service account for SQL Server has read permission.

    Monday, January 25, 2016 8:47 AM