Script Sql Server

Scripts utiles para SQL Server 2005


-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advancedoptions.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO

BACKUP DATABASE prueba_SQLTO DISK = 'D:\bd_backup\prueba_SQL2.bak'
WITH REPLACE;
GO

RESTORE DATABASE prueba_SQL
FROM DISK = 'D:\bd_backup\prueba_SQL2.bak'
WITH REPLACE;
GO


DECLARE @dbName sysnameDECLARE @backupPath NVARCHAR(500)
DECLARE @cmd NVARCHAR(500)
DECLARE @fileList TABLE (backupFile NVARCHAR(255))
DECLARE @lastFullBackup NVARCHAR(500)
-- variables
SET @dbName = 'MUNIXXXX'SET @backupPath = 'C:\Backup\'
-- get list of files
SET @cmd = 'DIR /b ' + @backupPath
INSERT INTO @fileList(backupFile)
EXEC master.sys.xp_cmdshell @cmd
-- Find latest full backup
SELECT@lastFullBackup = MAX(backupFile)
FROM @fileList
WHERE backupFile LIKE '%.BAK'
AND backupFile LIKE @dbName + '%'
--Restore script
SET @cmd = 'RESTORE DATABASE ' + @dbName + ' FROM DISK ='''
+ @backupPath + @lastFullBackup + ''' WITH RECOVERY, REPLACE'
PRINT @cmd


SELECT 'BACKUP DATABASE ' + name + ' TO DISK = ''E:\SQLBackup\' + name + '_20101116_ForUpgrade.bak'' WITHCHECKSUM, STATS = 1'
, 'RESTORE DATABASE ' + name + ' FROM DISK = ''E:\SQLBackup\' + name + '_20101116_ForUpgrade.bak'' WITH RECOVERY, STATS = 1'
FROM sys.databases
WHERE database_id >= 5 --Exclude system databases


Cambiar, restablecer la clave del usuario sa en SQL Server 2005
Si por algún motivo olvidaste la clave del usuario “sa” del SQL Server 2005 y necesitas restablecerlo,existen varios métodos para ello, pero todos requieren de iniciar sesión dentro del servidor.
- Abrir el SQL Server Management Studio
- Abrir un “New Query” desde la parte superior izquierda
-...