title
Creating altering and dropping a database - Part 2

description
In this video we will learn 1. Creating, altering and dropping a database 2. Putting a database in single user mode Text version of the video http://csharp-video-tutorials.blogspot.co.uk/2012/08/creating-altering-and-dropping-database.html Healthy diet is very important both for the body and mind. If you like Aarvi Kitchen recipes, please support by sharing, subscribing and liking our YouTube channel. Hope you can help. https://www.youtube.com/channel/UC7sEwIXM_YfAMyonQCrGfWA/?sub_confirmation=1 Slides http://csharp-video-tutorials.blogspot.com/2013/08/part-2-creating-altering-and-dropping.html All SQL Server Text Articles http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html All SQL Server Slides http://csharp-video-tutorials.blogspot.com/p/sql-server.html All Dot Net and SQL Server Tutorials in English https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd All Dot Net and SQL Server Tutorials in Arabic https://www.youtube.com/c/KudvenkatArabic/playlists

detail
{'title': 'Creating altering and dropping a database - Part 2', 'heatmap': [{'end': 245.569, 'start': 217.218, 'weight': 1}], 'summary': "Tutorial series covers sql server database management, including creating and managing databases using sql server management studio, renaming databases and stored procedures, and dropping databases safely using 'alter database' command and 'set single user with rollback immediate' method.", 'chapters': [{'end': 292.332, 'segs': [{'end': 48.173, 'src': 'embed', 'start': 22.667, 'weight': 0, 'content': [{'end': 27.548, 'text': 'Now remember, in SQL Server, most of the things can be done in two ways.', 'start': 22.667, 'duration': 4.881}, {'end': 32.949, 'text': 'One is graphically using SQL Server Management Studio or using a query.', 'start': 28.148, 'duration': 4.801}, {'end': 38.711, 'text': 'Now let us see how to create a database graphically using SQL Server Management Studio.', 'start': 33.349, 'duration': 5.362}, {'end': 42.552, 'text': "Let's flip back to SQL Server Management Studio.", 'start': 39.271, 'duration': 3.281}, {'end': 48.173, 'text': 'Now within the Object Explorer window, you see a databases folder.', 'start': 43.472, 'duration': 4.701}], 'summary': 'In sql server, tasks can be done graphically or via query. creating a database graphically is demonstrated using sql server management studio.', 'duration': 25.506, 'max_score': 22.667, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/TuxuHHacIWU/pics/TuxuHHacIWU22667.jpg'}, {'end': 115.223, 'src': 'embed', 'start': 68.078, 'weight': 1, 'content': [{'end': 76.461, 'text': "So what's going to happen now? Behind the scenes, SQL Server Management Studio is going to issue a query to have this database created for us.", 'start': 68.078, 'duration': 8.383}, {'end': 83.578, 'text': 'So if you expand the databases folder you should see sample one database created there.', 'start': 78.151, 'duration': 5.427}, {'end': 87.043, 'text': 'Now we can also create a database using a query.', 'start': 84.46, 'duration': 2.583}, {'end': 90.327, 'text': 'Now let us see how to create a database using a query.', 'start': 87.323, 'duration': 3.004}, {'end': 95.739, 'text': 'Now database is also one of the types of SQL Server objects.', 'start': 91.249, 'duration': 4.49}, {'end': 100.66, 'text': 'Just like database, you can create tables, views, triggers, store procedures, etc.', 'start': 95.799, 'duration': 4.861}, {'end': 102.7, 'text': ", which we'll be talking about in a later session.", 'start': 100.66, 'duration': 2.04}, {'end': 109.962, 'text': "But remember, irrespective of the type of the object that you're creating in SQL Server, you use the CREATE statement.", 'start': 103.101, 'duration': 6.861}, {'end': 111.583, 'text': 'So CREATE.', 'start': 110.762, 'duration': 0.821}, {'end': 115.223, 'text': 'Now, if I want to create a database, I will say database.', 'start': 112.223, 'duration': 3}], 'summary': 'Sql server management studio creates a database, which is one of the types of sql server objects. the create statement is used to create any type of object in sql server.', 'duration': 47.145, 'max_score': 68.078, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/TuxuHHacIWU/pics/TuxuHHacIWU68078.jpg'}, {'end': 217.198, 'src': 'embed', 'start': 193.545, 'weight': 4, 'content': [{'end': 200.427, 'text': 'These databases are provided by SQL Server installation itself and these are required for proper functioning of the SQL Server.', 'start': 193.545, 'duration': 6.882}, {'end': 201.428, 'text': 'So they should be there.', 'start': 200.447, 'duration': 0.981}, {'end': 205.089, 'text': 'So system databases and user-defined databases.', 'start': 202.288, 'duration': 2.801}, {'end': 207.61, 'text': 'User-defined databases are the databases that we create.', 'start': 205.189, 'duration': 2.421}, {'end': 217.198, 'text': 'Now, for every database that we have, whether it is a system database or a user-defined database, there are two files that gets generated.', 'start': 209.056, 'duration': 8.142}], 'summary': 'Sql server requires system and user-defined databases, each generating two files.', 'duration': 23.653, 'max_score': 193.545, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/TuxuHHacIWU/pics/TuxuHHacIWU193545.jpg'}, {'end': 245.569, 'src': 'heatmap', 'start': 217.218, 'weight': 1, 'content': [{'end': 226.761, 'text': "Now, let's right-click on one of the user-defined databases, go into Properties, and in the Files tab, you will have a path.", 'start': 217.218, 'duration': 9.543}, {'end': 229.262, 'text': 'So there is a path here.', 'start': 228.141, 'duration': 1.121}, {'end': 237.144, 'text': 'Let me copy that path, paste that in the Run window, and press Enter.', 'start': 229.642, 'duration': 7.502}, {'end': 245.569, 'text': 'Now if you look at this, you see there is sample1.mdf and sample1.log.ldf.', 'start': 237.844, 'duration': 7.725}], 'summary': 'Demonstrating the process to find database files, resulting in sample1.mdf and sample1.log.ldf.', 'duration': 28.351, 'max_score': 217.218, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/TuxuHHacIWU/pics/TuxuHHacIWU217218.jpg'}, {'end': 292.332, 'src': 'embed', 'start': 268.032, 'weight': 6, 'content': [{'end': 275.818, 'text': 'whereas LDF is a transaction log file, and we use this file basically to recover the database in case if there are any problems.', 'start': 268.032, 'duration': 7.786}, {'end': 284.285, 'text': 'We will be talking about these two files in a great detail in a later session, but for now understand that whenever we create a database,', 'start': 276.879, 'duration': 7.406}, {'end': 286.607, 'text': 'these two files get generated.', 'start': 284.285, 'duration': 2.322}, {'end': 288.108, 'text': 'mdf and .', 'start': 286.607, 'duration': 1.501}, {'end': 292.332, 'text': 'ldf mdf, master data file, ldf, log data file.', 'start': 288.108, 'duration': 4.224}], 'summary': 'Ldf is used to recover the database, alongside mdf, when creating a database.', 'duration': 24.3, 'max_score': 268.032, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/TuxuHHacIWU/pics/TuxuHHacIWU268032.jpg'}], 'start': 0.209, 'title': 'Sql server database management', 'summary': 'Covers how to create and manage databases in sql server using sql server management studio, highlighting methods like graphical and query-based creation, and emphasizing the generation of system and user-defined databases with essential .mdf and .ldf files.', 'chapters': [{'end': 67.678, 'start': 0.209, 'title': 'Creating and managing databases in sql server', 'summary': "Discusses how to create a database in sql server using sql server management studio, highlighting the two methods, graphical and query-based, and the process of creating a new database named 'sample 1'.", 'duration': 67.469, 'highlights': ['Creating a database can be done using SQL Server Management Studio or a query in SQL Server. The chapter emphasizes that in SQL Server, creating a database can be done through SQL Server Management Studio or using a query.', "Demonstrating the process of creating a new database named 'Sample 1' in SQL Server Management Studio. The session walks through the process of creating a new database named 'Sample 1' using SQL Server Management Studio, showcasing the practical application of the discussed concepts."]}, {'end': 168.299, 'start': 68.078, 'title': 'Creating sql server database', 'summary': 'Covers creating databases in sql server using sql server management studio and querying the creation of databases, emphasizing the use of the create statement and specifying the type of object to be created.', 'duration': 100.221, 'highlights': ['SQL Server Management Studio issues a query to create a database for us, resulting in the creation of the sample one database.', 'The process of creating a database using a query is demonstrated, with the creation of a database named sample2 using the CREATE DATABASE statement.', 'The importance of the CREATE statement in creating various objects in SQL Server, such as tables, views, triggers, and store procedures, is highlighted.', 'The method to refresh the Databases folder to view the newly created database, sample 2, is explained.', 'The steps to reopen the Object Explorer if it has been closed are outlined, demonstrating how to access it through the View menu.', 'The chapter also briefly covers creating databases graphically using the designer in SQL Server Management Studio.']}, {'end': 292.332, 'start': 168.599, 'title': 'Database files and types', 'summary': 'Discusses the generation of system and user-defined databases in sql server, emphasizing the presence of two files per database, namely .mdf for data and .ldf for transaction logs, which are essential for database recovery and proper functioning.', 'duration': 123.733, 'highlights': ['System databases like Master, Model, MSDB, and TempDB are provided by SQL Server installation and are essential for its proper functioning. Mentioned the system databases Master, Model, MSDB, and TempDB as essential for SQL Server, emphasizing their importance for proper functioning.', 'User-defined databases are the ones created by users, and for every database, two files are generated, namely .mdf for data and .ldf for transaction logs. Explained the concept of user-defined databases, highlighting the generation of two essential files, .mdf for data and .ldf for transaction logs, for each database.', 'The .mdf file contains the actual master data, while the .ldf file is used for recovering the database in case of any problems. Described the purpose of .mdf and .ldf files, stating that .mdf contains actual master data while .ldf is used for database recovery.']}], 'duration': 292.123, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/TuxuHHacIWU/pics/TuxuHHacIWU209.jpg', 'highlights': ['Creating a database can be done using SQL Server Management Studio or a query in SQL Server.', "Demonstrating the process of creating a new database named 'Sample 1' in SQL Server Management Studio.", 'The process of creating a database using a query is demonstrated, with the creation of a database named sample2 using the CREATE DATABASE statement.', 'The importance of the CREATE statement in creating various objects in SQL Server, such as tables, views, triggers, and store procedures, is highlighted.', 'System databases like Master, Model, MSDB, and TempDB are provided by SQL Server installation and are essential for its proper functioning.', 'User-defined databases are the ones created by users, and for every database, two files are generated, namely .mdf for data and .ldf for transaction logs.', 'The .mdf file contains the actual master data, while the .ldf file is used for recovering the database in case of any problems.']}, {'end': 738.381, 'segs': [{'end': 326.265, 'src': 'embed', 'start': 294.36, 'weight': 0, 'content': [{'end': 296.522, 'text': 'All right, we have seen how to create a database.', 'start': 294.36, 'duration': 2.162}, {'end': 300.786, 'text': "Now, let's see how to rename a database after it's been created.", 'start': 296.782, 'duration': 4.004}, {'end': 303.108, 'text': 'Now, again, to rename the database, there are two ways.', 'start': 300.946, 'duration': 2.162}, {'end': 308.033, 'text': 'One, you can graphically do that using SQL Server Management Studio, or you can write a query.', 'start': 303.249, 'duration': 4.784}, {'end': 316.722, 'text': 'And to graphically do that, just right-click on the database and select Rename, just like how you rename a file within the Windows file system.', 'start': 308.314, 'duration': 8.408}, {'end': 319.423, 'text': 'and then provide your new name there and press Enter.', 'start': 317.162, 'duration': 2.261}, {'end': 321.203, 'text': 'The name will be changed automatically.', 'start': 319.683, 'duration': 1.52}, {'end': 326.265, 'text': 'But on the other hand, let us see how to rename a database using a query.', 'start': 321.483, 'duration': 4.782}], 'summary': 'Learn to rename a database using sql server management studio or a query.', 'duration': 31.905, 'max_score': 294.36, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/TuxuHHacIWU/pics/TuxuHHacIWU294360.jpg'}, {'end': 360.997, 'src': 'embed', 'start': 330.686, 'weight': 1, 'content': [{'end': 333.547, 'text': 'One is to basically use alter database command.', 'start': 330.686, 'duration': 2.861}, {'end': 341.51, 'text': 'So, alter database and then the name of the database, modify name is equal to new database name.', 'start': 333.907, 'duration': 7.603}, {'end': 343.639, 'text': "Alright, let's see how to do that.", 'start': 342.438, 'duration': 1.201}, {'end': 347.964, 'text': "Let's say I want to change the name of sample2 database to sample3.", 'start': 344, 'duration': 3.964}, {'end': 354.23, 'text': 'So alter database and I want to alter sample2 database.', 'start': 348.264, 'duration': 5.966}, {'end': 360.997, 'text': 'And what do I want to alter? I want to alter the name to sample3.', 'start': 356.613, 'duration': 4.384}], 'summary': 'Use alter database command to change database name.', 'duration': 30.311, 'max_score': 330.686, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/TuxuHHacIWU/pics/TuxuHHacIWU330686.jpg'}, {'end': 439.596, 'src': 'embed', 'start': 407.669, 'weight': 2, 'content': [{'end': 414.477, 'text': 'which are basically used for functions like renaming a stored procedure, or if you want to view the text of a stored procedure,', 'start': 407.669, 'duration': 6.808}, {'end': 416.279, 'text': 'you can use sp underscore help text.', 'start': 414.477, 'duration': 1.802}, {'end': 423.407, 'text': 'If you want to find out how many indexes are there on a table, you can basically use a stored procedure called sp underscore help index.', 'start': 416.659, 'duration': 6.748}, {'end': 427.572, 'text': 'There are several system stored procedures that we use for a variety of different tasks.', 'start': 423.707, 'duration': 3.865}, {'end': 430.833, 'text': 'which we will be looking at as we go through this course.', 'start': 428.152, 'duration': 2.681}, {'end': 439.596, 'text': 'But for now, understand that if you want to rename a database, there is a system stored procedure called SP underscore rename DB, which can be used.', 'start': 430.953, 'duration': 8.643}], 'summary': 'System stored procedures used for tasks like renaming and viewing indexes, with examples like sp_helpindex and sp_renamedb.', 'duration': 31.927, 'max_score': 407.669, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/TuxuHHacIWU/pics/TuxuHHacIWU407669.jpg'}, {'end': 580.228, 'src': 'embed', 'start': 554.163, 'weight': 3, 'content': [{'end': 559.107, 'text': 'To drop any object, let it be database, table, view, stored procedure, etc.', 'start': 554.163, 'duration': 4.944}, {'end': 561.029, 'text': ', we basically use the drop statement.', 'start': 559.107, 'duration': 1.922}, {'end': 564.872, 'text': 'Drop What do I want to drop? I want to drop a database, so database.', 'start': 561.369, 'duration': 3.503}, {'end': 568.775, 'text': "And then the name of the database that you want to drop, let's say sample4.", 'start': 565.692, 'duration': 3.083}, {'end': 575.483, 'text': "So when I execute this query, what's going to happen? Sample 4 database will be deleted.", 'start': 571.038, 'duration': 4.445}, {'end': 580.228, 'text': 'And behind the scenes, both the MDF and LDF files will also be deleted.', 'start': 575.723, 'duration': 4.505}], 'summary': "Use the 'drop' statement to delete a database like sample4, including its mdf and ldf files.", 'duration': 26.065, 'max_score': 554.163, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/TuxuHHacIWU/pics/TuxuHHacIWU554163.jpg'}, {'end': 686.878, 'src': 'embed', 'start': 663.653, 'weight': 4, 'content': [{'end': 673.117, 'text': 'So you will have to bring it back to single user mode before you can actually drop that if there are people currently connected to that database that you are trying to delete.', 'start': 663.653, 'duration': 9.464}, {'end': 678.355, 'text': 'And the way we do that is we again use the alter database command.', 'start': 674.934, 'duration': 3.421}, {'end': 686.878, 'text': 'Alter database, the database name that we want to alter and we want to set the database in single user mode with rollback immediate.', 'start': 678.735, 'duration': 8.143}], 'summary': 'To delete a database, switch to single user mode using alter database command.', 'duration': 23.225, 'max_score': 663.653, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/TuxuHHacIWU/pics/TuxuHHacIWU663653.jpg'}], 'start': 294.36, 'title': 'Renaming databases and stored procedures in sql', 'summary': 'Discusses the methods to rename a database in sql, including using sql server management studio or writing a query, emphasizing the alter database command and system stored procedures. it also covers types of stored procedures and system stored procedures for database management tasks, including considerations for dropping a database and the need to set the database to single user mode and rollback immediate in case of active connections.', 'chapters': [{'end': 386.139, 'start': 294.36, 'title': 'Renaming databases in sql', 'summary': 'Discusses two methods to rename a database in sql, including using sql server management studio or writing a query, with emphasis on the alter database command and system stored procedures.', 'duration': 91.779, 'highlights': ['The chapter explains two methods to rename a database in SQL, graphically using SQL Server Management Studio or by writing a query, providing flexibility for users.', 'It emphasizes the use of the alter database command to rename a database, demonstrating the syntax and practical example of altering the database name from sample2 to sample3.', 'Additionally, it mentions another approach to rename a database using system stored procedures, offering alternative methods for database modification.']}, {'end': 738.381, 'start': 386.139, 'title': 'Stored procedures and database management', 'summary': 'Covers the types of stored procedures, system stored procedures for database management tasks like renaming and dropping databases, and the considerations for dropping a database, including the need to set the database to single user mode and rollback immediate in case of active connections.', 'duration': 352.242, 'highlights': ['There are two types of stored procedures: system stored procedures and user defined stored procedures. The transcript explains the two types of stored procedures, system stored procedures and user defined stored procedures.', 'System stored procedures are provided by Microsoft SQL Server for tasks like renaming a stored procedure, viewing the text of a stored procedure, and finding out the number of indexes on a table. The system stored procedures provided by Microsoft SQL Server are described for tasks such as renaming a stored procedure, viewing the text of a stored procedure, and finding out the number of indexes on a table.', 'The process of dropping a database is outlined, including the graphical method and using a query with the drop statement, along with the need to ensure the database is not in use before dropping it. The transcript details the process of dropping a database, including the graphical method and using a query with the drop statement, and emphasizes the need to ensure the database is not in use before dropping it.', 'Considerations for dropping a database are discussed, including the need to put the database in single user mode and use the alter database command with rollback immediate if there are active connections to the database. The considerations for dropping a database are discussed, emphasizing the need to put the database in single user mode and use the alter database command with rollback immediate if there are active connections to the database.']}], 'duration': 444.021, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/TuxuHHacIWU/pics/TuxuHHacIWU294360.jpg', 'highlights': ['The chapter explains two methods to rename a database in SQL, providing flexibility for users.', 'It emphasizes the use of the alter database command to rename a database, demonstrating the syntax and practical example.', 'System stored procedures are provided by Microsoft SQL Server for tasks like renaming a stored procedure and finding out the number of indexes on a table.', 'The process of dropping a database is outlined, including the graphical method and using a query with the drop statement.', 'Considerations for dropping a database are discussed, emphasizing the need to put the database in single user mode and use the alter database command with rollback immediate if there are active connections.']}, {'end': 915.631, 'segs': [{'end': 904.707, 'src': 'embed', 'start': 840.541, 'weight': 0, 'content': [{'end': 842.102, 'text': "And at the same time, I'm trying to drop that.", 'start': 840.541, 'duration': 1.561}, {'end': 847.438, 'text': "look at what's gonna happen cannot drop the database because it's currently in use.", 'start': 842.554, 'duration': 4.884}, {'end': 853.743, 'text': 'okay, so so obviously, if you have to drop a database, that database cannot be in use.', 'start': 847.438, 'duration': 6.305}, {'end': 862.43, 'text': 'otherwise you get an error stating so, okay, so if I drop this now, the database will be dropped, and if you refresh,', 'start': 853.743, 'duration': 8.687}, {'end': 864.792, 'text': 'you should see that the database is gone now.', 'start': 862.43, 'duration': 2.362}, {'end': 866.354, 'text': 'okay, keep that in mind.', 'start': 864.792, 'duration': 1.562}, {'end': 872.083, 'text': "if you want to drop a database that's in use, you can use alter database, database name,", 'start': 866.354, 'duration': 5.729}, {'end': 884.433, 'text': "set single user with rollback immediate and with this rollback immediate option will basically roll back all incomplete transactions and closes any connections to the database that you're trying to drop.", 'start': 872.083, 'duration': 12.35}, {'end': 888.597, 'text': 'and also a very important point to keep in mind system databases cannot be dropped.', 'start': 884.433, 'duration': 4.164}, {'end': 894.021, 'text': "for example, if I try to delete one of the system database, you cannot do that, you'll get an error.", 'start': 888.597, 'duration': 5.424}, {'end': 895.202, 'text': 'you know, drop database.', 'start': 894.021, 'duration': 1.181}, {'end': 899.105, 'text': "let's say, for example, I want to drop the master database.", 'start': 895.202, 'duration': 3.903}, {'end': 904.707, 'text': "And if I execute that, look at that, cannot drop the database master because it's a system database.", 'start': 899.225, 'duration': 5.482}], 'summary': 'Dropping databases, handling in-use, and system database restrictions explained.', 'duration': 64.166, 'max_score': 840.541, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/TuxuHHacIWU/pics/TuxuHHacIWU840541.jpg'}], 'start': 738.381, 'title': 'Dropping a database safely', 'summary': "Discusses the process of dropping a database, emphasizing the use of 'alter database' command with 'set single user with rollback immediate' to safely drop a database in use, while highlighting the inability to drop system databases.", 'chapters': [{'end': 915.631, 'start': 738.381, 'title': 'Dropping a database safely', 'summary': "Discusses the process of dropping a database, emphasizing the use of 'alter database' command with 'set single user with rollback immediate' to safely drop a database in use, while highlighting the inability to drop system databases.", 'duration': 177.25, 'highlights': ["The 'alter database' command with 'set single user with rollback immediate' can be used to safely drop a database in use, rolling back incomplete transactions and closing any connections to the database.", "System databases cannot be dropped, attempting to drop a system database like 'master' results in an error.", 'Attempting to drop a database in use results in an error, emphasizing the need to ensure the database is not in use before attempting to drop it.']}], 'duration': 177.25, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/TuxuHHacIWU/pics/TuxuHHacIWU738381.jpg', 'highlights': ["The 'alter database' command with 'set single user with rollback immediate' can be used to safely drop a database in use, rolling back incomplete transactions and closing any connections to the database.", 'Attempting to drop a database in use results in an error, emphasizing the need to ensure the database is not in use before attempting to drop it.', "System databases cannot be dropped, attempting to drop a system database like 'master' results in an error."]}], 'highlights': ["The 'alter database' command with 'set single user with rollback immediate' can be used to safely drop a database in use, rolling back incomplete transactions and closing any connections to the database.", 'The process of dropping a database is outlined, including the graphical method and using a query with the drop statement.', 'The chapter explains two methods to rename a database in SQL, providing flexibility for users.', 'Creating a database can be done using SQL Server Management Studio or a query in SQL Server.', 'System databases like Master, Model, MSDB, and TempDB are provided by SQL Server installation and are essential for its proper functioning.']}