SQL Server

  • 1. Login on to the server you want to change the name of  2. Run this in Microsoft SQL Server Management Studio 3. Click on Create New Query 4. Enter this code to get the existing name. select @@servername 5. After code is entered click the red exclamation point to execute 6. The current name will be returned.  7. Next enter the following text filling in the appropriate information for you environment. sp_dropserver 'old_name' go sp_addserver 'new_name','local' go 8. After code is entered click the red exclamation point to execute 9. Now close Microsoft SQL Server Management Studio 10. Run the command prompt Administrator and run the following two commands net stop mssqlserver net start mssqlserver 11. Now reopen Microsoft SQL Server Management Studio and rerun select @@servername   12. Make sure the name matches the ‘new_name’
  • Log onto server Open SQL Server Management Studio (SSMS) Click About then Help To the right of SQL Server Management Studio match the Versions with the list below to determine your service pack level. SQL Server 2008 Service Pack 3 10.00.5500.00 SQL Server 2008 Service Pack 2 10.00.4000.00 SQL Server 2008 Service Pack 1 10.00.2531.00 SQL Server 2008 RTM 10.00.1600.20 SQL Server 2008 R2 Service Pack 1 10.50.2500.0 SQL Server 2008 R2 RTM 10.50.1600.1
  • Since Netback Database Client will not backup Microsoft SQL Express. The backup solution I came up with was one .sql file each for full and incremental backup, and two .cmd files which execute the respective .sql files via sqlcmd. The full backup SQL file looks like this: PRINT ‘backup.sql ‘ + CONVERT(VARCHAR, GETDATE(), 120) DECLARE @backupSetId AS INT DECLARE @Filename NVARCHAR(256) DECLARE @Database NVARCHAR(256) DECLARE @Backup NVARCHAR(256) SET @Database = N” SET @Filename = N’E:Backups.’ + REPLACE(CONVERT(NVARCHAR, GETDATE(), 102), ‘.’, ‘-‘) + N’.bak’ SET @Backup = @Database + N’ Full Database Backup’ BACKUP DATABASE @Database TO DISK = @Filename WITH NOFORMAT, NOINIT, NAME = @Backup, SKIP, NOREWIND, NOUNLOAD, STATS = 10 SELECT @backupSetId = position FROM msdb..backupset WHERE database_name = @Database AND backup_set_id = (SELECT MAX(backup_set_id) FROM msdb..backupset WHERE database_name=@Database ) IF @backupSetId IS NULL PRINT N’Verify failed. Backup information for database ”’ + @Database + N”’ not found.’ ELSE RESTORE VERIFYONLY FROM DISK = @Filename WITH FILE = @backupSetId, NOUNLOAD, NOREWIND This piece of code is repeated for every database you want to backup. The incremental backup file has the WITH DIFFERENTIAL option in the BACKUP command, and excludes verification. The batch file which executes the backup.sql file simply calls sqlcmd: sqlcmd -S  -i backup.sql -o backup.log Next Scheduling Task To setup a scheduled task you need to open the folder where you can create a new scheduled task. This can be found under Accessories -> System Tools -> Scheduled Tasks or under Control Panel. The first thing to do is to click on “Add Scheduled Task” and the following wizard will run. Select the application that you want to run. For our purposes we will be using SQLCMD.EXE. In order to find SQLCMD.EXE you will need to click on the Browse… button. You should be able to find this in the following directory “C:Program FilesMicrosoft SQLServer90ToolsBinn“. Give the scheduled task a name and specify when to perform...
  • January 10, 2010

    SQL Memory Leak Settings Work Around

    Microsoft SQL Memory Leak work around.  Microsoft SQL is a huge memory resource consumer there is no fix besides add more memory but there is a work around Microsoft provides 1.Log into the SQL Server using SQL Server Management Studio 2.Right click on the server go to properties 3.Click on the memory tab 4.Now the calculation is done in Megabytes 1024 MB is a gig 5.Subtract the system ram you want to leave free from your over all RAM. 6.I would leave at least 4 gigs of RAM free for the Operating System if possible. 7.The final answer is the number you type into the box that says “Maximum Server Memory (in MB)” 8.Click OK