Tech News

Those of you looking for a simple backup setup for your SQL server 2005 databases are in luck! I’ve stumbled across this script, and modified it to work for me in my environment.

I am not suggesting that your entire SQL server backup strategy should be to create backup files from your databases nightly. However, a nightly backup scripted to an alternate location is absolutely a crucial component of your SQL server backup strategy.

Use SQL Server Agent to create and schedule a job containing the following code as the command portion of the only step the job will contain:

DECLARE @DBName varchar(255)

DECLARE @DATABASES_Fetch int

DECLARE DATABASES_CURSOR CURSOR FOR
select
DATABASE_NAME = db_name(s_mf.database_id)
from
sys.master_files s_mf
where
– ONLINE
s_mf.state = 0

– Only look at databases to which we have access
and has_dbaccess(db_name(s_mf.database_id)) = 1

– Not master, tempdb or model
and db_name(s_mf.database_id) not in (’Master’,'tempdb’,'model’)
group by s_mf.database_id
order by 1

OPEN DATABASES_CURSOR

FETCH NEXT FROM DATABASES_CURSOR INTO @DBName

WHILE @@FETCH_STATUS = 0
BEGIN
declare @DBFileName varchar(256)
set @DBFileName = datename(dw, getdate()) + ‘ – ‘ +
replace(replace(@DBName,’:',’_'),’\',’_')

exec (’BACKUP DATABASE [' + @DBName + '] TO DISK = N”c:\db backup\’ +
@DBFileName + ”’ WITH NOFORMAT, INIT, NAME = N”’ +
@DBName + ‘-Full Database Backup”, SKIP, NOREWIND, NOUNLOAD, STATS = 100′)

FETCH NEXT FROM DATABASES_CURSOR INTO @DBName
END

CLOSE DATABASES_CURSOR
DEALLOCATE DATABASES_CURSOR

Edit the line which contains c:\db backup\ to point at the location where you would like the database backup files to be deposited.

Modify the line with datename(dw, getdate()) in it to change the output file name of your database backups. If you leave the above script unchanged, your database backup files will be named: Monday – dbname.bak, Tuesday – dbname.back etc. In my case, I chose to leave this naming convention for one particular reason. I schedule the SQL Server Agent job to run a few hours before I schedule a task in Windows to execute a batch file which copies the contents of my backup directory to a network file server. By keeping all of my files named using days of the week as the only delimiting factor, I know that my file server will never have more than 7 days worth of data saved onto it (since every Monday will overwrite every past Monday, and every Tuesday will overwrite every past Tuesday, etc).

2 Comments »

The creation of a database driven website is very simple in concept; add content to the database, and display the content on pages. The work going on behind the scenes to make this concept function, is not always so simple. There are numerous methods and products that can be used to accomplish different parts of this task, but getting them all to work nicely together can deliver headaches to anyone.

One popular combination often chosen by developers is the use of the PHP scripting language, and MySQL database. Both products are free, open source, cross platform, and have a large base of corporate and personal users.

Getting PHP and MySQL to work nicely with Windows Server 2003 and IIS can be troublesome, mostly due to the many different versions of PHP and MySQL, and the frequency that each product is updated (don’t get me wrong, free updates to products are great, once they are configured and working as expected). After experiencing some problems related to versions myself, I decided to write this tutorial on how to configure an operational MySQL database capable of interacting with PHP, IIS 6.0 on Windows Server 2003.

Required:

• PHP 5.2.3
• MySQL 5.0
• MySQL 5.0 GUI Tools

Background:

1. IIS 6.0 previously installed on a Windows 2003 Server.

PHP Installation

1. Create a folder on your root (C:) and name it PHP. (C:\PHP)
2. Unzip the contents of the PHP 5.2.3 ZIP file to the PHP folder created in step 1.
3. Open the IIS MMC snap-in.
   1. In the properties of the website you wish to use PHP with (most likely the default website), go to the Home Directory tab, and select Configuration.
   2. Under Application Configuration, check to see if .php is in the list. If it is, you will need to click edit and modify the Executable to C:\php\php5isapi.dll. If it is missing from the list, click add, and use C:\php\php5isapi.dll as the Executable and .php as the extension.
4. Copy php.ini-recommended from C:\PHP to C:\Windows and rename to php.ini .
5. Reboot.

To test the PHP installation, create a new file in notepad that contains the code below. Save the file as test.php in the root directory of your website.

<?PHP
phpinfo();
?>

Open a web browser from your web server and test http://127.0.0.1/test.php . If PHP is installed correctly, you will get a listing of the PHP configuration.

MySQL Installation

1. Install MySQL 5.0
2. Install the GUI tools for MySQL 5.0
3. Run the MySQL Server Instance Config Wizard from the Start menu.
   1. Detailed Configuration
   2. Developer Machine
   3. Non-Transactional Database Only
   4. Decision Support (DSS)/OLAP
   5. Check Enable TCPIP Networking
   6. Port 3306
   7. Standard Character Set
   8. Install as a Windows Service
   9. Assign a root password (remember this password!!)

   *Remember the password that you assign to the root account! Document the password in a secure, reliable location.

4. Launch the MySQL Administrator Program (the GUI tools installed earlier)
   1. Server host: localhost
   2. username: root
   3. password: use the password assigned above.
5. If the tool connects and lists information about your MySQL server, then MySQL has been configured correctly.

To finish things off, we need to configure PHP to load some MySQL extensions. This will enable the web server, PHP, and MySQL to work together while server content to web pages from the MySQL database.

1. Copy C:\PHP\libmysql.dll to C:\windows\system32\ .
2. Open C:\Windows\PHP.ini in Notepad.
3. Search the INI file for the following lines, and edit them to read as follows. If there is a semi-colon in front of the line, remove it.
   1. extension_dir=”c:\php\ext\”
   2. extension=php_mysql.dll
   3. extension=php_mysqli.dll
4. Reboot.

To test the configuration, create a new file in notepad that contains the code below. Save the file as mysqltest.php in the root directory of your website. Edit the code to replace the root password with the one previously assigned.

<?PHP

//(”server name”,”username”,”password”)
$rst = @mysql_connect(”localhost”,”root”,”insert_password_here”);

if (!$rst){
echo( “<p>Unable to connect to database manager.</p>”);
die(’Could not connect: ‘ . mysql_error());
exit();
} else {
echo(”<p>Successfully Connected to MySQL Database Manager!</p>”);
}

if (! @mysql_select_db(”mysql”) ){
echo( “<p>Unable to connect database…</p>”);
exit();
} else {
echo(”<p>Successfully Connected to Database ‘MYSQL’!</p>”);
}
?>

Open a web browser from your web server and test http://127.0.0.1/mysqltest.php . If everything is configured correctly, your web browser will display Successfully Connected to MySQL Database Manager and Successfully Connected to Database ‘MYSQL’.

The Web Server is now ready to serve PHP web pages and communicate with a MySQL database.

32 Comments »