Skip to content

TechRepublic

  • Top Products Lists
  • AI
  • Developer
  • Payroll
  • Security
  • Project Management
  • TechRepublic Academy
  • Innovation
  • Cheat Sheets
  • Big Data
  • Tech Jobs
  • TechRepublic Premium
  • Top Products Lists
  • AI
  • Developer
  • Payroll
  • Security
  • Project Management
  • TechRepublic Academy
  • Innovation
  • Cheat Sheets
  • Big Data
  • Tech Jobs
  • See All Topics
  • Sponsored
  • Newsletters
  • Forums
  • Resource Library
TechRepublic Premium
Join / Sign In
Data Management

How Do I… Restore a SQL Server database to a new server?

By steven s. warren June 20, 2007, 1:07 AM PDT

Image
1
of 15

91840.jpg
91840.jpg
How Do I… Restore a SQL Server database to a new server?

By Steven Warren

By Steven Warren

As a DBA, I have been in many situations where I had to move databases to a new server due to old hardware, failed hardware, failed drives, etc. You have two options you could use to carry out this task:

  • SQL Server Management Studio (GUI)
  • T-SQL (Command line)

The method I prefer is T-SQL. It is simple and easy and avoids clicking the mouse all over the screen. This How do I… will show you both methods and allow you to choose the one you prefer.

This gallery is also available as a TechRepublic blog entry and as a TechRepublic download.

How Do I… Restore a SQL Server database to a new server?

Search field

Search field

Moving a database with Microsoft SQL Server Management Studio
We will begin by opening SQL Server Management Studio from the Start Menu by choosing Start and typing SQL Server in the Instant Search field.

How Do I… Restore a SQL Server database to a new server?

SQL Server Management Studio

SQL Server Management Studio

The SQL Server Management Studio appears and it will be the main area you use to restore your backups.

How Do I… Restore a SQL Server database to a new server?

Restore Database

Restore Database

Note: I am going to assume that you already know how to backup a SQL Server database and that you have placed the backups on a file server or copied the backups to the new server. We will continue the tutorial from this point.

Now that you have the Management Studio opened, right-click on Databases and choose Restore Database.

How Do I… Restore a SQL Server database to a new server?

To Database

To Database

The Restore Database window appears and we will begin by typing the name of the Database we want to restore in the To Database field.

How Do I… Restore a SQL Server database to a new server?

From Device

From Device

Choose the From Device radio button to choose where your backup file is located.

How Do I… Restore a SQL Server database to a new server?

Select backups

Select backups

Your file now appears in the Select backups to restore text box. Place a check in the checkbox to continue.

How Do I… Restore a SQL Server database to a new server?

New path

New path

You are now at the critical point of the restore where you choose Options from Select a Page. This is where you specify a new path for your database files. It is the same as the move option that will be discussed later in this tutorial. Simply type a new path to the database and log file. For example, the current structure is the following:

  • C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Database_Name_Here.mdf
  • C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Database_Name_Here_1.ldf

How Do I… Restore a SQL Server database to a new server?

Move to path

Move to path

We want to move these database files to a new path. Simply type the new path. For purposes of this tutorial, we will move it to the following:

  • D:\ SQL\DATA\Database_Name_Here.mdf
  • D:\SQL\Logs\Database_Name_Here_1.ldf

How Do I… Restore a SQL Server database to a new server?

Progress

Progress

You are now ready to click OK and let the database be restored.

How Do I… Restore a SQL Server database to a new server?

Evidence

Evidence

You have now successfully restored and moved the database files — more evidence.

How Do I… Restore a SQL Server database to a new server?

New query

New query

Let’s move on to my preferred method which eliminates all the point and clicking. You can do this same thing using a TSQL Restore with move statement.

Moving a database with T-SQL
Let’s begin by opening up SQL Server Management Studio and clicking the New Query button.

How Do I… Restore a SQL Server database to a new server?

Logical names

Logical names

Our first step will be to run the following query:

Restore FILELISTONLY FROM DISK=’d:\Business_Data.bak’

This query allows us to find out the logical name of the database and log file which is needed to appropriately restore a database to a new path.

How Do I… Restore a SQL Server database to a new server?

Restore to new path

Restore to new path

Once we have these names, we will use the following query to restore a database to a new location.

