Saving Time with Differential Backups
Recently I've been migrating a lot of databases from stand-alone physical servers into a PolyServe environment. Since this obviously requires a downtime, we have to schedule the moves with limited amounts of time. To add to the issues at night, the NetApp filers are busy so copies are slower.
In order to speed up the process and reduce the application downtime, I decided to use differential backups - which I will admit to not using that often.
If my downtime starts at 11PM, I will take a Full database backup earlier in the evening with SSMS. I backed the database up to the remote server that was to become the new SQL Server (\\server\path\db.bak) in order to avoid having to copy the backup file later.
After the full backup was completed, I restored it to the new server and made sure all my logins were moved over with sp_help_revlogin. Make sure when you restore the full backup that you choose the RESTORE WITH NO RECOVERY option, or you will not be able to apply the differential backup. Also, verify that the directory structure you are restoring the files to is correct. It will try to restore to the directory structure used by the old server.
Once the downtime started, I made sure all user connections to the database were gone and kicked off my differential backup. My full backup was nearly 20GB, but my differential was less than 200MB, which I also backed up to the new database server.
Once the differential backup was completed, I restored this database (using the Restore Database functionality in SSMS) as well. This time select the option to Restore with Recovery if you do not have any transaction logs to restore. If you shut down database access and took a differential like I did, there will be no transaction logs to worry about. Once you Restore With Recovery, the database will be available for use immediately after the restore is complete.
At first I wasn't sure if I should Restore Log or Restore Database, but Restore Database seemed like the best option and it was the right one. For some reason I had to fix the paths to my databases again, because the backup was using the directory structure from the old server here as well. I hoped it would be smart enough to know what to do on its own, but wasn't.
I did not specify OVERWRITE. The restore initially was quick but then hung on 90% for a while. It finally finished up and my database was ready to go. My downtime was 15 minutes - the time it took to take the differential and restore it on the other server. For a savings of at least 30 to 40 minutes.
I'd rather put the time in up front and reduce downtime with an added bonus of not having to stay up as late waiting on it. I used the time I saved to write this article as a quick reference for anyone else who is looking for the same information.
SQL Backup Process Hanging and Stuck in Kill/Rollback – HyperBac
One of the most important tasks a DBA should perform daily is verifying database backup jobs are successful. Using Database Mail to e-mail upon the failure of a job is something everyone should be doing. But in some cases, that is not enough. You can query the MSDB database in order to find out the last time a database was backed up. If you have a large amount of servers, consider using PowerShell's SMO capabilities to query a list of servers and compile the information into a database table. Then you can query that table for backup dates older than a day or two, depending on your backup schedule. That's the topic of another article.
Using a similar approach, I recently discovered a database that had missed being backed up for 2 days over the weekend. I had not received any email regarding a failure. Using SSMS, I pulled up the list of jobs in Object Explorer Details and noticed that the job was stuck executing since the previous Friday night. The database backup file was not actively being written to, and since backups are serialized commands, any subsequent attempt to backup the database while that process is running will be unsuccessful. Using SSMS, I stopped the executing job.
A quick query of the sys.sysprocesses table (select * from sys.sysprocesses) showed that the process was still running. I killed the process since it was being so difficult. From the sysprocesses table results, find the spid for the process. Execute the statement Kill spid where spid is the actual ID that is shown. If you prefer to use SSMS, launch Activity Monitor, find the process in the list and kill it from there.
Once I killed the process, it went into a rollback state, which is expected. I'm not 100% sure what a backup transaction is rolling back, but it sure will try. Take note of the "waittime" column in the sys.sysprocesses table. The value may increase initially, but at some point it should max out and start decreasing. It is possible to track the progress of the rollback by executing the following command:
kill spid with statusonly
The result stated that the rollback was 16% completed. I decided to wait and see how long it would take. My waittime value continued to increase. I went on with my business and checked back later. After many hours I returned to find that the rollback was still at 16% and the waittime was STILL increasing. I knew something was wrong. My first quick-fix idea was to restart the SQL Service. This actually would have fixed my problem, but being that the system is in production, I wanted to find a better way. Also, a restart would not help me figure out why the problem was occurring in the first place.
Eventually, I realized that this server was using HyperBac, which is a really cool compression tool that intercepts a native backup and zips it up. I decided to stop the HyperBac service to see if it would help. After attempting to stop the service, the service got stuck in the "Stopping" state. I checked the log file in the HyperBac (Program Files) folder and saw a line stating that it was attempting to close out the very backup that had been stuck for 3 days.
I gave the service a large amount of time to stop, but it never did. I eventually had to End Task a few times on the HyperBac process. Once the process ended, my SQL backup process disappeared.
I cannot definitively say if HyperBac caused the SQL backup process to hang, or if the SQL process caused HyperBac to hang. Of the hundreds of backups that run every night, I have run into this issue 3 times. All 3 times were on large databases (~70GB) using HyperBac to back up to a network drive. Perhaps a network blink during the backup process is to blame for initiating the issue. Whatever the reason, I opened a ticket with HyperBac and was told that version 4.2.x fixes this situation. I should also comment on how quickly HyperBac responded to my problem with an answer, so thumbs up on customer support. I plan on upgrading to 4.2.x soon, but server reboots are required. If the issue happens again before then, it is easy enough to fix.
SQL 2008 Installation Failure on Reporting Services
Recently, SQL Server 2008 was uninstalled from a Windows 2008 Server machine due to some configuration issues with Reporting Services. Attempts to re-install SQL Server with the Reporting Services option would fail. I was tasked with the re-install and as always, I welcome this type of work because there is something to learn nearly every time. Before the installation began, I went to "Programs and Features" and uninstalled anything referencing SQL Server. The Native Client installation was still listed, so I removed that. There was also a reference to SQL Server 2008 itself, but attempts to remove it failed instantly saying it was already uninstalled. Red flag number 1.
An installation summary was provided from a previous failed attempt and it provided an exception link with the following information:
Product: SQL Server
ID: 50000
Source: setup.rll
Version: 10.0
Component: SQL Server Native Client
Message: A network error occurred while attempting to read from the file '%.*ls'.
The page went on to explain that an attempt was made to install (or update) SQL Server Native Client on a computer where SQL Server Native Client is already installed, and where the existing installation was from an MSI file that was not named sqlncli.msi.
Having just removed the SQL Server Native Client, I was confident that my next installation attempt would be successful. To make a long story short, my installation failed again when trying to install Reporting Services with the same exact error. Ok, so we must be dealing with a registry key issue, so I decided to run regclean to clean up the system. I would caution to do this at your own risk. In my case, there was nearly nothing else installed on the server and if anything drastic happened, I would have been able to get a file system restore or having the system rebuilt without losing anything major. I reviewed the undo reg file that was created by regclean and found references to Reporting Services.
Attempt 2! I was confident yet again that it would work and to make another long story short - it failed. At the same point, with the same error! Ok, now it's time to go through the error log a little bit closer. And for those of you who don't care about all the steps and only the solution, you should have skipped down to this part.
After the installation failure, the setup directed me to a summary log file. In the log file, there was a path to another log containing the failure information:
C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20100428_094920\sql_rs_Cpu64_1.log
I opened this file and located the following line:
MSI (s) (3C:80) [09:57:00:939]: Specified instance {2453DBC8-ACC4-4711-BD03-0C15353AA3D8} via transform :InstID01.mst;:InstName01.mst is already installed. MSINEWINSTANCE requires a new instance that is not installed.
Fire up RegEdit and search for the instance ID specified in the log. In my case it was {2453DBC8-ACC4-4711-BD03-0C15353AA3D8}.
Delete the registry keys that match up with that ID. You will notice that some of the values contained in that key folder reference Reporting Services and the initial installation path. In my case, I knew it was the right place to be because the previous install was done from a different location than where I was installing from.
After deleting the registry keys, I launched my Setup and added Reporting Services successfully. I was unable to choose the default configurations, however. My only option was to install without configuring. Once the installation was completed, I had to launch Reporting Services Configuration Manager and set up the new ReportServer databases. You have the option to create new databases or use existing ones.
This methodology would probably come in handy in any type of SQL Server 2008 installation failure where you suspect bad registry entries.
SQL Server: Error: 3041, Severity: 16, State: 1
There are probably a good number of reasons this error shows up in your SQL Server log, along with the following message:
Source: Backup
Message: BACKUP failed to complete the command BACKUP LOG DATABASE_NAME. Check the backup application log for detailed messages.
In my case, the recovery model was set to SIMPLE and the DB maintenance plan was set to backup all user databases, which included all databases set to SIMPLE. I have my suspicions as to why those production databases were set to SIMPLE but FULL is best in most cases anyway, so I set all recovery models to FULL, and manually backed up all databases. To see a list of all of your databases and their recovery models, run the following query:
select [name],databasepropertyex([name],'Recovery') as [Recovery]
from master.dbo.sysdatabases
order by [name]Note that the following system databases are set to SIMPLE by default: master, tempdb and msdb.
Asynchronously mirroring a SharePoint 2007 Content Database between Two Farms
Recently, I triumphantly setup an asynchronous content mirror between two separate SharePoint farms.
My SharePoint architecture consists of two separate farms in two different cities (San Diego and Las Vegas), both accessible over a corporate intranet. Each farm is unique and the configuration and administration databases contain unique information that cannot be mirrored from one site to another. The technique outlined below works, however, because the content databases are identical between the two sites and that is the only information that must be mirrored. One farm is a fail over for the other farm, so each farm contains the same sites.
After searching the Internet for information on how to mirror SharePoint data, I only found one MSDN article which stated that it should be done either with SQL Server clustering or with synchronous mirroring requiring a witness server. Eventually I found a picture in the MSDN showing a way to mirror only the SharePoint content databases between two farms asynchronously.
This picture outlined what I wanted, but I knew SharePoint well enough to know it was going to cause problems. I decided to try my hand at a solution, nevertheless. I started on the primary farm in Las Vegas, where I set up mirroring on the Sharepoint_Content database and mirrored it to the Sharepoint_Content database in San Diego, which was designated as the backup farm. By design, the San Diego content database was inaccessible while the mirroring was in place, so I had little chance of the backup farm actually loading up any pages. This was acceptable, however, because the backup farm did not need to work unless I failed over to it. I made some changes to my primary farm by adding a few announcements to a web part on a site and attached a picture to it. After some time, I decided to remove mirroring from the primary database. The reason I removed mirroring instead of doing a fail over, is because I could not have downtime on the primary farm. Nor did I want to take any chances breaking the database by reversing the mirroring if my technique did not work.
After breaking the mirror, the mirrored database showed as "Restoring.." until I ran the command: RESTORE DATABASE [SharePoint_Content] WITH RECOVERY.
This method is used to make the mirror available again as a stand-alone database. Once the database was available, I loaded up my SharePoint site on the fail over farm with my fingers crossed. As the page loaded, I saw a dreaded "Error 400 - Bad Request." I figured this would happen, as things are rarely straightforward with SharePoint. I didn't have time to address the issue so I decided to just restore from my most recent backup and put my experiment on hold. After issuing a restore command using stsadm, I saw a peculiar error which stated that it could not restore my site because no content databases were associated and suggested that I add a content database before proceeding.
Perhaps this could work after all! I loaded up the administration panel, found at http://servername:8080, and navigated to Content Databases under Application Management. The Sharepoint_Content database was listed as it should have been but I figured since it was not working as desired anyway, I could remove the SharePoint_Content database from the configurations and re-add it. First, I removed the content database and re-added a new content database with the same name and selected my SQL Server from the drop-down menu. I clicked OK and SharePoint updated its configuration. Soon the new content database was listed.
I hurried back to my site, refreshed the browser and to my delight, the page loaded and the announcements I had added in Las Vegas were displayed on the site running in San Diego. It was at this moment when I realized that this instance of making the impossible possible would be perfect fodder for my first blog post on netnerds.net.
Free SQL Server eBooks available
Brad McGehee just published what's sure to be another incredibly informative ebook on SQL Server Maintenance Plans. A list of free eBooks, including the SQL Server Tackle Box, can be found on Brad's blog. Check it, one time.
How to Import Data into SQL Server 2005 From DB2 (AS400/iSeries)
Ever since I read how much IBM's DB2 SMOKED Oracle and SQL Server in the tpc-c (OLTP) benchmarks, I've wanted to work with it. The benchmarks go a little something like this:
| RDBMS | tpc-c |
| IBM DB2 | 6,085,166 |
| Oracle | 4,092,799 |
| Microsoft SQL Server | 1,231,433 |
I mean, I don't work with servers that require nearly that horsepower but it's interesting to see.
So now I have a chance to work with DB2 on an iSeries server. Granted, I'm not doing much other than SELECT statements, but I'm having fun. The initial setup was a little confusing for me. According to IBM's documentation website, there's two ways to access the DB2 server: via SQL or via "traditional file access." The firm I work with uses traditional file access terminology so translation to SQL terms was necessary. Overall it was pretty easy; here's a cheat sheet:
| SQL Server | IBM file access |
| Schema/Owner | Database/Library |
| Table/View | File |
| Row | Record |
| Column | Field |
The accounting department gave me a PDF which contained the following information.
| DB Info | |
| File | CARPDIEM |
| Library | CDCLIENT |
| Fields | M00000, M00001 |
To get the Linked Server going, I installed the iSeries Client Access on the SQL Server which supplied the IBM AS400 OLE DB Provider. From there, I created the Linked Server with the following information:
| General | |
| Linked Server | AS400 |
| Provider | IBM AS400 OLE DB Provider |
| Product Name | <anything> |
| Data Source | <IP of server> |
| Security Options | |
| I mapped a local account using the credentials supplied to me by the accounting department. | |
Initally, when expanding the table structure, I received an error: The OLE DB Provider "IBMDA400" for linkedServer "AS400" reported an error. Acccess is denied. Cannot obtain the required interface ("IID_IDBSchemaRowset") from OLE DB Provider... Error: 7399. but after researching, found that I simply needed to select the box "Allow Inprocess" under the IBMDA400 Provider options and this error went away. I then experimented with a few different SELECT statements to see which one was easiest. Ultimately, I ended up using the following queries to import the data from the DB2 server into SQL Server 2005:
SELECT * INTO importClients from AS400.ServerName.CARPDIEM.CDCLIENT
If you don't know the variable for ServerName in the above query, you can refer to this tutorial. Also, I was able to use Microsoft's DB2 Provider, as described here. The Initial Catalog is the ServerName and the Package Collection and Default Schema are both the schema/username. Not sure what the speed differences between the two providers are, if I find the time to benchmark, I'll be sure to post the results.
Create a DB2-C Express 9.5 Linked Server in SQL Server 2008
In order to emulate a DB2/iSeries<->SQL Server setup that I deal with at work, I installed DB2 Express-C on SuSE Linux Enterprise Server 10. The DB2/Linux Server details, as well as a sample database and table name, are as follows:
| IBM DB2 Database | |
| Network Name | LUCAS |
| TCP Port | 50000 |
| Instance | DB2INST1 |
| Database | SAMPLE |
| Schema/Owner | DB2USER |
| Table | EMPLOYEES |
After the initial install and sample database/table creation, I set out to create a Linked Server in SQL Server 2008. Initially, I installed IBM Data Server Client client tools, which includes a few JDBC drivers, on the SQL Server. One of these providers is "IBM OLE DB Provider for DB2," showed up in the list of Linked Server Provider in SQL Server after the software was successfully installed.
Setting up the Linked Server was pretty straight-forward and initially seemed to work, using the following details:
| General | |
| Name | IBMDB2 |
| Provider | IBM OLE DB Provder for DB2 |
| Product Name | IBMADDB2.DB2COPY* |
| Data Source | SAMPLE |
| Provider String | <blank> |
| Location | LUCAS |
| Security Options | |
| Login Info | Your call. I mapped a local user to db2user. |
| Security Options | |
| All Options | Default |
| * IBMADDB2.DB2COPY is the name of the IBM DB2 provider in the Linked Server Provider list. | |
The server was created easily enough, but SQL Server's automatically generated SELECT statements kept erroring out. The syntax was as follows: SELECT fName, lName FROM IBMDB2..DB2USER.EMPLOYEES. Upon execution, the following error was returned:
Msg 7318, Level 16, State 1, Line 1
The OLE DB provider "IBMDADB2.DB2COPY1" for linked server "IBMDB2" returned an invalid column definition for table ""DB2USER"."EMPLOYEES"".
So I added in the database name (IBMDB2.SAMPLE.DB2.EMPLOYEES) to the SELECT statement, executed the query and received the error:
Msg 7313, Level 16, State 1, Line 1
An invalid schema or catalog was specified for the provider "IBMDADB2.DB2COPY1" for linked server "IBMDB2".
Past experience taught me that it was now probably time to look for a new driver. After some research, I found an IBM DB2 Driver in the SQL Server 2008 Feature Pack which can be found: OLEDB Provider for DB2 (DB2OLEDB.exe). According to Microsoft..
The Microsoft OLE DB Provider for DB2 version 2.0 is a COM component for integrating vital data stored in IBM DB2 databases with new solutions based on Microsoft SQL Server 2008 Enterprise Edition and Developer Edition. SQL Server developers and administrators can use the provider with Integration Services, Analysis Services, Replication, Reporting Services, and Distributed Query Processor.
After extracting the contents and clicking setup.exe as instructed, the full-on GUI-based installer failed. Ultimately, I had to run the install from the command line: setup.exe /L c:\HISInstall.log /S /INSTALLDIR C:\HIS and it worked flawlessly after a reboot that was required anyway by Windows Update.
Once the promising new driver was installed, it an additional 2 hours to figure out exactly what Linked Server parameters were required. I kept running into an error that said "The parameter is incorrect." Ultimately, I figured out how to set it up by running the Data Access Tool included with Microsoft's DB2 Provider. After I setup a successful connection to the DB2 Server, the Data Access Tool gave me the Provider String that I needed to create a successful connection.
| General | |
| Name | DB2 |
| Provider | Microsoft OLE DB Provider for DB2 |
| Product Name | DB2OLEDB2 |
| Data Source | LUCAS |
| Provider String |
Provider=DB2OLEDB;Initial Catalog=SAMPLE;Network Transport Library=TCP;Host CCSID=37; PC Code Page=1252;Network Address=lucas;Network Port=50000;Package Collection=DB2USER; Default Schema=DB2USER;Process Binary as Character=False;Units of Work=RUW; DBMS Platform=DB2/MVS;Defer Prepare=False;Rowset Cache Size=0;Persist Security Info=True; Connection Pooling=False;Derive Parameters=False; |
| Location | <grayed out> |
| Catalog | SAMPLE |
| Security Options | |
| Login Info | Your call. I mapped a local user to db2user. |
| System Options | |
| All Options | Default |
The Data Access Tool gives the username and password in clear text in the Provider String but I just removed that and chose to setup the username and password under the Security Options. Ultimately, this was the setup that worked for me. I am now able to query the data using a simple 4 part name: SELECT fName, lName FROM DB2.SAMPLE.DB2.EMPLOYEES. Success! If you'd like to create the Linked Server using only T-SQL, run the following command, suited to your environment, of course.
EXEC master.dbo.sp_addlinkedserver @server = N'DB2', @srvproduct=N'DB2OLEDB', @provider=N'DB2OLEDB',
@datasrc=N'LUCAS', @provstr=N'Provider=DB2OLEDB;Initial Catalog=SAMPLE;Network Transport Library=TCP;
Host CCSID=37;PC Code Page=1252;Network Address=lucas;Network Port=50000;Package Collection=DB2USER;
Default Schema=DB2USER;Process Binary as Character=False;Units of Work=RUW;DBMS Platform=DB2/MVS;
Defer Prepare=False;Rowset Cache Size=0;Persist Security Info=True;Connection Pooling=False;
Derive Parameters=False;', @catalog=N'SAMPLE'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'DB2',@useself=N'False',
@locallogin=NULL,@rmtuser=N'db2user',@rmtpassword='########'In my next post, I'll cover a more likely DB2<->SQL Server scenario: creating a linked server in SQL Server 2005 to IBM DB2 running on an AS400/iSeries server.
Migrate/Transfer SQL Server 2008/2005/2000/7 Logins to SQL Server 2008
To my knowledge, Microsoft has not yet updated their Knowledge Base to inform administrators how to migrate SQL Server logins to SQL Server 2008. The good news is that the SQL Server 2005 login migration knowledge base article applies to SQL Server 2008 as well. Below I've included a compilation of all of the migration procedures between various versions of SQL Server.
Migration is very easy -- basically, a stored procedure named sp_help_revlogin is created and executed on the source server. The resulting output is then copied and executed on the destination server.
Detailed instructions can be found at Microsoft KB Article 918992 and Microsoft KB Article 246133. Oh, also, Dell has a marvelous 54-page SQL Server 2000 to 2008 Migration Guide. It actually outlines, in an easy-to-read style, a lot of the best practices I learned about while studying for my MCITP certification.
Code For Login Migrations Between SQL Server 7 and SQL Server 2000
----- Begin Script, Create sp_help_revlogin procedure -----
USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@binvalue varbinary(256),
@hexvalue varchar(256) OUTPUT
AS
DECLARE @charvalue varchar(256)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
DECLARE @tempint int
DECLARE @firstint int
DECLARE @secondint int
SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
SELECT @firstint = FLOOR(@tempint/16)
SELECT @secondint = @tempint - (@firstint*16)
SELECT @charvalue = @charvalue +
SUBSTRING(@hexstring, @firstint+1, 1) +
SUBSTRING(@hexstring, @secondint+1, 1)
SELECT @i = @i + 1
END
SELECT @hexvalue = @charvalue
GO
IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @xstatus int
DECLARE @binpwd varbinary (256)
DECLARE @txtpwd sysname
DECLARE @tmpstr varchar (256)
DECLARE @SID_varbinary varbinary(85)
DECLARE @SID_string varchar(256)
IF (@login_name IS NULL)
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name = @login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
IF (@@fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated '
+ CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
PRINT 'DECLARE @pwd sysname'
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
IF (@xstatus & 4) = 4
BEGIN -- NT authenticated account/group
IF (@xstatus & 1) = 1
BEGIN -- NT login is denied access
SET @tmpstr = 'EXEC master..sp_denylogin ''' + @name + ''''
PRINT @tmpstr
END
ELSE BEGIN -- NT login has access
SET @tmpstr = 'EXEC master..sp_grantlogin ''' + @name + ''''
PRINT @tmpstr
END
END
ELSE BEGIN -- SQL Server authentication
IF (@binpwd IS NOT NULL)
BEGIN -- Non-null password
EXEC sp_hexadecimal @binpwd, @txtpwd OUT
IF (@xstatus & 2048) = 2048
SET @tmpstr = 'SET @pwd = CONVERT (varchar(256), ' + @txtpwd + ')'
ELSE
SET @tmpstr = 'SET @pwd = CONVERT (varbinary(256), ' + @txtpwd + ')'
PRINT @tmpstr
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
+ ''', @pwd, @sid = ' + @SID_string + ', @encryptopt = '
END
ELSE BEGIN
-- Null password
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
+ ''', NULL, @sid = ' + @SID_string + ', @encryptopt = '
END
IF (@xstatus & 2048) = 2048
-- login upgraded from 6.5
SET @tmpstr = @tmpstr + '''skip_encryption_old'''
ELSE
SET @tmpstr = @tmpstr + '''skip_encryption'''
PRINT @tmpstr
END
END
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
----- End Script -----
Code For Login Migrations From SQL Server 7/2000 to SQL Server 2005/2008
USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@binvalue varbinary(256),
@hexvalue varchar(256) OUTPUT
AS
DECLARE @charvalue varchar(256)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
DECLARE @tempint int
DECLARE @firstint int
DECLARE @secondint int
SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
SELECT @firstint = FLOOR(@tempint/16)
SELECT @secondint = @tempint - (@firstint*16)
SELECT @charvalue = @charvalue +
SUBSTRING(@hexstring, @firstint+1, 1) +
SUBSTRING(@hexstring, @secondint+1, 1)
SELECT @i = @i + 1
END
SELECT @hexvalue = @charvalue
GO
IF OBJECT_ID ('sp_help_revlogin_2000_to_2005') IS NOT NULL
DROP PROCEDURE sp_help_revlogin_2000_to_2005
GO
CREATE PROCEDURE sp_help_revlogin_2000_to_2005
@login_name sysname = NULL,
@include_db bit = 0,
@include_role bit = 0
AS
DECLARE @name sysname
DECLARE @xstatus int
DECLARE @binpwd varbinary (256)
DECLARE @dfltdb varchar (256)
DECLARE @txtpwd sysname
DECLARE @tmpstr varchar (256)
DECLARE @SID_varbinary varbinary(85)
DECLARE @SID_string varchar(256)
IF (@login_name IS NULL)
DECLARE login_curs CURSOR STATIC FOR
SELECT sid, [name], xstatus, password, isnull(db_name(dbid), 'master')
FROM master.dbo.sysxlogins
WHERE srvid IS NULL AND
[name] <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT sid, [name], xstatus, password, isnull(db_name(dbid), 'master')
FROM master.dbo.sysxlogins
WHERE srvid IS NULL AND
[name] = @login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb
IF (@@fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated '
+ CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
PRINT ''
PRINT ''
PRINT '/***** CREATE LOGINS *****/'
WHILE @@fetch_status = 0
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
IF (@xstatus & 4) = 4
BEGIN -- NT authenticated account/group
IF (@xstatus & 1) = 1
BEGIN -- NT login is denied access
SET @tmpstr = '' --'EXEC master..sp_denylogin ''' + @name + ''''
PRINT @tmpstr
END
ELSE
BEGIN -- NT login has access
SET @tmpstr = 'IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE [name] = ''' + @name + ''')'
PRINT @tmpstr
SET @tmpstr = CHAR(9) + 'CREATE LOGIN [' + @name + '] FROM WINDOWS'
PRINT @tmpstr
END
END
ELSE
BEGIN -- SQL Server authentication
EXEC sp_hexadecimal @SID_varbinary, @SID_string OUT
IF (@binpwd IS NOT NULL)
BEGIN -- Non-null password
EXEC sp_hexadecimal @binpwd, @txtpwd OUT
SET @tmpstr = 'CREATE LOGIN [' + @name + '] WITH PASSWORD=' + @txtpwd + ' HASHED'
END
ELSE
BEGIN -- Null password
SET @tmpstr = 'CREATE LOGIN [' + @name + '] WITH PASSWORD='''''
END
SET @tmpstr = @tmpstr + ', CHECK_POLICY=OFF, SID=' + @SID_string
PRINT @tmpstr
END
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb
END
IF @include_db = 1
BEGIN
PRINT ''
PRINT ''
PRINT ''
PRINT '/***** SET DEFAULT DATABASES *****/'
FETCH FIRST FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb
WHILE @@fetch_status = 0
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
SET @tmpstr = 'ALTER LOGIN [' + @name + '] WITH DEFAULT_DATABASE=[' + @dfltdb + ']'
PRINT @tmpstr
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb
END
END
IF @include_role = 1
BEGIN
PRINT ''
PRINT ''
PRINT ''
PRINT '/***** SET SERVER ROLES *****/'
FETCH FIRST FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb
WHILE @@fetch_status = 0
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
IF @xstatus &16 = 16 -- sysadmin
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''sysadmin'''
PRINT @tmpstr
END
IF @xstatus &32 = 32 -- securityadmin
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''securityadmin'''
PRINT @tmpstr
END
IF @xstatus &64 = 64 -- serveradmin
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''serveradmin'''
PRINT @tmpstr
END
IF @xstatus &128 = 128 -- setupadmin
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''setupadmin'''
PRINT @tmpstr
END
IF @xstatus &256 = 256 --processadmin
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''processadmin'''
PRINT @tmpstr
END
IF @xstatus &512 = 512 -- diskadmin
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''diskadmin'''
PRINT @tmpstr
END
IF @xstatus &1024 = 1024 -- dbcreator
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''dbcreator'''
PRINT @tmpstr
END
IF @xstatus &4096 = 4096 -- bulkadmin
BEGIN
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''bulkadmin'''
PRINT @tmpstr
END
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb
END
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
exec sp_help_revlogin_2000_to_2005 @login_name=NULL, @include_db=1, @include_role=1
GO
Code For Login Migrations From SQL Server 2005/2008 to SQL Server 2005/2008
USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@binvalue varbinary(256),
@hexvalue varchar (514) OUTPUT
AS
DECLARE @charvalue varchar (514)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
DECLARE @tempint int
DECLARE @firstint int
DECLARE @secondint int
SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
SELECT @firstint = FLOOR(@tempint/16)
SELECT @secondint = @tempint - (@firstint*16)
SELECT @charvalue = @charvalue +
SUBSTRING(@hexstring, @firstint+1, 1) +
SUBSTRING(@hexstring, @secondint+1, 1)
SELECT @i = @i + 1
END
SELECT @hexvalue = @charvalue
GO
IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @type varchar (1)
DECLARE @hasaccess int
DECLARE @denylogin int
DECLARE @is_disabled int
DECLARE @PWD_varbinary varbinary (256)
DECLARE @PWD_string varchar (514)
DECLARE @SID_varbinary varbinary (85)
DECLARE @SID_string varchar (514)
DECLARE @tmpstr varchar (1024)
DECLARE @is_policy_checked varchar (3)
DECLARE @is_expiration_checked varchar (3)
DECLARE @defaultdb sysname
IF (@login_name IS NULL)
DECLARE login_curs CURSOR FOR
SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
IF (@@fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
IF (@type IN ( 'G', 'U'))
BEGIN -- NT authenticated account/group
SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
END
ELSE BEGIN -- SQL Server authentication
-- obtain password and sid
SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
-- obtain password policy state
SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'
IF ( @is_policy_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
END
IF ( @is_expiration_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
END
END
IF (@denylogin = 1)
BEGIN -- login is denied access
SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
END
ELSE IF (@hasaccess = 0)
BEGIN -- login exists but does not have access
SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
END
IF (@is_disabled = 1)
BEGIN -- login is disabled
SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
END
PRINT @tmpstr
END
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
Mmm, hella swole code.
PowerShell 1.0: Backup and Restore a Database Between Two SQL 2005 Servers
My technical partner-in-crime, Brandon, created this PowerShell script last year in an effort to help him understand PowerShell a bit more. He noted that while it may not all be best practices SQL-wise, he tried his best to use as much PowerShell as possible.
#Powershell to backup and restore a SQL database from server1 to server2.
#2008, Brandon Abshire / netnerds.net
#load assemblies
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
# create source and destination sql servers
$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') 'server1'
$d = new-object ('Microsoft.SqlServer.Management.Smo.Server') 'server2'
# set backup directory, dbname and grab date
$bkdir = 'e:\'
$remoteDir = 'e$\'
$dbname = 'databasename'
$dt = get-date -format yyyyMMddHHmmss
#create backup object and set properties
$dbbk = new-object ('Microsoft.SqlServer.Management.Smo.Backup')
$dbbk.Action = 0
$dbbk.BackupSetDescription = "Backup of " + $dbname
$dbbk.BackupSetName = $dbname + " Backup"
$dbbk.Database = $dbname
$dbbk.Devices.AddDevice($bkdir + "\" + $dbname + $dt + ".bak", 2)
$dbbk.MediaDescription = "Disk"
$dbbk.Incremental = $false
#Backup the database
$dbbk.SqlBackup($s)
#create restore object and set properties.
$dbres = New-Object ('Microsoft.SqlServer.Management.Smo.Restore')
$dbres.Database = $dbname
$dbres.Action = 0
$dbres.ReplaceDatabase = $true
#Add the backup that is on the remote server by whatever method you wish.
#You can copy it locally first. I just used named pipes to grab it off the network, since it is a fast enough connection.
$dbres.Devices.AddDevice("\\" + $s + "\" + $remoteDir + $dbname + $dt + ".bak", 2)
$dbres.NoRecovery = $false
#Without the KillAll, my restore got stuck in "Restoring..." forever.
$d.KillAllProcesses('databasename')
#restore the database.
$dbres.SqlRestore($d)
#The database is now restored from one server to another.
#We must re-map the mappings on the database for my user, or login will fail.
#Set db to the specific database we want on the destination server.
$db = $d.databases["databasename"]
#Set newUser to the user we want to give permissions to and drop it.
#This user was restored from the other database and doesn't match up
#to the one on the destination SQL server.
#sp_change_users_login 'autofix','userlogin' could also be used but I
#wanted to practice PS moreso than SQL
$newuser = $db.users["username"]
$newuser.drop()
#Create a new user. Set the parent to the database we restored.
$newuser = New-Object ('Microsoft.SqlServer.Management.Smo.User')
$newuser.parent = $db
#Set the username and login name.
$newuser.name = "My User"
$newuser.login = "username"
#Set the default Schema, create the user and add the appropriate roles.
$newuser.defaultSchema = "dbo"
$newuser.Create()
$newuser.AddToRole("db_datareader")
$newuser.AddToRole("db_datawriter")Credit goes out to the Allen White, MVP of the MSDN Forums for a PowerShell/SQL quickstart.
About
-
We are IT consultants
who grew up living and loving life
in Cajun Country.
Authors
Categories
What's Your Job Title?
Loading ...
information
wants to be free
