User manual TANDBERG TMS SQL DATABASE CONFIGURATION GUIDE

[. . . ] Cisco TMS SQL Database Product Configuration Guide December 2010 D14216. 01 Table of contents Table of contents Tip format. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5 TIP T100 ­Different types of SQL Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6 Extra reference links . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6 TIP T101 ­SQL Server management tools . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . [. . . ] Start Menu -> Control Panel -> Administrative Tools -> Services Find the Services whose names begin with TMS. Repeat for all services whose name starts with TMS Right-Click the World Wide Web Publishing Service and select Stop Repeat the above steps for all Cisco TMS servers pointed at the Cisco TMS database The backup set contains the physical file path of the database files. When switching servers, the physical location for database files is often different. This requires adding addition MOVE parameters to the restore command for each file in the backup set to tell the server where to restore the database files on the new server. Typically the new location for the files should be the DATA folder of the new SQL server. The files that make up the tmsng database may vary based on when your database was created. To verify the filenames and paths of files in your backup set c:\tms\backup. bak, use the following command in a command prompt. This will output each file, their path, and additional information. osql ­E ­S servername ­Q "RESTORE filelistonly FROM DISK = `c:\tms\backup. bak'" If the file paths are different on the new server, you must add the MOVE parameter for each file. The syntax is: Cisco TMS Database Knowledge Tips Page 23 of 35 Tips category ­ Maintaining/Upkeep RESTORE databasename FROM DISK = `filename' WITH MOVE `file' TO `path\filename', MOVE `file' to `path\filename' If the new server's DATA directory is d:\databases the command syntax to restore the backup to the new folder would be: osql ­E ­S servername ­Q "RESTORE database FROM disk = `c:\tms\backup. bak' WITH MOVE `tmsng' TO `d:\databases\tmsng. mdf', MOVE `tmsng_log' TO `d:\databases\tmsng_log. ldf'" Once the restore is complete, restart the services that were stopped using the Services Control panel by right-clicking on each and selecting Start. For additional help on osql command line options, see `TIP T109 ­Using osql for Cisco TMS tasks' Extra reference links How to manage the SQL Server Desktop Engine (MSDE 2000) or SQL Server 2005 Express Edition by using the osql utility Backing Up and Restoring Databases in SQL Server Copying Databases with Backup and Restore RESTORE Arguments (Transact-SQL) Cisco TMS Database Knowledge Tips Page 24 of 35 Tips category ­ Maintaining/Upkeep TIP T302 ­Automating backups with SQL 2005 Express Cisco TMS Versions Cisco TMS 11. 0 or newer SQL Server Versions SQL 2005 ­ All Versions SQL 2005 Express ­ All Versions SQL 2005 Express can be managed with the SQL Management Studio Express (SMSE) which makes it easy to perform tasks - including backups. Cisco TMS ships with a DB Management Utility that was originally created because MSDE did not have any GUI tools making it difficult for customers to maintain their databases. With the availability of SMSE, Cisco TMS DB Management Tool is of limited value. Customers looking to add maintenance for their databases should be looking at SMSE as a way to manage their databases. One omission from SMSE is the lack of a scheduler agent. This means while you can back up the database easily, you cannot automate this process from within SMSE. There are several ways to accomplish this depending on your needs. The basic premise is simply to create a SQL script or BAT file to execute the SQL commands to do the backup, and have the Windows Scheduler task run the script. The problem with this method is it does not provide for functionality such as rotating backup files. An open source method to achieve automation in backups and file rotation is described here http://www. sqldbatips. com/showarticle. asp?ID=27 . This includes a stored procedure method and a stand-alone utility to perform the task. This site is referenced by Microsoft for alternatives to automating backups. [. . . ] The Windows administrator is an administrator of the SQL Server by default, so logging in as that user allows the sa password to be reset. Log into the Windows server hosting Cisco TMS open a command prompt 2. Change complexpass with the password of your choice. osql ­E ­S localhost\sqltms ­Q "sp_password @old = null, @new = 'complexpwd', @loginame ='sa'" 3. [. . . ]