Skip to content

TechRepublic

  • Premium
  • Top Products
  • AI
  • Developer
  • Payroll
  • Security
  • Project Management
  • Academy
Resources
  • TechRepublic Premium
  • TechRepublic Academy
  • Newsletters
  • Resource Library
  • Forums
  • Sponsored
Go Premium
Popular Topics
  • Premium
  • Top Products
  • AI
  • Developer
  • Payroll
  • Security
  • Project Management
  • Academy
  • Project Management
  • Innovation
  • Cheat Sheets
  • Big Data
  • Tech Jobs
View All Topics
Go Premium
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

Daily Tech Insider

If you can only read one tech story a day, this is it.

TechRepublic TechRepublic
  • TechRepublic on Facebook
  • TechRepublic on Twitter
  • TechRepublic on YouTube
  • TechRepublic RSS
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
  • Editorial Policy
  • Legal Terms
  • Privacy Policy
© 2024 TechnologyAdvice. All rights reserved.
CLOSE

Create a TechRepublic Account

Get the web's best business technology news, tutorials, reviews, trends, and analysis—in your inbox. Let's start with the basics.

Already registered? Sign In
Use Facebook
Use Linkedin

* - indicates required fields

CLOSE

Sign in to TechRepublic

Not a member? Create an account
Use Facebook
Use Linkedin

Lost your password? Request a new password

CLOSE

Reset Password

Please enter your email adress. You will receive an email message with instructions on how to reset your password.

Check your email for a password reset link. If you didn't receive an email don't forgot to check your spam folder, otherwise contact support.

Back to login
1 Finish Profile
2 Newsletter Preferences
CLOSE

Welcome. Tell us a little bit about you.

This will help us provide you with customized content.

No thanks, continue without
1 Finish Profile
2 Newsletter Preferences
CLOSE

Want to receive more TechRepublic news?

Newsletter Name
Subscribe
Daily Tech Insider
Daily Tech Insider AU
TechRepublic UK
TechRepublic News and Special Offers
TechRepublic News and Special Offers International
Executive Briefing
Innovation Insider
Project Management Insider
Microsoft Weekly
Cloud Insider
Data Insider
Developer Insider
TechRepublic Premium
Apple Weekly
Cybersecurity Insider
Google Weekly
Toggle All
No thanks, continue without

You're All Set

Thanks for signing up! Keep an eye out for a confirmation email from our team. To ensure any newsletters you subscribed to hit your inbox, make sure to add newsletters@nl.technologyadvice.com to your contacts list.

Back to Home Page