May 30, 2012 at 7:57 am
Hi all
I need to detach 200+ db's from different SQL Server Express 2005 machines
Is there an easy way of creating a script to do this so I can automate it as much as possible?
Thanks
Craig
May 30, 2012 at 8:08 am
Craig
Probably the easiest way (especially if you are going to do this more than once) is to create an SSIS package to loop through all the servers and detach the required databases. Other than that, you'll have to connect to each server individually and execute a script on each. You could execute the same script on each if it included an existence test for the databases, but be careful not to detach databases of the same name on different servers that you did not intend to!
John
May 30, 2012 at 8:37 am
Unfortunately they are not all in the same place
I have a team of 10 (non technical) people who are going to visit each location with a memory stick to remove the db, which is why I wanted a script to eliminate any thinking on their part 🙂
May 30, 2012 at 9:07 am
Craig
Does the database have the same name in each case? Why not write an sp_detach_db script that your people can execute on each server? To make it even more foolproof, use sqlcmd and put it into a batch file so that they can just run that.
John
May 30, 2012 at 9:11 am
Hi John
It does have the same name
May 30, 2012 at 10:39 am
use a batch file. In it
sqlcmd -E -Q "sp_detach_db @dbname='mydb'"
They can double click the batch file to detach the database.
May 31, 2012 at 4:27 am
Thanks for this - however when I'm testing it I get the an error stating
Named Pipes Provider could not provide a connection to SQL Server
SQLcmd: Error: Microsoft SQL Native Client: An error has occured while establishing a connection to the server.
When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections
Sqlcmd: Error: Microsoft SQL Natuve Client : logintimeout expired
TCP and named pipes are allowed though?
Thanks
May 31, 2012 at 4:43 am
Craig
You may need to use the -S parameter in sqlcmd to specify the server, if you use named instances or non-default ports.
John
May 31, 2012 at 4:46 am
Thanks John
I've just tried it using a batch file to stop the SQL Service and then copy the mdf and ldf to a datastick which worked fine
Thanks
Craig
May 31, 2012 at 5:00 am
But then you still have the database online when you restart SQL Server. Wasn't the idea of the exercise to detach it? If you just want a copy of the database, your best option is to take a backup, and then there's no downtime. Better still, since you back up your databases every night (don't you?), just copy the most recent backup on to your datastick. I know it's more difficult to get sqlcmd working than just stopping and starting SQL Server, but consider the time you spend learning it as an investment in your future - once you know how to do it, you'll be able to use it again and again. This will stand you in good stead in environments where downtime is not an option.
John
May 31, 2012 at 5:06 am
True - I should stop taking the easy way out! -S switch you say....
I really only want the data from the PC - and to make sure it isnt accessible on the PC anymore
The databases are not backed up as I can restore\resync from the main server at anytime - the reason I need the data is that the main server is being decomissioned (my company no longer owns it) as is the software on the PC's but I need to keep the data from each PC for 7 years in case I'm audited
Thanks
Craig
May 31, 2012 at 5:17 am
craighenderson (5/31/2012)
I really only want the data from the PC - and to make sure it isnt accessible on the PC anymore
In which case, you will indeed need to detach the database and not just copy the data files.
The databases are not backed up as I can restore\resync from the main server at anytime - the reason I need the data is that the main server is being decomissioned (my company no longer owns it) as is the software on the PC's but I need to keep the data from each PC for 7 years in case I'm audited
If you can restore from the main server at any time, why not get the data from there and save your colleagues a trip to all those sites? Reading between the lines, it sounds as if the option to restore from the main server may not have been open to you from the time your company handed it over. If that's the case, you really ought to have been performing backups locally.
If you're in an EU country (or even if you're not), make sure you're not in breach of Data Protection laws by holding on to data for that long.
John
May 31, 2012 at 5:52 am
My colleagues are visiting the sites anyway to perform training and I though it would save time - potentially it wont though.
Can I not just attach the mdf to an instance of SQL Express elsewhere? I'm sure I've done this in the past.
I will be remotely cleaning these PC's up over the course of the next year but just don't have the time to remotely take a back up just now unfortunatley
May 31, 2012 at 6:14 am
craighenderson (5/31/2012)
Can I not just attach the mdf to an instance of SQL Express elsewhere? I'm sure I've done this in the past.
As far as I'm aware, you can, provided that the product version of the new server is no older than that of the old one. There may be some gotchas associated with attaching databases that haven't been formally detached - you may wish to do some research on that.
John
Edit - corrected typo.
May 31, 2012 at 6:16 am
No worries
Thanks for your advice John
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply