GrundläggandeMaintenance Tasks på SQL Server för icke-DB-Admins

Basic install

– Database Components
– Integration Service
– Management tools

Maintenance Plans

Maintenance plan (Kräver Integration Services)

Skapa Backupjobb (Scheduleras dagligen innan så att den är klar innan bandbackup)

Dra in ett Database backup block

  • Välj Databaser
  • Välj Typ
  • Välj Destination

Dra in ett Maintenance Cleanup Task block

  • Välj Backup Files
  • Välj mapp
  • Ställ in “Delete files based on…” till 3 dagar

Dra in ett “History Cleanup Task” block

  • Välj “Local Server Connection”
  • Välj “Backup and Restore history”
  • Välj “SQL Server Agent job history”
  • Välj “Maintenance Plan History”
  • Ställ in tid (ex 4 veckor)

Koppla ihop blocken enligt: Backup – Maintenance Cleanup – History Cleanup

Skapa Indexering och Verifiering (Scheduleras en gång i veckan)

Dra in “Rebuild Index Task” block

  • Välj Databaser
  • Välj “Reorganize pages…”

Dra in “Update Statistics Task” block

  • Välj Databaser
  • Välj “All existing statistics”

Dra in “History Cleanup Task” block

  • Välj “Backup and restore history”
  • Välj “SQL Server Agent job history”
  • Välj “Maintenance plan history”
  • Ställ in tid (ex 4 weeks)

Koppla ihop blocken enligt: Rebuild – Update Stats – History Cleanup

Check Database Integrity (Scheduleras en gång i veckan efter Indexering och verifiering)

Dra in “Check Database Integrity task” block

  • Välj databaser
  • Välj Include indexes

Dra in “History Cleanup Task”

  • Välj “Backup and restore history”
  • Välj “SQL Server Agent job history”
  • Välj “Maintenance plan history”
  • Ställ in tid (ex 4 weeks)

Problems with Managemnet Studio on x64 SQL 2005 server

I had a problem today with editing maintenance plans in Management Studio 2005 on a server with Windows Server 2003 x64.

The error I get is:

Cannot show the editor for this task.
Additional information:
Exception from HRESULT:  0xC0010014 (Microsoft.SqlServer.DTSRuntimeWrap)

The solution I found was this:

%windir%syswow64regsvr32 “%ProgramFiles(x86)%Microsoft SQL Server90dtsbinndts.dll” 

Source:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=609326&SiteID=1

SQL Server Tips and Tricks for non-DBAs

I have collected some Good-To-Know-Things here and will add more as time goes by…

Cleaning up backups in SQL 2005

In SQL pre 2005 you could set up a backup job to delete backupfiles older than X number of days (see picture). This feature has been removed in SQL 2005.
 

If you want to do the same thing in SQL 2005 you do the following:

  1. Goto the maintenance plan that does the backup
  2. Rightclick and select Modify
  3. Drag and drop a Maintenance Cleanup Task and connect it after the last task
  4. Edit the task
  5. Specify the Path to the backup files, the extension och the files and the number of days you want to keep the backups
  6. Click OK to save the job

Adding more memory to a SQL Server

  1. Start Management Studio
  2. Open properties for the server
  3. Go to memory and change minimum and maximum memory
  4. Reboot the server

Change the default path for databases and logs

1. In management studio, right click on the server and select properties.
2. Goto database Settings
3. Change Database and Log paths

Reports in Microsoft CRM not working

I just set up a Microsoft CRM server for a customer. After one of our developers had set up the Biztalk connection (and changed some settings in IIS to work with his web service 🙁 ) the reports in MS CRM did not work anymore.

When I tried to connect directly to the ReportingServices virtual directory I got the message:

Unable to generate a temporary class (result=1). error CS2001: Source file ‘C:WINDOWSTEMP9a1mhqzb.0.cs’ could not be found error CS2008: No inputs specified

I tried to look at the permissions on C:WindowsTEMP but they seemed OK. Just for troubleshooting purposes I added Everyone Full Control to the folder.

The error message changed to:

“The report server is not responding. Verify that the report server is running and can be accessed from this computer.”

