Categories
Articles Backups SQL Windows Server

How to Perform a Database Copy in SQL Server

Copying databases can often be quite useful, but knowing how to do it is crucial. In SQL Server, an easy way to copy a database is to use the “Database Copy Wizard.” Here’s how to do it using this wizard:

  1. First, open the SQL Server Management Studio (SSMS) application and connect to your SQL Server.

You can access the article where I previously explained the installation process from here...  READ MORE ❯❯❯

Categories
Articles SQL

Finding Active and Inactive Databases in SQL Server

To find active and inactive databases in SQL Server when you have a large number of databases, you can use the following code:

CREATE TABLE #T (dbName varchar(100),last_user_seek datetime,last_user_scan datetime,last_user_lookup datetime,last_user_update datetime)
declare @dbId as int
declare @dbname as varchar(100)
declare crs cursor for select dbid,name from sysdatabases 
open crs
fetch next from crs into @dbId,@dbname
while @@FETCH_STATUS=0
begin
Insert Into #T 
Select @dbname,
last_user_seek = MAX(last_user_seek),
last_user_scan = MAX(last_user_scan),
last_user_lookup = MAX(last_user_lookup),
last_user_update = MAX(last_user_update)
From
sys.dm_db_index_usage_stats
WHERE
database_id=@dbId

fetch next from crs into @dbId,@dbname
end 
close crs
deallocate crs 

select * from #t 
drop table #t

Here are the steps to execute this query in SQL Server Management Studio (SSMS): ...  READ MORE ❯❯❯

Categories
Articles SQL

Index Maintenance in SQL Server: Determining REORGANIZE and REBUILD Operations

The concept of using SQL queries to determine and rectify the fragmentation rates of indexes is quite important. This query serves as a useful tool to understand how indexes require maintenance for improving the performance of databases. I will explain the process of using the provided SQL query to check index fragmentation rates and perform necessary corrections when needed.

SQL Query: ...  READ MORE ❯❯❯