Copy sql db to external usb drive

Bokeh

We are upgrading a few of our computers, and I need to pull the sql 2008 db from 12 machines and put them on 12 new ones. I stopped the service, found the files I needed, and attempted to copy them. I am told right away that there is not enough room on the disk. Files are less than 5gb, and the external drive has 250gb free. I tried the google approach, and the few suggestions I saw didnt work.

Has anyone done this successfully?

meadIT

What about connecting using SQL management studio and just running a backup job? Are other file types able to be copied to the drive?

erpadmin

Bokeh wrote: »

We are upgrading a few of our computers, and I need to pull the sql 2008 db from 12 machines and put them on 12 new ones. I stopped the service, found the files I needed, and attempted to copy them. I am told right away that there is not enough room on the disk. Files are less than 5gb, and the external drive has 250gb free. I tried the google approach, and the few suggestions I saw didnt work.

Has anyone done this successfully?

Wait, your SQL databases are less than 5 gb?

I don't know what you're doing, but normally when I want to do a database copy, I make a backup of the db, copy the safeset (file with .bak extension), and then restore it to whatever SQL instance I want.

I'm guessing you are trying to copy the data and log files and then trying to reattach them. That can and does work too, but I prefer the backup/restore option, personally.

RobertKaucher

erpadmin wrote: »

I don't know what you're doing, but normally when I want to do a database copy, I make a backup of the db, copy the safeset (file with .bak extension), and then restore it to whatever SQL instance I want.

I'm guessing you are trying to copy the data and log files and then trying to reattach them. That can and does work too, but I prefer the backup/restore option, personally.

I agree with ERP, here. And I want to add that any person who manages SQL Servers should know the BACUP DATABASE and RESTORE DATABASE commands inside out and cold.

I would suggest creating a backup.sql file and a bactch file that executes it using SQLCMD to do the backup. It might take a little time, but would save you time in the long run. Another option, and probably the best, would be writing a PoSh script that uses SQLPSX to take a list of servers/instances and then backs them all up to a network drive which you then copy to your USB disk. Test the restore to another, test instance before you do anything to the systems!

How to: Create a Full Database Backup (Transact-SQL)

How to: Restore a Database Backup (SQL Server Management Studio)

SQL Server PowerShell Extensions

Bokeh

Thanks for the tips. These db's are small, the only purpose they serve is for Goldmine PE 8.5. So each one of our computers have a copy of sql 2008 on it, running XP. Management decided to upgrade the hardware for 12 desktops, so I just need to travel to the 12 places and get the data off. I'll set up a backup and then zip that for transfer to the ftp site, and then reload on the new machines before delivering them later this month.

RobertKaucher

Bokeh wrote: »

Thanks for the tips. These db's are small, the only purpose they serve is for Goldmine PE 8.5. So each one of our computers have a copy of sql 2008 on it, running XP. Management decided to upgrade the hardware for 12 desktops, so I just need to travel to the 12 places and get the data off. I'll set up a backup and then zip that for transfer to the ftp site, and then reload on the new machines before delivering them later this month.

OK, if they are 12 different sites the PowerShell idea is probably not workable. I would certainly write a batch file, though. Plug in the USB drive and then just double click... Would make it quite easy!

it_consultant

Did you ask goldmine what steps you need to take to migrate from one PC to another. I am sure the database backup is very similar to what other posters have said, in my experience though, it is better to use the manufacturers procedures. In case something gets screwed up and you end up talking to their tech support, it will be much easier to troubleshoot.

RobertKaucher

it_consultant wrote: »

Did you ask goldmine what steps you need to take to migrate from one PC to another. I am sure the database backup is very similar to what other posters have said, in my experience though, it is better to use the manufacturers procedures. In case something gets screwed up and you end up talking to their tech support, it will be much easier to troubleshoot.

GREAT POINT!

You should always follow the manufacturer's guidelines for backing up SQL DBs for stuff like this as some software requires that additional files also be backed up. So I would certainly make sure you look on their support site or give them a quick call.

Bokeh

GM Tech Support were the folks who said just stop the service, copy these files, and you'll be good to go to reinstall on another machine. I contacted them first before trying anything.

jimmy1829

Check the file system on the external hd, make sure it is on NTFS

Bokeh

That was the deal. I spoke with a senior support person at FrontRange. The portable drive needs to be NTFS, which it wasn't. So I'll clean everything off and then reformat the drive.

RobertKaucher

jimmy1829 wrote: »

Check the file system on the external hd, make sure it is on NTFS

Gotta love that file size copy limit!

As a DBA I would still do this as a backup and not copy the mdf and ldf files, though.

erpadmin

RobertKaucher wrote: »

Gotta love that file size copy limit!

As a DBA I would still do this as a backup and not copy the mdf and ldf files, though.

+1 on that...as I said, it CAN work, but if you don't know what you're doing, a bunch of stuff can go wrong.

RobertKaucher

erpadmin wrote: »

+1 on that...as I said, it CAN work, but if you don't know what you're doing, a bunch of stuff can go wrong.

Plus it's easier to move it to servers that might have a different drive configuration.