I checked the SQL Reporting Services Service but it was running. Then I got a tip on a MS SQL tool called RS Config Tool (rsconfigtool.exe) which is installed with MS SQL 2005. When I ran this gave me an error in “Web Services Identity” which ment that the ASP.NET services was not running as the correct user. It seemed the the developer had changed it to IWAM_Computername instead of NETWORK SERVICE.

To change this I went into IIS Manager under Application Pools. Righitclick on Default Application Pool and ReportServer, goto the tab Identity and change it to NETWORK SERVICE

Links:

Error CS2001 & CS2008
Change the account that ASP.NET runs under

Installation av Microsoft CRM Server 3.0

Installation av Microsoft CRM Server 3.0

(Detta är en editerad version av postningen den 22/5 2006)

Förutsättning: Microsoft CRM installeras i detta fall på samma server som Microsoft SQL. Allt installeras på en Windows Server 2003 SP2.

Installera IIS med följande komponenter:
 – ASP.NET
 – WWW Publishing Service
 – SMTP Service

Installera SQL Server 2005 (Jag installerades SP0. Har inte kunna testa med SP1 och SP2.)
 – SQL Server Database Service
 – Reporting Services (kan installeras av CRM om du använder SQL 2000)
 – Windows Authentication Mode
 – Finnish Swedish

Skapa Användare mm i AD
 – Skapa OU Microsoft CRM
 – Skapa Användare CRMAdmin (Domain Admin under installationen)
 – CRM Mail – Vanligt konto med mailbox (Mailrouter till Exchange)
 – Skapa DNS Pekare CRM till servern
 – Verifiera Full-Text Searching i SQL Enterprise Manager
 – Starta Indexing Service och sätt automatisk
 – Starta SQL Agent och sätt automatisk

Installera Microsoft CRM som CRM Admin

CD Key finns på CDKit. om du inte hunnit få detta kan du använda en utvärderingsnyckel (se nedan). Denna kan bytas mot den riktiga nyckeln inom 90 dagar.

  Create New Website
 – Ange Exchange Server (endast vid mailintegration)
 
 – Koppla upp http://localhost:5555

Installera Microsoft CRM Rollup 1

Edit: Jag installerade SQL SP1 i efterhand och det verkar fungera

OBS Plocka inte bort den website som läggs upp av installation och ändra inte port OBS

Post Install Tasks

Microsoft CRM

Verifiera att AD grupperna (PrivUserGroup, SQL Access Group, Reporting Group och UserGroup) har skapats.

Lägg Microsoft CRM Service, IIS Service Account och SQL Server Service Account konton i “Pre-Windows 2000 Compatible” säkerhetsgruppen.

Registrera CRM installationen inom 30 dagar

Skapa ett nytt adminkonto i MS CRM. Kryssa i restricted access för detta konto. Kryssa ur Restricted access för CRMAdmin

Reporting Services

(Om CRM körs på samma server som MS SQL 2005) Byt Application Pool på den virtuella katalogen reports till Default Application Pool.

Kopiera mscrm.css, mscrmx.css, mscrmp.css, mscrmxp.css från wwwrootCRMReportsrsstyles på MSCRM Installationsskivan till c:ProgramFilesMicrosoftSQL ServerMSSQLReportingServicesReportServerstyles

Ändra rättigheter på ovanstående filer: Lägg till UserGroup Read

Verifiera Reporting Services genom att öppna Workplace, under My Work, klicka Reports och välj User Summary

Gå till Microsoft KB Artikel om Backup
Microsoft CRM Implementation Guide – Planning the Backup and Restoration of the Microsoft CRM System

Update Rollup 1 for Microsoft Dynamics CRM 3.0 is available
Update Rollup 1 for Microsoft Dynamics CRM 3.0 (KB922815)

Tidbits on MS CRM

Microsoft CRM 3.0: 90-Day Trial Versions

Configuring MSDE

I am not a database guru so the othet day I needed to edit the setup in my SBS MSDE database… I googled and found this. A supernice short run-through on how to configure MSDE.

I saved a copy incase it would disapear
———————————————————————————–

Introduction
This article describes how to configure the Microsoft MSDE database engine. Below you will find an overview of the topics.

