SQL Server : Find File Location for All Database
Here is the quick script I use find file location for all the database in my server instance. SELECT Name, FileName FROM sysdatabases if you have any other way to find please share!!!
» Read moreTag: SQL
SQL Live Monitor
Hello Friends, I have tried the Utility SQL Live Monitor, it really helpful for me…. That’s why I am sharing with you all … Features This tool is designed to provide realtime performance data on SQL Server, as well as data capture for offline analysis. The tools features are: Realtime SQL and System performance data Colour coded alerts Capture data logging to CSV – sample interval configurable PAL Perfmon counter logged to .blg or .csv for offline analysis using PAL
» Read moreMigrating Logins from One SQL Server to Another
As I need to migrate the databases from one server to another server. I have to deal with migrate not only the data, but the SQL Server logins that access that data as well. There may be different ways to migrate SQL Server logins like you can manually re-enter the entire existing login on the new server. You can use DTS to transfer logins. But here sharing one of other way of migration of SQL Server logins, by using the
» Read moreLinked Server
——————————————————— Create Linked Server through Scripts ——————————————————— USE [master] GO EXEC sp_addlinkedserver @server=’dsql2k’, @srvproduct=”, @provider=’SQLNCLI’, @datasrc=’source_name’ GO USE [master] GO EXEC master.dbo.sp_serveroption @server=N’dsql2k’, @optname=N’rpc’, @optvalue=N’true’ GO EXEC master.dbo.sp_serveroption @server=N’dsql2k’, @optname=N’rpc out’, @optvalue=N’true’ GO select * from master.dbo.sysservers ————————————————————————— Alternative Script to Create Linked Server and Mapping of Users ————————————————————————— Use Master go EXEC master.dbo.sp_addlinkedserver @server = N’dsql2k’, @srvproduct=N”, @provider=N’SQLOLEDB’, @datasrc=N’testserver’ GO EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N’dsql2k’, @locallogin = N’sa’, @useself = N’False’, @rmtuser = N’sa’, @rmtpassword = N’sa’ GO
» Read moreSQL Backup Plan with Dynamic file Name
DECLARE @day VARCHAR(5) DECLARE @month VARCHAR(15) DECLARE @year VARCHAR(5) DECLARE @hour VARCHAR(5) DECLARE @filename VARCHAR(500) SET @day = DATENAME(DAY, GETDATE()) SET @month = DATENAME(MONTH, GETDATE()) SET @year = DATENAME(YEAR, GETDATE()) SET @hour = DATENAME(HOUR, GETDATE()) SET @filename = ‘D:DATABASE Backupdb_name_’ + @day + @month + @year + @hour + ‘.bak’ Backup DATABASE db_name TO Disk = @filename WITH format BACKUP log db_name TO disk = @filename WITH format
» Read more