Microsoft SQL server provides three recovery models; full, simple, and bulk-logged. Each have their benefits. The details of the three recovery models is outlined below.
You will need to choose the best option for your scenario. For a production database that you need to be able to recover from, the best option will generally be the full recover model.
Full recovery model:
The full recovery model maintains the entire transaction log history up until the point where the database log file is backed up. The major benefit of the full recovery model, is that you have the ability to restore the database to a specific point in time. For example, you can restore back to 4:23pm, even if a full backup was not taken at that time.
If using the full recovery model, you will need to make sure the transaction logs are backed up reguarly as to prevent the log growing too big and filling up all free space on the server.
Simple recovery model:
The simple recovery model still has a transaction log file, however it will only store transactions for a short period of time, and cant be used to restore to a specific point in time. With the simple recovery model, you can only restore to a database backup file, at the time the backup was performed.
Bulk-Logged recovery model:
The bulk-logged recovery model is similar to the full recovery model, but it handles bulk data (for example, a data import) in a different way. When dealing with bulk data, instead of keeping track of every transaction in the log file, it will simply log the end result after the bulk transactions have occured. This is known as ‘minimal logging’. This helps reduce the impact that logging may have on the performance of the server.
With this model, you wont be able to restore to a specific point in time. You will only be able to restore back to the end of the transaction log backup.
Ideally, you would use this model on an as needed basis. It provides better protection in a disaster over the simple recovery model, however it lacks behind the full recovery model by not being able to recover to a specific point in time.
Sometimes you may encounter data integrity issues with one or more tables on a Microsoft SQL server. These errors may be found during a data integrity check using DBCC CHECKDB, or even via general use of the database. There are 3 options to correct the database integrity issues; safe repair, restore, and non safe repair (in that order).
Its always good practice to take an extra backup before any of these commands are run (specifically the non-safe repair option).
Safe repair using REPAIR_REBUILD:
Change the database to single user mode.
ALTER DATABASE <database_name> SET SINGLE_USER
Repair the database using a safe repair that will not cause data loss (if possible).
DBCC CHECKDB ('<database_name>', REPAIR_REBUILD)
Change the database to back to multi user mode.
ALTER DATABASE <database_name> SET MULTI_USER
Replace <database_name> with the name of your database.
Restore the database from a backup:
If the above safe repair does not work, the best option is to restore the database from a backup. Only if a database backup is not available, look at the next option, which is a non-safe repair.
Non-Safe repair using REPAIR_ALLOW_DATA_LOSS:
This option is non-safe as it will repair the database, by simply removing the data associated with the integrity errors. This option will fix the integrity errors, however it may remove needed data at the same time. Only perform this option if there the safe repair doesn’t work, and there is no good backup.
ALTER DATABASE <database_name> SET SINGLE_USER
Repair the database using a non-safe repair that will potentially cause data loss.
DBCC CHECKDB ('<database_name>', REPAIR_ALLOW_DATA_LOSS)
Change the database to back to multi user mode.
ALTER DATABASE <database_name> SET MULTI_USER
Replace <database_name> with the name of your database.
Microsoft SQL server 2008 has the ability to automatically shrink database files. By default this option is disabled. With automatic shrinking of database files turned on, the SQL server will periodically check the available free space within the database files, and will automatically shrink the files if needed.
If your database has heavy use, it might be best to keep this option disabled to prevent performace degration.
To check what the current auto shrink setting:
SELECT DATABASEPROPERTYEX('<database_name>', 'IsAutoShrink')
Replace <database_name> with the name of your database.
This will output a 1 if auto shrink is enabled, or a 0 if it is disabled.
To enable automatic file shrinking:
ALTER DATABASE <database_name> SET AUTO_SHRINK ON
Replace <database_name> with the name of your database.
To disable automatic file shrinking:
ALTER DATABASE <database_name> SET AUTO_SHRINK OFF
Replace <database_name> with the name of your database.
Microsoft SQL Server 2008 Express Edition is a great database engine that is more than capable of handling most small business database requirements. There are however some limitations. The details below outline the main limitations in SQL 2008 Express Edition.
(Note: This information is not for SQL Server 2008 R2. If you are after the details for the limitations on SQL Server 2008 R2, please check out Microsoft SQL Server 2008 R2 Express Limitations).
CPU:
1 CPU (based on a socket). No limit on cores.
Memory:
Maximum of 1GB for the database engine.
(Note: SQL 2008 Express Edition with Advanced Services includes reporting features, and has a limit of 4GB for the reporting services. It still has a 1GB limit for the database engine).
Database Size:
The maximum database size is 4GB.
(Note: SQL Server 2008 R2 has increased this limit from 4GB to 10GB. If you need more than 4GB, please upgrade to SQL Server 2008 R2).
Other:
For a full rundown of the features that each SQL 2008 editions have included, you can visit http://msdn.microsoft.com/en-us/library/cc645993(v=sql.100).aspx
Microsoft SQL Server 2008 R2 Express Edition is a great database engine that is more than capable of handling most small business database requirements. There are however some limitations. The details below outline the main limitations in SQL 2008 R2 Express Edition.
CPU:
1 CPU (based on a socket). No limit on cores.
Memory:
Maximum of 1GB for the database engine.
(Note: SQL 2008 R2 Express Edition with Advanced Services includes reporting features, and has a limit of 4GB for the reporting services. It still has a 1GB limit for the database engine).
Database Size:
The maximum database size is 10GB.
(Note: this maximum database size of 10GB in SQL 2008 R2 has been increased from 4GB in SQL Server 2008).
Other:
For a full rundown of the features that each SQL 2008 R2 editions have included, you can visit http://msdn.microsoft.com/en-us/library/cc645993(v=sql.105).aspx
Microsoft SQL Server 2012 Express Edition is a great database engine that is more than capable of handling most small business database requirements. There are however some limitations. The details below outline the main limitations in SQL 2012 Express Edition.
CPU:
1 CPU (based on a socket) or 4 cores.
The lesser of the two is used.
Eg:
If you have 1 CPU that contains 8 cores, it will only use 4 cores.
or
If you have a system with 2 dual core CPU’s, it will only use 2 cores (from the 1 CPU socket as that’s the lesser of the two).
Memory:
Maximum of 1GB for the database engine.
(Note: SQL 2012 Express Edition with Advanced Services includes reporting features, and has a limit of 4GB for the reporting services. It still has a 1GB limit for the database engine).
Database Size:
The maximum database size is 10GB.
Other:
For a full rundown of the features that each SQL 2012 editions have included, you can visit http://msdn.microsoft.com/en-us/library/cc645993(v=SQL.110).aspx
This guide will show you how to restore a Microsoft SQL database using T-SQL commands rather than using the wizards in SQL Management Studio. This has the added benefit of being able to be scripted if needed.
Restoring a Microsoft SQL database:
RESTORE DATABASE [database_name] FROM DISK='c:\path\to\backup\file.bak'
Replace ‘database_name’ with the name of the database you want to restore to.
Eg:
RESTORE DATABASE [testing] FROM DISK='C:\temp\testing.bak'
Sample output:
Processed 312 pages for database 'testing', file 'Testing' on file 1. Processed 3 pages for database 'testing', file 'Testing_log' on file 1. RESTORE DATABASE successfully processed 315 pages in 0.417 seconds (5.885 MB/sec).
By default, this will overwrite the database if it already exists.
The path to the SQL backup file needs to be a path on the server running SQL server. This means that if you run the restore script from a client workstation, make sure the path to the backup file refers to a path on the server, and not a path on the clients workstation.
This guide will show you how to backup a Microsoft SQL database using T-SQL commands rather than using the wizards in SQL Management Studio. This has the added benefit of being able to be scripted if needed.
Backing up a Microsoft SQL database:
BACKUP DATABASE [database_name] TO DISK='c:\path\to\backup\file.bak'
Replace ‘database_name’ with the name of the database you want to backup.
Eg:
BACKUP DATABASE [testing] TO DISK='C:\temp\testing.bak'
Sample output:
Processed 312 pages for database 'testing', file 'Testing' on file 1. Processed 3 pages for database 'testing', file 'Testing_log' on file 1. BACKUP DATABASE successfully processed 315 pages in 0.487 seconds (5.039 MB/sec).
Make sure the account that the SQL server service is running as had access to write to the path you want to save the backup into. If not you will get an error such as “Operating system error 5(Access is denied.)”.
The backup path location should be on a physical disk and folder on the server running SQL server. This means that if you run the backup script from a client workstation, make sure the backup path references the server path, and not the client workstation’s path.
You can use UNC network paths for the destination output, however this isn’t supported by Microsoft, and you can easily run into permission issues.
Refreshing All Views After Table Structure Changes in Microsoft SQL
July 13, 2012 Database, Microsoft SQLAfter making structure changes to a database table, you need to update any views that may be using those tables, otherwise the views may reference the wrong field, and cause all sorts of issues.
The T-SQL script below will refresh all views on a database.
Run this in a new query window in Microsoft SQL Server Management Studio.
Make sure the database you wish to refresh is selected.
DECLARE @ViewName VARCHAR(256) DECLARE cViews CURSOR READ_ONLY FOR SELECT name from sys.views OPEN cViews FETCH NEXT FROM cViews INTO @ViewName WHILE @@FETCH_STATUS != -1 BEGIN EXEC SP_REFRESHVIEW @ViewName PRINT 'View ''' + @ViewName + ''' has been refreshed.' FETCH NEXT FROM cViews INTO @ViewName END CLOSE cViews DEALLOCATE cViews
The Microsoft SQL Browser Service is a service that runs on a machine that has Microsoft SQL Server installed, and provides information to clients about the SQL server instances installed on the PC.
When you are connecting an application to a SQL server, generally the software has the capabilities to send a broadcast query out onto the network, and detect what SQL servers are available. This is generally presented to the end user with a drop down box listing all the SQL services, and the instance names. This broadcast query doesn’t communicate to the actual SQL Server directly, but it communicates with the SQL Browser service. This means that if the SQL Browser service is stopped, no client software will be able to automatically determine that there is a SQL server running.
With Microsoft SQL Server you can install a number of independent SQL Servers on a machine, and differentiate them with “instance names”. If you only want one SQL server installed, you can either set up an instance name, or use no instance name, which will be known as the “default instance”. With SQL 2008 R2, there is a limit of 50 instances per computer.
Each instance is completely separated, and databases set up on the individual instances. This can allow you to separate payroll database with tighter security, run multiple versions of SQL server, have a production and development SQL server, etc.
Since there can me many SQL instances running on the one computer, there needs to be a way to separate them at a network level. Each instance will run on its own unique TCP/IP port number. This is where the SQL Browser service comes into play. The SQL Browser service is queried before each initial SQL server connection, and can determine what instance name you are trying to connect to. It passes back to the client the port number that the specific SQL instance is listening on. If the SQL Browser service is stopped, you will no longer be able to connect to any SQL server instances (apart from perhaps the default instance if your lucky).
The SQL Browser service listens for incoming SQL queries on UDP port 1434. You must make sure this port, and also the TCP port that all your SQL instances are using, are allowed through any firewalls that might be in place on the machine running the SQL server.
Windows 2008 Server has a tendency to block these ports by default, so it can cause a few issues if your not aware of it.
Even if the SQL Browser service is stopped, it doesn’t prevent users from connecting to it, as long as you know the server name, instance name, and port number.
To connect directly to a SQL server instance running on a specific port, you would use the following format:
SERVERNAME\INSTANCENAME,PORTNUMBER
eg:
SQLSVR3\DEVELOPMENT,15032
This will bypass the SQL Browser service query, and connect directly to the SQL server called “SQLSVR3” with an instance name of “DEVELOPMENT” which is running on TCP port 15032.
For more information, check out the Microsoft article on the subject at http://msdn.microsoft.com/en-us/library/ms181087.aspx
If you need information on setting up SQL to listen on TCP instead of just Named Pipes, please check out my guide on Enabling TCP/IP in Microsoft SQL Server 2005.