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 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . [. . . ] SQL provides several types of backups, but to perform a full backup issue the following command in a command prompt osql ­S servername ­E ­Q "backup database tmsng TO DISK = `c:\filename. bak'" filename. bak is now a full backup of the database tmsng and is free to be moved or stored 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 Cisco TMS Database Knowledge Tips Page 22 of 35 Tips category ­ Maintaining/Upkeep TIP T301 ­Restoring the Cisco TMS Database using osql Cisco TMS Versions SQL Server Versions SQL 2000 ­ All Versions MSDE 2000 ­ All Versions SQL 2005 ­ All Versions SQL 2005 Express ­ All Versions All A database can be restored to an existing database, or have it create a database if it does not exist. If restoring a database to a different server or newly installed server, additional steps must be taken. The below examples assume a default Cisco TMS installation and you are a SQL administrator. Restoring a database to the same SQL server To restore a database, the database must be idle and have no active connection. To stop all connections to the Cisco TMS database, stop all Cisco TMS Windows Services and IIS web servers pointed at the Cisco TMS database. 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 SQL provides several types of backups, but to perform a full backup issue the following command in a command prompt. Example to restore the Cisco TMS database from a backup set at c:\tms\backup. bak osql ­S servername ­E ­Q "restore database tmsng FROM DISK = `c:\tms\backup. bak'" Once the restore is complete, restart the services that were stopped using the Services Control panel by right-clicking on each and selecting Start. Restoring a database to a different SQL server To restore a database, the database must be idle and have no active connection. To stop all connections to the Cisco TMS database, stop all Cisco TMS Windows Services and IIS web servers pointed at the Cisco TMS database. 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. 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. This means while you can back up the database easily, you cannot automate this process from within SMSE. [. . . ] Save the file as update. txt DECLARE @table_name varchar(1000) declare c1 cursor for SELECT name Cisco TMS Database Knowledge Tips Page 32 of 35 Tips category ­ Maintaining/Upkeep FROM sysobjects WHERE xtype = 'U' order by name open c1 fetch next from c1 into @table_name while Fetch_Status = 0 begin print(@table_name) DBCC DBREINDEX (@table_name, '') fetch next from c1 into @table_name end print('finished') close c1 deallocate c1 GO DECLARE @table_name varchar(1000), @sql nvarchar(4000) declare c1 cursor for SELECT name FROM sysobjects WHERE xtype = 'U' order by name open c1 fetch next from c1 into @table_name while Fetch_Status = 0 begin print(@table_name) Select @sql = 'UPDATE STATISTICS '+ @table_name +' WITH FULLSCAN' exec sp_executesql @sql fetch next from c1 into @table_name end print('finished') close c1 deallocate c1 GO 2. Execute the script against your Cisco TMS database by entering the following command in a command prompt. [. . . ]