146

This is likely caused by the restore script adding the WITH NORECOVERY parameter, to make the database ready for a transaction log apply after the restore. The database is now waiting for the latest transaction log file. You can either: Apply the latest transaction log, using RESTORE LOG database_name FROM backup_device WITH RECOVERY; ... or Restore the ...


68

From the docs: Sets certain database behaviors to be compatible with the specified version of SQL Server. ... Compatibility level provides only partial backward compatibility with earlier versions of SQL Server. Use compatibility level as an interim migration aid to work around version differences in the behaviors that are controlled by the ...


63

Use SCOPE_IDENTITY() if you are inserting a single row and want to retrieve the ID that was generated. CREATE TABLE #a(identity_column INT IDENTITY(1,1), x CHAR(1)); INSERT #a(x) VALUES('a'); SELECT SCOPE_IDENTITY(); Result: ---- 1 Use the OUTPUT clause if you are inserting multiple rows and need to retrieve the set of IDs that were generated. INSERT #...


62

When should I use the full recovery model and when should I use the simple recovery model for databases? You should use the full recovery model when you require point-in-time recovery of your database. You should use simple recovery model when you don't need point-in-time recovery of your database, and when the last full or differential backup is ...


62

You should use the option -b in sqlcmd. -b Specifies that sqlcmd exits and returns a DOS ERRORLEVEL value when an error occurs. The value that is returned to the DOS ERRORLEVEL variable is 1 when the SQL Server error message has a severity level greater than 10; otherwise, the value returned is 0 http://msdn.microsoft.com/en-us/library/ms162773.aspx


55

Simple T-SQL Script for Solving this problem : write this script on New Query window and execute : RESTORE DATABASE [DataBase Name] WITH RECOVERY;


55

There's a lot going on here, and most of it is pretty broad and vague. 2008R2 RTM came out on April 21, 2010. It's totally out of support. You'll want to prioritize getting on the latest Service Pack, which came out just about 3 years ago to the day. That way you'll be covered if you're hitting a weird bug or something. Head on over here to figure out what ...


54

SQL Server will consume as much memory as you will allow it. By default, that number would encompass 100% of your numerical memory on your machine. That's why you're seeing what you're seeing. If you give SQL Server 24 GB of memory, then SQL Server will do its best to use 24 GB of memory. Then you have SQL Server and the OS battling for resources, and it'...


54

You do not specify if your table has a clustered index or not, so let's walk through all options. I am going to use this example partition function, partition scheme and table: CREATE PARTITION FUNCTION pf1(INT) AS RANGE LEFT FOR VALUES(10,20,30,40); GO CREATE PARTITION SCHEME ps1 AS PARTITION pf1 ALL TO ([PRIMARY]) GO CREATE TABLE dbo.pt(pc INT NOT NULL, ...


53

Does the order of columns in a PK index matter? Yes it does. By default, the primary key constraint is enforced in SQL Server by a unique clustered index. The clustered index defines the logical order of rows in the table. There may be a number of extra index pages added to represent the upper levels of the b-tree index, but the lowest (leaf) level of a ...


48

You need to give the database a valid owner. Use the ALTER AUTHORIZATION command to do this: ALTER AUTHORIZATION ON DATABASE::[DatabaseName] TO [A Suitable Login]; Related Knowledge Base article Note the sp_changedbowner system stored procedure has been deprecated in favour of ALTER AUTHORIZATION.


43

The potential size of the buffer pool affects plan selection by the query optimizer in a number of ways. As far as I know, hyper-threading does not affect plan choice (though the number of potentially available schedulers certainly can). Workspace Memory For plans that contain memory-consuming iterators like sorts and hashes, the size of the buffer pool (...


37

The foremost step to do is to run the Upgrade Advisor on SQL Server 2000 database and address all the issues reported by it. As a best practice, use the Upgrade Advisor tool on your SQL Server 2000 legacy database and import a trace file to the Upgrade Advisor tool for analysis. The trace file lets the Upgrade Advisor detect issues that might not show up ...


35

The key difference between Full and Copy-only backups is whether or not the LSN (Log Sequence Number), and specifically the DatabaseBackupLSN is updated. When you take a Full backup, the DatabaseBackupLSN is updated. After taking the full backup, if you take a Differential backup that backup has a DatabaseBackupLSN which matches that of the Full backup, ...


33

User principals must exist in a database before you can grant them permissions. use Restored_Prod GO CREATE USER [chris] FROM LOGIN [chris]; exec sp_addrolemember 'db_owner', 'chris'; GO http://technet.microsoft.com/en-us/library/ms173463.aspx


32

This calls for a recursive CTE: WITH FindRoot AS ( SELECT Id,ParentId, CAST(Id AS NVARCHAR(MAX)) Path FROM dbo.MyTable UNION ALL SELECT C.Id, P.ParentId, C.Path + N' > ' + CAST(P.Id AS NVARCHAR(MAX)) FROM dbo.MyTable P JOIN FindRoot C ON C.ParentId = P.Id AND P.ParentId <> P.Id AND C.ParentId <> C.Id ) SELECT * ...


32

This construction is not currently supported in SQL Server. It could (and should, in my opinion) be implemented in a future version. Applying one of the workarounds listed in the feedback item reporting this deficiency, your query could be rewritten as: WITH UpdateSet AS ( SELECT AgentID, RuleID, Received, Calc = SUM(...


32

There are two key differences between EXCEPT and NOT IN. EXCEPT EXCEPT filters the DISTINCT values from the left-hand table that do not appear in the right-hand table. It's essentially the same as doing a NOT EXISTS with a DISTINCT clause. It also expects the two tables (or subset of columns from the tables) to have the same number of columns in the ...


30

Since you get the correct plan with the ORDER BY, maybe you could just roll your own TOP operator? SELECT DOCUMENT_ID, COPIES, REQUESTOR, D_ID, FILE_NUMBER FROM ( SELECT dc.DOCUMENT_ID, dc.COPIES, dc.REQUESTOR, dc.D_ID, cj.FILE_NUMBER, ROW_NUMBER() OVER (ORDER BY cj.FILE_NUMBER) AS _rownum FROM ...


30

The subscript 2 is not part of the varchar character set (in any collation, not just Modern_Spanish). So make it a nvarchar constant: UPDATE test SET description = N'CO₂' WHERE id = 1;


29

No. This hard-coded restriction exists for good reasons related to possible excessive query plan compilation time. You can workaround it by listing the VALUES clauses in a CTE, then INSERTing from the CTE, but I do not recommend it. Break the INSERT statement up into VALUES clauses of <= 1,000 lines each as a workaround, or use an alternative data loading ...


29

Try forcing a hash join* SELECT TOP 1 dc.DOCUMENT_ID, dc.COPIES, dc.REQUESTOR, dc.D_ID, cj.FILE_NUMBER FROM DOCUMENT_QUEUE dc INNER HASH JOIN CORRESPONDENCE_JOURNAL cj ON dc.DOCUMENT_ID = cj.DOCUMENT_ID AND dc.QUEUE_DATE <= GETDATE() AND dc.PRINT_LOCATION = 2 ORDER BY cj.FILE_NUMBER The ...


29

Edit: +1 works in this situation because it turns out that FILE_NUMBER is a zero-padded string version of an integer. A better solution here for strings is to append '' (the empty string), as appending a value can affect order, or for numbers to add something which is a constant but contains a non-deterministic function, such as sign(rand()+1). The idea of '...


28

How does LIKE '%123456789%' benefit from indexing? Only a little bit. The query processor can scan the whole nonclustered index looking for matches instead of the entire table (the clustered index). Nonclustered indexes are generally smaller than the table they are built on, so scanning the nonclustered index may be faster. The downside, is that any ...


28

As alluded to by @Souplex in the comments one possible explanation might be if this column is the first NULL-able column in the non clustered index it participates in. For the following setup CREATE TABLE Foo ( A UNIQUEIDENTIFIER NOT NULL DEFAULT NEWSEQUENTIALID() PRIMARY KEY, B CHAR(1) NOT NULL DEFAULT 'B' ) CREATE NONCLUSTERED INDEX ix ...


27

Larger nvarchar (max) data items (over 8000 bytes or so) will spill over into text storage and require additional I/O. Smaller items will be stored in-row. There are options that control this behaviour - see this MSDN article for more details. If stored in-row there is no significant I/O performance overhead; there may be additional CPU overhead on ...


27

There is no WHERE in that part of the MERGE statement. See MERGE (Transact-SQL) in the documentation for syntax help. There is an optional AND part in WHEN MATCHED clause so the straightforward answer is to move the condition there: MERGE @Emp emp USING @EmpUpdates eup ON emp.empid = eup.empid WHEN MATCHED AND emp.empaddress <> eup.empaddress ...


26

Collations in SQL Server determine the rules for matching and sorting character data. Normally, you would choose a collation first based on the comparison semantics and sorting order the consumers of the data require. Humans generally do not find that binary collations produce the sorting and comparison behaviours they expect. So, although these offer the ...


25

OK, enough brain cells are dead. SQL Fiddle WITH cte AS ( SELECT [ICFilterID], [ParentID], [FilterDesc], [Active], CAST(0 AS varbinary(max)) AS Level FROM [dbo].[ICFilters] WHERE [ParentID] = 0 UNION ALL SELECT i.[ICFilterID], i.[ParentID], i.[FilterDesc], i.[Active], Level + CAST(i.[ICFilterID] AS ...


25

Figured it out: SELECT CONVERT ( VARCHAR(MAX), CAST('' AS XML).value('xs:base64Binary(sql:column("BASE64_COLUMN"))', 'VARBINARY(MAX)') ) AS RESULT FROM ( SELECT 'cm9sZToxIHByb2R1Y2VyOjEyIHRpbWVzdGFtcDoxNDY4NjQwMjIyNTcxMDAwIGxhdGxuZ3tsYXRpdHVkZV9lNzo0MTY5ODkzOTQgbG9uZ2l0dWRlX2U3Oi03Mzg5NjYyMTB9IHJhZGl1czoxOTc2NA==' ...


Only top voted, non community-wiki answers of a minimum length are eligible