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...

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 TableCurso...

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.

Visual Studio 2022 - Run-time Exception System.BadImageFormatException

I had this problem in visual studio 2022 after taking an application that was originally using the .net 35 framework and change it to .net 4.8. After this change, the application compiles without errors, but throws this error at runtime: System.BadImageFormatException was unhandled Message: An unhandled exception of type 'System.BadImageFormatException' occurred in Unknown Module. Additional information: Could not load file or assembly 'TimersXP.exe' or one of its dependencies. This assembly is built by a runtime newer than the currently loaded runtime and cannot be loaded. SOLUTION: Change the APP.CONFIG file, leaving the <STARTUP> section with the 4.0 framework only. After that, I did a CLEAR on the project and a BUILD. And the app.config has been changed to: BEFORE: <startup>     <supportedRuntime version="v2.0.50727"/>     <supportedRuntime version="v4.0"/> </startup> LATER: I removed the line <supportedRuntime ve...

How to add a program in windows server 2012 startup?

Below is a procedure that works on Windows 10, but from what I've seen it works on Server 2012 as well. Step 1. Press “WinKey + R” to open “Run”; Step 2. Type “%AppData%\Microsoft\Windows\Start Menu\Programs\Startup” (without quotes) and press Enter; Step 3. A Windows Explorer window will open. It is in this folder that you must copy a shortcut to the application that you want to start with Windows; Step 4. To create shortcuts, right-click on the application, “Send to” and choose “Desktop (create shortcut)”. Finally, copy the shortcut you just created into the “Startup” folder.

Speeding up SQL Server Management Studio Startup