What is MSDE?
Limitations
What you get
How to install and configure the MSDE
Arguments to the setup.exe file
Using the “setup.ini” file
Post reconfiguration
Configure the server
Configure the client
Fire it up!
How do I determine what version I am running?
Create and delete a database
How do I list all the databases on the system?
How do I detach a database from the system?
How do I attach a database to the system?
How do I list all tables in a database?
Database users
Add a new user with SQL authentication
With Win authentication
How to list all users and roles in a database
How to remove users from a database
Exit osql
What is MSDE?
MSDE is a limited version of the Microsoft SQL Server. In short, it is the Microsoft SQL Server 2000 database engine without any of the fancy UI tools, and with some limitations in the database size and the number of connections. The MSDE database is free, and can be distributed embedded in your own applications or as a small stand alone SQL server. It is ideal for small websites and small businesses with less than 25 simultaneous users. The database is limited to 2 GB of data storage space, but you can easily upgrade it to a full Microsoft SQL Server without any limitations. Among the choices are, a standard edition or an enterprise edition. An MSDE database is a good and affordable starting point for any business, organization or even home solutions. The database does not have to be installed on the hard drive on the deployment computer, but it could be running from a CD-ROM.

Limitations
There are some limitations to a MSDE database. You don’t get:

Failover clustering.
Log shipping.
Parallel DBCC.
Parallel create index.
Enhanced read ahead and scan.
Indexed views.
Federated database server.
System area network support (SAN).
Graphical DBA, and developer utilities and wizards.
Full-text search.
Analysis services.
OLAP partitions.
Partition wizard.
Linked OLAP cubes.
ROLAP dimension support.
HTTP Internet support.
Custom rollups.
Calculated Cells.
Write back to dimensions.
Very large dimension support.
Actions.
Real-time OLAP.
Distributed partition cubes.
Data mining.
English query.
What you get
This is what you get:

Maximum number of processors: 2 in NT/Win2000/XP/2003, 1 in Win98/ME.
Maximum physical memory: 2 GB.
Maximum user connections: 5 before the work load governor will limit the performance.
Maximum data storage: 2 GB.
Desktop embedded or network access.
Leading SQL database performance.
It’s free. =:-)
How to install and configure the MSDE
The MSDE can be downloaded from the Microsoft web site for free. Download the archive and uncompress it to a folder on your hard drive. There are four different ways to run the MSDE depending on how you want to access the database. They are all listed below.

You can configure the MSDE in three different ways:

Arguments to the setup.exe file.
Using the “setup.ini” file.
Post reconfiguration.
The four different running modes are:

Windows authentication without network access.
Windows authentication with network access.
Mixed Windows and SQL mode authentication without network access.
Mixed Windows and SQL mode authentication with network access.
Please use Windows authentication whenever possible. SQL mode will send the login and the password information over the network in plain text. This means it can be sniffed by intruders or hackers. Windows authentication uses SSPI which you can read more about on these web pages MSDN [1], and MSDN [2]. A default installation will disable the network access and uses the Windows authentication mode.

Before you start the installation of the MSDE, disable Norton Antivirus and the Internet Security Pack. You will enable it after the installation has completed. Norton Antivirus will not let you install the MSDE successfully, when enabled.

In all of our examples, we will be using “password” for the system administrator, and “myinstance” for the database instance.

Arguments to the setup.exe file
You can run configure the MSDE by giving arguments to the setup.exe file on a command prompt.

Windows authentication with network access.
Example 1 (Win access over a network).

In this example, we will be using Win authentication, because we do not want to send the username and password in plain text over the network. Only Windows applications and .NET applications can access the database. The application uses automatically the login name and password on the client computer to login to the MSDE.

C:…MSDE>Setup.exe DISABLENETWORKPROTOCOLS=0
              SAPWD=”password “INSTANCENAME=”myinstance”
Mixed Windows and SQL mode authentication with network access.
Example 2 (Java and Win access over a network):

In this example, we will be using the mixed mode authentication, because we want to access the database from both Windows applications and Java applications over a network. The username and password will be sent over the network in plain text.

C:…MSDE>Setup.exe DISABLENETWORKPROTOCOLS=0
      SAPWD=”password ” INSTANCENAME=”myinstance” SECURITYMODE=SQL
