SQL 7.0 Proper backup and restore procedures

[grez]

Hi! I have a client (check cashing business) who uses a program called Cashwise. It's database is able to be backed up via SQL 7.0. The current computer is running W2K Pro with Cashwise & SQL 7.0 (two computers, P2P environment, one XP Pro workstation connected to and running cashwise off of the W2K machine). I am familiar with where the files are that need to be backed up, but I have NEVER messed with SQL. I did call Cashwise and get step by step instructions, however, wanted to run it by some here so I can feel more confident.

The instructions on how to backup and restore seem to be straight forward. Here is my concern. If I backup the database to the "xp workstation", then replace the W2K box with a new box, we'll call this the "XP Server" (although it's really not a server), perform a fresh install of cashwise, then SQL 7.0, then perform the restore from the "xp workstation", will this work just fine? I keep seeing things about "detatch" and "attatch". Would these terms apply here? If so, where can I get GOOD information, step by step? Any other suggestions or recommendations?

Thank you in advance for your time and efforts!!

grez

 

[mrdenny]

If you are switching from one machine to another Detaching and attaching the databases would be the easiets way.

Check BOL for the best info. I'm not sure if these are documented in BOL for SQL 7. You might need to get the BOL for SQL 2000. In this instance the syntax is the same.

sp_detach_db 'databasename'

copy the files to the new sql server.

to attach sp_attach_db @dbname='databasename',
@filename1='c:\pathtoafile.mdf',
@filename2='c:\pathtoanotherfile.ldf'


Denny

 

[grez]

Denny,

Thanks for your response! Please excuse my ignorance here.

1)What is "BOL"?
2)Where / when exactly do I insert the syntax?

Any good web sites on Enterprise Manager for SQL 7.0 where I can learn something kind of quick, like step by step? I do have a step by step available, but would like to see a visual to gain more knowledge first.

Thanks again!!

Ron

 

[mrdenny]

BOL is books online. You can get it from the SQL Site.

http://www.microsoft.com/sql/

You'll use Query Analyzer to run the commands.

I don't know of any sites. This will probally be a good place to get info.

Denny

 

[nigelrivett]

I would advise a backup and restore rather than a detach and attach. You should do a backup before the detach anyway in case there is a problem so why not use it - it should be smaller than the mdf too.

>> will this work just fine?
It depends on the application. If it holds all info in the database (it probably does) then it should be ok but if it holds data in the registry or ini files then it might have problems.

You will probably need to install sql server before cashwise.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[grez]

OK, I have the machine in front of me. Going through the backup process. Here is the error I receive:

(header) MSFT SQL-DMO (ODBC SQL State: 42000)
The volume on device "C:'MSSQL7\BACKUP\*filename*.bak" is not part of a multifamily (RAID) media set. Use WITH FORMAT to form a new RAID set.

Need some serious help with this please. Thanks in advance!

 

[nigelrivett]

???

what is the c drive? Is it the local system disc?
How are you doing the backup.
Does it have enough space for the backup?
How are backups taken at the moment? Can't you just use the last one?

In enterprise manager right click on database, all tasks, backup database.
Get rid of any devices that are there.
add..
Put in a backup file to create - you can see a list of the available drives/directories. Then OK.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[grez]

Hi again. Yes, the c drive is the local disk. Finally beyond this point though. There was a scheduled backup that did take place at midnight last night. Copied those files over to a test machine and all is well with that. However, now I have a restore situation. All goes perfect with the restore setup, but when I go to hit "OK", it states it must be in single user mode. With that, did some research, and found to use the following at the command line: "sqlservr.exe -c -m". It actually performed the operation in front of me, went back to Enterprise Mgr, and it STILL came up "must be in single user mode".

Any ideas from here? Thank you to all again!

grez

 

[nigelrivett]

That is only for restoring a system database.

In enterprise manager
Right click on databases, all tasks, restore database
from device, select devices, add
type or browse to path of backup file.
ok, ok
retore as database - type in the name of the database (this is probaly your problem)
options, move to physical filename, - type in paths for .mdf and ldf that exist - it will create the files but needs the directory to exist.


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[grez]

Hi. Tried that already. Acutally did that exact process and was able to select the database and tried to back up and also typed in the db name and tried to backup. No go.

grez

 

[grez]

Let me throw this one out. Again, please excuse my ignorance to this stuff, just trying to understand it to get it done.

If I use "detach/attach", then I would do the following (correct me where I'm wrong here):

1)On the current machine, run the detach commands at the command line.
2)After running the commands, this allows the files to be *just copied* like a right click / copy deal, I can copy them over the network the new machine then.
3)After placing them on the new machine, run the command line on the new machine "attach" (etc) and then the database would be in good order.

Correct?? Now, my major concern is this, *what if* the detach part works fine, but the attach part does not, are the original files NOT USABLE?? I need to keep the original machine in tact just in case this does not work out.

Thanks again!

 

[nigelrivett]

This is the problem with th edetach/attach procedure - if the file is not attachable then you lose it. For this reason you should always take a backup first - if you are doing that then you may as well restore the backup - it's much more reliable.

>> Hi. Tried that already. Acutally did that exact process and was able to select the database and tried to back up and also typed in the db name and tried to backup. No go.

You should be trying to restore not back up. The message you are getting about single user mode should only happen if you are trying to restore over (replace) a system database - e.g. if master is in the "retore as database" field rather than the database you are tryiong to create.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[grez]

Thanks for the info about detach/attach. Definitely won't try that since I'm the rookie here.

In reviewing your second part (restore as), this is what I am using. Could this be my problem? I am using "restore as database: master. Then restoring from device, select the file (and have tried to just type it too) etc. all the way through. The instructions I rec'd from Cashwise stated to select "force restore over existing db" on the options tab. Tried that checked off and unchecked and still get the single user messsage.

 

[nigelrivett]

master is the system database that holds all the info about user logins and the databases attached as welll as all the system SPs and tables to control locking and such.
You don't want to get involved in restoring that.

If CashWise is creating and using objects in this database then you are in trouble and I would advise staying clear of the product.
But I'm pretty sure they will have a database created for the application and it is this database you need to restore not master.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[grez]

Yes, I'm at that point. I am going to hold off on today's install and call Cashwise tomorrow.

Thank you nigelrivett and all who responded!!

 

[nigelrivett]

Have a look at your server databases - you will probably find one called CashWise or something like that.

If not try running the profiler (look at enterprise manager, tools. If you trace what is happenning on the server and do some searches from the workstations you should see activity against the database that it is using.

What is your backup strategy like? Are you backing up all databases on the server? If not I would advise you to do it urgently.
See

http://www.nigelrivett.net/BackupAllDatabases.html

for a procedure that you can schedule to do it.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[grez]

Yes, backups are taking place each night at midnight, so I'm covered there. Will check out the profiler later on tonight.

Thanks a million again!