Skip to main content


Showing posts from September, 2018

Attach & Detach all databases

While working in my current project, I faced many scenarios where I have to attach one or more Databases and then detaches these databases. Later I found one script here  which can easily generate the “attach” code for each database, make any path or filename changes necessary, and re-attach all the databases with a single mouse click. Refer the below script and test this before you run in Production environment: USE [master]; DECLARE @database NVARCHAR(200), @cmd NVARCHAR(1000), @attach_cmd NVARCHAR(4000), @file NVARCHAR(1000), @i INT; DECLARE dbname_cur CURSOR STATIC LOCAL FORWARD_ONLY FOR SELECT  RTRIM(LTRIM([name])) FROM    sysdatabases WHERE   [dbid] > 4  -- exclude system databases OPEN dbname_cur FETCH NEXT FROM dbname_cur INTO @database WHILE @@FETCH_STATUS = 0 BEGIN SELECT @i = 1; -- Initial attach command stub SET @attach_cmd = '-- ' + QUOTENAME(@database) + CHAR(10) + 'EXEC sp_attach_db @dbname = ''' + @database + '''' + CHAR(10

Full Backup script in SQL Server

The below script will generate FULL BACKUP Script. After running the below command, copy the output and run the copied script to create Full Database Backup. Declare @Path VARCHAR(100) SEt @Path='C:\DatabaseBackup\' Select 'BACKUP DATABASE ['+name+'] TO DISK = N'''+@Path+''+name+'.bak'' WITH NOFORMAT, NOINIT, NAME = N'''+name+'-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10' from sys.databases where database_id>4