Mixed Windows and SQL mode authentication without network access.
Example 3 (Local Java and Win access only):

In this example, we will not be granting access from the network. Only local applications can access the database. We will be using mixed mode authentication for Java applications.

C:…MSDE>Setup.exe SAPWD=”password “INSTANCENAME=”myinstance” SECURITYMODE=SQL
Windows authentication without network access
Example 4 (Local Win access only):

In this example, we will not grant network access to the database. We will also only grant Win authentication access. This is the most secure setup, and should be used for embedded MSDE applications.

C:…MSDE>Setup.exe SAPWD=”password ” INSTANCENAME=”myinstance”
Using the “setup.ini” file
You will find a file called “setup.ini” in the MSDE folder, if you want to use a pre configured setup file. Please remember that anyone with read access to this file can see the system administrator password if this is an issue. The file looks like this:

[Options]
Here you will enter the configuration arguments.

Mixed Windows and SQL mode authentication with network access.
Example 1 (Java and Win access over a network):

In this example, we will be using mixed mode authentication, because we want to access the database from both Windows applications and Java applications over a network. The username and password will be sent over the network in plain text.

[Options]
DISABLENETWORKPROTOCOLS=0
SAPWD=”password “
INSTANCENAME=”myinstance”
SECURITYMODE=SQL
To install MSDE, run the “setup.exe” install application.

Windows authentication with network access.
Example 2 (Win access over a network):

In this example, we will be using Win authentication, because we do not want to send the username and password in plain text over the network. Only Windows applications and .NET applications can access the database. The application uses automatically the login name and password on the client computer to login to the MSDE.

[Optio
ns]
DISABLENETWORKPROTOCOLS=0
SAPWD=”password “
INSTANCENAME=”myinstance”
To install MSDE, run the “setup.exe” install application.

Mixed Windows and SQL mode authentication without network access.
Example 3 (Local Java and Win access only):

In this example, we will not be granting access from the network. Only local applications can access the database. We will be using mixed mode authentication for Java applications.

[Options]
SAPWD=”password “
INSTANCENAME=”myinstance”
SECURITYMODE=SQL
To install MSDE, run the “setup.exe” install application.

Windows authentication without network access
Example 4 (Local Win access only):

In this example, we will not grant network access to the database. We will also only grant Win authentication access. This is the most secure setup, and should be used for embedded MSDE applications.

[Options]
SAPWD=”password “
INSTANCENAME=”myinstance”
To install MSDE, run the “setup.exe” install application.

Post reconfiguration
It is possible to reconfigure the authentication mode for an already installed instance of the MSDE, through the registry. Stop the database instance in the Control Panel (“Administrative Tools” and “Services”). Open the “regedt32.exe”, and locate the “HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServermyinstanceMSSQLServer” and the key “LoginMode”. For Windows authentication, set this key to “1”; for SQL authentication, set this key to “0” (or “2”?). Restart the database instance in the Control Panel (“Administrative Tools” and “Services”).

Do you want to change the system administrator (sa user) password?
Login to the MSDE using the osql tool:

Using Win authentication:

C:> osql –E –S localhostmyinstance
1> use master
2> go
1> exec sp_password @old = null, @new = ‘newpassword’, @loginame = ‘sa’
2> go
Password changed.
1>quit
Using SQL authentication:

C:> osql –U sa –P password –S localhostmyinstance
1> use master
2> go
1> exec sp_password @old = null, @new = ‘newpassword’, @loginame = ‘sa’
2> go
Password changed.
1>quit
Configure the server:
Run the SQL Server network utility. Open a command prompt and type:

C:> svrnetcn.exe
Enable the “Named Pipes” protocol and the “TCP/IP” protocol. Click on “Properties” on the “TCP/IP” protocol. Here, you will configure the default port MSDE will be listening on if you want network access. Default is 1433.

Configure the client:
Run the SQL Server client network utility. Open a command prompt and type:

C:> cliconfg.exe
Enable the “Named Pipes” and the “TCP/IP” protocols. Click on “Properties” on the “TCP/IP” protocol. Here you configure the port number you found with the “svrnetcn.exe” utility. Default is 1433.