SQL Server Management Studio typically takes a minute or so to start on my machine. I just figured it was a slow app, but then I saw it load much faster on another machine. Searching for a solution led me to Microsoft SQL Server Management Studio is too Slow (archive.org) , which nicely solved the problem. Now it starts up in five seconds. As the link may be broken in the future, I will transcribe the solution below: In a corporate environment it is not uncommon for Microsoft SQL Server Management Studio (SSMS) for SQL Server to take over 45 seconds to start as well as lags and delays when opening various windows and dialog boxes from within the application. If you are experiencing this issue, then a quick fix is to add an entry in your HOSTS. file that points crl.microsoft.com to 127.0.0.1 Exit SSMS Press the keys [Win] + [R] Enter the following.. notepad %systemroot%\system32\drivers\etc\hosts. Append the following.. 127.0.0.1    crl.microsoft.com Save the file. Start SSMS (...

Operating with #TEMP tables in SQL SERVER

 Temporary tables are created in the TempDB database and can be classified into Local and Global: Local Temporary Tables are created with the prefix "#" and have restricted visibility for the connection responsible for their creation; other connections do not "see" the table. Global Temporary Tables are created with the prefix "##" and are visible by all connections Syntax: CREATE TABLE #Tablename      (       fieldnameA varchar(80),       fieldnameB money      ) Temporary tables are widely used when we need to gather several records from several tables in a single selection and display them in any application (eg Delphi, Visual FoxPro, Visual Studio, ASP.NET, etc.) Check if a temp table exists and delete if it exists before creating a temp table IF OBJECT_ID('tempdb..#Tablename') IS NOT NULL DROP TABLE #Results GO CREATE TABLE #TableName ( test CHAR(3)) GO SELECT * from #TableName GO

KEY - KEY - Generic activation keys - 180 DAYS - Windows Key - Licenses

Generic installation keys Commonly used in response files - ANSWER FILE All examples provided are installation keys only;  they will not activate your installed version of Windows.  These are the default keys that are entered if you choose to skip entering a product key during the installation process. The product keys listed in this section can be used with any of the answer files and scripted examples.  They are locked in the Microsoft clearing center and therefore cannot be used to activate any system.  They provide several days for you to complete the activation process.  The keys provided are not architecture dependent. It is absolutely essential that you use the correct type of key, which is a function of the edit type.  Choose the key corresponding to the edition of Windows you want to install . Windows Edition Product Key Windows Vista Starter X9PYV-YBQRV-9BXWV-TQDMK-QDWK4 Windows Vista Home Basic RCG7P-TX42D-HM8FM-TCFCW-3V4VD Wind...

Microsoft SQL Server Internal Database Versions and Compatibility Levels

A database created by a more recent version of   Microsoft SQL Server   cannot be attached or restored to an earlier version. This restriction is there because an older version cannot know about file format changes that were introduced in the newer release. If you attempt to attach a database to an earlier version, you will get SQL Server error  948  with the internal version numbers listed in the error message text: -- Attaching SQL2019 database to SQL2017 CREATE DATABASE Database2019 ON ( FILENAME = N'C:\SqlServerData\Database2019.mdf' ), ( FILENAME = N'C:\SqlServerData\Database2019_log.ldf' ) FOR ATTACH ; Msg 1813, Level 16, State 2, Line 1 Could not open new database 'Database2019'. CREATE DATABASE is aborted. Msg 948 , Level 20, State 1, Line 1 The database 'Database2019' cannot be opened because it is version 904 . This server supports version 869 and earlier. A downgrade path is not supported. The internal database versions for SQL Serve...

Always selecting the last line added in the listbox using the visual foxpro.

  Add the code below in the ADDITEM method of the listbox: LPARAMETERS citem IF _screen.Visible = .T. THIS.SETFOCUS() KEYBOARD '{DNARROW}' THIS.SELECTED(THIS.LISTCOUNT) = .T. ENDIF

Incorrect syntax near 'GO' - How to Solve???

Incorrect syntax near 'go' in SQL Server Management Studio SQL Server Management Studio cannot handle some unprintable characters. Check the newline characters, you probably have Linux (LF) or Mac (CR) style instead of Windows style (CR and LF).  You can check with any advanced text editor, for example Notepad++  To solve, in notepad++, select the entire document, go to EDIT > END OF LINE CONVERSION > CONVERT TO WINDOWS Don't forget to save the file with the changes. That's all... Now it will work!!!!

SQLCMD - Run Transact-SQL Script Files from MS-DOS Prompt

Create a script file To create a simple Transact-SQL script file using Notepad, follow these steps: Click   Start  , select   All Programs  , go to   Accessories ,   and then click   Notepad  . Copy and paste the following Transact-SQL code into Notepad: Save the file as   myScript.sql   on drive C. USE AdventureWorks2012; GO SELECT p.FirstName + ' ' + p.LastName AS 'Employee Name', a.AddressLine1, a.AddressLine2 , a.City, a.PostalCode FROM Person.Person AS p INNER JOIN HumanResources.Employee AS e ON p.BusinessEntityID = e.BusinessEntityID INNER JOIN Person.BusinessEntityAddress bea ON bea.BusinessEntityID = e.BusinessEntityID INNER JOIN Person.Address AS a ON a.AddressID = bea.AddressID; GO Run the script file Open a command prompt window. In the Command Prompt window, type:   sqlcmd -S myServer\InstanceName -i C:\myScript.sql Pr...

How to TRUNCATE all tables in Microsoft SQL SERVER instance

When we are developing some software and we want to clean the SQL database to simulate a new installation, it would be interesting to have a routine that would erase the contents of all tables. This script performs this task, it deletes the contents of all the tables of the instance that is open in SQL Server Management Studio.  ATTENTION: The TRUNCATE command erases all the contents of the tables and there is no way to restore, only with backup.  Use this script very consciously and in test environments where you really want to do a general clean up in your database.  I am not responsible for the use of this in your bank.  Created by my friend  Airton Junior  , it follows the killer script...  USE MASTER; --TABELA TEMPORÁRIA PARA ARMAZENAR OS BANCOS SEPARADOS PARA LIMPEZA CREATE TABLE #TEMPORARIA (NOMEBANCO VARCHAR(255)); GO --SEPARATION OF DATABASES INSERT INTO #TEMPORARIA (NOMEBANCO) SELECT name FROM master.sys.databases WHERE name LIK...

How to search for commands that have already been executed on the Microsoft SQL Server instance.

Hi, With the query below it is possible to view commands that have already been executed in the SQL SERVER instance. SELECT DMExQryStats.last_execution_time AS [Executed At], DMExSQLTxt.text AS [Query] FROM sys.dm_exec_query_stats AS DMExQryStats CROSS APPLY sys.dm_exec_sql_text(DMExQryStats.sql_handle) AS DMExSQLTxt ORDER BY DMExQryStats.last_execution_time DESC If you want to check for especificios commands, add the WHERE clause to search the command content. Example: If you want to search all UPDATES performed in your database, add this clause WHERE WHERE lower(substring(DMExSQLTxt.text,1,6))='update'