Skip to main content

Posts

How to Restore a SQL Server Database from a Backup File

If the restored database has the name '@DatabaseName', you'll need to rename it manually to 'Autocom3_Filial_Movimento_Mensal_2023_03'. You can do this by right-clicking on the database name in SQL Server Management Studio and selecting Rename, or by using the following SQL command:Restoring a database from a backup file is a common task for database administrators. In this article, we'll walk through the steps to restore a SQL Server 2008 R2 database from a backup file using T-SQL. This is a straightforward process, but it's important to do it correctly to avoid errors and ensure that the data is fully recovered. Prerequisites SQL Server 2008 R2 installed A backup file of the database (.bkp) Steps to Restore a Database 1. Define Variables for Backup and Database Names We need to specify the path to the backup file and the name we want to give to the restored database.  -- Set the backup file path DECLARE @BackupFile NVARCHAR(500) = 'C:\autocom3_temp\aut
Recent posts

Count the Number of Rows in All Tables in a SQL Server Database

If you're working with SQL Server databases, you might often find yourself needing to quickly get the count of rows for each table within a database. This can be helpful for various tasks such as data analysis, debugging, or performance tuning. In this article, we will cover a SQL script that will help you to fetch the number of rows in all the tables in a SQL Server database.  -- Switch to your database USE YourDatabaseName; -- Replace 'YourDatabaseName' with the name of your database -- Declare variables DECLARE @TableName AS VARCHAR(255) DECLARE @SQL AS NVARCHAR(MAX) -- Create a temporary table to store the results CREATE TABLE #TableCounts (     TableName VARCHAR(255),     RecordCount INT ) -- Create a cursor to loop through all the tables DECLARE TableCursor CURSOR FOR  SELECT table_name FROM information_schema.tables WHERE table_type = 'BASE TABLE' -- Open the cursor and fetch the first table into @TableName OPEN TableCursor FETCH NEXT FROM TableCursor INTO @T

Master SQL Server: 7 Beginner-Friendly Lessons by Professor Airton

  Learn SQL Server in a simple and practical way with the 7 amazing lessons taught by the talented professor Airton, who works at our company Autocom3. And the best part: the content is completely free and you have permission to share it freely! If you're just starting your journey in the world of SQL Server, you can't miss this opportunity to learn from an expert. The lessons have been carefully prepared to meet the needs of beginners and cover everything from basic concepts to more advanced topics. Here are some of the topics covered in the lessons: Introduction to SQL Server: Get to know the environment and essential tools. Fundamental SQL commands: Learn the key commands for data manipulation. Table creation and relationships: Understand how to create and manage tables in SQL Server. Advanced querying: Explore advanced query features to obtain accurate information. Functions and stored procedures: Use functions and stored procedures to automate tasks and improve performance

How to change the recovery model from FULL to SIMPLE in all databases in sql server

Yes, it is possible to use a SQL query to change the recovery model of all databases on a SQL Server server to "SIMPLE". To do this, you can use the following query: EXEC sp_msforeachdb 'USE [?]; IF DATABASEPROPERTYEX(''?'', ''recovery'') = ''FULL'' ALTER DATABASE [?] SET RECOVERY SIMPLE;' This query uses the SQL Server function sp_msforeachdb to iterate through all databases on the server and changes the recovery model of all databases that use the "FULL" model to "SIMPLE". Note that this query should be run with care and you should back up your databases before running this query as changing your recovery model may affect your ability to recover data in case of server failures. I hope this helps!

How to check all databases that are using the recovery model as "FULL" in the SQL SERVER database.

To verify all databases on a SQL Server server that are using the "FULL" transaction log recovery model and have a pending full transaction log backup, you can use the following query: SELECT name AS 'Nome do banco de dados' FROM sys.databases WHERE recovery_model_desc = 'FULL' AND name NOT IN ('master', 'tempdb', 'model', 'msdb') AND database_id NOT IN (     SELECT DISTINCT database_id FROM msdb.dbo.backupset     WHERE type = 'L' AND is_copy_only = 0 AND backup_finish_date IS NULL ) order by name and to check the ones that are simple, just change it to SIMPLE SELECT name AS 'Nome do banco de dados' FROM sys.databases WHERE recovery_model_desc = 'SIMPLE' AND name NOT IN ('master', 'tempdb', 'model', 'msdb') AND database_id NOT IN (     SELECT DISTINCT database_id FROM msdb.dbo.backupset     WHERE type = 'L' AND is_copy_only = 0 AND backup_finish_date IS NULL ) order

How to Create LDF from MDF

If you only have the MDF and it is healthy, you can attach it to your SQL Server. SQL Server will create the database from the .MDF and will create the .LDF for you. Try the following: USE MASTER EXEC sp_attach_single_file_db @dbname = 'YourBank', @physname = 'MDF location'   Microsoft recommends not using this procedure anymore, as it will possibly be discontinued. You can get the same result through CREATE DATABASE with the FOR ATTACH parameter.   USE MASTER CREATE DATABASE MeuBanco ON (NAME='FileLogicalName', FILENAME='FileLocation') FOR ATTACH_REBUILD_LOG IMPORTANT: The wizard does not work, only via script.