Add an alias for the “Named Pipes” and the “TCP/IP” under the “Alias” box. You will use an alias if you do not want to configure all applications to a specific configuration. The client just accesses the alias, and the client utility will map the request to the right configuration.

Fire it up!
Let’s start the MSDE. Open the Control Panel, the Administrative Tools and the “Services” utility. Find the instance “MSSQL$MYINSTANCE” in the list. Right click it, and choose “Start”. Startup type should be “Automatic”.

How do I determine what version I am running?
OK, the MSDE should now be up and running. Let’s test it. Run the osql tool.

C:> osql –E –S localhostmyinstance
1: select @@VERSION
2: go
3: quit

8.00.194 – MSDE 2000
8.00.384 – MSDE 2000 SP1
8.00.534 – MSDE 2000 SP2
8.00.760 – MSDE 2000 SP3
Create and delete a database
By default, the MSDE has four databases installed. These databases are:

Master – Contains all of the system meta-data, like login accounts, settings, and the location of the database files etc. Files: master.mdf, mastlog.ldf.
Tempdb – Contains work tables and temporary storage. All the data in this database will be deleted on disconnect. Files: tempdb.mdf, templog.ldf.
Model – This is a model of all databases on the system. It acts as a template for new databases. Files: model.mdl, modellog.ldf.
Msdb – Used for the SQL Server Agent to schedule jobs. Files: msdbdata.mdf, msdblog.ldf.
You should not use any of these databases for any application purposes. They are holy land as far as the applications know it. They are out of reach, simple as that. Create a new database to store your application data.

Let us create a database with the name “Books”. First of all, you must login as a system administrator (sa) or as a user with dbcreator privileges. Open the osql administration utility and login as system administrator.

C:> osql –U sa –P password –S localhostmyinstance
Or

C:> osql –E –S localhostmyinstance
Creating a simple database with the Model template:
1> use master
2:> go
1> CREATE DATABASE Books
2> go
The CREATE DATABASE process is allocating 0.63 MB on disk ‘Books’.
The CREATE DATABASE process is allocating 0.49 MB on disk ‘Books_log’.
Delete the Books database
1> use master
2:> go
1> DROP DATABASE Books
2> go
Deleting database file ‘C:Program FilesMicrosoft SQL
ServerMSSQL$MYINSTANCEDataBooks_log.LDF’.
Deleting database file ‘C:Program FilesMicrosoft SQL
ServerMSSQL$MYINSTANCEDataBooks.mdf’.
Creating a database specifying the file name
1> use master
2> go
1> CREATE DATABASE Books
2> ON
3> ( NAME = books_dat,
4> FILENAME = ‘D:Databasebooks.mdf’ )
5> go
The CREATE DATABASE process is allocating 0.63 MB on disk ‘books_dat’.
The CREATE DATABASE process is allocating 0.49 MB on disk ‘Books_log’.
NAME = Logical name used to specify the database in TSQL statements.
FILENAME = Physical location of the database on the hard drive.
Creating a database specifying the filename and the size
1> use master
2> go
1> CREATE DATABASE Books
2> ON
3> ( NAME = books_dat,
4> FILENAME = ‘D:Databasebooks.mdf’,
5> SIZE = 10,
6> MAXSIZE = 100,
7> FILEGROWTH = 5 )
8> go
The CREATE DATABASE process is allocating 10.00 MB on disk ‘books_dat’.
The CREATE DATABASE process is allocating 2.50 MB on disk ‘Books_log’.
NAME = Logical name used to specify the database in TSQL statements.
FILENAME = Physical location of the database on the hard drive.
SIZE = Initial file size.
MAXSIZE = The database maximum size.
FILEGROWTH = Increments of the database file. The DB file will grow with this size in MBs.
Creating a database specifying the filename, size, and logfile
1> use master
2> go
1> CREATE DATABASE Books
2> ON
3> ( NAME = Books_dat,
4> FILENAME = ‘D:Databasebooks.mdf’,
5> SIZE = 10,
6> MAXSIZE = 100,
7> FILEGROWTH = 5 )
8> LOG ON
9> ( NAME = ‘Books_log’,
10> FILENAME = ‘D:Databasebookslog.ldf’,
11> SIZE = 5,
12> MAXSIZE = 50,
13> FILEGROWTH = 2 )
14> go
The CREATE DATABASE process is allocating 10.00 MB on disk ‘Books_dat’.
The CREATE DATABASE process is allocating 5.00 MB on disk ‘Books_log’.
NAME = Logical name used to specify the log and the database in TSQL statements.
FILENAME = Physical location of the log file and the database on the hard drive.
SIZE = Initial file size.
MAXSIZE = The log and the database maximum size.
FILEGROWTH = Increments of the log and the database file. The DB file will grow with this size in MBs.
How do I list all the databases on the system?
If you want to list all the databases in the instance, please open the osql utility and do the following query:

