Change SQL Server default backup folder

If, like me, you have a folder other than the default where you like to keep all your database backups, you've probably wondered why there is no option in the server properties to specify this folder. It gets to be a royal pain if you are doing a lot of backups through management studio and you have to navigate back from something like C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\ to c:\backups\ each time.


Fortunately, there is a way to change this setting as it is stored in the windows registry and can be easily modified using Regedit. If you have multiple instances of SQL Server installed, you will have multiple folders under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\ but you will be looking for the key called BackupDirectory in a folder like HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.3\MSSQLServer\

Update this key and you'll see your default backup folder change. I have three instances installed (SQL Express, SQL 2005 and SQL 2008) and I updated the keys for both 2005 and 2008.

This will affect backups you execute using the UI but will also affect backups you run from the command line, so a BACKUP statement with no folder specified will also default to the new location.

Alter object schema in SQL Server

I was using a third party tool recently to copy data into SQL Server 2005. The tool did not allow you to specify the schema of the tables it would create but would default to dbo. After importing the data, I then had to change the schema of the newly created tables to fit in with the rest of the database. This is achieved in SQL 2005 with a simple ALTER SCHEMA as follows.

ALTER SCHEMA new_schema TRANSFER dbo.tablename

where new_schema is an existing schema.

To achieve the same result in SQL 2000 you must use the sp_changeobjectowner stored procedure, passing in the object name and the new schema name as follows:

EXEC sp_changeobjectowner 'objectname', 'new_schema'

Export SQL Server data to Excel

I am often called upon to import data from Excel into SQL Server. This can easily be achieved even if the schema is unknown using a SELECT * INTO [tablename] FROM OPENROWSET(). Slightly more difficult however is exporting from a SQL Server database to an Excel file. This can be achieved if you don't mind spending some time setting up an Excel template first but this can lead to problems when a) the database has a large number of tables or b) the schema is likely to change.

The solution I came up with for this was to write a script to generate code to both build and populate the Excel template. The generated code is in two parts. The first is Excel macro code and will set up a file with a given filename containing a worksheet for each table in the database. Each worksheet will have a column for each field in the matching database table. The second is T-SQL containing an INSERT INTO OPENROWSET() command for each table in the database. Tables with no rows are skipped.

To generate the code, copy the script below into a query window in management studio, select the correct database, set the results to text, set the @filename variable to a valid file and folder (this file need not yet exist) and execute. The first section in the results (down as far as the first INSERT) should be copied into a macro in a new Excel spreadsheet and executed there. This will build your template file and you should then save this to the location specified in @filename earlier and close it. The second section (the INSERTs) should be copied to a new query window in management studio and executed. This will copy the data from SQL Server to your Excel file and depending on the database may take some time.

Notes

• I have tested this in SQL 2005/2008 and Excel 2007. Be aware of the row and column limits in Excel (65536 rows, 256 columns in 2000 and 1,048,576 rows and 16,384 columns in 2007).
• If you have a large number of tables and/or fields, the macro code may exceed the 64k limit for a procedure and you will encounter the following error in Excel when you attempt to run it: Compile error: Procedure too large. In this situation, split the code into a number of procedures and run each in turn.
• The script makes use of the Information_schema views so will not work in any version of SQL Server prior to 2005.

DECLARE @cnt            INT

DECLARE @schema         VARCHAR(128)
DECLARE @table          VARCHAR(128)
DECLARE @schematable    VARCHAR(256)
DECLARE @col            VARCHAR(128)
DECLARE @cmd            VARCHAR(2500)
DECLARE @nextcoli       INT
DECLARE @nextcolc       VARCHAR(10)
DECLARE @i              INT
DECLARE @j              INT
DECLARE @filename       VARCHAR(250)
DECLARE @sqlcmd         VARCHAR(1500)

SET NOCOUNT ON

SET @nextcoli = 0

SET @filename = 'C:\ExcelData.xls'

CREATE TABLE #rowcount (tablename VARCHAR(128), schematable VARCHAR(256), rowcnt INT)
CREATE TABLE #XLCmdlist (xlid INT identity (1,1), cmd VARCHAR(1000))
CREATE TABLE #SQLCmdlist (xlid INT identity (1,1), cmd VARCHAR(3000))

DECLARE tables CURSOR FOR

 SELECT table_schema, table_name, '[' + table_schema + '].[' + table_name + ']' AS table_name FROM information_schema.tables WHERE table_type = 'base table' ORDER BY table_schema + '.' + table_name
