Restoring to a new DB - The basics

Hiya,

I've recently gotten into the administration side of SQL and am muddling my way through it as best I can. We have a DB server with three or four databases on it. Maintenance routines do a full backup of each one nightly and weekly and put all sets into one backup (.bak) file.

I've never had to restore them, but I thought I'd put myself through a test run since this is unexplored territory for me and I would prefer not to have to learn in an emergency.

A scenario I can foresee is where I'd have to restore just one table, perhaps to a new location (i.e. not overwrite the live db, just bring up a copy of part of it elsewhere so I can do a data dump from some old data). But when I go to 'restore database' or 'restore file/filegroups' and select the file, it seems to really want to restore the full backup directly over top of the existing database. If I try to restore it to a new, non-existent database, I get an error about how 'logical file master is not a part of database 'new-backup'), and if I try to restore it to a new db that I've created, I get an error about how the databases don't match.

It seems to me that my problem is a philosophical one - perhaps I don't understand the nature of backups, but I feel like what I'm trying to do is pretty simple. I want to take the full backup of database A and restore it, in part (or in full if I have to) to new database B. What do I need to do to accomplish this?

Thanks for indulging me - this must be brain-dead stuff for most of you!

First off, I'll assume you're using SQL 2005. This can be accomplished with SQL 2000, but the steps may be different.

You cannot restore just one table - you'll have to restore the entire DB. If you have Enterprise Edition, you can restore the primary filegroup and any others that you need, and have the database online.

From 'restore database',Choose your backup file.

Instead of picking an existing DB from the drop-down list, just type in a new name, say 'PROD-temp'.

Click on 'options'

In the 'restore as' column, modify the filespecs so that they aren't the same as the original database. (different disk(s), different directory, different name, whatever - just so it's unique)

Hit OK, and you're done.

|||

Hi Kevin,

Thanks for the prompt response.

I don't have Enterprise edition - I have standard edition.

When I do what you describe, I get the following error:

'Logical file master is not part of database backup. Use RESTORE FILELISTONLY to list logical file names.'

When backup is the new DB that doesn't exist yet.

|||

So, with standard edition, you need to do a full database restore.

If you're using Management Studio:

Right-click on Databases and choose 'Restore Database...'

Select 'From File' as source, and use the tool to find your backup file.

Check the 'restore' box.

Then type in the new database name, making sure it isn't the same as any existing DB.

Now go to Options page.

Do not change the 'Original File Name' column.

Do change the 'Restore As' column to specify unique filenames.

That should work.

If it does not, try doing a separate full backup NOT to the same backup file as all the others, and use that as your source.

|||

No luck - that ended being exactly what I'd tried the first time (changing restore as - I don't have an option to change the original file name column). Same error as above.

I can try making a separate full backup, but are you telling me that my backup sets are worthless? I'm willing to accept there's a lot I don't know about SQL backups, but that seems a bit silly.

|||If I'm not restoring the 'master' database, why are the original files in the 'options' screen 'master' and 'mastlog'?|||

You appear to have hit a bug in Management Studio. I looked in our bug database, and it was recently reported.

What happens is that when you have one backup file containing backups from multiple databases, the GUI will populate the database files in the Options page from the FIRST database in the backup file, rather than the one which you select.

Workarounds:

1) For ongoing efforts, I'd recommend modifying your backup job to send backups of each database to a separate file.

2) For now, your best bet is to use T-SQL commands to restore the database. These would take the form:

Use RESTORE FILELISTONLY FROM (DISK="full-path-to-backup-file") WITH FILE = <backup number within file>

to find the proper logical file names for your database.

RESTORE DATABASE <new_database_name> FROM (DISK="full-path-to-backup-file") WITH FILE = <backup number within file>, MOVE 'logical_file_name_in_backup' TO 'operating_system_file_name' , MOVE 'logical_file_name_in_backup' TO 'operating_system_file_name' ]|||

Thanks! I ended up copying the existing database, and then restoring the backup directly to the copied database - and as the databases matched, that was enough for it to work without having to write out the SQL (which was my next stop).

Cheers,

SK