[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Full Restore with TSQL & Name Change of Database

Posted on 2006-05-16
5
Medium Priority
?
620 Views
Last Modified: 2011-09-20
Hi everyone,

I'm currently taking an SQL Server 2000 course.  I had to do a full back of the Northwind database using TSQL in Query Analyzer.  I did that.  Now I have to delete the database and restore and change the name of the database it using my backup copy.

I've deleted the database and have been able to complete restore it.  However, I cannot figure out how to change the name.  I've looked through this site for help and just got more confused.  Here is the full restore code I have that works.  Just need help or guided in the right direction on how to change the name.

--Restore Northwind from "RhondaColeman_WindBackup_TSQL"
--Recover the database and change the name to "nWindRestored"

USE MASTER
RESTORE DATABASE Northwind
FROM Rhonda_WindBackup_TSQL
WITH FILE = 1,
RECOVERY

Thanks,
Rhonda
0
Comment
Question by:Rhonda1928
  • 2
  • 2
5 Comments
 
LVL 13

Expert Comment

by:Atlanta_Mike
ID: 16693836
Easiest way:

RESTORE DATABASE [NewDbName]
FROM  DISK = 'D:\MSSQL\BACKUP\backupfilename.bak' -- make sure the location of the file is correct
WITH  FILE = 1,  NOUNLOAD
GO
0
 
LVL 20

Expert Comment

by:Sirees
ID: 16693879
When you use the T-SQL RESTORE command, you can either (1) specify the name
of a current database or (2) give a new name [a database that does not exist
yet].

If you provide a new name, the database will be created from your backup
file with a new name.  MAKE SURE that you use the WITH MOVE option to move
the data and log files to a different location!


0
 

Author Comment

by:Rhonda1928
ID: 16697854
I was able to restore the database (with a different name) using TSQL.  However, I need to also make copies of and change the name of the "northwind.ldf" and "northwind.mdf" files on my computer.  Any idea how to do that or where to go to read about that?

Rhonda
0
 

Author Comment

by:Rhonda1928
ID: 16701074
The Northwind database has been deleted and the "northwind.ldf" and "northwind.mdf" files are no longer in the \Microsoft SQL Server\MSSQL\Data folder.

Can I copy and change the names of "northwind.ldf" and "northwind.mdf" files from the .BAK file that I am using for restore?

Thanks,
Rhonda
0
 
LVL 13

Accepted Solution

by:
Atlanta_Mike earned 2000 total points
ID: 16709408
Yes, no problem. Something liek this...

RESTORE DATABASE [Northwind] FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Northwind.BAK' WITH  FILE = 1,  MOVE N'Northwind_Data' TO N'C:\Program Files\Microsoft SQL Server\MSSQL\Data\NorthwindNewName.mdf',  MOVE N'Northwind_Log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL\Data\NorthwindNewName_log.ldf',  NOUNLOAD
GO
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question