Connecting to a Database without Entering Parameters Every time from SSMS

If you are using SSMS regularly, you know that you have supply database name every time. You can avoid this by supplying a default database for a user. However this is not recommended because in case that database is dropped or renamed, user will not be able to login to SQL Server and will receive this error.

Cannot open user default database. Login failed.
Login failed for user 'sa'. (Microsoft SQL Server, Error: 4064)

SQL Server Management Studio is executed from a exe called SSMS which resides by default at  "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe"

For this you can supply server name and database name as following.

"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe" -S Dinesh-PC -D AdventureWorks2008

You can create a short cut with above values and when ever you click the short cut SSMS will database as AdventureWorks2008. However, problem with this is that, Object Explorer will not load objects unless you use conventional connect dialog box.

Delete Old Backup Files

If you have used SQL Server 2000, you know that in SQL Server 2000, deleting previous backup files are easy. In the SQL Server 2000 , the Specify Backup Disk Directory you simple have to specify the period and the extension as shown in following figure.

Where is this option in SQL Server 2005? Well, this is FAQ in many forums and therefore, thought of writing this blog post.

In SQL Server 2005, if you drag and drop the Back Up Database Task, you will get the options to create backup file. However, there won’t be any place to enter remove backup option as in SQL Server 2000.

Because of this, many users are using scripting mechanisms to delete them. However, if you want to delete files inside subfolders, the script will become complex.

In the tool box of Maintenance Plan Tasks there is another task called Maintenance Cleanup Task which can be used to delete old backup as shown in following image.

After configuring this task, you need to connect the task to the backup task as shown in following image.

Changing Parameters for Select Top and Edit Top

In SQL Server 2008, Object Explorer of the SQ Server Management Studio, there are couple of options which are highlighted in the following image.

In SQL Server 2005, there was an option named Open Table, which will open entire table. If that table has lot of rows and columns it will take quite lot of time and resources.

However, in SQL Server 2008, Select Top 1000 Rows will list first 1000 rows and Edit Top 200 Rows will give you the option of editing only first 200 rows.

Problem is how to edit these numbers in case of a need.

In the option menu item of the SQL Server Management Studio, you can change those values as shown in the above diagram. If you want to get all the all the rows, you need to type 0 at the desired place.

Also, this values will be immediately available upon clicking the ok button thus you don’t have to restart SQL Server Management Studio.

Error when modifying table from SQL Server Management Studio in SQL Server 2008

In SQL Server 2008, when you change table from SQL Server management studio, if that change needs table to be dropped and re-created, it will return and error and change will not be affected. Error is shown in following image.

This will effect operations like changing int column property to identity and changing varchar column to int etc.. In case of table recreation, it can take lot of time if there is lot of records in the tables.

However, there is an option to allow these changes to occur.

Select Options from the Tools menu. In that dialog go to Designers option and de-select highlighted option. By default this option is selected.