Discussion Locked

Whats the best way to backup a SQLserver

0 helpful
3 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
I have SQL server 7.0 that is the live server. I want to back up my databases that are on that server to my development server which is on a remote network. What is the best way about backing up my databases to that remote server. Sometimes when Iuse Enterprise Manager, I lose primary key information and stuff like that. I'm not sure why. Sometimes, when I transfer tables and stored procedures over, It fails as well and I have no clue why.

3 replies

by mtnsqs

The quickest way (and simplest) is to use SQL server's own backup from Enterprise Manager. Once the backup is compete, copy the file to the new server and do a restore. Your erros suggest differences between the systems and SQL server setups.

BOTH servers must be setup EXACTLY the same - including the little things like default systems locales etc (in regional settings) and that you have your SQL devices setup prperly - also look at default character sets, languages etc. Also requires that your users and groups are setup in same way on each machine - you can use Enterprise manager to create a script to generate your users and groups for you and run that ont he second machine after deleteing all its old users.

If the machines are different, then the only really reliable way is to BCP out the data and then BCP it back in again. Its a lot slower but once you have scripts setup it isn't too hard - just tedious.

This discussion is closed to new replies.