[adsense slot=”5402167102″ width=”468″]
One of those clean-up tasks we come across in development severs is to move database mdf and ldf files to a new location. SQL Server isn’t as GUI-friendly yet to allow one to just change the paths on database properties. I have tried this out in SQL Server 2008, but the method must essentially be the same in 2005 and 2000 versions also.
What we have to do is this.
– Detach the Database
– Move the files
– Attach the database with the new location
Instead of “use master”, you can just set the query window drop-down to any DB other than DB_To_Move
Now, you need to move the files from its current location to wherever you need it to be (say, d:\data\)
sp_attach_db DB_To_Move, 'D:\data\dbtomove.mdb', 'D:\data\dbtomove_log.ldb'
Again, ideally it is just the sp_attach_db line that you would need to execute. Make sure the file security permissions are set aptly at the target location.
However, things get a bit tricky if you want to move system db files to a new location (we don’t usually findourselves doing this). Please refer this for further reference on that.
SQL Server 2008 Management Studio does not allow “saving” a table change, when it involves recreation. This is a good feture in general terms but may not be desired in a development environment. Here is how we can ask the designer to allow such changes.
Go to Tools > Options
And in the dialog below that opens, go to Designers > Table and Database Designers
This is a simple (but often useful) tool to generate the xml schema of a database table. The tool supports only SQL Server over Windows Authentication. The source code here can be easily extended for other uses.
SqlConnection LocalDB = new SqlConnection("Data Source=" + ServerName + ";Initial Catalog=" + DatabaseName + ";Integrated Security=True");
String Query = "SELECT * FROM " + TableName;
SqlDataAdapter Adapter = new SqlDataAdapter(Query, LocalDB);
DataSet Set = new DataSet("DS" + TableName);
Often there is a requirement to get column values in a table to a concatenated string. Say, we have e-mail ID as one column on a person table and want a comma separated list of all (or a subset) of e-mail ID’s.
Here is one dirty little piece of code to do just that! :)
SELECT EmailID + ';' AS [text()] FROM EventDetails WHERE EventName='MyEvent' FOR XML PATH('')
The above query returns a concatenated string of values in EmailID column, separated by semi-colon.