How can I compact a SQL server database and change the backup location

[lesj1968]

Hi

I have noticed my SQL server database is expanding in size which is not unusual. In MS Access I can compact my databases. However how do I do the same thing for a SQL server database? When I right click the database name there is an option called "Shrink Database". What does this do?

Also, how can I change the default location to where the backup databases are saved?

Many thanks for any help.

Les

 

[lesj1968]

Hi

By default location to where the backup databases are saved I am referring to the automatic system backups that take place. I can manually save the database to a location of my choice. I was wondering how to edit the schedule (if there is on) and default location to where the automatic backups end up. Do the backups have the extension BAK? There is also the extension TRN - what does this refer to?

Many thanks.

Les

 

[Jomercat]

Hi lesj1968,

If you have a backup job schedule, just right click the job on the detail pane of the Enterprice manager; then click properties. Go to the steps tab, edit the step, and lastly you can edit the command to change the location and new file that you want to create. Do not forget to click apply. That should do it.

The .BAK extension is the default extension assigned to backup files.

Hope this helps.

Jose.

 

[lesj1968]

Hi Jose

Many thanks for your reply. I do have a backup job schedule but cannot remember how to see the job or how to click on it to edit it. Please can you tell me where I can locate it? I have Windows 2000.

Many thanks.

Les

 

[Jomercat]

1.Open the Enterprice Manager
2.Expand the Microsoft SQL Servers
3.Expand the SQL Server Group
4.Expand the server instance
5.Expand Management\SQL Server Agent.
6.Click the Jobs, which is located right below Operators.

On the Detail Pane (Right hand side of the screen)you should see your available jobs. Just right click the one you are using to backup and modify it as I explained you on the previous message.

Hope it helps.

Jose.

 

[lesj1968]

Thanks for your help. That's perfect.

Les

 

[Jomercat]

Glad to help!!!!

Jose.

 

[mrdenny]

I noticed that one no covered the compacting databases question. There is no way in SQL to compact a database. Shrinking a database will simple remove the empty space from the end of the database file. If you have 50 GB of free space within the database file then shrinking the database so that it had 1 GB of free space within the database could be worth while.

Under normal situation there isn't any need to shrink the database.

Database grow, this is normal. By shrinking a database will will be putting unneeded load on the hard drive and CPU as when the data file fills up it will need to grow larger again.

If your hard drive with the database files on it is filling up the best option will be to buy more hard drive space for the server. Shrinking the database will give you a false sense of security.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com