ALTER DATABASE oldDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE ALTER DATABASE oldDBname MODIFY NAME =newDBname ALTER DATABASE newDBname SET MULTI_USER
Category: sql server administration
this happened because I had an instance of SS 2008 installed on my machine and then I installed visual studio express with ss express. I needed to set the properties of the instance to allow both windows and sql server authentication.
select SPECIFIC_NAME from msdb.information_schema.routines where routine_type = ‘PROCEDURE’ order by SPECIFIC_NAME
SELECT is_broker_enabled FROM sys.databases WHERE name = ‘msdb’; EXECUTE msdb.dbo.sysmail_help_status_sp; –if stopped then run EXECUTE msdb.dbo.sysmail_start_sp;
in order to take more than 1 hop – you must have kerberos setup to check if it is – run this: select auth_scheme from sys.dm_exec_connections where session_id=@@spid if it is running ntlm – you can only take 1 hop for example – if you…
To change the multiserver results options In Management Studio, on the Tools menu, click Options. Expand Query Results, expand SQL Server, and then click Multiserver Results. On the Multiserver Results page, specify the option settings that you want, and then click OK.
In Registered Servers, click the server type on the Registered Servers toolbar. If Registered Servers is not visible, click Registered Servers on the View menu. Right-click a server or a server group, point to New, and then click Server Group. In the New Server Group…
Login into SQL Server using Windows Authentication. In Object Explorer, open Security folder, open Logins folder. Right Click on SA account and go to Properties. Change SA password, and confirm it. Click OK. Make sure to restart the SQL Server and all its services and…
SELECT * INTO AdventureWorks.dbo.CustomersTemp FROM Northwind.dbo.Customers Just remember that using this query will only transfer the schema and data. It does not transfer the indexes, foreign keys, statistics etc.