Posted on

SQLSERVER – Backup on mapped drive

Scenario

You ar trying to backup a sqlserver db on mapped network drive. You have already mapped the drive in Windows and you can see that drive in Windows Explorer. You are not able to see the drive which is mapped when you open the backup procedure using SqlServer Managment studio.

Solution

First of all you not need the previous mapped network drive created by Explorer. You have to create this drive using SqlServer, and you’ll not be able to see it using Exploer.

Enable xp_cmdshell

You need to execute below to enable xp_cmdshell as its disabled by default due to security reasons. (Please turn off again once you done with the work)

Using SSMS execute thesse commands :

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'xp_cmdshell',1
GO
RECONFIGURE
GO

After this you ‘ll have a positive reaction a this command that using SSMS you should use for testing previous operations :

EXEC XP_CMDSHELL 'Dir C:'

Map Network Drive

To map network drive you have to use the same command that you should use over Windows ysig command prompt :

'net use Z: \\networkShare\Test'

So, using SSMS you have to run the command

EXEC XP_CMDSHELL 'net use Z: \\networkShare\Test'

Now you should test this connection with the command

EXEC XP_CMDSHELL 'Dir Z:'

but, the most important goal, is tha you’ll be able to see teh drive Z during backup proceure over SSMS

Map Network Drive cmd – net use user password

The above command will work and completes successfully without asking the user to provide a username/password if the user has authorized access to this network share. If not, But the easy way is to use the “net use” command on the command prompt line explained above.

net use Z: \\networkShare\Test /u:domainname\username password

So, using SSMS you have to run the command

EXEC XP_CMDSHELL 'net use Z: \\networkShare\Test /u:domainname\username password'
Posted on

le istruzioni sql che non ricordo mai (ms sql server)

Creazione di una tabella partendo da una tabella – Copia

SELECT * INTO NEWTABLE FROM OLDTABLE


Accodamento di righe da una tabella ad un altra

INSERT INTO DESTINATIONTABLE SELECT * FROM ORIGINTABLE


Sostituzione di una stringa all’interno di una stringa

UPDATE MYTABLE SET MYFIELD = REPLACE(MYFIELD,’oldstring’,’newstring’) WHERE …….


Quale versione di SqlServer sto usando ?

SELECT SERVERPROPERTY(‘productversion’), SERVERPROPERTY (‘productlevel’),SERVERPROPERTY (‘edition’)


Quando è stata creata (o modificata) una tabella ?

SELECT name, create_date, modify_date FROM MYDB.sys.tables order by create_date desc


Trova il numero dei record di ogni tabella

SELECT sc.name +’.’+ ta.name TableName
,SUM(pa.rows) RowCnt
FROM sys.tables ta
INNER JOIN sys.partitions pa
ON pa.OBJECT_ID = ta.OBJECT_ID
INNER JOIN sys.schemas sc
ON ta.schema_id = sc.schema_id
WHERE ta.is_ms_shipped = 0 AND pa.index_id IN (1,0)
GROUP BY sc.name,ta.name
ORDER BY SUM(pa.rows) DESC