Restoring DB in VB6 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Restoring DB in VB6

Author

Topic

bogus
Starting Member

41 Posts

Posted - 2006-04-25 : 18:13:34

I found this thread and posted in it initally: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=35072

TKizer asked me to start a new thread, so here I am.

My client has a desire to be able to make backups and restore them under different names so they can run tests before going live with runs - different scenarios and things of that nature. This will save my fellow staff members a lot of time; cause then we won't have to do their backups and restores.

For example, lets say we have a db called "CompanySalesMaster" and they want to test different commission scenarios against it.

"CompanySalesMaster" is backed-up to companymastersales.bak.

I want to be able to restore this file as BAK_<date>_CompanySalesMaster and not be constrained to the existing name.

I know this is doable via Enterprise Manager, hense, it must be doable via the command line... and the last thing I wanna do is give the client EM!

I have tried using the RESTORE command with a new filename, but all that does is throw an error.

BTW, I am using VB6/SP6, and SQLServer 2000.

For testing purposes, I am using Northwind because of it's size - or lack thereof.

This is the backup code:

oConn.SQL_STRING = " BACKUP DATABASE Northwind TO DISK = 'c:\Northwind.bak'"

This is the restore code:

mRSDatabases.SQL_STRING = "RESTORE Database BAK_Northwind FROM DISK = 'c:\Northwind.bak'"

mRSDatabases.ExecuteSQL

The error:

-2147217900 - The file cannot be overwritten. It is being used by 'Northwind'.

TKizer suggestion:

RESTORE DATABASE SomeDatabase
FROM DISK = 'E:\SomeBackupFile.BAK'
WITH REPLACE, MOVE 'SomeDatabase_Data' TO 'F:\MSSQL\DATA\SomeDatabase_Data.MDF',
MOVE 'SomeDatabase_Log' TO 'G:\MSSQL\DATA\SomeDatabase_Log.LDF'

Thanks for any input!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-25 : 18:15:08

You must use the WITH MOVE option in the RESTORE command as the MDF and LDF file are already in use. So you need to move the files to a different location or file name.

Tara Kizer
aka tduggan

bogus
Starting Member

41 Posts

Posted - 2006-04-25 : 18:22:18

Ok. I will play with that. Thanks for the assistance!

bogus
Starting Member

41 Posts

Posted - 2006-04-26 : 18:20:07

Tara,

I have been playing with your suggestion, and all I get is an error...

This is my SQL statement:

Restore Database BAK_20060426_Northwind From Disk = 'C:\Test\Northwind.bak'
With Replace, Move 'C:\Test\Northwind.mdf' To 'C:\DATA\BACKUP_TESTING\Northwind.mdf',
Move 'C:\Test\Northwind.ldf' To 'C:\DATA\BACKUP_TESTING\Northwind_log.ldf'

Error:

Logical file 'C:\Test\Northwind.mdf' is not part of database 'BAK_20060426_Northwind'. Use RESTORE FILELISTONLY to list the logical file names.

The goal is to be able to rename the database to something new and exciting.

I am using Northwind as my test database due to its small size.

Thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-26 : 18:22:19

Your command is incorrect. You'll need to run RESTORE FILELISTONLY to see what the logical names are. You are using the physical names where you shouldn't.

RESTORE FILELISTONLY
FROM Disk = 'C:\Test\Northwind.bak'

C:\Test\Northwind.mdf and C:\Test\Northwind.ldf are physical names. You need to replace these with the logical ones. The RESTORE FILELISTONLY command will show them.

Tara Kizer
aka tduggan

bogus
Starting Member

41 Posts

Posted - 2006-04-26 : 18:36:23

interesting... I will figure this out eventually.

thanks! you sure are a big help!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-04-26 : 18:50:39

You can also get the logical file names this way (for Northwind).

Change the database name to get the logical file names for your database.


select name from northwind.dbo.sysfiles

Results:

name
--------------------------------
Northwind                                                                                                                       
Northwind_log                                                                                                                   

(2 row(s) affected)

CODO ERGO SUM

bogus
Starting Member

41 Posts

Posted - 2006-04-26 : 18:54:55

oh, wait, I need the logical names... ok... I see.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-26 : 18:58:56

So I guess you didn't run RESTORE FILELISTONLY to get the logical names. You don't need to use the physical names in the RESTORE DATABASE command! Michael has shown the default Northwind logical names. Here is your updated query using the logical names instead of physical names:

Restore Database BAK_20060426_Northwind From Disk = 'C:\Test\Northwind.bak'
With Replace, Move 'Northwind' To 'C:\DATA\BACKUP_TESTING\Northwind.mdf',
Move 'Northwind_log' To 'C:\DATA\BACKUP_TESTING\Northwind_log.ldf'

Check out both RESTORE commands in SQL Server Books Online for more information.

EDIT: I see you edited your reply while I was typing. I should have quoted it so that it was in context. Oh well. You should now have the correct command.

Tara Kizer
aka tduggan

bogus
Starting Member

41 Posts

Posted - 2006-04-26 : 19:01:03

It works!

Thanks, guys! If I can ever help you get your Vette's running, look me up! :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources