SQL Server BACKUP and RESTORE
Restoring a database backup re-creates the database and all of its associated files that were in the database when the backup was completed.
However, any modifications made to the database after the backup was created are lost. To restore transactions made after the database backup was created, you must use transaction log backups or differential backups.
Steps:
1. Copies all of the data from the backup into the database.
2. Rolls back any incomplete transactions in the database backup to ensure that the database is consistent.
3. To prevent overwriting a database unintentionally, the restore operation performs safety checks automatically. The restore operation fails if: the database name in the restore operation does not match the database name recorded in the backup set.
4. The database named in the restore operation already exists on the server but is not the same database contained in the database backup. For example, the database names are the same, but each database was created differently.
5. One or more files need to be created automatically by the restore operation, but the file names already exist. These safety checks can be disabled if the intention is to overwrite another database.
If you restore a database on a different instance of SQL Server than the one on which the backup was created, you may need to run sp_change_users_login to update user login information.
Tags: backup, database, restore, sql server database back up, sql server database restore, MSSQL database back up, mssql database restore
Friday, April 25, 2008
sp_change_users_login - Link users to corresponding logins
Introduction:
Although the terms login and user are often used interchangeably, they are very different. A login is used for user authentication and a database user account is used for database access and permissions validation. Logins are associated to users by the security identifier (SID). A login is required for access to the SQL Server server. The process of verifying that a particular login is valid is called "authentication". This login must be associated to a SQL Server database user. You use the user account to control activities performed in the database. If no user account exists in a database for a specific login, the user that is using that login cannot access the database even though the user may be able to connect to the SQL Server server. The single exception to this situation is when the database contains the "guest" user account. A login that does not have an associated user account is mapped to the guest user. Conversely, if a database user exists but there is no login associated, the user is not able to log into SQL Server server.
Purpose:
The sp_change_users_login procedure has a specific purpose. It’s used to identify and correct users within a database which do not have a corresponding logins.
Scenario:
When a database is restored to a different server it contains a set of users and permissions but there may not be any corresponding logins or the logins may not be associated with the same users. A mismatch may occur between the security identification numbers (SIDs) of the logins in the master database and the users in the user database. An example of when this would happen is when you are restoring a database from Production to QA.
Syntax:
sp_change_users_login [ @Action = ] 'action' [ , [ @UserNamePattern = ] 'user' ] [ , [ @LoginName = ] 'login' ] [ , [ @Password = ] 'password' ]
Action: Describes the action to be performed.
Can be one of these values:
Value: Auto_Fix
Description: Links a user entry in the sysusers table in the current database to a login of the same name in sysxlogins. You should check the result from the Auto_Fix statement to confirm that the correct link is in fact made. Avoid using Auto_Fix in security-sensitive situations.
When using Auto_Fix, you must specify user and password; login must be NULL. user must be a valid user in the current database.
Value: Report
Description: Lists the users and corresponding security identifiers (SID) in the current database that are not linked to any login.
user, login, and password must be NULL or not specified.
Value: Update_One
Description: Links the specified user in the current database to login. login must already exist. user and login must be specified. password must be NULL or not specified.
UserNamePattern:
It is the name of a SQL Server user in the current database.
LoginName:
It is the name of a SQL Server login.
Password:
It is the password assigned to a new SQL Server login created by Auto_Fix. If a matching login already exists, the user and login are mapped and password is ignored. If a matching login does not exist, sp_change_users_login creates a new SQL Server login and assigns password as the password for the new login.
How to test the scenario?
1.
Add a login to the master database, and specify the default database as Northwind:
Use master go sp_addlogin 'test', 'password', 'Northwind'
2.
Grant access to the user you just created:
Use Northwind go sp_grantdbaccess 'test'
3.
Backup the database.
BACKUP DATABASE Northwind TO DISK = 'C:\MSSQL\BACKUP\Northwind.bak'
4.
Restore the database to a different SQL Server server:
RESTORE DATABASE Northwind FROM DISK = 'C:\MSSQL\BACKUP\Northwind.bak'
The restored database contains a user named "test" without a corresponding login, which results in "test" being orphaned.
5.
Now, to detect orphaned users, run this code:
Use Northwind go sp_change_users_login 'report'
The output lists all the logins, which have a mismatch between the entries in the sysusers system table, of the Northwind database, and the sysxlogins system table in the master database.
To re-link the user:
Use Northwind go sp_change_users_login 'update_one', 'test', 'test'
Although the terms login and user are often used interchangeably, they are very different. A login is used for user authentication and a database user account is used for database access and permissions validation. Logins are associated to users by the security identifier (SID). A login is required for access to the SQL Server server. The process of verifying that a particular login is valid is called "authentication". This login must be associated to a SQL Server database user. You use the user account to control activities performed in the database. If no user account exists in a database for a specific login, the user that is using that login cannot access the database even though the user may be able to connect to the SQL Server server. The single exception to this situation is when the database contains the "guest" user account. A login that does not have an associated user account is mapped to the guest user. Conversely, if a database user exists but there is no login associated, the user is not able to log into SQL Server server.
Purpose:
The sp_change_users_login procedure has a specific purpose. It’s used to identify and correct users within a database which do not have a corresponding logins.
Scenario:
When a database is restored to a different server it contains a set of users and permissions but there may not be any corresponding logins or the logins may not be associated with the same users. A mismatch may occur between the security identification numbers (SIDs) of the logins in the master database and the users in the user database. An example of when this would happen is when you are restoring a database from Production to QA.
Syntax:
sp_change_users_login [ @Action = ] 'action' [ , [ @UserNamePattern = ] 'user' ] [ , [ @LoginName = ] 'login' ] [ , [ @Password = ] 'password' ]
Action: Describes the action to be performed.
Can be one of these values:
Value: Auto_Fix
Description: Links a user entry in the sysusers table in the current database to a login of the same name in sysxlogins. You should check the result from the Auto_Fix statement to confirm that the correct link is in fact made. Avoid using Auto_Fix in security-sensitive situations.
When using Auto_Fix, you must specify user and password; login must be NULL. user must be a valid user in the current database.
Value: Report
Description: Lists the users and corresponding security identifiers (SID) in the current database that are not linked to any login.
user, login, and password must be NULL or not specified.
Value: Update_One
Description: Links the specified user in the current database to login. login must already exist. user and login must be specified. password must be NULL or not specified.
UserNamePattern:
It is the name of a SQL Server user in the current database.
LoginName:
It is the name of a SQL Server login.
Password:
It is the password assigned to a new SQL Server login created by Auto_Fix. If a matching login already exists, the user and login are mapped and password is ignored. If a matching login does not exist, sp_change_users_login creates a new SQL Server login and assigns password as the password for the new login.
How to test the scenario?
1.
Add a login to the master database, and specify the default database as Northwind:
Use master go sp_addlogin 'test', 'password', 'Northwind'
2.
Grant access to the user you just created:
Use Northwind go sp_grantdbaccess 'test'
3.
Backup the database.
BACKUP DATABASE Northwind TO DISK = 'C:\MSSQL\BACKUP\Northwind.bak'
4.
Restore the database to a different SQL Server server:
RESTORE DATABASE Northwind FROM DISK = 'C:\MSSQL\BACKUP\Northwind.bak'
The restored database contains a user named "test" without a corresponding login, which results in "test" being orphaned.
5.
Now, to detect orphaned users, run this code:
Use Northwind go sp_change_users_login 'report'
The output lists all the logins, which have a mismatch between the entries in the sysusers system table, of the Northwind database, and the sysxlogins system table in the master database.
To re-link the user:
Use Northwind go sp_change_users_login 'update_one', 'test', 'test'
Tuesday, April 22, 2008
SQL Basics (DDL,DML,DCL,TCL,SQL Keywords, SQL Syntax)
SQL - Structured Query Language
INTRODUCTION
SQL is divided into the following:
1) Data Definition Language (DDL)
2) Data Manipulation Language (DML)
3) Data Retrieval Language (DRL)
4) Transaction Control Language (TCL)
5) Data Control Language (DCL)
DDL -- create, alter, drop, truncate, rename
DML -- insert, update, delete
DRL -- select
TCL -- commit, rollback, savepoint
DCL -- grant, revoke
Create Table Syntax:
Create table table_name (col1 datatype1, col2 datatype2 …coln datatypen)
Insert Syntax:
Case 1: insert into table_name values (value1, value2, value3 …. Valuen)
Case 2:insert into table_name(col1, col2, col3 … Coln) values (value1, value2, value3.. Valuen)
Select Syntax:
Select * from table_name -- here * indicates all columns
or
Select col1, col2, … coln from table_name
Update Syntax:
Update table_name set col1 = value1, col2 = value2 where condition
Delete Syntax:
Delete from table_name where condition
Truncate Syntax:
truncate table table_name
Drop Syntax:
Drop table table_name
Rename Syntax:
rename old_table_name to new_table_name
CONDITIONAL SELECTIONS AND OPERATORS
We have two clauses used in this
1. Where
2. Order by
1. Using Where:
select * from table_name where condition
The following are the different types of operators used in where clause:
a. Arithmetic operators
b. Comparison operators
c. Logical operators
Arithmetic operators -- highest precedence
+, -, *, /
Comparison operators
(1) =, !=, >, <, >=, <=, <>
(2) between, not between
(3) in, not in
(4) null, not null
(5) like
Logical operators
(1) And
(2) Or -- lowest precedence
(3) not
Using NULL
This will gives the output based on the null values in the specified column.
Syntax:
Select * from table_name where col is null
2. Using Order by:
This will be used to ordering the columns data (ascending or descending).
Syntax:
Select * from table_name order by col desc
By default database server will use ascending order.
If you want output in descending order you have to use desc keyword after the column.
SUB QUERIES
-- Nesting of queries, one within the other is termed as a subquery.
-- A statement containing a subquery is called a parent query.
-- Subqueries are used to retrieve data from tables that depend on the values in the table itself.
Types
1. Single row subqueries
2. Multi row subqueries
3. Multiple subqueries
4. Correlated subqueries
Single Row Sub Queries
In single row subquery, it will return one value.
Ex: select * from emp where sal > (select sal from emp where empno = 7566)
MultiRow Sub Queries
In multi row subquery, it will return more than one value. In such cases we should include operators like any, all, in or not in between the comparision operator and the subquery.
Ex:
select * from emp where sal > any (select sal from emp where sal between 2500 and 4000)
select * from emp where sal > all (select sal from emp where sal between 2500 and 4000)
Multiple Sub Queries
There is no limit on the number of subqueries included in a where clause. It allows nesting of a query within a subquery.
Ex:
select * from emp where sal = (select max(sal) from emp where sal < (select max(sal) from emp)) Correlated Sub Queries
A subquery is evaluated once for the entire parent statement where as a correlated subquery is evaluated once for every row processed by the parent statement.
Ex:
select distinct deptno from emp e where 5 <= (select count(ename) from emp where e.deptno = deptno) Using Exists:
Suppose we want to display the department numbers which has more than 4 employees,
select deptno,ename from emp e1 where exists (select * from emp e2 where e1.deptno=e2.deptno group by e2.deptno having count(e2.ename) > 4) order by deptno,ename
SET OPERATORS
Types
-- Union
-- Union all
-- Intersect
-- Minus
UNION
This will combine the records of multiple tables having the same structure.
Ex:
select * from student1 union select * from student2
UNION ALL
This will combine the records of multiple tables having the same structure but including duplicates.
Ex:
select * from student1 union all select * from student2
INTERSECT
This will give the common records of multiple tables having the same structure.
Ex:
select * from student1 intersect select * from student2
MINUS
This will give the records of a table whose records are not in other tables having the same structure.
Ex:
select * from student1 minus select * from student2
Tags: SQL Basics, SQL Introduction, DDL, DML, DRL, DCL, TCL, Database Tables, SQL Keywords
INTRODUCTION
SQL is divided into the following:
1) Data Definition Language (DDL)
2) Data Manipulation Language (DML)
3) Data Retrieval Language (DRL)
4) Transaction Control Language (TCL)
5) Data Control Language (DCL)
DDL -- create, alter, drop, truncate, rename
DML -- insert, update, delete
DRL -- select
TCL -- commit, rollback, savepoint
DCL -- grant, revoke
Create Table Syntax:
Create table table_name (col1 datatype1, col2 datatype2 …coln datatypen)
Insert Syntax:
Case 1: insert into table_name values (value1, value2, value3 …. Valuen)
Case 2:insert into table_name(col1, col2, col3 … Coln) values (value1, value2, value3.. Valuen)
Select Syntax:
Select * from table_name -- here * indicates all columns
or
Select col1, col2, … coln from table_name
Update Syntax:
Update table_name set col1 = value1, col2 = value2 where condition
Delete Syntax:
Delete from table_name where condition
Truncate Syntax:
truncate table table_name
Drop Syntax:
Drop table table_name
Rename Syntax:
rename old_table_name to new_table_name
CONDITIONAL SELECTIONS AND OPERATORS
We have two clauses used in this
1. Where
2. Order by
1. Using Where:
select * from table_name where condition
The following are the different types of operators used in where clause:
a. Arithmetic operators
b. Comparison operators
c. Logical operators
Arithmetic operators -- highest precedence
+, -, *, /
Comparison operators
(1) =, !=, >, <, >=, <=, <>
(2) between, not between
(3) in, not in
(4) null, not null
(5) like
Logical operators
(1) And
(2) Or -- lowest precedence
(3) not
Using NULL
This will gives the output based on the null values in the specified column.
Syntax:
Select * from table_name where col is null
2. Using Order by:
This will be used to ordering the columns data (ascending or descending).
Syntax:
Select * from table_name order by col desc
By default database server will use ascending order.
If you want output in descending order you have to use desc keyword after the column.
SUB QUERIES
-- Nesting of queries, one within the other is termed as a subquery.
-- A statement containing a subquery is called a parent query.
-- Subqueries are used to retrieve data from tables that depend on the values in the table itself.
Types
1. Single row subqueries
2. Multi row subqueries
3. Multiple subqueries
4. Correlated subqueries
Single Row Sub Queries
In single row subquery, it will return one value.
Ex: select * from emp where sal > (select sal from emp where empno = 7566)
MultiRow Sub Queries
In multi row subquery, it will return more than one value. In such cases we should include operators like any, all, in or not in between the comparision operator and the subquery.
Ex:
select * from emp where sal > any (select sal from emp where sal between 2500 and 4000)
select * from emp where sal > all (select sal from emp where sal between 2500 and 4000)
Multiple Sub Queries
There is no limit on the number of subqueries included in a where clause. It allows nesting of a query within a subquery.
Ex:
select * from emp where sal = (select max(sal) from emp where sal < (select max(sal) from emp)) Correlated Sub Queries
A subquery is evaluated once for the entire parent statement where as a correlated subquery is evaluated once for every row processed by the parent statement.
Ex:
select distinct deptno from emp e where 5 <= (select count(ename) from emp where e.deptno = deptno) Using Exists:
Suppose we want to display the department numbers which has more than 4 employees,
select deptno,ename from emp e1 where exists (select * from emp e2 where e1.deptno=e2.deptno group by e2.deptno having count(e2.ename) > 4) order by deptno,ename
SET OPERATORS
Types
-- Union
-- Union all
-- Intersect
-- Minus
UNION
This will combine the records of multiple tables having the same structure.
Ex:
select * from student1 union select * from student2
UNION ALL
This will combine the records of multiple tables having the same structure but including duplicates.
Ex:
select * from student1 union all select * from student2
INTERSECT
This will give the common records of multiple tables having the same structure.
Ex:
select * from student1 intersect select * from student2
MINUS
This will give the records of a table whose records are not in other tables having the same structure.
Ex:
select * from student1 minus select * from student2
Tags: SQL Basics, SQL Introduction, DDL, DML, DRL, DCL, TCL, Database Tables, SQL Keywords
Subscribe to:
Posts (Atom)