OPEN tables
FETCH NEXT FROM tables INTO @schema, @table, @schematable
WHILE @@fetch_status = 0
 BEGIN

    SET @cmd = 'select ''' + @table + ''',''' + @schematable + ''', count(*) from ' + @schematable

    INSERT INTO #rowcount EXEC (@cmd)

    SELECT @cnt = rowcnt FROM #rowcount WHERE tablename = @table AND schematable = @schematable

    IF @cnt > 0

     BEGIN

        SET @nextcoli = 0

        SET @sqlcmd = ''

        INSERT #XLCmdlist (cmd) VALUES ('')

        INSERT #XLCmdlist (cmd) VALUES ('Sheets.Add After:=Sheets(Sheets.Count)')
        INSERT #XLCmdlist (cmd) VALUES ('Sheets(Sheets.Count).Select')
        INSERT #XLCmdlist (cmd) VALUES ('Sheets(Sheets.Count).Name = "' + @schematable + '"')

        DECLARE cols CURSOR FOR

         SELECT column_name FROM Information_Schema.columns WHERE table_schema =
@schema AND table_name = @table ORDER BY ordinal_position
        OPEN cols
        FETCH NEXT FROM cols INTO @col
        WHILE @@fetch_status = 0
         BEGIN
            SET @nextcoli = @nextcoli + 1
            SET @i = @nextcoli
            SET @nextcolc = ''

            WHILE @i > 0

             BEGIN
                SET @j = (@i - 1) % 26
                SET @nextcolc = Char(65 + @j) + @nextcolc
                SET @i = CAST(((@i - @j) / 26) AS INT)
             END

            INSERT #XLCmdlist (cmd) VALUES ('Range("' + @nextcolc + '1").Select')

            INSERT #XLCmdlist (cmd) VALUES ('ActiveCell.FormulaR1C1 = "' + @col +
'"')
            SET @sqlcmd = @sqlcmd + '[' + @col + '], '

            FETCH NEXT FROM cols INTO @col

         END

         CLOSE cols
         DEALLOCATE cols

         SET @sqlcmd = LEFT(@sqlcmd, LEN(@sqlcmd) - 1)

         INSERT #SQLCmdlist (cmd) VALUES ('INSERT INTO
OPENROWSET(''Microsoft.Jet.OLEDB.4.0'', ''Excel 8.0;Database=' + @filename + ';' + '''' + ', ' + '''' + 'SELECT ' + @sqlcmd + ' FROM [' + @table + '$]' + '''' + ')')

         INSERT #SQLCmdlist (cmd) VALUES ('SELECT ' + @sqlcmd + ' FROM [' + @table + ']')

         INSERT #SQLCmdlist (cmd) VALUES ('')

      END

  FETCH NEXT FROM tables INTO @schema, @table, @schematable

END
CLOSE tables
DEALLOCATE tables

SELECT cmd FROM #XLCmdlist ORDER BY xlid

SELECT cmd FROM #SQLCmdlist ORDER BY xlid

DROP TABLE #rowcount

DROP TABLE #XLCmdlist
DROP TABLE #SQLCmdlist

Changes to tables

One of my main tasks in my 9 to 5 is migrating data from a third party system to our own database. This will often involve reverse engineering parts of our own application into SQL scripts. One of the main tools I use for this is of course the profiler. I have also found it useful however to capture changes to the number of rows in a table. So for example, if I use the application to add a new company, I can see which tables had new rows added. To do this I perform the following actions.

1. Create a table in the database
2. Populate this table with a list of every table in the database
3. Determine and store the number of rows from each table
4. Perform the action in the applications
5. Determine and store the new number of rows from each table and compare to the previous values
6. Drop the table

So, firstly create the table and populate.

CREATE TABLE [dbo].[tablemon]
(
 TableName VARCHAR(128),
 PreCount INT NULL,
 PostCount INT NULL
)

INSERT INTO [dbo].[tablemon]

SELECT [name], 0, 0 FROM sys.tables ORDER BY [name]

I use the same code to determine and store the number of rows from each table before and after by setting the @preorpost variable to PRE or POST. To get the initial row counts:

DECLARE @preorpost CHAR(4)

DECLARE @tablename VARCHAR(128)
DECLARE @cSQL NVARCHAR(1000)
DECLARE @rowcount INT

SET @preorpost = 'PRE'

IF @preorpost = 'PRE'
 BEGIN
      UPDATE dbo.tablemon SET precount = 0, postcount = 0
 END

DECLARE tablecursor CURSOR FAST_FORWARD FOR SELECT [name] FROM sys.tables ORDER BY [name]
OPEN tablecursor
FETCH NEXT FROM tablecursor INTO @tablename
WHILE @@fetch_status = 0
 BEGIN
      IF @preorpost = 'PRE'
       BEGIN
             SET @cSQL = 'UPDATE dbo.tablemon SET precount = (SELECT COUNT(*) FROM ' + @tablename + ') WHERE tablename = ''' + @tablename + ''''
       END
      ELSE
       BEGIN
            SET @cSQL = 'UPDATE dbo.tablemon SET postcount = (SELECT COUNT(*) FROM ' + @tablename + ') WHERE tablename = ''' + @tablename + ''''
       END
      -- print @cSQL
      EXEC sp_executesql @cSQL
      FETCH NEXT FROM tablecursor INTO @tablename
END
CLOSE tablecursor
DEALLOCATE tablecursor

IF @preorpost = 'POST'

      SELECT * FROM [dbo].[tablemon] WHERE precount <> postcount

Now, after performing the action in the application, change the @preorpost variable to POST and run the code again. You will be presented with a list of the tables who's row counts have changed.



You can now drop the table or if you plan to run the routine again, leave it intact.