Mastering SQL Server Housekeeping routines Quick

[FuzMic]

Hi everyone

If we are using .mdb or .accdb as a split backend data file; it being a file we can rely on File System to back them up. We can also use “DBEngine.CompactDatabase” to compact and repair from the front end.

If we are using eg SQL Server Express 2008 R2, are we unable to do this. Do we only rely on this Server to manage any database within it? What will be the best practice for these housekeeping chores.

Can you still rely on File System to do our chores if use an SQL Server?

By the way how can we compact a database in an SQL Server?

Appreciate your views.

 

[AccessBlaster]

I use this for backend housekeeping SQL Server Management Studio It's free.

 

[NauticalGent]

...and...once you have installed SSMS, use this link to develop your maintenance plan. It has been a life saver for me.

http://assets.red-gate.com/community/books/sql-server-maintenance-plans-brad-ebook.pdf

 

[FuzMic]

Thanks friends, will study your leads.

 

[sonic8]

If we are using .mdb or .accdb as a split backend data file; it being a file we can rely on File System to back them up.

You can not rely on the file system to backup your backend database. Neither if it is an Access-DB nor if it is a SQL-Server-DB.


The file system may create a backup of the file, but if the database is still in use at the time, it might be in an inconsistent state and the backup created may be worthless. A file level backup is only "valid" if you made sure no one is using the database at the time.


For SQL-Server databases use the TSQL BACKUP command (wrapped in whatever client/script you like) to create a backup. This will be consistent even if the database is in use at the time.


I wrote a 3-part article series particularly on backup for SQL Express. This should answer most of your questions.

 

[AccessBlaster]

If you have IT department that you are working with, the backups are being done without your interaction.

Your tiny database(s) that resides on their servers are a fraction of what they are ready maintaining. HR, Payroll and Employee records are not maintained and backed up by individual departments. They are stored and maintained on the server side by IT.

If there is a system failure they will restore from their automated backups.

 

[isladogs]

@Phillip (sonic8)
Thank you for your very informative articles

SQL backups for my client databases are run in the middle of each night as a scheduled task from a separate Access utility. This is done after checking that all users of the main database have logged out. If not, users are given a short time (3-5 mins) to exit before their connection is forcibly closed.

The reason for doing this from Access is that the same utility is then used to import new & updated data each night from an external database. By running all actions from one source, maintenance is made much simpler

@AccessBlaster
Even though IT network teams do backup everything regularly, I long ago got into the habit of running daily backups of Access & SQL BE files myself so I had full control of these. It also meant the files were stored separately from the main network backups & easier to locate when needed

 

[FuzMic]

Enjoy the exchanges & learning.

For mdb or accdb i only backup at the start/end of each session by a user. In an multi users environment, the file system will not allow you to do it if another is using it anyway.

 

[AccessBlaster]

@AccessBlaster
Even though IT network teams do backup everything regularly, I long ago got into the habit of running daily backups of Access & SQL BE files myself so I had full control of these. It also meant the files were stored separately from the main network backups & easier to locate when needed

Good point personally I cannot spend the time required to duplicate the efforts of the IT staff.

IMHO most people who visit this forum will create small "bridge" applications to solve a very small and unique niche problem.

Most will not go on to be advance application builders. For those of us that fall into this range we need to get on with the business at hand like managing people or products.

A Man’s Got to Know His Limitations

 

[isladogs]

I definitely know my limitations...

Doing our own backups did indeed fit our specific situation but it was also partly because the network backup schedule couldn't easily be altered to fit our needs.