Detach and Attach SQL Server Database

Detach and Attach SQL Server Database

how to Detach and Attach SQL Server Database using SQL Server Management studio?

First,

According to the above, picture Detach your Selected database.
Next,

According to the above, picture Select "Update Stats" And Select "OK".
Next,
Move Database File Old Location to New location,
Ex: C:\databaseusergroup.mdf to D:\databaseusergroup.mdf
And Next Select "Attach" like Following Picture,

Next,

Attach Database like Following Pictures,

(If You Have multiple data file then you need to select only main MDF file)

Cheers...

and Most welcome your comments and ideas...

Get Executed SQLs From Database

Get Executed SQLs From Database

If you need to get executed SQL statements from database, then you can used this method to read it,

First we will insert one record,

INSERT INTO [DATABASEUSERGROUP].[dbo].[XX](col2)SELECT 2015GO 

Next you can read it as follows,

SELECT qs.last_execution_time, st.[TEXT] AS [SQLQuery]FROM sys.dm_exec_query_stats AS qsCROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS stWHERE (lower(st.[TEXT]) like '%insert%')AND (lower(st.[TEXT]) like '%xx%')AND (lower(st.[TEXT]) like '%col2%')ORDER BY qs.last_execution_time DESC

I'm not mistaken if you restart MSSQL Server service then this record will be deleted! I didn't try it just add comment after try it...

Cheers...
and Most welcome your comments and ideas...

RESTORE FULL and DIFFERENTIAL BACKUPS

RESTORE FULL and DIFFERENTIAL BACKUPS

If you need to restore database given following backups files,

• One Full Database Backup File
• Tow Differential Database Backup Files

First you need to have full database backup file as follows,

use masterGOBACKUP DATABASE [DATABASEUSERGROUP] TO  DISK = N'C:\MSSQL\Backups\DATABASEUSERGROUP_FULL.bak' WITH NOFORMAT, NOINIT,  NAME = N'DATABASEUSERGROUP-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10;GODECLARE @backupSetId AS intSELECT @backupSetId = position FROM msdb..backupset WHERE [database_name]=N'DATABASEUSERGROUP' AND backup_set_id=(SELECT MAX(backup_set_id) FROM msdb..backupset WHERE [database_name]=N'DATABASEUSERGROUP');IF @backupSetId is null BEGIN  RAISERROR(N'Verify failed. Backup information for database ''DATABASEUSERGROUP'' not found.', 16, 1); ENDRESTORE VERIFYONLY FROM  DISK = N'C:\MSSQL\Backups\DATABASEUSERGROUP_FULL.bak' WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWINDGO

10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
90 percent processed.
Processed 113752 pages for database 'DATABASEUSERGROUP', file 'DATABASEUSERGROUP' on file 1.
Processed 1416 pages for database 'DATABASEUSERGROUP', file 'SALES_01' on file 1.
Processed 8 pages for database 'DATABASEUSERGROUP', file 'SALES_02' on file 1.
Processed 176 pages for database 'DATABASEUSERGROUP', file 'ARCHIVE_OLD' on file 1.
Processed 424 pages for database 'DATABASEUSERGROUP', file 'ARCHIVE_2002' on file 1.
Processed 944 pages for database 'DATABASEUSERGROUP', file 'ARCHIVE_2003' on file 1.
100 percent processed.
Processed 2 pages for database 'DATABASEUSERGROUP', file 'DATABASEUSERGROUP_log' on file 1.
BACKUP DATABASE successfully processed 116722 pages in 3.060 seconds (298.002 MB/sec).
The backup set on file 1 is valid.

 Next you need to have two(You need to execute same script twice after change backup file Ex: DIFF_01.bak, DIFF_02.bak)  Differential database backup file as follows, 

use master
GO
BACKUP DATABASE [DATABASEUSERGROUP] TO  DISK = N'C:\MSSQL\Backups\DATABASEUSERGROUP_DIFF_02.bak'
WITH  DIFFERENTIAL , NOFORMAT, NOINIT, 
NAME = N'DATABASEUSERGROUP-Diff Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10
GO
DECLARE @backupSetId AS int
SELECT @backupSetId = position FROM msdb..backupset WHERE [database_name]=N'DATABASEUSERGROUP' and backup_set_id=(SELECT MAX(backup_set_id) FROM msdb..backupset WHERE [database_name]=N'DATABASEUSERGROUP' )
IF @backupSetId is null BEGIN
RAISERROR(N'Verify failed. Backup information for database ''DATABASEUSERGROUP'' not found.', 16, 1);
END
RESTORE VERIFYONLY FROM  DISK = N'C:\MSSQL\Backups\DATABASEUSERGROUP_DIFF_02.bak' WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND
GO

29 percent processed.
39 percent processed.
49 percent processed.
59 percent processed.
69 percent processed.
79 percent processed.
89 percent processed.
99 percent processed.
Processed 40 pages for database 'DATABASEUSERGROUP', file 'DATABASEUSERGROUP' on file 1.
Processed 8 pages for database 'DATABASEUSERGROUP', file 'SALES_01' on file 1.
Processed 8 pages for database 'DATABASEUSERGROUP', file 'SALES_02' on file 1.
Processed 8 pages for database 'DATABASEUSERGROUP', file 'ARCHIVE_OLD' on file 1.
Processed 8 pages for database 'DATABASEUSERGROUP', file 'ARCHIVE_2002' on file 1.
Processed 8 pages for database 'DATABASEUSERGROUP', file 'ARCHIVE_2003' on file 1.
100 percent processed.
Processed 1 pages for database 'DATABASEUSERGROUP', file 'DATABASEUSERGROUP_log' on file 1.
BACKUP DATABASE WITH DIFFERENTIAL successfully processed 81 pages in 0.031 seconds (20.208 MB/sec).
The backup set on file 1 is valid.

After Complete your Backup Task You need to restore above backup file as follows,

USE [master]GORESTORE DATABASE [DATABASEUSERGROUP] FROM  DISK = N'C:\MSSQL\Backups\DATABASEUSERGROUP_FULL.bak' WITH  FILE = 1,  NORECOVERY,REPLACE, NOUNLOAD,  STATS = 5;GO

You Have to mention NORECOVERY otherwise you can not restore remaining two Differential database backup files.
And After Restore full database backup,

Finally You Need to restore remaining two Differential database backup files as follows,

USE [master]GORESTORE DATABASE [DATABASEUSERGROUP] FROM  DISK = N'C:\MSSQL\Backups\DATABASEUSERGROUP_DIFF_01.bak' WITH  FILE = 1,NORECOVERY, NOUNLOAD,  STATS = 5GORESTORE DATABASE [DATABASEUSERGROUP] FROM  DISK = N'C:\MSSQL\Backups\DATABASEUSERGROUP_DIFF_02.bak' WITH  FILE = 1,RECOVERY, NOUNLOAD,  STATS = 5GO

Last Differential database backup files you need to restore as RECOVERY.

Cheers...
and Most welcome your comments and ideas...