Search

My notebook

Category

mssql

SQL SERVER – How to Change Server Name?

1. Show SQL-server name.

SELECT @@SERVERNAME

2. To drop the current server name:

EXEC sp_DROPSERVER 'oldservername'

3. To add a new server name. Make sure local is specified!

EXEC sp_ADDSERVER 'newservername', 'local'

4. Restart SQL Services.

PowerShell:
Stop-Service -Name MSSQLSERVER
Stop-Start -Name MSSQLSERVER
Get-Service -Name MSSQLSERVER

Show sql service name: (get-itemproperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server').InstalledInstances

5. Verify the new name using:

1. SELECT @@SERVERNAME
2. SELECT * FROM sys.servers WHERE server_id = 0

Advertisements

Backup script for SQL express server

Create sql_backup.bat file in folder c:\scripts\sql_backup

(Scripts in folder c:\script\sql_backup\ backups in folder d:\sqlbackup\)

File text:

sqlcmd -i c:\scripts\sql_backup\sql_backup.sql >  D:\SQLBACKUPS\reports\backuplog.txt

Create sql_backup.sql files in folder c:\scripts\sql_backup

File text:

DECLARE @basePath varchar(300); 
SET @basePath = N'D:\SQLBACKUPS\'; 

DECLARE @db sysname;
DECLARE user_db_cursor CURSOR FOR 
 SELECT name FROM sys.databases WHERE name NOT IN 
('master', 'model', 'msdb', 'tempdb') 
OPEN user_db_cursor 
FETCH NEXT FROM user_db_cursor 
INTO @db 

WHILE @@FETCH_STATUS = 0 
BEGIN 
 DECLARE @backupPath varchar(1000); 
 SET @backupPath = @basePath + @db + '\'; 

 DECLARE @backupName varchar(1000); 
 DECLARE @time datetime2; 
 SET @time = SYSDATETIME(); 
 SET @backupName = @db + '_backup_' + 
REPLACE(REPLACE(REPLACE(CONVERT(varchar, @time, 20),'-','_'),':',''),' ','_') 
 + '_' + CAST(DATEPART(NANOSECOND, @time)/100 as varchar) 

 --Create sub-directory 
 EXECUTE master.dbo.xp_create_subdir @backupPath 
 
 DECLARE @backupFileName varchar(1000); 
 SET @backupFileName = @backupPath + @backupName + '.bak'; 
 
 --Backup database 
 BACKUP DATABASE @db TO DISK=@backupFileName 
 WITH NOFORMAT, NOINIT, NAME=@backupName , 
 SKIP, REWIND, NOUNLOAD, STATS=10 
 FETCH NEXT FROM user_db_cursor 
INTO @db 
END
CLOSE user_db_cursor 
DEALLOCATE user_db_cursor

Delete old backups:

Create

sqlcmd -i D:\SQLBACKUPS\Script\deleteold.sql > D:\SQLBACKUPS\reports\delete.txt
DECLARE @basePath varchar(300); 
SET @basePath = N'D:\SQLBACKUPS\'; 

DECLARE @time datetime; 
SET @time = DATEADD(DAY,-3, GETDATE() ); 

EXECUTE master.dbo.xp_delete_file 0,@basePath,N'bak',@time,1 

Create a free website or blog at WordPress.com.

Up ↑