Restoring to a differently named DB ?

Question

Restoring to a differently named DB ?

ZeeAtl

SSCommitted

Points: 1569
More actions
October 5, 2009 at 6:27 am

#210378

How do I backup one named Database and restore to a differently named database?
I tried it with wizards and was unsuccessful.
I'm sure it's dirt simple.
Pardon my newbieness.
Zee
Accidental DBA

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

@SQLNumpty SSCrazy Points: 2940 More actions · Answer 1

I am assuming your are using the 2005 SSMS and have connected to your target server for the restore.

* If you right click on Databases and choose Restore Database, the General screen should be on To database; type the name of the new database here

* Click on From device and enter the path and name of your backup. Don't forget to tick the Restore box ...

* Click on Options

* Update the path to the location where you want the files to be placed

* Update the Recovery State if needed

This should be all you need. You can script it (my favourite) if you like.

Hope this helps.

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 2

Mark's procedure will work. It's how I typically restore things for one-off situations. However, I'd recommend you try scripting the restore. You can set up the restore, and then click the script button at the top of the dialog. It will let you change any options you need to, and also learn how this works. In a DR scenario, or if you need to do this repeatedly (like for QA), it's good to know the T-SQL.

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 3

It's also a lot faster to learn the TSQL. Once you know how to type out the RESTORE statement, you'll find that it's quicker than trying to click your way through the GUI for restoring databases.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Grant Fritchey (10/5/2009)
It's also a lot faster to learn the TSQL. Once you know how to type out the RESTORE statement, you'll find that it's quicker than trying to click your way through the GUI for restoring databases.

I guess I've been doing it enough that I find using the GUI to create the script is faster than my typing it from scratch. I still find myself making some minor changes to the generated script, but still faster than writing from scratch.

Mark Marinovic SSCrazy Points: 2472 More actions · Answer 5

Personally, I have NEVER done a restore through the GUI, either in SSMS or through EM :blush:

I have a script that I use over and over again in which the *.bak file, physical paths, logical names, etc. are variables and I just tweak options and file names here and there to create the restored backup. The way I wrote it only accomodates one data and one log file, but that covers 99% of the cases I deal with. Exceptions are just dealt with as they come up.

I VERY recently had a TempDB dedicated disk fail on a very important server (NAS). The ONLY way to create last-minute backups (strictly for a CYA measure before we took the server offline) was using sqlcmd. SSMS would not start properly as tempdb on the target server is used to create the database/object trees that give the clicky-clicky magic something to click on. Of course we had backups and this was only a precautionary measure but that's beside the point.

I think it's crucial to learn the T-SQL behind everything, as there may be a day when GUI is NOT an option. Makes you more valuable during a DR situation as well.

My $0.02.

MJM

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Mark Marinovic (10/5/2009)
Personally, I have NEVER done a restore through the GUI, either in SSMS or through EM :blush:
I have a script that I use over and over again in which the *.bak file, physical paths, logical names, etc. are variables and I just tweak options and file names here and there to create the restored backup. The way I wrote it only accomodates one data and one log file, but that covers 99% of the cases I deal with. Exceptions are just dealt with as they come up.
I VERY recently had a TempDB dedicated disk fail on a very important server (NAS). The ONLY way to create last-minute backups (strictly for a CYA measure before we took the server offline) was using sqlcmd. SSMS would not start properly as tempdb on the target server is used to create the database/object trees that give the clicky-clicky magic something to click on. Of course we had backups and this was only a precautionary measure but that's beside the point.
I think it's crucial to learn the T-SQL behind everything, as there may be a day when GUI is NOT an option. Makes you more valuable during a DR situation as well.
My $0.02.
MJM

Just to clarify, I didn't say I didn't know how to write a restore script, just that using SSMS is faster than my typing.

Mark Marinovic SSCrazy Points: 2472 More actions · Answer 7

Lynn Pettis (10/5/2009)
Just to clarify, I didn't say I didn't know how to write a restore script, just that using SSMS is faster than my typing.

Gotcha - and understood. I didn't mean for it to come out that way if it did 🙂

As I was reading what I was writing - It's becoming apparent that as I age I become more stuck in my ways on certain things.

Mark

Ignacio A. Salom Rangel SSC-Insane Points: 20443 More actions · Answer 8

At work we have a script that generates all the restore T-SQL for all servers and Instances. We used for our DRT's, it is a great script (I didn't write it).

My blog

WILLIAM MITCHELL SSChampion Points: 13766 More actions · Answer 9

Here is a short script to restore a production backup over a test database:

USE master

GO

/*

ABC = production database

ABCTEST = test database

*/

RESTORE DATABASE [ABCTEST]

FROM

DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\ABC_backup_200910212030.bak' -- edit file name to match

WITH

FILE = 1, -- normally this would be = 1

MOVE N'ABC' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ABCTEST.mdf',

MOVE N'ABC_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ABC_log.LDF',

NOUNLOAD,

REPLACE,

STATS = 10

GO

ALTER DATABASE [ABCTEST] MODIFY FILE (NAME=N'ABC', NEWNAME=N'ABCTEST')

GO

mcvilbar Say Hey Kid Points: 701 More actions · Answer 10

mcvilbar

Say Hey Kid

Points: 701

October 27, 2009 at 10:30 am

#1071333

Hi William,

This is a great script. 🙂

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

Mark Marinovic (10/5/2009)
Lynn Pettis (10/5/2009)
Just to clarify, I didn't say I didn't know how to write a restore script, just that using SSMS is faster than my typing.
Gotcha - and understood. I didn't mean for it to come out that way if it did 🙂
As I was reading what I was writing - It's becoming apparent that as I age I become more stuck in my ways on certain things.
Mark

Mark,

I also went back read some what you had written and want you to know that I agree with knowing how to do to things. For instance, many years ago I was a Computer Operator working on DG MV sytems with the AOS/VS OS. We had finger saver macros we used like this one: CXMDD. When training new operators I would not allow them to use it until the fully understood what it was doing. It would label a tape at 6250 BPI on the specified drive with the given label than mount the tape, ie CXMDD 0 MyLabel would label the tape on MTD0 at 6250 BPI with the name MyLabel and then mount the tape for use.

We had another senior operator who taught those up front without making sure that the junior operator knew what it was doing behind the scenes. That resulted in an interesting moment one night with a new op almost relabeled the first tape in a backup set.

Knowing how to do things is important, but I don't mind allowing letting the system do things for me when it is easier and quicker.

Rich Mechaber SSChampion Points: 11130 More actions · Answer 12

I use the GUI for restores, b/c it's not something I do often.

One gotcha in restoring from a backup file using SSMS GUI is that you need to check the Options section. You'll be presented with options to modify the file names and locations for the data/index/log files: "Restore the database files as"

If the filepath/names for the files are the same as existing file names, then SSMS will (correctly) fail b/c you'd be trying to create a new database with filenames identical to the files for an existing database. Simply enter new filenames for the new database that are unique and you should be OK.

For some reason, it often defaults to wanting to create the same filenames for the data and index files, which again will fail -- cannot have 2 separate files with the same name. Edit these to create unique names for the data.mdf, indexes.mdf, and log.ldf files that match your institution's Standards for Naming SQL Server Database Files. 🙂

You can always use the GUI to generate a matching script, which I find very handy for learning T-SQL code I don't use often (like backups/restores). Click the script button in the top of the GUI window. This button exists on nearly all of the SSMS GUI tasks. You may (will?) find that the generated T-SQL needs tweaking, but it's great to have as a starting point.

Good luck,

Rich