RESTORE DATABASE Business_Data_TSQLFROM DISK=’d:\Business_Data.bak’WITHMOVE ‘Business_Data’ TO ‘D:\TSQL\Business_Data.mdf’, MOVE ‘Business_Data_log’ TO ‘D:\TSQL\Business_Data_log.ldf’

This query will restore the database to a new path.

How Do I… Restore a SQL Server database to a new server?

Percentage finished

Percentage finished

You can see where the logical name and the physical name are necessary for the Restore FileListOnly TSQL statement. You can also add the stats clause if it is a big database to know the percentage finished.

RESTORE DATABASE Business_Data_TSQLFROM DISK=’d:\Business_Data.bak’WITHMOVE ‘Business_Data’ TO ‘D:\TSQL\Business_Data.mdf’, MOVE ‘Business_Data_log’ TO ‘D:\TSQL\Business_Data_log.ldf’, STATS=5

In this tutorial, I restored Full Backups. If you are restoring differential or transactions log backups, do not forget to use the With NORECOVERY clause in your statement.

  • Data Management
  • Account Information

    Share with Your Friends

    How Do I… Restore a SQL Server database to a new server?

    Your email has been sent

Share: How Do I... Restore a SQL Server database to a new server?
Image of steven s. warren
By steven s. warren
Steven S. Warren is a popular author residing in Winter Haven, Florida with his wife and 2 children. As a columnist on such well-known IT web sites as Techrepublic.com, CNET, and ZDNET, Steven has published numerous articles. Additionally, Steven
  • Account Information

    Contact steven s. warren

    Your message has been sent

  • |
  • See all of steven s.'s content

TechRepublic Premium

  • TechRepublic Premium

    Point of Sale Security Guide and Checklist

    For retail businesses, the POS system is arguably their most important IT system. This TechRepublic Premium guide, and the accompanying checklist, will help you successfully secure a POS system for your business enterprise. From the guide: ACCESS CONTROLS The first line of defense in any POS system is the control of both physical and network ...

    Published:  September 24, 2023, 4:00 PM EDT Modified:  September 25, 2023, 5:00 PM EDT Read More See more TechRepublic Premium articles
  • TechRepublic Premium

    Quick Glossary: Spacetech

    The space industry is witnessing a profound transformation, driven by new technologies and the greater participation of the private sector. From satellites to weather, agriculture and communications to space connectivity, industrial IoT devices and scientific, government or space exploration missions, the potential is vast. TechRepublic Premium presents this spacetech glossary to help you understand the ...

    Downloads
    Published:  September 24, 2023, 4:00 PM EDT Modified:  September 25, 2023, 8:32 AM EDT Read More See more TechRepublic Premium articles
  • TechRepublic Premium

    Internet and Email Usage Policy

    This policy from TechRepublic Premium sets forth guidelines for the use of the internet, as well as internet-powered communication services, including email, proprietary group messaging services (e.g., Slack) and social networking services in business contexts. It also covers Internet of Things use and bring-your-own-device practices. From the policy: SAFE BROWSING • Use a modern, supported ...

    Downloads
    Published:  September 24, 2023, 4:00 PM EDT Modified:  September 25, 2023, 9:00 AM EDT Read More See more TechRepublic Premium articles
  • TechRepublic Premium

    Email/Instant Message/Voicemail Retention Policy

    Maintaining electronic communications in business involves walking a fine line. Employees want relevant information to be kept available for future reference so that they can do their jobs, but keeping these items long-term can pose security and resource risks. The purpose of this policy from TechRepublic Premium is to establish requirements for the retention and ...

    Downloads
    Published:  September 24, 2023, 4:00 PM EDT Modified:  September 25, 2023, 8:00 AM EDT Read More See more TechRepublic Premium articles

Services

  • About Us
  • Newsletters
  • RSS Feeds
  • Site Map
  • Site Help & Feedback
  • FAQ
  • Advertise
  • Do Not Sell My Information
  • Careers

Explore

  • Downloads
  • TechRepublic Forums
  • Meet the Team
  • TechRepublic Academy
  • TechRepublic Premium
  • Resource Library
  • Photos
  • Videos
  • TechRepublic
  • TechRepublic on Twitter
  • TechRepublic on Facebook
  • TechRepublic on LinkedIn
  • TechRepublic on Flipboard
© 2023 TechnologyAdvice. All rights reserved.
  • Privacy Policy
  • Terms of Use
  • Property of TechnologyAdvice