Skip to content

Mr SQL's Blog

Musings on Microsoft SQL Server

Tag Archives: database

It is possible to obtain the ‘date of creation’ of a table, as well as its last modification date (DDL, not DML) by running the following query:-

SELECT [name] AS 'Table', 
[create_date] AS 'Creation Date', 
[modify_date] AS 'Modification Date' 
FROM sys.objects
WHERE type_desc = 'USER_TABLE' 
ORDER BY [modify_date] DESC
Advertisements

Tags: , , , ,

I am often called upon to restore a copy of a live database to a development server. Taking a one-off backup in the normal manner would cause problems, as the sequence of LSNs (Log Sequence Numbers) would be disrupted on the production server. This means that subsequent log or differential backups would follow the one-off backup, rather than the file produced from the regular scheduled backup plan. Thankfully there is a solution….

Issuing a backup with the COPY_ONLY option stops the log sequence chain from being broken. Use the option as follows:-

BACKUP DATABASE [LiveDB]
TO DISK = 'E:\Backups\TestRestoreFile.bak'
WITH INIT,  NAME = 'Full Database Backup', COPY_ONLY,  STATS = 10

Unfortunately, it is not possible to select a COPY_ONLY backup through the Management Studio GUI. The backup command has to be issued through T-SQL. It is also not possible to restore through the interface. As you can see from the following screenshot, you can select the backup file, but the backup set will not appear in the list.

RestoreTestDB
To perform the restore, issue the following command, specifying the target location(s) for data and log files. Include REPLACE to overwrite any existing test database. I would also recommend you rename the files so that they now match the name of the test database, rather than the live database.

RESTORE DATABASE [TestDB]
FROM  DISK = 'E:\Backups\TestRestoreFile.bak'
WITH  FILE = 1,
MOVE 'LiveDB_data' TO 'E:\Data\TestDB.mdf',
MOVE 'LiveDB_log' TO 'E:\Logs\TestDB_log.ldf',
NOUNLOAD,  REPLACE,  STATS = 1
GO

If you now issue log backups on the production server, the LSNs will be in the correct sequence and the COPY_ONLY backup will effectively have been ignored.

Tags: , , , , , , ,

Following on from my previous post on sp_help, the sp_helpdb stored procedure provides useful information at the database level.

Running sp_helpdb without specifying a database provides an overview of all databases within the instance.

sp_helpdb_resultsThe query returns the following information:-

name
database size
owner
database id
creation date
database status
compatibility level

By specifying a database,

sp_helpdb 'AdventureWorks' 

additional information is provided on that databases data files and log files:-

sp_helpdb

Tags: , , , , ,

The collation settings of SQL Server determine the range of characters that can be stored in a database. Collation also has a direct bearing on how data is sorted (ordered) and compared. SQL Server can have either a Windows or a SQL collation configured at the server level. At the database level, individual databases can have different collation settings applied, which override those set at the server level. As a general rule, if tables include both Unicode and non-Unicode (ASCII) data, then a Windows collation should be selected.

I tend to use Latin1_General_CI_AS across all of my servers. This ensures compatibility when querying or moving data across multiple servers and databases. It leads to fewer conflicts and more consistent, predictable query results. Latin1 includes all major English and European-style characters. The CI (case insensitive) part of the collation ensures that queries do not differentiate between lower case and upper case characters. Thus,

SELECT firstname
FROM mytable
WHERE firstname = 'Zoe'

will return anyone with a name of ‘Zoe’, ‘zoe’, ‘ZOE’ etc. Setting the collation to CS (case sensitive) would lead to the same query returning only ‘Zoe’. By specifying AS (accent sensitive) in the collation, accented characters such as ö would affect query results. The above query would not return Zöe unless AI (accent insensitive) were specified.

To find the collation of all databases on a server, execute the following query:-

SELECT [name], [collation_name]
FROM sys.databases

To return a list of all available collations, run the following:-

SELECT *
FROM :: fn_helpcollations()

To return server-level collation settings:-

SELECT CONVERT(CHAR, SERVERPROPERTY(‘collation’))

Tags: , , , , , , ,

%d bloggers like this: