Whilder S. Mendonça | Blog

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

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

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

 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

See this article: Remove duplicate rows from a SQL Server table by using a script

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

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

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

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

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'

 QUERY:  SELECT DATEADD(month, DATEDIFF(month, -1, getdate()) - 2, 0) as Primeirodiadomes,     DATEADD(ss, -1, DATEADD(month, DATEDIFF(month, 0, getdate()), 0)) as Ultimodiadomes RESULT :