C: >osql -E -S localhostmyinstance
1> use master
2> go
1> select name from sysdatabases
2> go
 name

————————————
master
tempdb
model
msdb
test
Products
 (6 rows affected)
In this example, I have created two databases: test and Products.

How do I detach a database from the system?
To detach a database called larsinge from the system, please do the following:

1> exec sp_detach_db ‘larsinge’
2> go
In this example, we detached a database called “larsinge”. The database can now be moved, backed-up, or you can do whatever you want to do with the files.

How do I attach a database to the system?
Simply do this to connect a database called larsinge:

1> exec sp_attach_db @dbname = ‘larsinge’,
2> @filename1 =
      ‘C:Program FilesMicrosoft SQL ServerMSSQL$LITBASEDatalarsinge.mdf’,
3> @filename2 =
      ‘C:Program FilesMicrosoft SQL ServerMSSQL$LITBASEDatalarsinge_log.LDF’
4> go
How do I list all tables in a database?
If you want to list all tables in an instance, please open the osql utility and do the following query:

C: >osql -E -S localhostmyinstance
1> use Products
2> go
1> select name from sysobjects where type = ‘U’
In this example, we list all tables in the Products database in the myinstance. Type ‘U’ means “User Table”. Please try to change type to “S” if you want to list out all System tables.

C: >osql -E -S localhostmyinstance
1> use Products
2> go
1> select name from sysobjects where type = ‘S’
Database users
Add and grant user access to the database.

Add a new user with SQL authentication:
This will add a new SQL login user ‘lars’ with the password ‘pass45’ and set the default database to ‘larsinge’. After creating the user, remember to grant access to the database and the tables for the user.

1> use master
2> go
1> EXEC sp_addlogin ‘lars’, ‘pass45’, ‘larsinge’
2> go
New login created.
Grant access to the database:

1> use larsinge
2> go
1> EXEC sp_grantdbaccess ‘lars’
2> go
Granted database access to ‘lars’.
1> grant all on table_name to lars
2> go
With Win authentication
Database name is ‘larsinge’, the Win auth user is ‘TARGUStestuser’:

1> use larsinge
2> go
1> exec sp_grantdbaccess ‘TARGUStestuser’
2> go
Granted database access to ‘TARGUStestuser’.
How to list all users and roles in a database?
Please open the osql utility, and do the following query to list all users in the Products database:

1> use Products
2> go
1> select name from sysusers
2> go
How to remove users from a database
This is how you revoke a user ‘lars’ access to the database ‘larsinge’.

1> use larsinge
2> go
1> exec sp_revokedbaccess ‘lars’
2> go
Exit osql

Unable to add SQL Server ODBC DSN

If you are unable to add a new SQL Server DSN in Datasources it is probably due to a missing or corrupt sqlsrv32.rll file in c:windowssystem32.

Extract a new one from MDAC (the version you are currently using) with mdac_typ.exe /C /T:C:destination and copy the file to c:windowssystem32

This has also been known to stop Axapta from starting.

MDAC uninstall workaround

Here is the trick we bought from Microsoft customer support for $250.00

Let me know if you have any additional questions/concerns.
1. Download the MDAC version you want on the box from:
http://www.msdn.microsoft.com/data/mdac/downloads/default.aspx

2. Open the registry editor by entering the following at a command prompt:
regedit

3. Navigate to the following hive and delete the exception components:

HKEY_LOCAL_MACHINESOFTWAREMicrosoftWindowsCurrentVersionSetupExceptionComponents

