USE master;
alter database DB1_DEV set SINGLE_USER with rollback immediate
go
ALTER DATABASE
DB1_DEV
Modify Name = DB1;
GO
alter database DB1 set MULTI_USER
go
Category: SQL Server
Creating a link between two SQL servers
To create a link between two SQL Servers you need to use the sp_addlinkedserver and sp_addlinkedsrvlogin stored procedures.
First,
sp_addlinkedserver @server='DBLINK01',@srvproduct='',@provider='SQLOLEDB',@datasrc='SQL-HOU-01';
Where,
@server is the name of the link
@srvproduct is the type of product, by default SQL Server
@datasrc is the server you link to
Then, you need to set the username and password to use on the link.
sp_addlinkedsrvlogin 'DBLINK01','false',null,'sa','Pa$$w0rd';
The first parameter is the name of the link to use,
The fourth is the account of the remote server to use,
The fifth is the password for the account.
Manually adding a mail account to SQL2005/2008
I was having trouble adding configuring Database Mail on a SQL2005 server today.
I followed the wizard just like I had done on the previous 6 servers, but this one just didn’t want to add it. It was giving me an error about not being able to insert a NULL into the server name field, although I was giving it a server name.
I tried manually adding the data into the table, but of course that didn’t work. You have to use a stored procedure.
You need to use msdb.dbo.sysmail_add_account_sp with options like below,
msdb.dbo.sysmail_add_account_sp @account_name='company.com mail server',@email_address='john.doe@company.com',@display_name='John Doe',@replyto_address='john.doe@company.com',@mailserver_name='mail.company.com',@port=25;
Simple script to backup all SQL Server databases
DECLARE @name VARCHAR(50) -- database name DECLARE @path VARCHAR(256) -- path for backup files DECLARE @fileName VARCHAR(256) -- filename for backup DECLARE @fileDate VARCHAR(20) -- used for file name SET @path = 'C:Backup' SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) DECLARE db_cursor CURSOR FOR SELECT name FROM master.dbo.sysdatabases WHERE name NOT IN ('master','model','msdb','tempdb') OPEN db_cursor FETCH NEXT FROM db_cursor INTO @name WHILE @@FETCH_STATUS = 0 BEGIN SET @fileName = @path + @name + '_' + @fileDate + '.BAK' BACKUP DATABASE @name TO DISK = @fileName FETCH NEXT FROM db_cursor INTO @name END CLOSE db_cursor DEALLOCATE db_cursor
How to list all the databases on a SQL server
You can list all the databases on a SQL server by running the following query.
SQL> select name from sys.sysdatabases
Creating a link between two SQL servers
To create a link between two SQL Servers you need to use the sp_addlinkedserver and sp_addlinkedsrvlogin stored procedures.
First,
[code]
sp_addlinkedserver @server=’DBLINK01′,@srvproduct=”,@provider=’SQLOLEDB’,@datasrc=’SQL-HOU-01′;
[/code]
Where,
@server is the name of the link
@srvproduct is the type of product, by default SQL Server
@datasrc is the server you link to
Then, you need to set the username and password to use on the link.
[code]
sp_addlinkedsrvlogin ‘DBLINK01′,’false’,null,’sa’,’Pa$$w0rd’;
[/code]
The first parameter is the name of the link to use,
The fourth is the account of the remote server to use,
The fifth is the password for the account.
Manually adding a mail account to SQL2005/2008
I was having trouble adding configuring Database Mail on a SQL2005 server today.
I followed the wizard just like I had done on the previous 6 servers, but this one just didn’t want to add it. It was giving me an error about not being able to insert a NULL into the server name field, although I was giving it a server name.
I tried manually adding the data into the table, but of course that didn’t work. You have to use a stored procedure.
You need to use msdb.dbo.sysmail_add_account_sp with options like below,
[code]
msdb.dbo.sysmail_add_account_sp @account_name=’company.com mail server’,@email_address=’john.doe@company.com’,@display_name=’John Doe’,@replyto_address=’john.doe@company.com’,@mailserver_name=’mail.company.com’,@port=25;
[/code]
Simple script to backup all SQL Server databases
[draft]
DECLARE @name VARCHAR(50) — database name
DECLARE @path VARCHAR(256) — path for backup files
DECLARE @fileName VARCHAR(256) — filename for backup
DECLARE @fileDate VARCHAR(20) — used for file name
SET @path = ‘C:\Backup\’
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN (‘master’,’model’,’msdb’,’tempdb’)
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + ‘_’ + @fileDate + ‘.BAK’
BACKUP DATABASE @name TO DISK = @fileName
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
http://www.mssqltips.com/tip.asp?tip=1070
How to list all the databases on a SQL server
You can list all the databases on a SQL server by running the following query.
SQL> select name from sys.sysdatabases
Renaming a SQL Server instance.
SQL> sp_dropserver 'oldservername';
SQL> sp_addserver 'newservername';
don’t run in same batch.
the two statements must be executed separately.
via sp_addserver Transact-SQL.