SQL Server – Backup all the sql server databases

Sharing with you script to backup all the SQL server databases Script: DECLARE @name VARCHAR(50) — database name DECLARE @path VARCHAR(256) — path for backup files DECLARE @fileName NVARCHAR(256) — filename for backup DECLARE @fileDate VARCHAR(20) — used for file name SET @path = ‘D:Backup’ SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),113) SELECT name,flag=0 INTO #temp FROM master.dbo.sysdatabases WHERE name NOT IN (‘master’,’model’,’msdb’,’tempdb’) –according to requirement we can filter databases in where clause SET ROWCOUNT 1 WHILE (exists(SELECT * FROM #temp WHERE flag=0))

» Read more

Add the value into an Identity Column in SQL Server

Identity columns are commonly used as primary keys in database tables. These columns automatically assign a value for each new row inserted. But sometime Identity columns  missed the value, we want to insert missed value into the column. For that we need to enable IDENTITY_INSERT for the table. USE mytempDB GO CREATE TABLE myTable ( myIdentity INT NOT NULL IDENTITY(1,1) PRIMARY KEY, myValue NVARCHAR(30) NOT NULL ) GO INSERT myTable(myIdentity, myValue) VALUES (5, ‘Varinder Sandhu’) –Result =  Error because IDENTITY

» Read more

Find EDITION, VERSION and SERVICEPACK information of SQL Server

Hello Friends Generally, we choose the  @@VERSION to get the version information for SQL Server, but using this function we can’t get the Service Pack information installed… Alternative way Get Editions of SQL Server SELECT SERVERPROPERTY(‘EDITION‘) AS EDITION Get Version Detail of SQL Server SELECT SERVERPROPERTY(‘PRODUCTVERSION‘) AS VERSION Get the Service Pack installed on SQL Server SELECT SERVERPROPERTY(‘PRODUCTLEVEL‘) AS SERVICEPACK

» Read more

SQL SERVER 2008 – Enable xp_cmdshell

—  To allow advanced options to be changed EXEC sp_configure ‘show advanced options’, 1 GO —- To update the currently configured value for advanced options. RECONFIGURE GO —- To enable the feature. EXEC sp_configure ‘xp_cmdshell’, 1 GO —- To update the currently configured value for this feature. RECONFIGURE GO Alternative way 1. Click the Start button. 2. Select All Programs. 3. Navigate to the Microsoft SQL Server 2008 folder. 4. Right Click on Server name then click facets options 5. In the Facets Option choose Surface Area Configuration in dropdown 6. At the bottom of the window, mark True for xp_cmdshell. 7. Click OK.

» Read more

Migrating Logins from One SQL Server to Another

As I need to migrate the databases from one server to another server. I have to deal with migrate not only the data, but the SQL Server logins that access that data as well. There may be different ways to migrate SQL Server logins like you can manually re-enter the entire existing login on the new server. You can use DTS to transfer logins. But here sharing one of other way of migration of SQL Server logins, by using the

» Read more

Linked Server

——————————————————— Create  Linked Server through Scripts ——————————————————— USE [master] GO EXEC sp_addlinkedserver @server=’dsql2k’, @srvproduct=”, @provider=’SQLNCLI’, @datasrc=’source_name’ GO USE [master] GO EXEC master.dbo.sp_serveroption @server=N’dsql2k’, @optname=N’rpc’, @optvalue=N’true’ GO EXEC master.dbo.sp_serveroption @server=N’dsql2k’, @optname=N’rpc out’, @optvalue=N’true’ GO select * from master.dbo.sysservers ————————————————————————— Alternative Script to Create Linked Server and Mapping of Users ————————————————————————— Use Master go EXEC master.dbo.sp_addlinkedserver @server = N’dsql2k’, @srvproduct=N”, @provider=N’SQLOLEDB’, @datasrc=N’testserver’ GO EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N’dsql2k’, @locallogin = N’sa’, @useself = N’False’, @rmtuser = N’sa’, @rmtpassword = N’sa’ GO

» Read more

SQL Backup Plan with Dynamic file Name

DECLARE @day VARCHAR(5) DECLARE @month VARCHAR(15) DECLARE @year VARCHAR(5) DECLARE @hour VARCHAR(5) DECLARE @filename VARCHAR(500) SET @day = DATENAME(DAY, GETDATE()) SET @month = DATENAME(MONTH, GETDATE()) SET @year = DATENAME(YEAR, GETDATE()) SET @hour = DATENAME(HOUR, GETDATE()) SET @filename = ‘D:DATABASE Backupdb_name_’ + @day + @month + @year + @hour + ‘.bak’ Backup DATABASE db_name TO Disk = @filename WITH format BACKUP log db_name TO disk = @filename WITH format

» Read more

Move SQL Server Databases Using Detach and Attach

–How to move SQL Server databases to a new location by using Detach and Attach functions in SQL Server –Prerequisites –Note You can determine the name and the current location of all files that a database uses by using the sp_helpfile stored procedure use go sp_helpfile go –Detach the database as follows: use master go sp_detach_db ‘mydb’ go –Next, copy the data files and the log files from the current location (D:Mssql7Data) to the new location (E:Sqldata). –Re-attach the database.

» Read more
1 2 3