Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • Restoring SQLServer Databases from backup files

    • 0
    • 1
    • 1
    • 1
    • 0
    • 0
    • 2
    • 0
    • 447
    Comment on it

    There are many ways to restore MSSQL Server database from backup files(.bak) like using tools available in SQLServer Management tools. But we face problems when the backup was not created on the same machine or it was shared by someone else or it was created in older version. This blog may help you in making this process hassle free.

    First thing we need to know is the logical name of mdf and ldf files of the original database. To get those open a new query window in SQLServer Management Studio and type the following command :

    RESTORE FILELISTONLY FROM DISK='D:\Backups\DBBackup.bak';
    

    Where Backups should be replaced by the folder location of your backup file and DBBackup should be replaced by name of your backup file.

    The output will be similar to :

    alt text

    The values of our interest are in the column named LogicalName the first value in that column is the logical name of the mdf file and second is the logical name of ldf file

    Now use the following command :

    RESTORE DATABASE DBName
    FROM DISK='D:\Backups\DBBackup.bak' WITH REPLACE,
    MOVE 'magazinedb' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\DBName.mdf',
    MOVE 'magazinedb_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\DBName.ldf';
    

    Here DBName should be replaced by the name of the database to be restored, magazinedb should be replaced by the logical name of your mdf file and magazinedb_log should be replaced by the logical name of your ldf file. Both the file names we have from the first command.

    On successful restoration the output will be similar :

    alt text

    Restoring SQLServer Databases from backup files

 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: