Use Backup to Copy a DB

Question

Use Backup to Copy a DB

Felix Burkhard

SSC Rookie

Points: 49
More actions
May 12, 2003 at 5:56 am

#106075

Hi All
I want to be able to use a backup to create a different copy of my DB for training purposes. Eg.
1) backup Northwind
2) restore the resulting .BAK as TestNorthwind
My App then allows to dynamically switch between the two different DBs, so that users can test scenarios before they commit themselves to use the 'live' DB
Anyone out there done this? I can't fuigure out how I can convince restore to use a different DB name. I worked it out, using Detach/Attach, but then the DB is offline for some short time, which is inconvenient...
Thanks anyone
Felix Burkhard

Viewing 11 posts - 1 through 10 (of 10 total)

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

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

Hi,

if I got you right, in EM right-click on the db you want to restore -> All tasks -> Restore Database. From the following dialog choose the db you want and some other settings AND don't forget on the Options tab to change the filename in the 'Restore As' row!!!!

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Frank Kalis SSC Guru Points: 111183 More actions · Answer 2

in addition to my previous post. On the General tab you can simply overwrite the value in the 'Restore as database' combo. You shouldn't forget this as well 🙂

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

DRBuckingham SSC Eights! Points: 996 More actions · Answer 3

To perform this via Query Analyzer, first do (adjust file and path accordingly):

RESTORE FILELISTONLY FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL\Backup\Northwind.BAK'

Take note of the logical file names listed.

Then do (replace 'Northwind_Data' and 'Northwind_Log' with the logical file names from the previous step and adjust the paths as necessary):

RESTORE DATABASE [TestNorthwind]

FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL\Backup\Northwind.BAK'

WITH RECOVERY, MOVE 'Northwind_Data' TO N'C:\Program Files\Microsoft SQL Server\MSSQL\Data\TestNorthwind_Data.MDF', MOVE 'Northwind_Log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL\Data\TestNorthwind_Log.LDF'

Once that is complete, you may optionally correct the logical file names by doing (adjust logical name as necessary):

ALTER DATABASE [TestNorthwind]

MODIFY FILE (NAME = 'Northwind_Data', NEWNAME = 'TestNorthwind_Data')

GO

ALTER DATABASE [TestNorthwind]

MODIFY FILE (NAME = 'Northwind_Log', NEWNAME = 'TestNorthwind_Log')

GO

We do this frequently in-house. It drives me nuts for the logical names to reflect incorrect database names, so I always do the MODIFY NAME to make everything consistent.

David R Buckingham, MCDBA,MCSA,MCP

Andy Warren SSC Guru Points: 119902 More actions · Answer 4

Fixing the file names saves a lot of headaches, worth the extra minute to do.

Andy

http://www.sqlservercentral.com/columnists/awarren/

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter

Felix Burkhard SSC Rookie Points: 49 More actions · Answer 5

Thanks DRBuckingham for the very useful reply, particularly the rename of the logical names. Everything is working now like I wanted.

I have a 'Globals' table and at the end I insert a timestamp so that I can check in the program, when I updated the test DB the last time.

Thanks everyone for your time!

David.Poole SSC Guru Points: 76035 More actions · Answer 6

What exactly does N'string' do? Is it really necessary?

LinkedIn Profile

Frank Kalis SSC Guru Points: 111183 More actions · Answer 7

Hi,

quote:
What exactly does N'string' do? Is it really necessary?

not sure if it is really necessary, but it convert the string to unicode

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

David.Poole SSC Guru Points: 76035 More actions · Answer 8

It takes a special sort of genius to name a function so that is so short it qualifies for a noise word in search engines!

I can't see that anyone would want to cast a character in the ASCII range to the same number, still in the ASCII range.

LinkedIn Profile

Frank Kalis SSC Guru Points: 111183 More actions · Answer 9

quote:
It takes a special sort of genius to name a function so that is so short it qualifies for a noise word in search engines!
I can't see that anyone would want to cast a character in the ASCII range to the same number, still in the ASCII range.

These are strange days

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

DRBuckingham SSC Eights! Points: 996 More actions · Answer 10

No, the N'string' is not necessary. It's a carry-over from the Query Analyzer template that I've never bothered to remove. The conversion will obviously take place without it being specified.

David R Buckingham, MCDBA,MCSA,MCP