Note: this prevent you from overwriting MDAC is they exist therefore they need deleted.

4. navigate to the following hive HKEY_LOCAL_MACHINESOFTWAREMicrosoftDataAccess

and set the following key to the following value: FullInstallVer, RollbackVersion, and Version to 2.60

Note: when MDAC checker it will see it’s old and allow the upgrade to 2.6 SPx or higher

5. Disable anti-virus(very important), stop services such as SQL server, IIS, etc., close applications

6. Run the MDAC_typ.exe from step 1 above to install they version on the box.

7. Reboot and verify the install by running the MDAC Component checker downloadable from

http://www.msdn.microsoft.com/data/mdac/downloads/default.aspx

OR try

1. Windows operating system repair to get to the RTM release of MDAC.

2. Download the version he wanted on the box from http://msdn.microsoft.com/data/mdac/downloads/default.aspx

3. Disable Anti-virus(VERY IMPORTANT), close all applications, stop services such as SQL and IIS, then run the MDAC_tpy.exe for the version downloaded in step 2 above to install it.

4. Verify the install via the MDAC Component Checker also on http://msdn.microsoft.com/data/mdac/downloads/default.aspx

5. Apply Windows updates and hotfixes.

How to back up and restore http://companyweb data in Windows Small Business Server 2003

How to back up and restore http://companyweb data in Windows Small Business Server 2003

Article ID : 829112
Last Review : May 4, 2005
Revision : 5.0

SUMMARY

This article discusses how to back up a Windows SharePoint Services http://companyweb database in Microsoft Small Business Server 2003 and how to restore the database back to the original server. You can do the backup and the restore operations by using Microsoft Windows NT Backup or by using the Stsadm.exe command-line tool.

For these steps to work, you must meet the following criteria:

You must restore the database to the same server where you performed the backup operation.
The following Windows SharePoint Services database and log files must be on the server:

STS_Config.mdf
STS_Config_log.ldf
STS_Servername_1.mdf
STS_Servername_1_log.ldf

back to the top

Back up and restore a Windows SharePoint Services http://companyweb database by using Windows NT Backup

1. To back up the Windows SharePoint Services http://companyweb database by using Windows NT Backup, follow these steps:

a. Click Start, point to All Programs, point to Accessories, point to System Tools, and then click Backup.
b. In the Backup or Restore Wizard, click Advanced Mode, and then click the Backup tab.
c. Click to select the check box of the folder where the Windows SharePoint Services databases are installed. By default, this is Program FilesMicrosoft SQL ServerMSSQL$SharePointData.

Note This is the default installation folder for Windows SharePoint Services databases.

d. Specify a destination for the backup, and then click Start Backup.
2. To restore a Windows SharePoint Services http://companyweb database by using Windows NT Backup, follow these steps:

Note This action overwrites the existing Windows SharePoint Services databases. Microsoft recommends that you back up the current Program FilesMicrosoft SQL ServerMSSQL$SharePointData folder before you restore files.

a. Click Start, point to All Programs, point to Accessories, point to System Tools, and then click Backup.
b. In the Backup or Restore Wizard, click Advanced Mode.
c. Click the Restore and Manage Media tab.
d. Select the tape drive or other backup storage device that contains the http://companyweb database(s) that you backed up in step 1.
e. Click to select the check box of the folder where the Windows ShareP
oint Services databases are installed. By default, this is Program FilesMicrosoft SQL ServerMSSQL$SharePointData .
f. On the Tools menu, click Options.
g. Click the Restore tab, click to select the Always replace the file on my computer check box, and then click OK.

h. Verify that Restore Files to is set to Original location, and then click Start Restore. Click OK to start restoring your data.

Note After you click Start Restore, you can click Advanced in the Confirm Restore dialog box. Microsoft recommends that you do not make any changes to the default advanced restore options until you know that the restore was successful.

For more information about advanced restore options, visit the following Microsoft Web site:

i. When the restore is complete, click Close in the Restore Progress dialog box, and then click Yes to restart the server.

back to the top

Back up and restore a Windows SharePoint Services http://companyweb database by using the Stsadm.exe command-line tool

