Need help: WITH MOVE clause 1

[k108]

Hi,

I am restoring a database to a new server.

I just read that if I use the "WITH MOVE" clause for the restore, then I don't have to create the databases first.

But what do I do about the logfile? Does this get created automatically as well?

I ask because I WAS in the process of creating my database FIRST in EM... and this is where you specify the name/location of your logfile.

If I don't do this first in EM, then how does it get created?

Thanks, I find all of this very confusing.

 

[mrdenny]

There is no need to create the database before restoring it.

When you start the restore just about first thing that the SQL Server does is drop the old database. It's totaly irelevent to the restored database.

The MOVE clause is used to change the location of the database files. If you are restoring a backup to the same server that it was backed up from then there is no need to use the MOVE clause unless you want to change the drive/folder that the data files (mdf, ndf, or ldf) are located in.

The MOVE clause is handy when backing a database up from server1 and restoring it to server2 when server2 has a different drive setup that server1.

When using the MOVE clause you specify the logical name of the database file. All database files have logical names (including log files).

Lets assume that server1 has a drive setup of C and D with the database files in d:\MSSQL2000\Data. The server that we want to restore two, server2, has a drive setup of C and E and we want to put the database files in e:\MSSQL\MSSQL\DATA.

On server1 we backup the database like normal.

On server2 we use the RESTORE FILELISTONLY to get the logical names of all the files in the database.

RESTORE FILELISTONLY from disk='e:\db.bak'

That will give us the logical names, and physical names of all physical locations that the files used to be in on the old server (server1). We can now use the MOVE option to change the locations.

RESTORE DATABASE db from disk='e:\db.bak'
WITH MOVE 'db_data' to 'e:\MSSQL\MSSQL\DATA\db_data.mdf',
   MOVE db_log' to 'e:\MSSQL\MSSQL\DATA\db_log.ldf'
go

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

Donate to Katrina relief

 

[k108]

Hi MrDenny,

OK, I have some questions for you:

1.) Does the restore have to be from a backup? That is, NOT from the "live" database file? (Sorry if this a dumb question, but not all our databases are backed up, but I still need to restore them to the other server - so do I need to back the un-backed-up database FIRST? I assume the answer is YES.)

2.) Also, same question about the log file. In your script above, you do a move on the log file. This isn't the LIVE logfile is it? I can't see how this would work on a the active logfile. BOL doesn't show how to do a move on the logfile. It only shows how to restore your logfile backups to the other machine.

THANKS

 

[k108]

3.) And what if you DON'T want to restore you logfile??

How does the logfile get created on the target server after you've done the database restore?

Thanks

 

[mrdenny]

1) Yes
2) This would be the transcation log as it appears on the production server at the time the backup database command was issued.
3) You have to restore the log file as it was when you did the full backup. There is no way to restore the database without also restoring the log file. If the log is large on the production server to a log backup before doing the full backup. The log backup should empty the commit transactions from the transaction log. This way when you restore it it won't have all the data in it. It will however still be the same size it was in production.

You can shrink the log file (either in production or after you have restored the database) via the DBCC SHRINKFILE command (check BOL, it's got some good info about it in there).

The log file is restore with the other database files. It's not done after the database is restored.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

Donate to Katrina relief

 

[k108]

3) There is no way to restore the database without also restoring the log file.

Really? This doesn't sound right... I am pretty sure you can restore *just* the database... in fact, I am 99.99% sure.

Which still leaves me at my original question. If you don't restore the log file, can it be created separately (and therefore empty)?

And please understand, I am not trying to contradict you... this just doesn't make sense to me. I am just trying to get to a point where it does make sense to me.

Thanks

 

[k108]

Ok, I think your post is making more sense to me. I guess what fouled me up is the idea that I don't have to restore the logfile along with the datafile. But I just did it in EM, and it does restore the active logfile along with the backup data file.

 

[mrdenny]

yep, that pesky log file is in there. If you didn't restore the log file in theory you could restore the database in an unusable state.

If you had a transaction that was half complete when you started your backup (which can easly happen) and then you restore the database, if you don't have the log your transaction is half complete on the restored database. You can't roll it forward since you don't have the transaction, and you can't roll it back for the same reason.

After the restore is finished any open transactions in the database are rolled forward and completed before you are given the database.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

Donate to Katrina relief

 

[k108]

Hi MrDenny,

As you can tell I don't have much experience with the restore process... however, I have since discovered that everything you said was correct.

I didn't know that when you backup a database that it also "includes" the active log file as part of the backup. But I have now verified this myself. It's confusing because there is also a separate BACKUP LOG command.

You are also correct that you don't have to create the database before restoring. You CAN, but it's just an extra, unnecessary step.

I have now successfully installed SQL Server and restored databases to a separate production server. It's not a big deal, once you know how.

Thanks for your help!!

 

[mrdenny]

no problem. The BACKUP LOG command is there so that you can do quick log backups to improve your restore ability.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

Donate to Katrina relief