1. To back up a Windows SharePoint Services http://companyweb database by using Stsadm.exe, follow these steps:

a. Click Start, and then click Run.
b. In the Open: box, type cmd, and then click OK.
c. At the command prompt, type the following, and then press ENTER:

cd %programfiles%Common FilesMicrosoft Sharedweb server extensions60BIN
d. At the command prompt, type the following, where Backup_Path is the path of the backup file, and then press ENTER:

stsadm -o backup -url http://companyweb -filename Backup_Path

For example, if you type stsadm -o backup -url http://companyweb -filename c:backup.dat, you will back up http://companyweb to the Backup.dat file. This file is located in the root directory of drive C.

Note This command backs up the http://companyweb top-level Web site and its subwebs only. If you have additional top-level Web sites, you must use Stsadm.exe to back up each top-level site.

e. Type exit, and then press ENTER.
2. To restore a Windows SharePoint Services http://companyweb database by using Stsadm.exe, follow these steps:

a. Click Start, and then click Run.
b. In the Open: box, type cmd, and then click OK.
c. At the command prompt, type the following and then press ENTER:

cd %programfiles%Common FilesMicrosoft Sharedweb server extensions60BIN
d. At the command prompt, type the following, where Backup_Path is the path of the backup file, and then press ENTER:

stsadm -o restore -url http://companyweb -filename Backup_Path -overwrite

For example, if you type stsadm -o restore -url http://companyweb -filename c:backup.dat -overwrite, you will restore http://companyweb from the Backup.dat file. This file is located in the root directory of drive C.

e. Type exit, and then press ENTER.

Example script to create daily backups of the http://companyweb database

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and the tools that are used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. If you have limited programming experience, you may want to contact a Microsoft Certified Partner or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Partners, visit the following Microsoft Web site:

For additional information about the support options available from Microsoft, visit the following Microsoft Web site:

The following example script uses a numbered value for the day of the week. The script then appends this number to the name of the backup file that the script creates in the X:Backups location. To use this script, copy the following code to a Notepad file, and then customize the text string that specifies the backup file (x:backupscompanyweb). Next, save the Notepad file by using the file name Wssback.vbs, and then create a scheduled task to run this script daily.

dim dayweekdim strBackupdayweek = datepart("w",(date))strBackup = """C:Program FilesCommon FilesMicrosoft Sharedweb server extensions60BINstsadm""" + _"-o backup -url http://companyweb -filename x:backupscompanyweb" + cstr(dayweek) + ".dat -overwrite"wscript.echo "Backing up Companyweb"Set WshShell = WScript.CreateObject("WScript.Shell")Return = WshShell.Run(strBackup, 0, true)Set WshShell = Nothing

To add this script as a scheduled task, follow these steps:

1. Open Control Panel.
2. Double-click Scheduled Tasks.
3. On the File menu in Scheduled Tasks, point to New, and then click Scheduled Task.
4. Type a name for the scheduled task, such as Companyweb Backup.
5. Double-click the new task to open the task properties.
6. In the Run box, type cscript Drive:Wssback.vbs.

Note In this step, Drive is the location of the Wssback.vbs file.

7. Click Set password, type the password for the user account that the task will use in the Password box and in the Confirm password box, and then click OK.
8. Click the Schedule tab, configure the appropriate schedule, and then click OK.
9. To test the scheduled task, right-click the task, and then click Run. After the task runs, visit the location that you specified for the backup file to verify that the script created the file.

back to the top

REFERENCES

For more information about the Windows SharePoint Services Software Development Kit (SDK), visit the following Microsoft Web site:

For more information about the Windows SharePoint Services Administrator’s Guide, visit the following Microsoft Web site:

For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

829113 How to restore a Windows SharePoint Services CompanyWeb database after the intranet component and SQL Server or MSDE 2000 have been removed on Windows Small Business Server

For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

827701 How to perform a disaster recovery operation of SharePoint Services Companyweb and SharePoint Services Databases

back to the top


APPLIES TO
Microsoft Windows Small Business Server 2003 Premium Edition
Microsoft Windows Small Business Server 2003 Standard Edition
Microsoft Windows SharePoint Services
Keywords: 
kbhowtomaster kbbug KB829112

Link to this Article